Main Tutorials

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

References

About Author

author image
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

Subscribe
Notify of
7 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
topher splat
5 years ago

again….this isnt an update. you are inserting! please name your articles correctly

Rick irby
11 years ago

Trying to learn Java coding techniques…

Thx for the helpful info.

Rick.

Sushil Kumar
11 years ago

What if some queries are successful and some failed due to some reasons?
Will the entire batch will be rolled back?

javid
10 years ago
Reply to  mkyong

i just read transactions thanks for helping..

javid
10 years ago
Reply to  mkyong

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.

harish
10 years ago
Reply to  mkyong

Can you show one example by using transaction.