Java – how to test SQL scripts is the standard SQL in JUnit testing?

Is there a way to test SQL scripts that include standard SQL for Java / JUnit testing?

At present, we have SQL scripts for creating databases in Postgres database, but when using HSQLDB, everything fails That's why I want to know if there are any java tools to test whether SQL statements are standard SQL

Or is it wise for each database vendor to create a different set of scripts? If so, is there a way to test whether a given script is suitable for Postgres / HSQLDB?

Solution

H2 database supports different modes, which can help you with Postgres testing. I found that our SQL usually contains functions that do not support H2, but you can create your own "stored procedures" and actually call static Java methods to solve this problem If you want to support different database vendors, you should follow vendor specific script routing unless you are doing a real basic query

If you have available resources, I suggest you set up a fully mature UAT environment. You can use it to test the real-time Postgres database, because even seemingly small database configuration differences will affect the query plan in unexpected ways