[FIXED] db2 query bad grammar, how to run unit test with h2

Issue

Two-part question here with the following db2 query in mind:

select col1!!col2 from table1

1. I had never seen a double exclamation mark (!!) operator. From the results it returns, it appears to be doing a simple concatenation of col1 and col2. I've looked online in the db2 documentation but haven't been able to find a definition. Can someone please verify?
2. I am not allowed to change the query to use a different syntax and I would like to be able to run a unit test with an h2 in memory database for this query and other queries that have some db2 quirks. How can I achieve this with junit4?

Solution

The way I ended up solving this problem is perhaps a bit ugly but it was the best I could do within the limits I had:

• can't use a docker container for a test instance of the actual db2 database
• can't directly modify the queries defined in the source files

In my unit tests I did the following:

1. set up spy beans on the service methods that returned the raw queries
2. created when conditions on the spy beans such that instead of returning a query that I knew wouldn't work (as per the examples, stuff with !!, ^<, etc), the when conditions would return equivalent queries with an h2-compatible syntax (||, >=, etc)
3. queried the h2 database with the modified query

Hopefully this can help someone.

Answered By - geco17
Answer Checked By - Timothy Miller (JavaFixing Admin)