Java JDBC Statement - Insert Multiple Records Example with MySQL Database

---

< Previous Next >

JDBC Tutorial

---

In this article, we will discuss how to insert multiple records in a database table via JDBC statement. JDBC Statement interface provides Statement.executeUpdate() method from which we can insert a record in a database table as:

Statement statement = dbConnection.createStatement();
// execute the insert SQL stetement
statement.executeUpdate(insertTableSQL);

We need to pass below SQL script to Statement.executeUpdate() method for execution:

private static final String INSERT_MULTIPLE_USERS_SQL = "INSERT INTO Users "
+ "VALUES (3, 'Pramod', '[email protected]', 'India', '123'),"
+ "(4, 'Deepa', '[email protected]', 'India', '123'),"
+ "(5, 'Tom', '[email protected]', 'India', '123');"

Technologies used

1. JDK - 1.8 or later
2. MySQL - 5.7.12
3. IDE - Eclipse Neon
4. JDBC API - 4.2

Steps to Process Insert SQL statement with JDBC

1. Establishing a connection
2. Create a statement
3. Execute the query
4. Using try-with-resources Statements to Automatically Close JDBC Resources

From JDBC 4.0, we don't need to include 'Class.forName()' in our code, to load JDBC driver. When the method 'getConnection' is called, the 'DriverManager' will automatically load the suitable driver among the JDBC drivers that were loaded at initialization and those loaded explicitly using the same class loader as the current application.

Connection conn = DriverManager.getConnection(Urldatabase,Username,Password);

Any JDBC 4.0 drivers that are found in your classpath are automatically loaded. (However, you must manually load any drivers prior to JDBC 4.0 with the method Class.forName.)

JDBC Statement Insert Multiple Records Example

Below example insert multiple records in a database table. Before insert record to a database table, we need to create a table in a database. 

Refer this article to create a users table JDBC Statement Create a Table Example

package com.javaguides.jdbc.statement.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Insert Statement JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class InsertStatementExample {
    private static final String INSERT_MULTIPLE_USERS_SQL = "INSERT INTO Users " +
        "VALUES (3, 'Pramod', '[email protected]', 'India', '123')," +
        "(4, 'Deepa', '[email protected]', 'India', '123')," + "(5, 'Tom', '[email protected]', 'India', '123');";

    public static void main(String[] argv) throws SQLException {
        InsertStatementExample createTableExample = new InsertStatementExample();
        createTableExample.insertRecord();
    }

    public void insertRecord() throws SQLException {
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            Statement statement = connection.createStatement();) {

            // Step 3: Execute the query or update query
            int result = statement.executeUpdate(INSERT_MULTIPLE_USERS_SQL);
            System.out.println("No. of records affected : " + result);
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

Output:

No. of records affected : 3

Note that Statement.executeUpdate() method returns either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing.

int result = statement.executeUpdate(INSERT_MULTIPLE_USERS_SQL);
System.out.println("No. of records affected : " + result);

---

< Previous Next >

JDBC Tutorial

---