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();
Code language: Java (java)

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();
Code language: Java (java)

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();
Code language: Java (java)

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();
Code language: Java (java)

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)
Code language: HTML, XML (xml)

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();
Code language: Java (java)

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.

Get our Articles via Email. Enter your email address.

You may also like...

81 Comments

  1. zdenek says:

    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

  2. mkhan523 says:

    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.

    • 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 says:

      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

  3. Superb :)

  4. Suresh says:

    Awesome Tutorial. Really Great.

  5. Herman Barrantes says:

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

    • Avinash says:

      same problem I have faced. Did you find the solution for it.

  6. Andy says:

    Excellent tutorial. Concise and explained very well

  7. Mallesh says:

    Really this good… for learners

  8. Dhiraj says:

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

    • Thanks Dhiraj :)

      • pankaj says:

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

  9. VINAYAK says:

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

  10. Dhens Galaxian says:

    good idea… thanx

  11. Gaurav says:

    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 says:

      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 says:

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

  12. Michel says:

    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 -> [email protected]

  13. Bunty says:

    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 says:

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

      Please help … if u gt answer ..

      • Marko says:

        I am also looking for the same, pls, someone help!!!!

  14. Tushar says:

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

  15. Preetam says:

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

  16. thanx dude

  17. m says:

    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 !!

  18. Anita says:

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

  19. sendi_T says:

    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 ?

  20. Patrick Flood says:

    Thanks for this Viral very helpful, good on you.

  21. Luis Rocha says:

    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.

  22. Sonal says:

    nice work.

    • Atul says:

      Thnks Viral for the best tutorials.
      thanks a lot.

  23. Dado says:

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

  24. Thank you very much.

  25. Rahul says:

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

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

    Superb job!

  27. Mike says:

    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.

  28. Mike says:

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

  29. shravan says:

    thanks nice code it is very useful

  30. 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?

  31. Kalaiselvi says:

    Clear Explanation. Thanks

  32. Diksha says:

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

  33. Sindhura says:

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

  34. Areeg says:

    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.

  35. raj says:

    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…

  36. Udo Schmitt says:

    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

  37. Johnson says:

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

  38. Rakesh Paul says:

    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

  39. Vaibhav Ajmera says:

    Nice articles for basic understanding of JDBC.

  40. Krishna says:

    Hi Viral Patel,

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

    Thanks & Regards
    Krishna

  41. Ramesh Pathak says:

    Please help me .. I am getting SQL Error: ORA-00001: unique constraint (TSMGR.PKMIMTX) violated while inserting data to database. I am having 11 sql queries added to batchupdate. My 1st qeries is giving this error, so i wanna skip it when ever it gives error and wants my rest 10 sql qeries to execute.

    String query1=q1;
    String query2=q2;
    String query3=q3;
    String query4=q4;
    String query5=q5;
    String query6=q6;
    String query7=q7;
    String query8=q8;
    String query9=q9;
    String query10=q10;
    String query11=q11;
    Connection con=null;

    try
    {
    con=DBUtil.getConnection();
    con.setAutoCommit(false);
    st=con.createStatement();
    st.addBatch(query1);
    st.addBatch(query2);
    st.addBatch(query3);
    st.addBatch(query4);
    st.addBatch(query5);
    st.addBatch(query6);
    st.addBatch(query7);
    st.addBatch(query8);
    st.addBatch(query9);
    st.addBatch(query10);
    st.addBatch(query11);
    int count[] = st.executeBatch();

    Please reply me, very urgently needs to solve this issue . [email protected]

    thanks.

  42. jessica says:

    Thank you for this usefull article!

  43. kaushik says:

    i tried this code :
    String []edu_record = getEduDetail(edu_table, emp_code.getText().trim(),form_no.getText().trim());
    for (String edu_record1 : edu_record) {

    pstmt.addBatch(edu_record1);
    }
    the method getEduDetail() generates my query and returns it string array.
    while adding into batch it gives me following error.

    SEVERE: null
    com.microsoft.sqlserver.jdbc.SQLServerException: The method addBatch() cannot take arguments on a PreparedStatement or CallableStatement.

    pls help me.

  44. biksah says:

    Hi ,

    What is it for(Employee employee:employee) ?

    Is it the table object from which we get the records ?

  45. vaibhav says:

    Hi ,
    i am inserting multiple records using batch query in database successfully but when again same value insert in database then show error.can u tell me how can resolve this issues i don’t want duplicate value in database.

  46. Alexandru says:

    Do you know if connection.rollback(); works with MySQL ?

    I’ve tested version MySQL 5.6 and latest Connector-J 5.1.33 and unfortunately the records that succeeded
    are still in the database even statement.executeBatch(); throws an exception

    I was expecting that connection.rollback() will remove the records that succeeded.

  47. Deepali says:

    Hi Viral,

    I am using batch inserts for batch size 1000.
    but the commit for 1000 rows is taking 4 to 5 minutes to complete.
    Isn’t it too long ?

    I am connected to remote database server through VPN.
    Is this the reason it is taking too much time for 1000 rows?

    one more question,
    Can i share one single connection object with multiple threads (All the threads having different statements)?
    What should be done ideally?

    Waiting for your response.

    Thanks,
    Deepali

  48. Oliver Lundag says:

    I enhance some part.

    if(++count % batchSize == 0) {
    		    		prepareStatement.executeBatch();
    		    		prepareStatement.clearBatch();//clear the batch after execution
    		    		count = 0;//reset count
    		    		//System.out.println(&quot;Reset : &quot; + count);
    		    	}
                            if (count &gt; 0) { //prevention from duplicate execution
    				prepareStatement.executeBatch(); // insert remaining records
    			}
    

  49. Raul says:

    Dear Viral Patel, I read with great pleasure publication about for JDBC batch insert. It has really been very useful when it comes to an appreciable amount of records (20000-70000), which is what I’ve tried. However, in a case of an insert 8000 records, to execute sentence ps.executeBatch () the program stops responding. Regardless of the length of the batch. I tested with 1000, 100 and 10 records is the same. Now if I try by one with ps.executeUpdate () sentence  is executed with a speed normally lets say. Do you have any idea what might be going on? I attach the method code for your appreciation. From already thank you very much.
    Raul from Argentina.

    public static void InsMasivaRegistros(Connection connLS2000, com.ibm.as400.access.AS400JDBCResultSet rs){
    
            try {
                int cRegIns = 0;
                int ins = 0;
                String consulta;     //                  1                 2                 3     4          5        
                consulta = &quot;Insert Into FNM01I00(CODIGO_NOMENCLADOR, TIPO_DE_NOMENCLADOR, NM1F10, NM4F10, CODIGO_SECTOR_PRESTADOR, LIBRE_CEROS, CANTIDAD_GALENOS_01, ABREV_SECTOR_PRESTADOR, CODIGO_ARANCEL_01, CANTIDAD_GALENOS_02, CODIGO_ARANCEL_02, NMFV03, NM1F03, NM2F03, NM3F03, NM4F03, NMFV04, NM1F04, NM2F04, NM3F04, NM4F04, NMFV05, NM1F05, NM2F05, NM3F05, NM4F05, CANTIDAD_GALENOS_03, CODIGO_ARANCEL_03, CANTIDAD_GALENOS_04, CODIGO_ARANCEL_04, FACTOR_MULTIPLICACION, FECHA_VIGENCIA_01, NM1F01, NM2F01, NM3F01, NM4F01, NMFV02, NM1F02, NM2F02, NM3F02, NM4F02, NMFV06, NM1F06, NM2F06, NM3F06, NM4F06, NMFV07, NM1F07, NM2F07, NM3F07, NM4F07, NMFV08, NM1F08, NM2F08, NM3F08, NM4F08, NMFV09, NM1F09, NM2F09, NM3F09, NM4F09, NMFV10, NM2F10, NM3F10, NMFV11, NM1F11, NM2F11, NM3F11, NM4F11, NMFV12, NM1F12, NM2F12, NM3F12, NM4F12, NMFV13, NM1F13, NM2F13, NM3F13, NM4F13, NMHPRS, NMHAYB, NMHANB, NMGASB, NMGEPR, NMGEAY, NMGEAN, NMGEGS, NMFMTM, NMLIB2, DESCRIPCION_PRESTACION) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)&quot;;
                interbase.interclient.Statement smt = (interbase.interclient.Statement) connLS2000.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                interbase.interclient.PreparedStatement ps;
                ps = (interbase.interclient.PreparedStatement) connLS2000.prepareStatement(consulta);
                System.out.println(&quot;Insersion Masiva para los &quot; + cRegIns + &quot; Registros&quot;);
                final int batchSize = 1000;  
                while (rs.next()){
                    ps.setFloat(1,rs.getFloat(&quot;CODIGO_NOMENCLADOR&quot;));            
                    ps.setFloat(2,rs.getFloat(&quot;TIPO_DE_NOMENCLADOR&quot;));           
                    ps.setFloat(3,rs.getFloat(&quot;NM1F10&quot;));                        
                    ps.setFloat(4,rs.getFloat(&quot;NM4F10&quot;));                        
                    ps.setInt(5,rs.getInt(&quot;CODIGO_SECTOR_PRESTADOR&quot;));           
                    ps.setFloat(6,rs.getFloat(&quot;LIBRE_CEROS&quot;));                   
                    ps.setFloat(7,rs.getFloat(&quot;CANTIDAD_GALENOS_01&quot;));           
                    ps.setString(8,rs.getString(&quot;ABREV_SECTOR_PRESTADOR&quot;));            
                    ps.setFloat(9,rs.getFloat(&quot;CODIGO_ARANCEL_01&quot;));             
                    ps.setFloat(10,rs.getFloat(&quot;CANTIDAD_GALENOS_02&quot;));          
                    ps.setFloat(11,rs.getFloat(&quot;CODIGO_ARANCEL_02&quot;));            
                    ps.setInt(12,rs.getInt(&quot;NMFV03&quot;));                           
                    ps.setFloat(13,rs.getFloat(&quot;NM1F03&quot;));                       
                    ps.setFloat(14,rs.getFloat(&quot;NM2F03&quot;));                       
                    ps.setFloat(15,rs.getFloat(&quot;NM3F03&quot;));                       
                    ps.setFloat(16,rs.getFloat(&quot;NM4F03&quot;));                       
                    ps.setInt(17,rs.getInt(&quot;NMFV04&quot;));                           
                    ps.setFloat(18,rs.getFloat(&quot;NM1F04&quot;));                       
                    ps.setFloat(19,rs.getFloat(&quot;NM2F04&quot;));                       
                    ps.setFloat(20,rs.getFloat(&quot;NM3F04&quot;));                       
                    ps.setFloat(21,rs.getFloat(&quot;NM4F04&quot;));                       
                    ps.setInt(22,rs.getInt(&quot;NMFV05&quot;));                           
                    ps.setFloat(23,rs.getFloat(&quot;NM1F05&quot;));                       
                    ps.setFloat(24,rs.getFloat(&quot;NM2F05&quot;));                       
                    ps.setFloat(25,rs.getFloat(&quot;NM3F05&quot;));                       
                    ps.setFloat(26,rs.getFloat(&quot;NM4F05&quot;));                       
                    ps.setFloat(27,rs.getFloat(&quot;CANTIDAD_GALENOS_03&quot;));          
                    ps.setFloat(28,rs.getFloat(&quot;CODIGO_ARANCEL_03&quot;));            
                    ps.setFloat(29,rs.getFloat(&quot;CANTIDAD_GALENOS_04&quot;));          
                    ps.setFloat(30,rs.getFloat(&quot;CODIGO_ARANCEL_04&quot;));            
                    ps.setFloat(31,rs.getFloat(&quot;FACTOR_MULTIPLICACION&quot;));        
                    ps.setFloat(32,rs.getFloat(&quot;FECHA_VIGENCIA_01&quot;));            
                    ps.setFloat(33,rs.getFloat(&quot;NM1F01&quot;));                       
                    ps.setFloat(34,rs.getFloat(&quot;NM2F01&quot;));                       
                    ps.setFloat(35,rs.getFloat(&quot;NM3F01&quot;));                       
                    ps.setFloat(36,rs.getFloat(&quot;NM4F01&quot;));                       
                    ps.setInt(37,rs.getInt(&quot;NMFV02&quot;));                           
                    ps.setFloat(38,rs.getFloat(&quot;NM1F02&quot;));                       
                    ps.setFloat(39,rs.getFloat(&quot;NM2F02&quot;));                       
                    ps.setFloat(40,rs.getFloat(&quot;NM3F02&quot;));                       
                    ps.setFloat(41,rs.getFloat(&quot;NM4F02&quot;));                       
                    ps.setInt(42,rs.getInt(&quot;NMFV06&quot;));                           
                    ps.setFloat(43,rs.getFloat(&quot;NM1F06&quot;));                       
                    ps.setFloat(44,rs.getFloat(&quot;NM2F06&quot;));                       
                    ps.setFloat(45,rs.getFloat(&quot;NM3F06&quot;));                       
                    ps.setFloat(46,rs.getFloat(&quot;NM4F06&quot;));                       
                    ps.setInt(47,rs.getInt(&quot;NMFV07&quot;));                           
                    ps.setFloat(48,rs.getFloat(&quot;NM1F07&quot;));                       
                    ps.setFloat(49,rs.getFloat(&quot;NM2F07&quot;));                       
                    ps.setFloat(50,rs.getFloat(&quot;NM3F07&quot;));                       
                    ps.setFloat(51,rs.getFloat(&quot;NM4F07&quot;));                       
                    ps.setInt(52,rs.getInt(&quot;NMFV08&quot;));                           
                    ps.setFloat(53,rs.getFloat(&quot;NM1F08&quot;));                       
                    ps.setFloat(54,rs.getFloat(&quot;NM2F08&quot;));                       
                    ps.setFloat(55,rs.getFloat(&quot;NM3F08&quot;));                       
                    ps.setFloat(56,rs.getFloat(&quot;NM4F08&quot;));                       
                    ps.setInt(57,rs.getInt(&quot;NMFV09&quot;));                           
                    ps.setFloat(58,rs.getFloat(&quot;NM1F09&quot;));                       
                    ps.setFloat(59,rs.getFloat(&quot;NM2F09&quot;));                       
                    ps.setFloat(60,rs.getFloat(&quot;NM3F09&quot;));                       
                    ps.setFloat(61,rs.getFloat(&quot;NM4F09&quot;));                       
                    ps.setInt(62,rs.getInt(&quot;NMFV10&quot;));                           
                    ps.setFloat(63,rs.getFloat(&quot;NM2F10&quot;));                       
                    ps.setFloat(64,rs.getFloat(&quot;NM3F10&quot;));                       
                    ps.setInt(65,rs.getInt(&quot;NMFV11&quot;));                           
                    ps.setFloat(66,rs.getFloat(&quot;NM1F11&quot;));                       
                    ps.setFloat(67,rs.getFloat(&quot;NM2F11&quot;));                       
                    ps.setFloat(68,rs.getFloat(&quot;NM3F11&quot;));                       
                    ps.setFloat(69,rs.getFloat(&quot;NM4F11&quot;));                       
                    ps.setInt(70,rs.getInt(&quot;NMFV12&quot;));                           
                    ps.setFloat(71,rs.getFloat(&quot;NM1F12&quot;));                       
                    ps.setFloat(72,rs.getFloat(&quot;NM2F12&quot;));                       
                    ps.setFloat(73,rs.getFloat(&quot;NM3F12&quot;));                       
                    ps.setFloat(74,rs.getFloat(&quot;NM4F12&quot;));                       
                    ps.setInt(75,rs.getInt(&quot;NMFV13&quot;));                           
                    ps.setFloat(76,rs.getFloat(&quot;NM1F13&quot;));                       
                    ps.setFloat(77,rs.getFloat(&quot;NM2F13&quot;));                       
                    ps.setFloat(78,rs.getFloat(&quot;NM3F13&quot;));                       
                    ps.setFloat(79,rs.getFloat(&quot;NM4F13&quot;));                       
                    ps.setFloat(80,rs.getFloat(&quot;NMHPRS&quot;));                       
                    ps.setFloat(81,rs.getFloat(&quot;NMHAYB&quot;));                       
                    ps.setFloat(82,rs.getFloat(&quot;NMHANB&quot;));                       
                    ps.setFloat(83,rs.getFloat(&quot;NMGASB&quot;));                       
                    ps.setFloat(84,rs.getFloat(&quot;NMGEPR&quot;));                       
                    ps.setFloat(85,rs.getFloat(&quot;NMGEAY&quot;));                       
                    ps.setFloat(86,rs.getFloat(&quot;NMGEAN&quot;));                       
                    ps.setFloat(87,rs.getFloat(&quot;NMGEGS&quot;));                       
                    ps.setFloat(88,rs.getFloat(&quot;NMFMTM&quot;));                       
                    ps.setString(89,rs.getString(&quot;NMLIB2&quot;));                     
                    ps.setString(90,rs.getString(&quot;DESCRIPCION_PRESTACION&quot;)); 
                    ps.executeUpdate();
                    if(++cRegIns % batchSize == 0) {
                        System.out.println(&quot;executeInsert para los &quot; + cRegIns + &quot; Registros&quot;);
                    }
            }
                connLS2000.commit();
                System.out.println(&quot;executeInsBatch para los &quot; + cRegIns + &quot; Registros&quot;);
                rs.close();
                ps.close();
                connLS2000.close();
            }
            catch (DataTruncation ex) {
                getLogger(FNM01I01LS2000Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
            catch (SQLException | ArrayIndexOutOfBoundsException ex) {
                getLogger(FNM01I01LS2000Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    

  50. Raul says:

    Continúo el mensaje anterior.
    El código que no funciona en ese caso es,

    ps.addBatch();
                    if(++cRegIns % batchSize == 0) {
                        ps.executeBatch();         // This is where the program crashes without any message.
                        connLS2000.commit();
                        System.out.println(&quot;executeInsBatch para los &quot; + cRegIns + &quot; Registros&quot;);
                    }
    

  51. Raul says:

    Sorry for the inconvenience, but yesterday I sent a query related with this issue, however I can not see it in the list. Maybe this topic is now closed?
    Thank you.
    Raul.

  52. Binh Thanh Nguyen says:

    Thanks, nice post.

  53. Anil says:

    for (Employee employee: “”employees””)

    i have quetion over this..
    what to write in place of emplyees.. my code is like in jsp
    table name msmnt in mysql
    model class name is mesurement.
    pls help me out fast

  54. Anil says:

    hey pls put the whole program once pls

  55. Ariel Tzentner says:

    Thank you very much for this post! Very helpful and clearly explained!

  56. Rajesh says:

    Hi Patel,

    I’ve one scenario, need to update a column with UUID in table where the column is null.
    I’m my table i’ve 7crore needs to be updated. It’s taking 1hr to update 1laks records.

    i’m using batchUpdate is there any way to improve the performance of updating..

    or is there any api for to do…

  57. omar says:

    Thanks, nice post.

  58. Magesh says:

    it’s really awesome one. Keep it up

  59. Sher says:

    Shouldn’t you be resetting count back to 0 in the ‘if’ condition after you executeBatch in batch with batch example.

  60. Sher says:

    Oops my bad, sorry the count need not be reset to 0!

  61. Mohan says:

    ps.executeBatch(); // insert remaining records

    can this line executed even if there is no new batch added (exactly 1000/2000 records) ?

  62. Prem says:

    well, my need is to insert multiple rows but with atomicity
    But the operation is not atomic
    if database throw exception in middle of insertion , it insert the rows executed before the exception

  63. Arjun says:

    what is employees in for(Employee employee:employees)?

  64. Andrés D says:

    Nice solution, you could try using multithread execution with forkJoinPool, if you are using java8 you can run parallel tasks.

    IntStream stream = IntStream.range(0, listOfValues.size()).sorted(); //Number of rows to insert
    ForkJoinPool forkJoinPool = new ForkJoinPool(100); //Using 100 threads

    try {
    forkJoinPool.submit(() -> {
    stream.parallel().forEach(x -> {
    //System.out.println(“Paralell: ” + x);
    loadData(x);//Load your data here, insert statement using jdb conection pool
    try {
    Thread.sleep(10);
    } catch (InterruptedException e) { }
    });
    }).get();
    forkJoinPool.shutdown();
    } catch (InterruptedException | ExecutionException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }

  65. Pankaj says:

    What if one query in a batch fails ?
    It will not allow other queries in batch to be executed hence leaving that batch unexpected how will we handle such scenarios?

  66. Jyotikanta says:

    Nice tutorial. Just a word of caution Autocommit true is not recommended while doing batch update. refer

    https://stackoverflow.com/questions/14625371/rollback-batch-execution-when-using-jdbc-with-autocommit-true

  67. Kanth says:

    Hi Viral,
    This post is very useful for beginners like me. I have question regarding prepared statement, after executing the batch we need to clear that batch? because I am working with 200 millions line of data. so while running the program performance is reduced. Can you please let me know ASAP. Can any one help me out from this issue

  68. Jones Wang says:

    A great post, which helps me understand batchUpdate, thank you very much.

  69. Emiliano says:

    for (Employee employee: employees) {
    ps.setString(1, employee.getName());
    ps.setString(2, employee.getCity());
    ps.setString(3, employee.getPhone());
    ps.addBatch();

    if(++count == batchSize) {
    ps.executeBatch();
    connection.commit();
    ps.clearBatch();
    count = 0;
    }
    }
    if(count > 0){
    ps.executeBatch(); // insert remaining records
    connection.commit();
    }

    I think is better use this way. I mean way use % if we can check directly with ==.
    Beside that in order to send remnants we should clear the prepareStatement

  70. This is a bit old, but it is gold, thanks for sharing !!

  71. Chris says:

    Thanks. Detailed one.

Leave a Reply

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