CODEWITHSUNDEEP
hibernateh2
Shorty123
Shorty123

Reputation: 569

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

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.

Upvotes: 1

Views: 1834

Answers (1)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

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.4 and older versions, because H2Dialect in them produces invalid SQL rejected by default by H2 2.x. It was fixed in 5.6.5.

Upvotes: 3

Related Questions