JDBC Batch insert update MySQL Oracle

Filed Under: Database

Today we will look into JDBC Batch insert and update examples in MySQL and Oracle databases. Sometimes we need to run bulk queries of a similar kind for a database. For example, loading data from CSV files to relational database tables.

As we know that we have option to use Statement or PreparedStatement to execute queries. Apart from that JDBC provides Batch Processing feature through which we can execute the bulk of queries in one go for a database.

JDBC Batch

JDBC batch statements are processed through Statement and PreparedStatement addBatch() and executeBatch() methods. This tutorial is aimed to provide details about JDBC Batch insert example for MySQL and Oracle database.

We will look into different programs so we have a project with the structure as below image.

JDBC Batch, JDBC Batch insert, mysql batch insert, jdbc batch update

Notice that I have MySQL and Oracle DB JDBC Driver jars in the project build path so that we can run our application across MySQL and Oracle DB both.

Let’s first create a simple table for our test programs. We will run the bulk of JDBC insert queries and look at the performance with different approaches.


--Oracle DB
CREATE TABLE Employee (
  empId NUMBER NOT NULL,
  name varchar2(10) DEFAULT NULL,
  PRIMARY KEY (empId)
);

--MySQL DB
CREATE TABLE `Employee` (
  `empId` int(10) unsigned NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We will read the Database configuration details from the property file so that switching from one database to another is quick and easy.

db.properties


#mysql DB properties
DB_DRIVER_CLASS=com.mysql.jdbc.Driver
DB_URL=jdbc:mysql://localhost:3306/UserDB
#DB_URL=jdbc:mysql://localhost:3306/UserDB?rewriteBatchedStatements=true
DB_USERNAME=pankaj
DB_PASSWORD=pankaj123

#Oracle DB Properties
#DB_DRIVER_CLASS=oracle.jdbc.driver.OracleDriver
#DB_URL=jdbc:oracle:thin:@localhost:1871:UserDB
#DB_USERNAME=scott
#DB_PASSWORD=tiger

Before we move into actual JDBC batch insert example to insert bulk data into the Employee table, let’s write a simple utility class to get the database connection.

DBConnection.java


package com.journaldev.jdbc.batch;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBConnection {

	public static Connection getConnection() {
		Properties props = new Properties();
		FileInputStream fis = null;
		Connection con = null;
		try {
			fis = new FileInputStream("db.properties");
			props.load(fis);

			// load the Driver Class
			Class.forName(props.getProperty("DB_DRIVER_CLASS"));

			// create the connection now
			con = DriverManager.getConnection(props.getProperty("DB_URL"),
					props.getProperty("DB_USERNAME"),
					props.getProperty("DB_PASSWORD"));
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
}

Now let’s look at the different approach we can take for JDBC batch insert example.

  1. Use Statement to execute one query at a time.

    JDBCStatement.java

    
    package com.journaldev.jdbc.batch;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class JDBCStatement {
    
    	public static void main(String[] args) {
    		
    		Connection con = null;
    		Statement stmt = null;
    		
    		try {
    			con = DBConnection.getConnection();
    			stmt = con.createStatement();
    			
    			long start = System.currentTimeMillis();
    			for(int i =0; i<10000;i++){
    				String query = "insert into Employee values ("+i+",'Name"+i+"')";
    				stmt.execute(query);
    			}
    			System.out.println("Time Taken="+(System.currentTimeMillis()-start));
    			
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			try {
    				stmt.close();
    				con.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    
    }
    
  2. Use PreparedStatement to execute one query at a time.

    JDBCPreparedStatement.java

    
    package com.journaldev.jdbc.batch;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class JDBCPreparedStatement {
    
    	public static void main(String[] args) {
    		
    		Connection con = null;
    		PreparedStatement ps = null;
    		String query = "insert into Employee (empId, name) values (?,?)";
    		try {
    			con = DBConnection.getConnection();
    			ps = con.prepareStatement(query);
    			
    			long start = System.currentTimeMillis();
    			for(int i =0; i<10000;i++){
    				ps.setInt(1, i);
    				ps.setString(2, "Name"+i);
    				ps.executeUpdate();
    			}
    			System.out.println("Time Taken="+(System.currentTimeMillis()-start));
    			
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			try {
    				ps.close();
    				con.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    
    }
    

    This approach is similar to using Statement but PreparedStatement provides performance benefits and avoids SQL injection attacks.

  3. Using Statement Batch API for bulk processing.

    JDBCStatementBatch.java

    
    package com.journaldev.jdbc.batch;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class JDBCStatementBatch {
    
    	public static void main(String[] args) {
    		
    		Connection con = null;
    		Statement stmt = null;
    		
    		try {
    			con = DBConnection.getConnection();
    			stmt = con.createStatement();
    			
    			long start = System.currentTimeMillis();
    			for(int i =0; i<10000;i++){
    				String query = "insert into Employee values ("+i+",'Name"+i+"')";
    				stmt.addBatch(query);
    				
    				//execute and commit batch of 1000 queries
    				if(i%1000 ==0) stmt.executeBatch();
    			}
    			//commit remaining queries in the batch
    			stmt.executeBatch();
    			
    			System.out.println("Time Taken="+(System.currentTimeMillis()-start));
    			
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			try {
    				stmt.close();
    				con.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    
    }
    

    We are processing 10,000 records with a batch size of 1000 records. Once the batch size reaches, we are executing it and continue processing remaining queries.

  4. Using PreparedStatement Batch Processing API for bulk queries.

    JDBCPreparedStatementBatch.java

    
    package com.journaldev.jdbc.batch;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class JDBCPreparedStatementBatch {
    
    	public static void main(String[] args) {
    		
    		Connection con = null;
    		PreparedStatement ps = null;
    		String query = "insert into Employee (empId, name) values (?,?)";
    		try {
    			con = DBConnection.getConnection();
    			ps = con.prepareStatement(query);
    			
    			long start = System.currentTimeMillis();
    			for(int i =0; i<10000;i++){
    				ps.setInt(1, i);
    				ps.setString(2, "Name"+i);
    				
    				ps.addBatch();
    				
    				if(i%1000 == 0) ps.executeBatch();
    			}
    			ps.executeBatch();
    			
    			System.out.println("Time Taken="+(System.currentTimeMillis()-start));
    			
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			try {
    				ps.close();
    				con.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    
    }
    

Let’s see how our programs work with MySQL database, I have executed them separately multiple times and below table contains the results.

MySQL DB Statement PreparedStatement Statement Batch PreparedStatement Batch
Time Taken (ms) 8256 8130 7129 7019

When I looked at the response time, I was not sure whether it’s right because I was expecting some good response time improvements with Batch Processing. So I looked online for some explanation and found out that by default MySQL batch processing works in a similar way like running without batch.

To get the actual benefits of Batch Processing in MySQL, we need to pass rewriteBatchedStatements as TRUE while creating the DB connection. Look at the MySQL URL above in db.properties file for this.

With rewriteBatchedStatements as true, below table provides the response time for the same programs.

MySQL DB Statement PreparedStatement Statement Batch PreparedStatement Batch
Time Taken (ms) 5676 5570 3716 394

As you can see that PreparedStatement Batch Processing is very fast when rewriteBatchedStatements is true. So if you have a lot of batch processing involved, you should use this feature for faster processing.

Oracle Batch Insert

When I executed above programs for Oracle database, the results were in line with MySQL processing results and PreparedStatement Batch processing was much faster than any other approach.

JDBC Batch Processing Exceptions

Let’s see how batch programs behave in case one of the queries throw exceptions.

JDBCBatchExceptions.java


package com.journaldev.jdbc.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

public class JDBCBatchExceptions {

	public static void main(String[] args) {
		
		Connection con = null;
		PreparedStatement ps = null;
		String query = "insert into Employee (empId, name) values (?,?)";
		try {
			con = DBConnection.getConnection();
			
			ps = con.prepareStatement(query);
			
			String name1 = "Pankaj";
			String name2="Pankaj Kumar"; //longer than column length
			String name3="Kumar";
			
			ps.setInt(1, 1);
			ps.setString(2, name1);
			ps.addBatch();
			
			ps.setInt(1, 2);
			ps.setString(2, name2);
			ps.addBatch();
			
			ps.setInt(1, 3);
			ps.setString(2, name3);
			ps.addBatch();
			
			int[] results = ps.executeBatch();
			
			System.out.println(Arrays.toString(results));
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

When I executed the above program for MySQL database, I got below exception and none of the records were inserted in the table.


com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'name' at row 2
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2939)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440)
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1008)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:908)
	at com.journaldev.jdbc.batch.JDBCBatchExceptions.main(JDBCBatchExceptions.java:37)

When executed the same program for Oracle database, I got below exception.


java.sql.BatchUpdateException: ORA-12899: value too large for column "SCOTT"."EMPLOYEE"."NAME" (actual: 12, maximum: 10)

	at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10070)
	at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:213)
	at com.journaldev.jdbc.batch.JDBCBatchExceptions.main(JDBCBatchExceptions.java:38)

But the rows before exception were inserted into the database successfully. Although the exception clearly says what the error is but it doesn’t tell us which query is causing the issue. So either we validate the data before adding them for batch processing or we should use JDBC Transaction Management to make sure all or none of the records are getting inserted in case of exceptions.

Same program with JDBC transaction management looks like below.

JDBCBatchExceptions.java


package com.journaldev.jdbc.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

public class JDBCBatchExceptions {

	public static void main(String[] args) {
		
		Connection con = null;
		PreparedStatement ps = null;
		String query = "insert into Employee (empId, name) values (?,?)";
		try {
			con = DBConnection.getConnection();
			con.setAutoCommit(false);
			
			ps = con.prepareStatement(query);
			
			String name1 = "Pankaj";
			String name2="Pankaj Kumar"; //longer than column length
			String name3="Kumar";
			
			ps.setInt(1, 1);
			ps.setString(2, name1);
			ps.addBatch();
			
			ps.setInt(1, 2);
			ps.setString(2, name2);
			ps.addBatch();
			
			ps.setInt(1, 3);
			ps.setString(2, name3);
			ps.addBatch();
			
			int[] results = ps.executeBatch();
			
			con.commit();
			System.out.println(Arrays.toString(results));
			
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

As you can see that I am rolling back the transaction if any SQL exception comes. If the batch processing is successful, I am explicitly committing the transaction.

Summary

That’s all for JDBC Batch insert update example, make sure to experiment with your data to get the optimal value of batch size for bulk queries. One of the limitations of JDBC batch processing is that we can’t execute different type of queries in the batch.

You can download JDBC example projects from our GitHub Repository.

Comments

  1. Surbhi Gulati says:

    Hi Pankaj,

    How can I identify which record has got failed out of 100 records in PreparedStatement batch approach?

    Thanks,
    Surbhi

  2. learner001 says:

    I still dont get the point of batch processing. Why it is so? what is the point of using batch processing?

  3. Mohd fazil says:

    Thanks for excellent blog and especially informing about the beauty and power of rewriteBatchedStatements=TRUE statement

  4. latha says:

    if we have 100 inserts added to batch and run execute batch it will create 100 Connections?

    1. Pankaj says:

      Obviously not, it will use the same connection to execute the batch of queries.

  5. Shiva says:

    good one. Thanks for putting together great examples. Good Job!

  6. menberbrains says:

    Very good article and very articulate. Thanks very much Pankaj

  7. vu says:

    Dear Pankaj,
    I am 74 years old. After a long searching in many Web sites, suddenly your web site appears before my eyes with the article that you wrote and that I need to my study (the study is for my personal satisfaction), an article rich in details and clearly explicated if compared with many others Web sites which I have read before. Thank a lot.
    Vu in France.

  8. Mastanvali says:

    will this statement “rewriteBatchedStatements=true” work for Sybase database?
    I could see batch statements are not working properly in Sybase database , Is sybase database is familiar with batch statements?

  9. sushil says:

    you are quite brilliant,thanks for posting such valuable things, your all interview question are fabulous
    like Struts 2,hibernate,spring… can you please post some cheat sheet of core spring and mvc.

    At last Good work Hats Off

  10. Deepti says:

    Thanks for deep and clear cut explanation……….. cleared the concept ………

  11. Binh Thanh Nguyen says:

    Thanks, nice post

  12. satish says:

    Good Job.. I Have a doubt that How to insert three Different table insertion in the same Batch Process to Execute it.

  13. Ajijul says:

    Nice suggestion. Great job.

  14. Arpan says:

    Hi Pankaj, thanks for the detailed code examples. One quick question, I want to process 1 million records from an XML/Flat file in a batch of lets say 10000 each. And I want to log the not processed (inserted/modified) records i.e. which are failed. But I don’t want to roll back, my batch program should continue to run and finish the XML/Flat file processing. So my requirement is to process all records and at the same time log the failed ones to report later.

    Thanks
    Arpan Ray

  15. Bryan says:

    What about a comparison with INSERT INTO table (a, b) VALUES (‘val’, ‘val2’), (‘val’, ‘val2’), (‘val’, ‘val2’);
    I’ve read that a single insert with multiple value sets are 10x faster than a batch. Have you tried or had any luck with that?

  16. ujwwala says:

    I have few queries as how I should set batch size like sometimes we get 1 recors or 100 records ,Please explain

    1. Pankaj says:

      You can check the number of records and write logic for the batch size. Usually batch programming helps in bulk data processing so for 100 records it wont have much benefit though.

  17. Siddu says:

    Can you please post article on DataSource class..
    How to implement connection pooling in core java applications.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages