JDBC Example – Batch Update using PreparedStatement

BatchProcess.java

/* BatchProcess.java */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

public class BatchProcess {

  Connection con;
	Statement stat;
	PreparedStatement ps;

	public void connect() {
		try {
			System.out.println("connect...");
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/BookStore", "root", "password");
			stat = con.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void disconnect() {
		try {
			System.out.println("disconnect...");
			stat.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void displayAllBooks() {
		connect();
		try {
			System.out.println("displayAllBooks...");
			String query = "SELECT * FROM BS_Books";

			ResultSet rset = stat.executeQuery(query);

			while (rset.next())
			{
				System.out.println(rset.getString(1) + " " + rset.getString(3));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		disconnect();
	}
	
	public void insertBatch(List<Map<String,String>> dataList) {
		connect();
		try {
			System.out.println("insert...");
			ps=con.prepareStatement("INSERT INTO BS_Books (cat_id,book_title,book_details,book_price,book_author) values (?,?,?,?,?)");
			
			for(int i=0;i<dataList.size();i++) {
				try {
					ps.setInt(1, Integer.parseInt(dataList.get(i).get("catId")));
					ps.setString(2, dataList.get(i).get("bookTitle"));
					ps.setString(3, dataList.get(i).get("bookDetails"));
					ps.setDouble(4, Double.parseDouble(dataList.get(i).get("bookPrice")));
					ps.setString(5, dataList.get(i).get("bookAuthor"));
					
					//add records to the batch
					ps.addBatch();
				}
				catch(Exception e) {
					e.printStackTrace();
				}
			}
			
			//execute the batch
			ps.executeBatch();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		disconnect();
	}

	public static void main(String[] args) {
		BatchProcess bp = new BatchProcess();
		
		bp.displayAllBooks();
		
		List<Map<String,String>> dataList = new ArrayList<Map<String,String>>();
		
		Map<String,String> temp = new Hashtable<String, String>();
		temp.put("catId", "1");
		temp.put("bookTitle", "Book1");
		temp.put("bookDetails", "Details of book1");
		temp.put("bookPrice", "100.0");
		temp.put("bookAuthor", "Author1");
		
		dataList.add(temp);
		
		temp = new Hashtable<String, String>();
		temp.put("catId", "1");
		temp.put("bookTitle", "Book2");
		temp.put("bookDetails", "Details of book2");
		temp.put("bookPrice", "150.0");
		temp.put("bookAuthor", "Author2");
		
		dataList.add(temp);
		
		temp = new Hashtable<String, String>();
		temp.put("catId", "1");
		temp.put("bookTitle", "Book3");
		temp.put("bookDetails", "Details of book3");
		temp.put("bookPrice", "200.0");
		temp.put("bookAuthor", "Author3");
		
		dataList.add(temp);
		
		bp.insertBatch(dataList);
		
		
		bp.displayAllBooks();
		
	}

}