JDBC PreparedStatement – Batch Update

A JDBC PreparedStatement example to send a batch of SQL commands (create, insert, update) to the database.

BatchUpdate.java

package com.mkyong.jdbc.preparestatement;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.Arrays;

public class BatchUpdate {

    public static void main(String[] args) {

        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             PreparedStatement psDDL = conn.prepareStatement(SQL_CREATE);
             PreparedStatement psInsert = conn.prepareStatement(SQL_INSERT);
             PreparedStatement psUpdate = conn.prepareStatement(SQL_UPDATE)) {

            // commit all or rollback all, if any errors
            conn.setAutoCommit(false); // default true

            psDDL.execute();

            // Run list of insert commands
            psInsert.setString(1, "mkyong");
            psInsert.setBigDecimal(2, new BigDecimal(10));
            psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
            psInsert.addBatch();

            psInsert.setString(1, "kungfu");
            psInsert.setBigDecimal(2, new BigDecimal(20));
            psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
            psInsert.addBatch();

            psInsert.setString(1, "james");
            psInsert.setBigDecimal(2, new BigDecimal(30));
            psInsert.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
            psInsert.addBatch();

            int[] rows = psInsert.executeBatch();

            System.out.println(Arrays.toString(rows));

            // Run list of update commands
            psUpdate.setBigDecimal(1, new BigDecimal(999.99));
            psUpdate.setString(2, "mkyong");
            psUpdate.addBatch();

            psUpdate.setBigDecimal(1, new BigDecimal(888.88));
            psUpdate.setString(2, "james");
            psUpdate.addBatch();

            int[] rows2 = psUpdate.executeBatch();

            System.out.println(Arrays.toString(rows2));

            conn.commit();

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static final String SQL_INSERT = "INSERT INTO EMPLOYEE (NAME, SALARY, CREATED_DATE) VALUES (?,?,?)";

    private static final String SQL_UPDATE = "UPDATE EMPLOYEE SET SALARY=? WHERE NAME=?";

    private static final String SQL_CREATE = "CREATE TABLE EMPLOYEE"
            + "("
            + " ID serial,"
            + " NAME varchar(100) NOT NULL,"
            + " SALARY numeric(15, 2) NOT NULL,"
            + " CREATED_DATE timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,"
            + " PRIMARY KEY (ID)"
            + ")";

}

This batch example is using transactions, either commit all or rollback all, if errors.


	conn.setAutoCommit(false);

	// SQL 
	
	conn.commit();

P.S Tested with PostgreSQL 11 and Java 8

pom.xml

	<dependency>
		<groupId>org.postgresql</groupId>
		<artifactId>postgresql</artifactId>
		<version>42.2.5</version>
	</dependency>

Download Source Code

References

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
17 Comment threads
2 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
17 Comment authors
Hayas Nctopher splatpragadeeshwaranAli Shaikhvaat666 Recent comment authors
newest oldest most voted
Roboz Bela
Guest
Roboz Bela

With mysql maybe you need to use this url paramaters: “?useServerPrepStmts=false&rewriteBatchedStatements=true” because without these the performance will be equal with executeUpdate()!!!

Alexandru Matei
Guest
Alexandru Matei

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

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

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

ganesh
Guest
ganesh

Hi Mkyong,

Nice site and articles.

Is there anyway to get the count of records using jdbc template prepare statement. I have list of employees and wanted to check any of them existed in DB or not.

Thanks,
Gani

bitpattern
Guest
bitpattern

Mkyoung! you codes are really helpful for me many times . So thank you .

topher splat
Guest
topher splat

this isnt batch update, its batch insert…

pragadeeshwaran
Guest
pragadeeshwaran

consider am having 1000 records which hav duplicates and i insert the first 500 into one table and next 500 so am using addbatch and execute batch if in case any id within the first 500 comes in the next 500 it shd be updated to the table it shoud not create a new records please let me know how to implement this……
thanks in advance.

Hayas Nc
Guest
Hayas Nc

Did you get any idea on how this can be done?

Ali Shaikh
Guest
Ali Shaikh

How to execute batch (multiple queries) with preparared statement to update in different tables

Joshi Nath
Guest
Joshi Nath

Is it possible to perform Batch Updations using Prepared Statements using different queries?

Ronn
Guest
Ronn

Hi im getting an error while doing executeBatch().
org.netezza.error.NzSQLException: ERROR: Update canceled: attempt to update a target row with values from multiple join rows..
Please enlighten me.

Please check this link for more info:

http://stackoverflow.com/questions/21086372/unable-to-do-a-executebatch-on-netezza-jdbc

K E Naresh
Guest
K E Naresh

con.setAutoCommit(false);
pst.setInt(1, 119);
pst.setString(2, “Praveen”);
pst.addBatch();

Hi,
pst.setInt(1, 120);
pst.setString(2, “Giri”);
pst.addBatch();
int[] count=pst.executeBatch();

pst.close();
//con.commit();

while executing the above code and i go to database and checked, i found 119,120 records inserted permanantly.

Ramsharan Shrestha
Guest
Ramsharan Shrestha

In case of INSERT why do we need to use batch if we want to insert in same table since we can insert multiple row using a single query in mysql.

vsriram
Guest
vsriram

Hi sir,Im having one doubt,i read somewhere that direct linking with prepared Statement is Good rather than Binding with String (like above “insertTableSQL”) is it Correct Sir?

tuanlv
Guest
tuanlv

tks u so much!
now, i understand batch how to work…:D

roby
Guest
roby

if i want to batch 10000 record, do i need to close every time i addBatch() to prepared statement? if i not close will it return too many open cursor?

hari
Guest
hari

hi,i had a doubt by seeing above example? i.e.,suppose if i have 2 differnet querys then can i u prepared statement and batch update?Is it possible?

bitpattern
Guest
bitpattern

I was doing the same thing for two different queries. I can do that from Statement but ………….I have one way to do it and i did it. let me know if anybody want that code.

trackback
JDBC Tutorials

[…] JDBC PreparedStatement example – Batch Update Example to insert records in batch process, via JDBC PreparedStatement. […]

vaat666
Guest
vaat666

Great, simple, and works. What else?