JDBC: Introduction to JDBC (Part III)-Batch Process

1.Batching with Statement Object

1) Batching with Create

package edu.xmu.jdbc.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import edu.xmu.jdbc.bean.Student;

public class StudentDao {
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "****";
    private String password = "****";

    static {
	try {
	    Class.forName("com.mysql.jdbc.Driver");

	} catch (ClassNotFoundException e) {
	    e.printStackTrace();
	}
    }


    public void batchCreateStudent(List<Student> studentList) {
	Connection conn = null;
	Statement statement = null;

	try {
	    conn = DriverManager.getConnection(url, username, password);
	    statement = conn.createStatement();
	    conn.setAutoCommit(false);
	    for (Student student : studentList) {
		String sql = "insert into student(name, gender, age) values ('"
			+ student.getName() + "', '" + student.getGender()
			+ "', " + student.getAge() + ")";

		statement.addBatch(sql);
	    }
	    int[] count = statement.executeBatch();
	    conn.commit();
	    for (int i = 0; i < count.length; i++) {
		System.out.println(count[i]);
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    try {
		statement.close();
	    } catch (SQLException e) {
		e.printStackTrace();
	    } finally {
		try {
		    conn.close();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
	    }
	}
    }

}

2) Batching with Retrieve 

package edu.xmu.jdbc.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import edu.xmu.jdbc.bean.Student;

public class StudentDao {
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "****";
    private String password = "****";

    static {
	try {
	    Class.forName("com.mysql.jdbc.Driver");

	} catch (ClassNotFoundException e) {
	    e.printStackTrace();
	}
    }

    // There is no a mechanism for select batches.
    public List<Student> batchRetrieveStudent(List<String> studentNameList) {
	Connection conn = null;
	Statement statement = null;
	List<Student> studentList = new ArrayList<Student>();
	try {
	    conn = DriverManager.getConnection(url, username, password);
	    statement = conn.createStatement();
	    conn.setAutoCommit(false);
	    String sql = "select name, gender, age from student where name in (";
	    for (String studentName : studentNameList) {
		sql += "'" + studentName + "', ";
	    }
	    sql = sql.substring(0, sql.length() - 2);
	    sql += ")";
	    System.out.println("Executing sql [" + sql + "]");
	    ResultSet resultSet = statement.executeQuery(sql.toString());

	    while (resultSet.next()) {
		String name = resultSet.getString("name");
		String gender = resultSet.getString("gender");
		int age = resultSet.getInt("age");
		Student student = new Student(name, gender, age);
		studentList.add(student);
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    try {
		statement.close();
	    } catch (SQLException e) {
		e.printStackTrace();
	    } finally {
		try {
		    conn.close();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
	    }
	}

	return studentList;
    }

}

Comments: As the statements in the link below, there in no mechanism for batch query. Probably because there is no apparant need for that.

                   As others have recommanded, you can simply retrieve all the rows you want at once by constructing the sql listed above.

3) Batching with Update

package edu.xmu.jdbc.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import edu.xmu.jdbc.bean.Student;

public class StudentDao {
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "****";
    private String password = "****";

    static {
	try {
	    Class.forName("com.mysql.jdbc.Driver");

	} catch (ClassNotFoundException e) {
	    e.printStackTrace();
	}
    }

    public void batchUpdateStudent(List<Student> studentList) {
	Connection conn = null;
	Statement statement = null;

	try {
	    conn = DriverManager.getConnection(url, username, password);
	    statement = conn.createStatement();
	    conn.setAutoCommit(false);
	    for (Student student : studentList) {
		String sql = "update student set gender='"
			+ student.getGender() + "', age=" + student.getAge()
			+ " where name='" + student.getName() + "'";

		statement.addBatch(sql);
	    }
	    int[] count = statement.executeBatch();
	    conn.commit();
	    for (int i = 0; i < count.length; i++) {
		System.out.println(count[i] + " rows affected.");
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    try {
		statement.close();
	    } catch (SQLException e) {
		e.printStackTrace();
	    } finally {
		try {
		    conn.close();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
	    }
	}
    }
}

4) Batching with Delete

package edu.xmu.jdbc.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class StudentDao {
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "****";
    private String password = "****";

    static {
	try {
	    Class.forName("com.mysql.jdbc.Driver");

	} catch (ClassNotFoundException e) {
	    e.printStackTrace();
	}
    }

    public void batchDeleteStudent(List<String> studentNameList) {
	Connection conn = null;
	Statement statement = null;
	try {
	    conn = DriverManager.getConnection(url, username, password);
	    statement = conn.createStatement();
	    conn.setAutoCommit(false);
	    for (String studentName : studentNameList) {
		String sql = "delete from student where name='" + studentName
			+ "'";
		statement.addBatch(sql);
	    }
	    int[] count = statement.executeBatch();
	    conn.commit();

	    for (int i = 0; i < count.length; i++) {
		System.out.println(count[i] + " rows affected.");
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    try {
		statement.close();
	    } catch (SQLException e) {
		e.printStackTrace();
	    } finally {
		try {
		    conn.close();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
	    }
	}
    }
}

 

2. Batching with PreparedStatement Object

1) Batching with Create

    public void batchCreateStudent(List<Student> studentList) {
	Connection conn = null;
	PreparedStatement statement = null;
	String sql = "insert into student(name, gender, age) values(?, ?, ?)";
	try {
	    conn = DriverManager.getConnection(url, username, password);
	    statement = conn.prepareStatement(sql);
	    conn.setAutoCommit(false);
	    for (Student student : studentList) {
		statement.setString(1, student.getName());
		statement.setString(2, student.getGender());
		statement.setInt(3, student.getAge());
		statement.addBatch();
	    }
	    int[] count = statement.executeBatch();
	    conn.commit();
	    for (int i = 0; i < count.length; i++) {
		System.out.println(count[i] + " rows affected.");
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    try {
		statement.close();
	    } catch (SQLException e) {
		e.printStackTrace();
	    } finally {
		try {
		    conn.close();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
	    }
	}
    }

Comments: Attention that we should use "statement.addBatch();" instead of "statement.addBatch(sql);"

2) Batching with Retrieve

    // There is no a mechanism for select batches.
    public List<Student> batchRetrieveStudent(List<String> studentNameList) {
	Connection conn = null;
	PreparedStatement statement = null;
	List<Student> studentList = new ArrayList<Student>();
	try {
	    conn = DriverManager.getConnection(url, username, password);
	    String sql = "select name, gender, age from student where name in (";
	    for (int i = 0; i < studentNameList.size(); i++) {
		sql += "?, ";
	    }
	    sql = sql.substring(0, sql.length() - 2);
	    sql += ")";
	    statement = conn.prepareStatement(sql);
	    conn.setAutoCommit(false);
	    for (int i = 0; i < studentNameList.size(); i++) {
		String studentName = studentNameList.get(i);
		statement.setString(i + 1, studentName);
	    }
	    ResultSet resultSet = statement.executeQuery();

	    while (resultSet.next()) {
		String name = resultSet.getString("name");
		String gender = resultSet.getString("gender");
		int age = resultSet.getInt("age");
		Student student = new Student(name, gender, age);
		studentList.add(student);
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    try {
		statement.close();
	    } catch (SQLException e) {
		e.printStackTrace();
	    } finally {
		try {
		    conn.close();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
	    }
	}

	return studentList;
    }

Comments: Also, attention that we don't have and even don't need the batch select mechanism.

3) Batching with Update

    public List<Student> batchUpdateStudent(List<Student> studentList) {
	Connection conn = null;
	PreparedStatement statement = null;
	try {
	    conn = DriverManager.getConnection(url, username, password);
	    String sql = "update student set gender=?, age=? where name=?";
	    statement = conn.prepareStatement(sql);
	    conn.setAutoCommit(false);
	    for (Student student : studentList) {
		statement.setString(1, student.getGender());
		statement.setInt(2, student.getAge());
		statement.setString(3, student.getName());
		statement.addBatch();
	    }
	    int[] count = statement.executeBatch();
	    conn.commit();

	    for (int i = 0; i < count.length; i++) {
		System.out.println(count[i] + " rows affected.");
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    try {
		statement.close();
	    } catch (SQLException e) {
		e.printStackTrace();
	    } finally {
		try {
		    conn.close();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
	    }
	}

	return studentList;
    }

4) Batching with Delete

    public void batchDeleteStudent(List<String> studentNameList) {
	Connection conn = null;
	PreparedStatement statement = null;
	try {
	    conn = DriverManager.getConnection(url, username, password);
	    String sql = "delete from student where name=?";
	    statement = conn.prepareStatement(sql);
	    conn.setAutoCommit(false);
	    for (String studentName : studentNameList) {
		statement.setString(1, studentName);
		statement.addBatch();
	    }
	    int[] count = statement.executeBatch();
	    conn.commit();

	    for (int i = 0; i < count.length; i++) {
		System.out.println(count[i] + " rows affected.");
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    try {
		statement.close();
	    } catch (SQLException e) {
		e.printStackTrace();
	    } finally {
		try {
		    conn.close();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
	    }
	}
    }

Comments:

1) As we can see, there is a lot of redundancy code of creating statements and handling exceptions.

    Also, we may forget to close resultset, statements and connection.

    Therefore, we need a more efficient framework to avoid these defacts. Here come Sping-JDBC-Framework.

2) Also, we've seen that every time we execute a sql, we just created a new connection which is time consuming.

    Connection-Pool mechanism offers a better way of managing the connection resources.

 

Reference Links:

1. http://stackoverflow.com/questions/9853197/jdbc-batch-query-for-high-performance offers justification why we do not need batch select

2. http://www.tutorialspoint.com/jdbc/statement-batching-example.htm    offers detailed tutorials & example for batch CRD

3. http://www.javaranch.com/journal/200510/Journal200510.jsp#a2 offers several alternative options for batch select.