Mysql session variable in JDBC string

Question

hi, am using this connection string to connect to mysql from java:

jdbc:mysql://localhost:3306/db?noDatetimeStringSync=true&useUnicode=yes&characterEncoding=UTF-8

is it possible to set the session variable in the string so that SET UNIQUE_CHECKS=0; would be executed upon connecting to server? the obvious

jdbc:mysql://localhost:3306/db?noDatetimeStringSync=true&useUnicode=yes&characterEncoding=UTF-8&unique_checks=0

doesn't seem to work, based on the fact that

'jdbc:mysql://localhost:3306/db?noDatetimeStringSync=true&useUnicode=yes&characterEncoding=UTF-8&unique_checks=blahblah`

doesn't generate any error.

Cheers!

Answer 1

Your question is thus more "How do I concat Strings in Java?" ?

If so, then just use the + operator:

int uniqueChecks = 0; // Assign session variable here.
String url = "jdbc:mysql://localhost:3306/db?unique_checks=" + uniqueChecks;

Alternatively you can also use String#format() wherein you can use the %d pattern to represent a decimal:

int uniqueChecks = 0; // Assign session variable here.
String url = String.format("jdbc:mysql://localhost:3306/db?unique_checks=%d", uniqueChecks);

Answer 2

You can use Statement.execute() to run pretty much every statement the DB understands, including such a SET-statement.

The advantage of using an URL parameter or a dedicated method is that the JDBC-driver is actually aware that the option was set and can react accordingly. This may or may not be useful or necessary for this particular option, but it's vital for other options (for example toggling autocommit with such a statement is a very bad idea).

Answer 3

BalusC, thanks for a reply! actually I need to do that in Talend etl tool(which itself is a java code generator) and the only line i can edit is the "jdbc:mysql://localhost:3306/db?noDatetimeStringSync=true&useUnicode=yes&characterEncoding=UTF-8" string, which gets translated to this java code:

String url_tMysqlBulkExec_1 = "jdbc:mysql://"
+ "localhost" +
":" 
+ "3306" 
+ "/" 
+ "db" 
+ "?" 
+ "noDatetimeStringSync=true&useUnicode=yes&characterEncoding=UTF-8&unique_checks=0";

that's the limitation, sorry for not pointing that out earlier.

Answer 4

According to mysql docs, there is no possibility to set the unique_checks setting, i guess i need to look for other solution than URL parameters (Joachim, thanks for reminding me that these things are called "URL parameters" - help a lot while googling :)

Answer 5

How about using sessionVariables:

 jdbc:mysql://localhost:3306/db?noDatetimeStringSync=true&useUnicode=yes&characterEncoding=UTF-8&sessionVariables=unique_checks=0