Issue
Recently I have upgraded H2
database in our SpringBoot 2.5.8
project from version 1.4.200
to 2.0.204
. It is used for testing purposes only. For production we use PostgreSQL 12.9
.
It seems that after upgrading some words become keywords in H2
database for example: day
, value
. After invoking integration test Hibernate
fails on DDL
part.
What is the best solution for that case?
- Review all entities and apply back-ticks around reserved column names:
@NotNull
@Column(name = "day", nullable = false)
private LocalDate day;
@NotNull
@Column(name = "`day`", nullable = false)
private LocalDate day;
- Provide dedicated
SpringPhysicalNamingStrategy
and overridetoPhysicalColumnName
method only for integration test purpose. Check list of reserved keywords inH2
database and quote them.
# Datasource related properties
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa
spring.sql.init.mode=always
spring.sql.init.continue-on-error=true
spring.sql.init.platform=h2
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.jdbc.time_zone=UTC
spring.jpa.hibernate.naming.physical-strategy=[project-related-package-name-here].strategy.CustomH2NamingStrategy
spring.jpa.defer-datasource-initialization=true
I think first solution should work both with PostgreSQL
and H2
databases. Although day
identifier is non-reserved in PostgreSQL 12.9
it might be in the future releases.
Second one should sort out the problem with H2
database.
What do you think about that? Maybe there are better solutions for that case? Or maybe take a list of reserved keywords from SQL:2016
standard and apply them to both databases via custom SpringPhysicalNamingStrategy
?
Solution
In H2 2.0 you can use SET NON_KEYWORDS setting by appending ;NON_KEYWORDS=DAY,VALUE
to JDBC URL, but the normal solution is to quote all identifiers in generated SQL unconditionally with spring.jpa.properties.hibernate.globally_quoted_identifiers=true
, for example.
You also should normally have ;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH
in JDBC URL of H2 when you try to use it instead of PostgreSQL. But it's a bad idea to use different DBMS for production and testing and Hibernate ORM doesn't fully support H2 2.0 yet. Edited: Hibernate ORM 5.6.5.Final has basic support of H2 2.*.* and some additional issues were fixed in newer versions.
Answered By - Evgenij Ryazanov
Answer Checked By - Robin (JavaFixing Admin)