JDBC PreparedStatement - Insert a Record Example

In this article, we will learn how to insert a record in a database table using JDBC PreparedStatement interface. As we know PreparedStatement interface improves performance like SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

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

PreparedStatement.executeUpdate() method

PreparedStatement interface provides executeUpdate() method - executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
// 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
 PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
 preparedStatement.setInt(1, 1);
 preparedStatement.setString(2, "Tony");
 preparedStatement.setString(3, "tony@gmail.com");
 preparedStatement.setString(4, "US");
 preparedStatement.setString(5, "secret");

 System.out.println(preparedStatement);
 // Step 3: Execute the query or update query
 preparedStatement.executeUpdate();
} catch (SQLException e) {
 // print SQL exception information
 printSQLException(e);
}

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 PreparedStatement - Insert a Record Example

In this example, we will use the users database table. Before inserting a record to a database, we need to first create a users table in a database. Here is article JDBC Statement Create Table Example will create a users table in a mysql_database database.
package com.javaguides.jdbc.preparestatement.examples;

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

/**
 * Insert PrepareStatement JDBC Example
 * 
 * @author Ramesh Fadatare
 *
 */
public class InsertPStatementExample {
    private static final String INSERT_USERS_SQL = "INSERT INTO users" +
        "  (id, name, email, country, password) VALUES " +
        " (?, ?, ?, ?, ?);";

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

    public void insertRecord() throws SQLException {
        System.out.println(INSERT_USERS_SQL);
        // 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
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, "Tony");
            preparedStatement.setString(3, "tony@gmail.com");
            preparedStatement.setString(4, "US");
            preparedStatement.setString(5, "secret");

            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            preparedStatement.executeUpdate();
        } 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


How can I get the SQL of a PreparedStatement?

Simply print a preparedStatement object as:
System.out.println(preparedStatement);
Above code will print:
com.mysql.jdbc.JDBC42PreparedStatement@579bb367: INSERT INTO users  (id, name, email, country, password) VALUES  (1, 'Tony', 'tony@gmail.com', 'US', 'secret');

Comments