时间戳

首页 > 解决方案 > 为什么我在使用 hibernate.hbm2ddl.auto 和 H2 时遇到异常，但使用 PostgreSQL 时它可以工作？

java - 为什么我在使用 hibernate.hbm2ddl.auto 和 H2 时遇到异常，但使用 PostgreSQL 时它可以工作？

问题描述

我已经将我的持久性配置从 Postgres 重新制作为 spring 配置文件的多个 DB 配置。我遇到了这个问题。我无法使用 hibernate.hbm2ddl.auto 创建 H2 DB，并且出现此异常。使用 Postgres 配置一切都很好。

我已经尝试过 HSQLDB 作为测试。但它仍然会导致 DDL 异常。

持久化配置：

@Configuration
@ComponentScan("com.beginnercourse.softcomputer")
@PropertySource({"classpath:persistence-postgres.properties"})
@PropertySource({"classpath:persistence-h2.properties"})
//@NoArgsConstructor
//@RequiredArgsConstructor(onConstructor = @__(@Autowired))
@EnableTransactionManagement
public class PersistenceConfig {

    @Autowired
    private Environment environment;

    @Bean
    @Autowired
    public HibernateTransactionManager transactionManager(SessionFactory sessionFactory) {
        HibernateTransactionManager transactionManager = new HibernateTransactionManager();
        transactionManager.setSessionFactory(sessionFactory);
        return transactionManager;
    }

    @Bean
    @Profile("postgres")
    public DataSource postgresDataSource() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(requireNonNull(environment.getProperty("jdbc.postgres.driverClassName")));
        dataSource.setUrl(requireNonNull(environment.getProperty("jdbc.postgres.connection_url")));
        dataSource.setUsername(requireNonNull(environment.getProperty("jdbc.postgres.username")));
        dataSource.setPassword(requireNonNull(environment.getProperty("jdbc.postgres.password")));

        return dataSource;
    }

    @Bean
    @Profile("postgres")
    public LocalSessionFactoryBean postgresSessionFactory() {
        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
        sessionFactory.setDataSource(postgresDataSource());
        sessionFactory.setPackagesToScan(
                new String[]{"com.beginnercourse.softcomputer"});
        sessionFactory.setHibernateProperties(postgresAdditionalProperties());

        return sessionFactory;
    }

    private Properties postgresAdditionalProperties() {
        final Properties hibernateProperties = new Properties();

        hibernateProperties.setProperty("hibernate.hbm2ddl.auto", requireNonNull(environment.getProperty("hibernate.postgres.hbm2ddl.auto")));
        hibernateProperties.setProperty("hibernate.dialect", requireNonNull(environment.getProperty("hibernate.postgres.dialect")));
        hibernateProperties.setProperty("hibernate.show_sql", requireNonNull(environment.getProperty("hibernate.postgres.show_sql")));
        hibernateProperties.setProperty("hibernate.default_schema", requireNonNull(environment.getProperty("hibernate.postgres.default_schema")));
//        hibernateProperties.setProperty("hibernate.cache.use_second_level_cache", requireNonNull(environment.getProperty("hibernate.cache.use_second_level_cache")));
//        hibernateProperties.setProperty("hibernate.cache.use_query_cache", requireNonNull(environment.getProperty("hibernate.cache.use_query_cache")));

        return hibernateProperties;
    }

    @Bean
    @Profile("oracle")
    public DataSource oracleDataSource() throws NamingException {
        return (DataSource) new JndiTemplate().lookup(requireNonNull(environment.getProperty("jdbc.url")));
    }


    @Bean
    @Profile("test")
    public LocalSessionFactoryBean testSessionFactory(DataSource dataSource ) {
        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
//        postgresSessionFactory.setDataSource(postgresDataSource());
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setPackagesToScan(
                new String[]{"com.beginnercourse.softcomputer"});
        sessionFactory.setHibernateProperties(testAdditionalProperties());

        return sessionFactory;
    }

    @Bean
    @Profile("test")
    public DataSource h2DataSource() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(requireNonNull(environment.getProperty("jdbc.h2.driverClassName")));
        dataSource.setUrl(requireNonNull(environment.getProperty("jdbc.h2.connection_url")));
        dataSource.setUsername(requireNonNull(environment.getProperty("jdbc.h2.username")));
        dataSource.setPassword(requireNonNull(environment.getProperty("jdbc.h2.password")));
        return dataSource;
    }

    private Properties testAdditionalProperties() {
        final Properties hibernateProperties = new Properties();

        hibernateProperties.setProperty("hibernate.hbm2ddl.auto", requireNonNull(environment.getProperty("hibernate.h2.hbm2ddl.auto")));
        hibernateProperties.setProperty("hibernate.dialect", requireNonNull(environment.getProperty("hibernate.h2.dialect")));
        hibernateProperties.setProperty("hibernate.show_sql", requireNonNull(environment.getProperty("hibernate.h2.show_sql")));

        return hibernateProperties;
    }
}

持久性-h2.properties

jdbc.h2.driverClassName=org.h2.Driver
jdbc.h2.connection_url=jdbc:h2:mem:e-commerce
jdbc.h2.username=sa
jdbc.h2.password=sa

hibernate.h2.dialect=org.hibernate.dialect.H2Dialect
hibernate.h2.show_sql=false
hibernate.h2.hbm2ddl.auto=update

Stacktrace（我不得不缩短它）：

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table order (id bigint generated by default as identity, placing_date date, status integer, customer_id bigint, primary key (id))" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE ORDER[*] (ID BIGINT GENERATED BY DEFAULT AS IDENTITY, PLACING_DATE DATE, STATUS INTEGER, CUSTOMER_ID BIGINT, PRIMARY KEY (ID)) "; expected "identifier"; SQL statement:
create table order (id bigint generated by default as identity, placing_date date, status integer, customer_id bigint, primary key (id)) [42001-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:451) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.199.jar:1.4.199]
    .
    .
    .
    .
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "ALTER TABLE ORDER_ITEM ADD CONSTRAINT FKT6WV8M7ESHKSP5KP8W4B2D1DM FOREIGN KEY (ORDER_ID) REFERENCES ORDER[*] "; expected "identifier"; SQL statement:
alter table order_item add constraint FKt6wv8m7eshksp5kp8w4b2d1dm foreign key (order_id) references order [42001-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:451) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.getSyntaxError(DbException.java:243) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.readColumnIdentifier(Parser.java:4530) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:4491) ~[h2-1.4.199.jar:1.4.199]

2019-07-10 14:05:30 WARN  ExceptionHandlerLoggedImpl:27 - GenerationTarget encountered exception accepting command : Error executing DDL "alter table technologist add constraint FKjw43e4g7i69l0a2hb4mr0q10 foreign key (order_item_id) references order" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table technologist add constraint FKjw43e4g7i69l0a2hb4mr0q10 foreign key (order_item_id) references order" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applyForeignKeys(AbstractSchemaMigrator.java:433) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:249) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "ALTER TABLE TECHNOLOGIST ADD CONSTRAINT FKJW43E4G7I69L0A2HB4MR0Q10 FOREIGN KEY (ORDER_ITEM_ID) REFERENCES ORDER[*] "; expected "identifier"; SQL statement:
alter table technologist add constraint FKjw43e4g7i69l0a2hb4mr0q10 foreign key (order_item_id) references order [42001-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:451) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.message.DbException.getSyntaxError(DbException.java:243) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.readColumnIdentifier(Parser.java:4530) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:4491) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.parseReferences(Parser.java:7749) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.parseAlterTableAddConstraintIf(Parser.java:7723) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.parseAlterTable(Parser.java:7204) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.parseAlter(Parser.java:6546) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.parsePrepared(Parser.java:828) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.parse(Parser.java:788) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.parse(Parser.java:760) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.command.Parser.prepareCommand(Parser.java:683) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Session.prepareLocal(Session.java:627) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.engine.Session.prepareCommand(Session.java:565) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1292) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217) ~[h2-1.4.199.jar:1.4.199]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205) ~[h2-1.4.199.jar:1.4.199]
    at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175) ~[commons-dbcp2-2.6.0.jar:2.6.0]
    at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175) ~[commons-dbcp2-2.6.0.jar:2.6.0]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.4.3.Final.jar:5.4.3.Final]
    ... 102 more

有没有其他人遇到过类似的事情？

标签： javasqlhibernateh2

解决方案

这很可能是因为您的表名“ORDER”是保留的 SQL 名称。我建议避免这种情况。但是，如果您仍然需要它，请查看https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html#boot-features-creating-and-dropping -jpa-databases，特别是globally_quoted_identifiers属性。

推荐阅读