[#CONJ-99] Support for batch statements rewrite

Mysql JDBC Connector has a rewriteBatchedStatements proptery (http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html) that does 2 things:
1. INSERT statements are is rewritten into a long INSERT INTO table VALUES(0, 'name0'), (1, 'name1') … query
2. Try to uses the “multiple queries” feature for things that does not fit (1.).

Would it be possible to implement support for this in MariaDB JDBC connector as well? It could be useful if the distance (ping) between client and server is high.

The performance gain for (1.) is very high. Although to be fair, a workaround is to use LOAD DATA INFILE that has equal or greater performance.

The performance gain for (2.) is medium, because it looks like (at least for me) that the server will reply with one TCP segment per query. At least the transfer of data to the server is more efficient.

JDBCBatchStatements.java
package se.lesc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Before;
import org.junit.Test;

public class JDBCBatchStatements {

private Connection connection;

@Before
public void setUp() throws SQLException {
String url = "jdbc:mysql://127.0.0.1/test?user=root&password=";
//rewriteBatchedStatements is supported by Mysql JDBC
//http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
url += "&rewriteBatchedStatements=true";
connection = DriverManager.getConnection(url);

Statement statement = connection.createStatement();
statement.execute("DROP TABLE IF EXISTS persons");
statement.execute("CREATE TABLE persons (ssn int, name VARCHAR(40))");
}

@Test
public void testInsert() throws SQLException {
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO persons VALUES(?, ?)");

for (int i = 0; i < 1000; i++) {
preparedStatement.setInt(1, i);
preparedStatement.setString(2, "name" + i);
preparedStatement.addBatch();
}
//Mysql JDBC driver will rewrite the SQL into:
//INSERT INTO persons VALUES(0, 'name0'),(1, 'name1'),(2, 'name2'),(3, 'name3') ...
preparedStatement.executeBatch();
}

@Test
public void testUpdate() throws SQLException {
testInsert(); //Populate the table

PreparedStatement preparedStatement = connection.prepareStatement("UPDATE persons SET name = ? WHERE SSN = ?");

for (int i = 0; i < 1000; i++) {
preparedStatement.setString(1, "updated name" + i);
preparedStatement.setInt(2, i);
preparedStatement.addBatch();
}

//Mysql JDBC driver will rewrite the SQL into:
//UPDATE persons SET name = 'updated name0' WHERE SSN = 0;UPDATE persons SET name = 'updated name1' WHERE SSN = 1; ...
preparedStatement.executeBatch();

//Unfortunately there will be one response packet per query:
//Rows matched: 1 Changed: 1 Warnings: 0
//It resulted in 1501 TCP segments (including ACK) for me
}
}

[CONJ-99] Support for batch statements rewrite Created: 2014-05-12 Updated: 2015-10-20 Resolved: 2014-10-21
Status:	Closed
Project:	MariaDB Connector/J
Component/s:	None
Affects Version/s:	1.1.7
Fix Version/s:	1.1.8

[CONJ-99] Support for batch statements rewrite Created: 2014-05-12 Updated: 2015-10-20 Resolved: 2014-10-21