With Spring Data JPA, I could use H2 for unit tests and postgres(or any other relational DB) for prod. Here, I need not worry about compatibility issues with queries as its abstracted by JPA.
Can I use the same combination of DBs with Spring Data JDBC without much worry about compatibility issues? If no, what is the recommended approach for unit testing?
Please note, I have explored the option of Testcontainers. It seems to be complex and time-consuming option, especially on MS Windows based dev environments.
I recommend using Testcontainers and the exact same database you use in production for Spring Data JDBC and Spring Data JPA.
In my experience Testcontainers is easy to set up and fast to use, and you get tests that run against your actual database. For Spring Data JDBC this is essential since if you provide a query, that query is database dependent.
But even for JPA there are differences in the details that make it very valuable to use the actual database you use in production.