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

    }

}