Batch Insert In Java – JDBC

Let’s see how we can perform batch insert in Java using JDBC APIs. Although you might already knew this, I will try to explain the basic to a bit complex scenarios.

In this note, we will see how we can use JDBC APIs like Statement and PreparedStatement to insert data in any database in batches. Also we will try to explore scenarios where we can run out of memory and how to optimize the batch operation.

So first, the basic API to Insert data in database in batches using Java JDBC.

Simple Batch

I am calling this a simple batch. The requirement is simple. Execute a list of inserts in batch. Instead of hitting database once for each insert statement, we will using JDBC batch operation and optimize the performance.

Consider the following code:

Bad Code

String [] queries = {
	"insert into employee (name, city, phone) values ('A', 'X', '123')",
	"insert into employee (name, city, phone) values ('B', 'Y', '234')",
	"insert into employee (name, city, phone) values ('C', 'Z', '345')",
};
			
Connection connection = new getConnection();
Statement statement = connection.createStatement();
			
for (String query : queries) {
	statement.execute(query);
}
statement.close();
connection.close();

This is the BAD code. You are executing each query separately. This hits the database for each insert statement. Consider if you want to insert 1000 records. This is not a good idea.

We’ll below is the basic code to perform batch insert. Check it out:

Good Code

Connection connection = new getConnection();
Statement statement = connection.createStatement();

for (String query : queries) {
	statement.addBatch(query);
}
statement.executeBatch();
statement.close();
connection.close();

Note how we used addBatch() method of Statement, instead of directly executing the query. And after adding all the queries we executed them in one go using statement.executeBatch() method. Nothing fancy, just a simple batch insert.

Note that we have taken the queries from a String array. Instead you may want to make it dynamically. For example:

import java.sql.Connection;
import java.sql.Statement;

//...

Connection connection = new getConnection();
Statement statement = connection.createStatement();

for (Employee employee: employees) {
	String query = "insert into employee (name, city) values('"
			+ employee.getName() + "','" + employee.getCity + "')";
	statement.addBatch(query);
}
statement.executeBatch();
statement.close();
connection.close();

Note how we are creating query dynamically using data from Employee object and adding it in batch to insert in one go. Perfect! isn’t it?

wait.. You must be thinking what about SQL Injection? Creating queries like this dynamically is very prone to SQL injection. And also the insert query has to be compiled each time.

Why not to use PreparedStatement instead of simple Statement. Yes, that can be the solution. Check out the below SQL Injection Safe Batch.

SQL Injection Safe Batch

Consider the following code:

import java.sql.Connection;
import java.sql.PreparedStatement;

//...

String sql = "insert into employee (name, city, phone) values (?, ?, ?)";
Connection connection = new getConnection();
PreparedStatement ps = connection.prepareStatement(sql);

for (Employee employee: employees) {

	ps.setString(1, employee.getName());
	ps.setString(2, employee.getCity());
	ps.setString(3, employee.getPhone());
	ps.addBatch();
}
ps.executeBatch();
ps.close();
connection.close();			

Checkout the above code. Beautiful. We used java.sql.PreparedStatement and added insert query in the batch. This is the solution you must implement in your batch insert logic, instead of above Statement one.

Still there is one problem with this solution. Consider a scenario where you want to insert half million records into database using batch. Well, that may generate OutOfMemoryError:

 
java.lang.OutOfMemoryError: Java heap space
    com.mysql.jdbc.ServerPreparedStatement$BatchedBindValues.<init>(ServerPreparedStatement.java:72)
    com.mysql.jdbc.ServerPreparedStatement.addBatch(ServerPreparedStatement.java:330)
    org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:171)

This is because you are trying to add everything in one batch and inserting once. Best idea would be to execute batch itself in batch. Check out the below solution.

Smart Insert: Batch within Batch

This is a simplest solution. Consider a batch size like 1000 and insert queries in the batches of 1000 queries at a time.

String sql = "insert into employee (name, city, phone) values (?, ?, ?)";
Connection connection = new getConnection();
PreparedStatement ps = connection.prepareStatement(sql);

final int batchSize = 1000;
int count = 0;

for (Employee employee: employees) {

	ps.setString(1, employee.getName());
	ps.setString(2, employee.getCity());
	ps.setString(3, employee.getPhone());
	ps.addBatch();
	
	if(++count % batchSize == 0) {
		ps.executeBatch();
	}
}
ps.executeBatch(); // insert remaining records
ps.close();
connection.close();

This would be the ideal solution. This avoids SQL Injection and also takes care of out of memory issue. Check how we have incremented a counter count and once it reaches batchSize which is 1000, we call executeBatch().

Hope this helps.



48 Comments

  • zdenek 2 March, 2012, 13:53

    Well this is great.
    But what about managing exception that could happen in some of the inserting records?
    I would obviously want to pass correct record and catch only records with some problem.
    Thanks

  • mkhan523 6 March, 2012, 13:32

    What if one of the columns is to be populated with sequence values??

    For example..
    String [] queries = {
    “insert into employee (id,name, city, phone) values (seq.nextval,’A’, ‘X’, ’123′)”,
    “insert into employee (id,name, city, phone) values (seq.nextval,’B’, ‘Y’, ’234′)”,
    “insert into employee (id,name, city, phone) values (seq.nextval,’C’, ‘Z’, ’345′)”, };
    when i execute the above query using batch.. it is inserting same id value in all the rows. How to solve this problem?? I do not want to use Triggers.

    • Viral Patel 7 March, 2012, 15:17

      Although you execute these queries in batch, Oracle should resolve the sequences properly and should update its values for each row. Let me know if the problem is not resolved. It seems to me some diff issue.

    • Michel 28 June, 2012, 13:13

      Hi, i declare one variable type int and in X, in construtor execute select * MAX(ID) from tableexample.
      X=select * MAX(ID) from tableexample.

      this next ID.

      insert into table (ID, name) values (x, “Michel”);
      this way you do not need to put the autoincrement ID or serial,
      always in the constructor, will a int x = select * MAX (id) +1, when you insert the same id to next item in the database
      insert into table (x, “orange”)
      insert into table (x, “apple”)
      insert into table (x, “pineapple”)

      so I decided to point of sale, I have helped

  • Faisal Memon 26 March, 2012, 22:21

    Superb :)

  • Suresh 30 March, 2012, 12:26

    Awesome Tutorial. Really Great.

  • Herman Barrantes 31 March, 2012, 22:46

    It has a problem, only works for the same DML, I can not to do an Update and an Insert at the same time

  • Andy 5 April, 2012, 6:30

    Excellent tutorial. Concise and explained very well

  • Mallesh 23 April, 2012, 20:00

    Really this good… for learners

  • Dhiraj 5 May, 2012, 6:12

    Very concise and to the point. Thanks for this link.

    • Viral Patel 8 May, 2012, 0:28

      Thanks Dhiraj :)

      • pankaj 25 November, 2013, 1:38

        how to create this kind of website plz…. suggest me..:)

  • VINAYAK 12 June, 2012, 17:54

    very interesting and to the point i will get all idea how to work out
    in batch insert

  • Dhens Galaxian 27 June, 2012, 0:19

    good idea… thanx

  • Gaurav 27 June, 2012, 13:53

    Nice atricle. I learned Batch insert today.
    one problem is there in one code snippet although..so just mentioning it.
    Problem exists where you wrote :

    if(++count % batchSize == 0) {
            ps.executeBatch();
    }
    

    If we have 5400 records abd Batchsize is 1000 then…above code will ignore last 400 record. Please correct me if i am wrong…

    • Gaurav 27 June, 2012, 14:00

      Something of this type looks to solve this prob here –

      if(++count % NO_OF_ELEMENT_TO_PROCESS == 0 || count==employee.size()) {
      //Execute Batch
      }
      
    • JDBC tips 19 December, 2012, 13:10

      Hi Gaurav, you should not worry as at the end of 5400, outer loop will complete and executeBatch after loop will do the remaining .

  • Michel 28 June, 2012, 12:54

    Hi, this code very good, i lije i am utililis is code. Congratulatios.
    I like send me a code complete, class employe, class connection.
    please, send in email -> maccba_mt@msn.com

  • Bunty 3 July, 2012, 22:21

    Hi,

    How to handle the scenario like what if a row failed to insert ? Suppose out of 1000 rows to be inserted, the 100th row failed to insert and the program ends abruptly without inserting the remaining rows(i.e. from 100th row to 1000th row). How to handle this kind of scenario such that even if the 100th row fails to be inserted, the process should carry on for the remaining rows(i.e. from 101th row to 1000th row) ?

    • Mandy 16 July, 2013, 11:28

      I am also looking for the same .. if something fails , how to ignore and process the rest ..

      Please help … if u gt answer ..

  • Tushar 21 July, 2012, 19:32

    Helpful stuff…
    Efforts appreciated..!!!
    Thnx..:)

  • Preetam 30 July, 2012, 12:46

    @Gaurav
    Line no 19 will do that part (inserting remaining 400 records)
    ps.executeBatch(); // insert remaining records

  • Ajit Chouhary 11 October, 2012, 21:52

    thanx dude

  • m 25 October, 2012, 9:27

    hi all
    i have problem with addbatch
    i want to commit every 200 row. but…
    “com.ibm.db2.jcc.b.vd: Non-atomic batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.”
    Ex.
    u_sql = “UPDATE unemploy SET STATUS = ’8′, D_UPDATE = current timestamp, E_UPDATE = ‘xxxx’ WHERE e_pid=’1234′ with ur”;
    prstUpdate.addBatch(u_sql);

    if(count++==200)
    prstUpdate.executeBatch();
    db2Conn.commit();
    ……………………………..
    help me please !!

  • Anita 5 December, 2012, 0:39

    Thanks for the tutorial, I am getting: SEVERE: BatchUpdateException Io exception: Checksum fail, when I try to insert the rest of the records.

  • sendi_T 11 December, 2012, 21:58

    hi M, you need
    if (count++ % 200)

    instead of

    if ( count++ == 200)

    Anita, do you have auto commit on or off ? and can you list full exception stack trace ?

  • Patrick Flood 3 March, 2013, 12:39

    Thanks for this Viral very helpful, good on you.

  • Luis Rocha 4 March, 2013, 21:01

    Hi, thanks for the tutorial….. I have a question….. doing a benchmark with the first approach…. how much faster will this batch insertion will be??? Is there any option to do a bulk load blocks of data in a database using JDBC or any other api??? Thanks in advance.

  • Sonal 7 March, 2013, 7:54

    nice work.

    • Atul 7 March, 2013, 7:58

      Thnks Viral for the best tutorials.
      thanks a lot.

  • Dado 12 March, 2013, 15:04

    Vital,
    Thanks a lot. Greatly presented. Saved me a lot of effort.

  • adil ouidad 19 March, 2013, 16:04

    Thank you very much.

  • Rahul 27 March, 2013, 14:40

    Plz send me details about actual procedure of data storing in the database using java?

  • Site Rank History 6 April, 2013, 20:09

    Simple and to the point explanation! Invariably google brings me to your website whenever I have a java query!

    Superb job!

  • Mike 17 April, 2013, 23:25

    I used this program to do insert and it took roughly about 21 minutes to insert 2 and a half million records? Is there anyway we can speed this up? I was originally using JetEngine query which take about a second to insert all of those records but the only problem I was having with that was that it doesn’t handle leading and trailing spaces.

  • Mike 17 April, 2013, 23:27

    ^^…Im using windows 7. processor i7 2.9GHZ with 8 GB ram to execute that.

  • shravan 14 May, 2013, 15:39

    thanks nice code it is very useful

  • adetunjiadegbite 12 June, 2013, 22:07

    Great posts here, however i want to parameterize my insert such that i can dynamicaly add fields and values to insert in a text file rather than hardcoding. Can you help on this?

  • Kalaiselvi 21 June, 2013, 14:46

    Clear Explanation. Thanks

  • Diksha 3 July, 2013, 15:44

    Hello,
    suppose my data is failed within the batch then how should I elaborate it ???

  • Sindhura 1 August, 2013, 14:41

    On using executebatch(), the data is getting inserted in reverse order.Sometimes it is working fine.

  • Areeg 9 September, 2013, 9:30

    Thank you….Please, Would you tell me what should I do if I want to do the following:

    ps.setString(2, employee.getCity());
    ps.setString(3, employee.getPhone());
    

    without setString to the first field, I just want to setString to the second and the third field.

  • raj 1 November, 2013, 15:57

    Hi VP,
    let i have a list of 10000 or more records i want to send this list to android using webservice,
    so i decide i split to list 100-100 records. and send this list to android mobile,
    now if all record 100 insert mobile side then send ack to server.
    i want to know.is it efficient way?
    or not?tell me…

  • Udo Schmitt 11 November, 2013, 12:59

    Hi all,
    we did exactly the same you insisted here. Using prepared Statements and then do a batchInsert all n records.
    Anyhow we discovered a memeory leakage and after several million inserts a out of memory exception.
    The reason seams to be that you have to call a clearBatch() after each executeBatch() else the PreparedStatement will not release its internal used memory.

    We tested this with MS SQL Server, I do not know if this is DB or JDBC-driver dependent.
    regards
    Udo

  • Johnson 11 December, 2013, 14:21

    it’s a beautiful way to operate DB in batch.

  • Rakesh Paul 3 January, 2014, 18:57

    I am trying to use this code to import the .csv file in a MSSQL server. I have added the “sqljdbc4-3.0.jar” file into the project and changed the required places. But I am getting this error
    Query: INSERT INTO new(NewLead_IDX,DateReceived,FirstName,LastName,Address,City,State,ZipCode,Email,Phone,PhoneExtension,Year,Make,Model,Trim,ExteriorColor,InteriorColor,TimeFrame,FinancialPreference,BestContactTime,Score,NewLeadSalePrice,LeadDescription,LeadStatus,RejectedDate,NewSource,Buyer,BuyerExternalId,AccountRepresentative,WholesaleId,ProviderId,) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    sun.jdbc.odbc.JdbcOdbcBatchUpdateException: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ‘)’.
    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.emulateExecuteBatch(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeBatchUpdate(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcStatement.executeBatch(Unknown Source)
    at net.viralpatel.java.CSVLoader.loadCSV(CSVLoader.java:118)
    at net.viralpatel.java.Main.main(Main.java:17)
    java.lang.Exception: Error occured while loading data from file to database.[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ‘)’.
    at net.viralpatel.java.CSVLoader.loadCSV(CSVLoader.java:126)
    at net.viralpatel.java.Main.main(Main.java:17)

    Please can anybody help me out from this. I am a beginner in Java. Thanks in advance

  • Vaibhav Ajmera 28 January, 2014, 12:02

    Nice articles for basic understanding of JDBC.

  • Krishna 24 February, 2014, 12:29

    Hi Viral Patel,

    Please guide bulk insert in spring jdbc for Parent child tables.

    Thanks & Regards
    Krishna

Leave a Reply

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

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]