How to set MySQL ANSI_QUOTES mode via MySQL Java Connector?

Question

I have a Scala (Java) Play Web Application where I wrap database identifiers with ANSI double-quotes in queries e.g.

select * 
from "account" 
where "deleted" is null
order by "account_name"

This is necessary because I use among others H2 in-memory and Postgres databases for deploying the application in different scenarios e.g. CI server. Now we need to deploy it also in MySQL and by default wrapping identifiers in double-quotes is not supported. However, following directions from this post mysql double-quoted table names I see we can set this session parameter and then it should work.

How can I set this session parameter while opening the connection through the MySQL Java Connector? My database URL looks like this: jdbc:mysql://odysseus:3306/idxsrs-trading?param=xxx

Answer 1

Mysql session variable in JDBC string

Using the above to link in combination with the link you've posted, I'd try this. jdbc:mysql://localhost:3306/db?sessionVariables=sql_mode=ANSI_QUOTES