本文的目的是介绍SpringBatch-无法在Postgres上创建元数据表并将实际数据加载到mysql的详细情况，我们将通过专业的研究、有关数据的分析等多种方式，同时也不会遗漏关于PostgreSQ

本文的目的是介绍Spring Batch-无法在Postgres上创建元数据表并将实际数据加载到mysql的详细情况，我们将通过专业的研究、有关数据的分析等多种方式，同时也不会遗漏关于PostgreSQL + Hibernate + Spring自动创建数据库、PostgreSQL 11.8版是否支持导入在PostgreSQL 8.4.20版中创建的数据库？、postgresql – Createdb不会在postgres中创建数据库：没有错误消息、postgresql – Knex在Postgres中创建POINT数据类型的知识。

• Spring Batch-无法在Postgres上创建元数据表并将实际数据加载到mysql
• PostgreSQL + Hibernate + Spring自动创建数据库
• PostgreSQL 11.8版是否支持导入在PostgreSQL 8.4.20版中创建的数据库？
• postgresql – Createdb不会在postgres中创建数据库：没有错误消息
• postgresql – Knex在Postgres中创建POINT数据类型

Spring Batch-无法在Postgres上创建元数据表并将实际数据加载到mysql

我正在发展Spring Boot v2.2.6.RELEASE + SpringBatch。在此示例中，我希望读取csv文件，将数据加载到mysql中，并将spring批处理元数据表加载到Postgres数据库中。

但这给了我错误：

java.lang.IllegalStateException: Failed to execute CommandLineRunner    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:787) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:768) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]    at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]    at com.example.SpringBatchMetadataInDiffSchemaApplication.main(SpringBatchMetadataInDiffSchemaApplication.java:27) [classes/:na]Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "batch_job_instance" does not exist  Position: 39    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:768) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstance(JdbcJobInstanceDao.java:151) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]    at org.springframework.batch.core.repository.support.SimpleJobRepository.getLastJobExecution(SimpleJobRepository.java:271) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366) ~[spring-tx-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99) ~[spring-tx-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at com.sun.proxy.$Proxy61.getLastJobExecution(Unknown Source) ~[na:na]    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:104) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) ~[spring-batch-core-4.2.1.RELEASE.jar:4.2.1.RELEASE]    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at com.sun.proxy.$Proxy58.run(Unknown Source) ~[na:na]    at com.example.SpringBatchMetadataInDiffSchemaApplication.run(SpringBatchMetadataInDiffSchemaApplication.java:36) [classes/:na]    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:784) [spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE]    ... 5 common frames omittedCaused by: org.postgresql.util.PSQLException: ERROR: relation "batch_job_instance" does not exist  Position: 39    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497) ~[postgresql-42.2.8.jar:42.2.8]    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233) ~[postgresql-42.2.8.jar:42.2.8]    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310) ~[postgresql-42.2.8.jar:42.2.8]    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446) ~[postgresql-42.2.8.jar:42.2.8]    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370) ~[postgresql-42.2.8.jar:42.2.8]    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149) ~[postgresql-42.2.8.jar:42.2.8]    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108) ~[postgresql-42.2.8.jar:42.2.8]    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.1.jar:na]    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.1.jar:na]    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:678) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]    ... 37 common frames omitted

JobConfig.java

@Configurationpublic class JobConfig {    @Autowired    private StepBuilderFactory stepBuilderFactory;    @Autowired    private JobBuilderFactory jobBuilderFactory;    @Qualifier("secondaryDS")    @Autowired    private DataSource dataSource;    @Bean    public FlatFileItemReader<Customer> customerItemReader(){        FlatFileItemReader<Customer> reader = new FlatFileItemReader<>();        reader.setLinesToSkip(1);        reader.setResource(new ClassPathResource("/data/customer.csv"));        DelimitedLineTokenizer tokenizer = new DelimitedLineTokenizer();        tokenizer.setNames(new String[] {"id", "firstName", "lastName", "birthdate"});        DefaultLineMapper<Customer> customerLineMapper = new DefaultLineMapper<>();        customerLineMapper.setLineTokenizer(tokenizer);        customerLineMapper.setFieldSetMapper(new CustomerFieldSetMapper());        customerLineMapper.afterPropertiesSet();        reader.setLineMapper(customerLineMapper);        return reader;    }    @Bean    public JdbcBatchItemWriter<Customer> customerItemWriter(){        JdbcBatchItemWriter<Customer> writer = new JdbcBatchItemWriter<>();        writer.setDataSource(this.dataSource);        writer.setSql("INSERT INTO CUSTOMER VALUES (:id, :firstName, :lastName, :birthdate)");        writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>());        writer.afterPropertiesSet();        return writer;    }    @Bean    public Step step1() {        return stepBuilderFactory.get("step1")                .<Customer, Customer> chunk(1000)                .reader(customerItemReader())                .writer(customerItemWriter())                .build();    }    @Bean    public Job job() {        return jobBuilderFactory.get("job")                .start(step1())                .build();    }   }

数据库配置文件

@Configurationpublic class DatabaseConfig {    @Autowired    private Environment env;    // For Test schema    @Bean(name="secondaryDS")    @ConfigurationProperties(prefix="spring.datasource")    public DataSource getSeconadaryBatchDataSource(){        return DataSourceBuilder.create()                .url(env.getProperty("spring.datasource.url"))                .driverClassName(env.getProperty("spring.datasource.driver-class-name"))                .username(env.getProperty("spring.datasource.username"))                .password(env.getProperty("spring.datasource.password"))                .build();    }    // For "batchmetadata" tables    @Bean(name="primaryDS")    @Primary    @ConfigurationProperties(prefix="spring.hello.datasource")    public DataSource getPrimaryBatchDataSource(){        return DataSourceBuilder.create()                .url(env.getProperty("spring.hello.datasource.url"))                .driverClassName(env.getProperty("spring.hello.datasource.driver-class-name"))                .username(env.getProperty("spring.hello.datasource.username"))                .password(env.getProperty("spring.hello.datasource.password"))                .build();    }    //Ref: https://stackoverflow.com/questions/28275448/multiple-data-source-and-schema-creation-in-spring-boot    @Bean(name = "primaryEntityManagerFactory")    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory( EntityManagerFactoryBuilder builder) {        Map<String, Object> properties = new HashMap<>();        properties.put("hibernate.hbm2ddl.auto", "update");        return builder                .dataSource(getSeconadaryBatchDataSource())                .packages("com.example.model")                .persistenceUnit("default")                .properties(properties)                .build();    }    /*@Bean(name = "secondaryEntityManagerFactory")    @Primary    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(            EntityManagerFactoryBuilder builder) {        Map<String, Object> properties = new HashMap<String, Object>();        properties.put("hibernate.hbm2ddl.auto", "create");        return builder                .dataSource(getPrimaryBatchDataSource())                .packages("com.example.model")                .persistenceUnit("default")                .properties(properties)                .build();    }*/}

application.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.url=jdbc:mysql://localhost:3306/testspring.datasource.username=rootspring.datasource.password=rootspring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialectspring.jpa.hibernate.ddl-auto=createspring.jpa.show-sql=truespring.jpa.properties.hibernate.format_sql=true# Postgresspring.hello.datasource.driver-class-name=org.postgresql.Driverspring.hello.datasource.url=jdbc:postgresql://localhost:5432/program?currentSchema=BATCHspring.hello.datasource.username=postgresspring.hello.datasource.password=admin#spring.batch.table-prefix=batchmetadata.BATCH_spring.batch.initialize-schema=alwaysspring.batch.job.enabled=falsespring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImplspring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl

答案1

也许您正在使用诸如“
BATCH_JOB_INSTANCE”之类的引号创建表。还要检查是否指定了架构，datasource.url因为您没有在查询中使用架构名称。

PostgreSQL + Hibernate + Spring自动创建数据库

我正在使用PostgreSQL和Spring 4，希望我的应用在运行时自动创建数据库。

我的实体类是：

@Entity@Table(name = "user", schema = "public")public class User extends BaseEntity {    private Integer id;    private String name;    private Integer contractId;    public User() {    }    public User(Integer id) {        super(id);    }    @Id    @Column(name = "usr_id", nullable = false)    @GeneratedValue(strategy= GenerationType.IDENTITY)    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    @Basic    @Column(name = "usr_name", nullable = true, length = -1)    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    @Basic    @Column(name = "usr_contract_id", nullable = true)    public Integer getContractId() {        return contractId;    }    public void setContractId(Integer contractId) {        this.contractId = contractId;    }}

HibernateConfig.java

@Configuration@EnableTransactionManagement(proxyTargetClass = true)@PropertySources({    @PropertySource(value = "classpath:application.properties")})@ConfigurationProperties(prefix = "spring.datasource")public class HibernateConfig {    @Autowired    private Environment environment;    @Autowired    private DataSource dataSource;    @Autowired    private MultiTenantConnectionProvider multiTenantConnectionProvider;    @Autowired    private CurrentTenantIdentifierResolver currentTenantIdentifierResolver;    public HibernateConfig() {}    @Bean    public LocalSessionFactoryBean sessionFactory() throws Exception {        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();        sessionFactory.setDataSource(dataSource);        sessionFactory.setHibernateProperties(hibernateProperties());        sessionFactory.setPackagesToScan(new String[] {            "com.xxx.xxx.model",        });        return sessionFactory;    }    private Properties hibernateProperties() {        Properties properties = new Properties();        properties.put(DIALECT, environment.getRequiredProperty(DIALECT));        properties.put(SHOW_SQL, environment.getRequiredProperty(SHOW_SQL));        properties.put(FORMAT_SQL, environment.getRequiredProperty(FORMAT_SQL));        properties.put(HBM2DDL_AUTO, environment.getRequiredProperty(HBM2DDL_AUTO));        return properties;    }    @Bean    @Primary    @Autowired    public HibernateTransactionManager transactionManager(SessionFactory s) {        HibernateTransactionManager txManager = new HibernateTransactionManager();        txManager.setSessionFactory(s);        return txManager;    }    @Bean    @Autowired    public HibernateTemplate hibernateTemplate(SessionFactory s) {        HibernateTemplate hibernateTemplate = new HibernateTemplate(s);        return hibernateTemplate;    }}

application.properties

# Database connection settings:jdbc.driverClassName=org.postgresql.Driverjdbc.url=jdbc:postgresql://localhost:5432/databasejdbc.username=postgresjdbc.password=111111hibernate.dialect=org.hibernate.dialect.PostgreSQLDialecthibernate.show_sql=falsehibernate.format_sql=falsehibernate.hbm2ddl.auto=updatespring.datasource.initialSize=50spring.datasource.maxActive=200spring.datasource.maxIdle=200spring.datasource.minIdle=50

但是，当我运行SQL访问表User时，将出现错误：表’User’不存在。

如何使Hibernate自动创建数据库？

答案1

该物业hibernate.hbm2ddl.auto将为您解决问题。创建SessionFactory时，它将自动验证模式DDL或将其导出到数据库。使用create-
drop时，显式关闭SessionFactory时将删除数据库架构。

Hibernate可以接受上述属性的这些选项。

validate：验证架构，不对数据库进行任何更改。

update：更新架构。

create：创建架构，销毁先前的数据。

create-drop：在会话结束时删除架构。

PostgreSQL 11.8版是否支持导入在PostgreSQL 8.4.20版中创建的数据库？

您需要还原您的转储文件，因此您应该将其提供给pg_restore。

尽管pg_restore将尽最大努力正确地阅读和解释8.4转储，但建议的做法是从v11中使用pg_dump转储8.4数据库。

postgresql – Createdb不会在postgres中创建数据库：没有错误消息

abramhandler-# createdb orleansgis
abramhandler-# \list
                                         List of databases
     Name     |    Owner     | Encoding |   Collate   |    Ctype    |       Access privileges       
--------------+--------------+----------+-------------+-------------+-------------------------------
 abramhandler | abramhandler | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres     | abramhandler | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0    | abramhandler | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/abramhandler              +
              |              |          |             |             | abramhandler=CTc/abramhandler
 template1    | abramhandler | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/abramhandler              +
              |              |          |             |             | abramhandler=CTc/abramhandler
(4 rows)

abramhandler-#

postgresql – Knex在Postgres中创建POINT数据类型

exports.up = (knex,Promise) => {
  return Promise.all([
    knex.schema.createTableIfNotExists(''users'',(table) => {
      table.uuid(''id'').primary()
      table.string(''username'',35)
      table.text(''pword'').notNullable()
      table.string(''first_name'',55)
      table.string(''last_name'',55)
      knex.schema.raw(''coordinates POINT DEFAULT POINT (37.3875,-122.0575)'')
      table.timestamp(''date_created'').defaultTo(knex.fn.Now())
    })
  ])
}

exports.down = (knex,Promise) => {
  return Promise.all([
    knex.schema.dropTableIfExists(''users'')
  ])
}

knex.schema.raw(''coordinates POINT DEFAULT POINT (37.3875,-122.0575)'')

knex.raw(''coordinates POINT DEFAULT POINT (37.3875,-122.0575)'')

{ error: coordinates POINT DEFAULT POINT (37.3875,-122.0575) - Syntax error at or near "coordinates"
    at Connection.parseE (/Users/james/plural/backend-development/node_modules/pg/lib/connection.js:554:11)
    at Connection.parseMessage (/Users/james/plural/backend-development/node_modules/pg/lib/connection.js:381:17)
    at Socket.<anonymous> (/Users/james/plural/backend-development/node_modules/pg/lib/connection.js:117:22)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:172:18)
    at Socket.Readable.push (_stream_readable.js:130:10)
    at TCP.onread (net.js:542:20)
  name: ''error'',length: 92,severity: ''ERROR'',code: ''42601'',detail: undefined,hint: undefined,position: ''1'',internalPosition: undefined,internalQuery: undefined,where: undefined,schema: undefined,table: undefined,column: undefined,dataType: undefined,constraint: undefined,file: ''scan.l'',line: ''1081'',routine: ''scanner_yyerror'' }

关于Spring Batch-无法在Postgres上创建元数据表并将实际数据加载到mysql的问题我们已经讲解完毕，感谢您的阅读，如果还想了解更多关于PostgreSQL + Hibernate + Spring自动创建数据库、PostgreSQL 11.8版是否支持导入在PostgreSQL 8.4.20版中创建的数据库？、postgresql – Createdb不会在postgres中创建数据库：没有错误消息、postgresql – Knex在Postgres中创建POINT数据类型等相关内容，可以在本站寻找。