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
$ git clone https://github.com/mkyong/java-jdbc.git
this isnt batch update, its batch insert…
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.
Did you get any idea on how this can be done?
How to execute batch (multiple queries) with preparared statement to update in different tables
Great, simple, and works. What else?
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.
Is it possible to perform Batch Updations using Prepared Statements using different queries?
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
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.
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.
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?
tks u so much!
now, i understand batch how to work…:D
With mysql maybe you need to use this url paramaters: “?useServerPrepStmts=false&rewriteBatchedStatements=true” because without these the performance will be equal with executeUpdate()!!!
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
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?
Mkyoung! you codes are really helpful for me many times . So thank you .
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?
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.