JDBC Statement – Batch Update
A JDBC Statement example to send a batch of SQL commands (drop,create, insert, update) to the database.
BatchUpdate.java
package com.mkyong.jdbc.statement;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
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");
Statement statement = conn.createStatement()) {
// optional, for transaction
// commit all or rollback all, if any errors
conn.setAutoCommit(false);
// add list of SQL commands and run as a batch
// drop table
statement.addBatch(SQL_DROP);
// create table
statement.addBatch(SQL_CREATE);
// insert
statement.addBatch(generateInsert("mkyong", new BigDecimal(1000)));
// insert
statement.addBatch(generateInsert("jane", new BigDecimal(2000)));
// update
statement.addBatch(updateSalaryByName("mkyong", new BigDecimal(888)));
int[] rows = statement.executeBatch();
System.out.println(Arrays.toString(rows)); // [0, 0, 1, 1, 1]
// commit everything
conn.commit();
// java 8, not yet implemented by pgStatement 42.2.5
// statement.executeLargeBatch();
} 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_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)"
+ ")";
private static final String SQL_DROP = "DROP TABLE IF EXISTS EMPLOYEE";
private static String generateInsert(String name, BigDecimal salary) {
return "INSERT INTO EMPLOYEE (NAME, SALARY, CREATED_DATE) " +
"VALUES ('" + name + "','" + salary + "','" + LocalDateTime.now() + "')";
}
private static String updateSalaryByName(String name, BigDecimal salary) {
return "UPDATE EMPLOYEE SET SALARY='" + salary + "' WHERE NAME='" + name + "'";
}
}
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
again….this isnt an update. you are inserting! please name your articles correctly
Trying to learn Java coding techniques…
Thx for the helpful info.
Rick.
What if some queries are successful and some failed due to some reasons?
Will the entire batch will be rolled back?
In your case, implement transaction.
i just read transactions thanks for helping..
Kindly say yes or no ?. What if some queries fails will all my queries fail in the batch. My requirement is that i want all queries to be successful otherwise all should fail. Thanks.
Can you show one example by using transaction.