Java JDBC Insert Multiple Rows Example with MySQL Database

In this example, we will discuss how to insert multiple records in a MySQL database table via a JDBC statement. 

Learn a complete JDBC tutorial at https://www.javaguides.net/p/jdbc-tutorial.html.

Fundamental Steps in JDBC

The fundamental steps involved in the process of connecting to a database and executing a query consist of the following:

  1. Import JDBC Packages
  2. Establishing a connection.
  3. Create a statement.
  4. Execute the query.
  5. Using try-with-resources statements to automatically close JDBC resources

Key points

  1. From JDBC 4.0, we don't need to include 'Class.forName()' in our code to load JDBC driver. JDBC 4.0 drivers that are found in your classpath are automatically loaded.
  2. We have used try-with-resources statements to automatically close JDBC resources.

JDBC Statement Insert Multiple Records Example

Below example insert multiple records in a database table. Before inserting a 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', 'pramod@gmail.com', 'India', '123')," +
        "(4, 'Deepa', 'deepa@gmail.com', 'India', '123')," + "(5, 'Tom', 'top@gmail.com', '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

References



Comments

Post a Comment