How programatically set rewriteBatchedStatements for mysql jdbc driver?

Question

Here is a way to speed up batch insert performance. Can rewriteBatchedStatements be set programatically, not via url?

Answer 1

If you don't want to do it through the URL, you can use the Properties object with DriverManager:

Properties props = new Properties();
props.setProperty("user", ...);
props.setProperty("password", ...);
props.setProperty("rewriteBatchedStatements", "true");
Connection connection = DriverManager.getConnection(url, props);

If you use a MysqlDataSource or MysqlConnectionPoolDataSource then you need to set the property rewriteBatchedStatements (or call setter setRewriteBatchedStatements(boolean)

To change this at runtime after you have obtained a connection, you should be able to use:

((com.mysql.jdbc.ConnectionProperties) connection).setRewriteBatchedStatements(true);

Note: I have only looked at the MySQL Connector/J sources for this last option, I haven't tested it.

UPDATED

For c3p0 you can use the following:

ComboPooledDataSource cpds = ...
Connection connection = cpds.getConnection();
connection.unwrap(com.mysql.jdbc.ConnectionProperties.class).setRewriteBatchedStatements(true);

c3p0 should be com.mchange:c3p0:0.9.5.2, be carefull with com.mchange - with other groupId this code does not work.

Answer 2

You should be able to do this using Connection.setClientInfo:

Connection c = ...;
c.setClientInfo("rewriteBatchedStatements", "true");