Testing: Incompatibility between MySQL and H2

user_495312 0
Viewed 677

I'm trying to test my JPA native @Query with H2. My native query is as follows:

  @Query(
      value = "SELECT * FROM accounts " +
              " WHERE 'account_name' LIKE LOWER(CONCAT('%', COALESCE(:searchTerm, ''), '%')) ",
      countQuery = "SELECT count(*) FROM accounts " +
                   " WHERE 'account_name' LIKE LOWER(CONCAT('%', COALESCE(:searchTerm, ''), '%')) ",
      nativeQuery = true
  )

When writing a unit test, I'm getting the following H2 error:

Caused by: org.h2.jdbc.JdbcSQLException: Table "ACCOUNTS" not found; SQL statement: SELECT * FROM accounts WHERE 'aws_account_name' LIKE LOWER(CONCAT('%', COALESCE(?, ''), '%')) limit ? [42102-197]

I can fix the H2 error by changing my SQL syntax to put table name in double-quotes:

  @Query(
      value = "SELECT * FROM \"accounts\" " +
              " WHERE 'account_name' LIKE LOWER(CONCAT('%', COALESCE(:searchTerm, ''), '%')) ",
      countQuery = "SELECT count(*) FROM \"accounts\" " +
                   " WHERE 'account_name' LIKE LOWER(CONCAT('%', COALESCE(:searchTerm, ''), '%')) ",
      nativeQuery = true
  )

HOWEVER, then my MySQL (actual non-test environment) complains:

Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"accounts" WHERE 'account_name' LIKE LOWER(CONCAT('%', COALESCE('ab', ''), ' at line 1

How can I test this native query with MySQL and H2?

The reason I'm using native query instead of JPQL is because I need to search without case sensitivity and allow "contains" matching.

h2 jpa mysql java
1 Answers

By default, MySQL recognizes a token enclosed in double quotes as a string literal, not an identifier (i.e. a table name or column name).

If we modify MySQL sql_mode to include ANSI_QUOTES, then a token enclosed in double quotes will be seen as an identifier. But this is going to cause a problem in SQL where string literals were enclosed in double quotes rather than the SQL standard single quotes.

If H2 compatibility mode is set to MySQL, then we should be able to use the normal MySQL backtick characters to escape identifiers. e.g

 SELECT *
   FROM `accounts`
        ^        ^

Also, MySQL isn't going to object to this construct:

WHERE 'aws_account_name' LIKE
      ^                ^

but enclosed in single quotes, MySQL sees the token 'aws_account_name' here as a string literal, not as a reference to a column.

If that's supposed to be a column name, we can use the MySQL backtick characters around a column reference.

To reduce confusion and make it easier on a future reader, we typically like to qualify column references, even where it isn't strictly required. For example, using a short table alias:

 SELECT t.*
   FROM `accounts` t
  WHERE t.`aws_account_name` LIKE ...
user_1077440
Related