Exceptions after updating from H2 in memory db version 1.4.X to 2.1.x-CodePudding

I use the H2 memory db for integrationtest instead of Oracle for the live system. After i updated to the new major H2 version 2 the tests throw exceptions like these:

Syntax error in SQL statement "select test0_.ID as id1_1_3_, test0_.TEST_ID as test_2_1_3_, test0_.PU_ID as pu8_1_3_, testpar1_.TEST_ID as test_4_2_5_, testpar1_.ID as id1_2_5_, testpar1_.ID as id1_2_0_, testpar1_.TEST_ID as test_4_2_0_, testpar1_.NAME as name2_2_0_, testpar1_.[*]VALUE as value3_2_0_, from testschema.TEST test0_ left outer join testschema.PU pu2_ on test0_.PU_ID=pu2_.ID where test0_.ID=?"; expected "identifier";

Here is a sample entity:

@Entity
@Table(name = "TEST")
@SequenceGenerator(name = "TEST_SEQUENCE_GENERATOR",
                   sequenceName = "TEST_SEQ",
                   allocationSize = 1)
public class Test  {

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "TEST_SEQUENCE_GENERATOR", strategy = GenerationType.SEQUENCE)
    private Long id;

and these are the set properties:

 properties.put("javax.persistence.jdbc.driver", "org.h2.Driver");
        properties.put(
                "javax.persistence.jdbc.url",
                "jdbc:h2:mem:testschema;DB_CLOSE_ON_EXIT=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS TESTSCHEMA");
        properties.put("javax.persistence.jdbc.user", "testschema");
        properties.put("javax.persistence.jdbc.password", "");
        properties.put("hibernate.default_schema", "testschema");
        properties.put("hibernate.show_sql", "false");
        properties.put(
                "hibernate.cache.region.factory_class",
                "org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory");
        properties.put("hibernate.hbm2ddl.auto", "create-drop");
        properties.put("hibernate.order_by.default_null_ordering", "last");
        properties.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");

I looked in the migration guide on the H2 webpage but could not find the failure.

CodePudding user response：

VALUE is a keyword in H2 and is a reserved word in the SQL Standard (even in archaic SQL-92). When it is used as identifier, it needs to be quoted as "VALUE". Hibernate ORM has a hibernate.globally_quoted_identifiers setting, it can be set to true to quote all identifiers, you can enable it.

If you don't want to quote them all for a some reason, you can add ;NON_KEYWORDS=VALUE to JDBC URL of H2, but this setting may not work in all cases.

You also need ;MODE=LEGACY for Hibernate ORM 5.6, because H2Dialect produces invalid SQL rejected by default by H2 2.x, this dialect is fixed in development sources of Hibernate ORM for version 6.0, but it isn't released yet.