1

I work on a legacy Spring Boot application that does not use JPA or Hibernate for any database connections or ORM. It uses the spring-boot-starter-jdbc dependency and a bunch of custom mappers written years ago to map from our Pojos to the database.

We are in the process of a database migration moving from Oracle to Postgres. We currently have a suite of automated cucumber tests that run against an in-memory H2 database. Currently this just uses whatever the default dialect is set to, it has not been update by us. However I have noticed when we connect those tests up to actual Postgres database, that a number of tests fail due to SQL compatibility issues. It is important that our H2 tests are reliable, so I want to update the database setup to ensure we are using the PostgreSQL dialect and then fix the failing tests.

This looks like it would be easy if we were using JPA and/or Hibernate, as it appears to just be a property we can set in our test properties file such as spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect or spring.jpa.database-platform = org.hibernate.dialect.PostgreSQL94Dialect But I have not been able to find an equivalent setting for the JDBC dependency.

In this spring blog it makes reference to this SO post which suggests implementing a custom DialectResolver, but this is for a different dependency spring-boot-starter-data-jdbc. I tried implementing a custom DialectResolver as shown in that link but I am not able to import DialectResolver.JdbcDialectProvider as it does not exist.

I can see in the above linked question that they tried using ?sql_dialect=3 on their connection url, but I'm not sure what this means, as I can't find any documentation on it... I don't know what "3" denotes in this case

We're running Spring Boot using spring-boot-starter-parent and spring-boot-starter-jdbc version 2.6.9 with basic database properties:

spring.jta.atomikos.datasource-xa-data-source-class-name=
spring.jta.atomikos.datasource-xa-properties.URL=
spring.jta.atomikos.datasource-xa-properties-user=
spring.jta.atomikos.datasource-xa-properties-password=

Any help on how to correctly set the dialect would be greatly appreciated

user3275784
  • 437
  • 1
  • 9
  • 18
  • 1
    https://phauer.com/2017/dont-use-in-memory-databases-tests-h2/ –  Nov 30 '22 at 11:21
  • You could use a connection string like `jdbc:h2:mem:test;MODE=PostgreSQL` to tell H2 you're using Postgres specifically, but I don't think it would then reject your current syntax. Seriously, what @a_horse_with_no_name said. You can use TestContainers for the tests, it's [super easy to set up](https://www.testcontainers.org/modules/databases/jdbc/) – crizzis Nov 30 '22 at 14:16
  • Yeah I've looked at putting H2 in that mode, but like you said it doesn't do any syntax validation, so it really doesn't help. I've used TestContainers before and they are handy, but unfortunately we don't have docker installed on the servers the tests run on. I can look at getting it installed but we're a highly regulated industry, and something like that is going to take a while to get approved, so I need a solution that uses our current setup of H2. – user3275784 Nov 30 '22 at 14:22

0 Answers0