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.

• Postgres 12 - Keywords
• H2 - Keywords

What is the best solution for that case?

1. 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;

2. Provide dedicated SpringPhysicalNamingStrategy and override toPhysicalColumnName method only for integration test purpose. Check list of reserved keywords in H2 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?

CodePudding user response：

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.