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

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. Read all published posts by

Comments

avatar
3000
newest oldest most voted
topher splat
Guest
topher splat

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

Rick irby
Guest
Rick irby

Trying to learn Java coding techniques…

Thx for the helpful info.

Rick.

trackback
JDBC Tutorials

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

Sushil Kumar
Guest
Sushil Kumar

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