Java JDBC PreparedStatement - Insert a Record Example

In this article, we will learn how to insert a record into a database table using the JDBC PreparedStatement interface. The PreparedStatement interface improves performance as SQL statements are precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute the statement multiple times.

Key Points

• JDBC 4.0: We don't need to include Class.forName() in our code to load the JDBC driver. JDBC 4.0 drivers that are found in your classpath are automatically loaded.
• try-with-resources: Statements are used to automatically close JDBC resources.
• PreparedStatement.executeUpdate(): The executeUpdate() method executes the SQL statement in the 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.

Technologies Used

• JDK: 21
• MySQL: 8.0
• IDE: Eclipse IDE or any other Java IDE
• JDBC API: 4.2

Steps to Process: Insert SQL Statement with JDBC

1. Establishing a connection.
2. Creating a statement.
3. Executing 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 the 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.

Example

In this example, we will use the users database table. Before inserting a record into a database, we need to first create the users table in the database.

Setting Up the MySQL Database

Execute the following SQL commands to set up the database and table:

CREATE DATABASE jdbc_example;

USE jdbc_example;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    country VARCHAR(100),
    password VARCHAR(100)
);

Adding MySQL JDBC Driver to Your Project

If you are using Maven, add the following dependency to your pom.xml file:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.30</version>
</dependency>

Establishing a Database Connection

We will start by establishing a connection to the MySQL database using JDBC.

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

public class JDBCExample {
    private static final String URL = "jdbc:mysql://localhost:3306/jdbc_example";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void main(String[] args) {
        try (Connection connection = getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the database!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Inserting a Record with JDBC PreparedStatement

We can use the Connection interface to create a PreparedStatement object and execute an SQL query to insert a record into the users table.

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

public class InsertRecordExample {
    private static final String INSERT_USERS_SQL = "INSERT INTO users (name, email, country, password) VALUES (?, ?, ?, ?)";

    public static void main(String[] args) {
        try (Connection connection = JDBCExample.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {

            // Set the parameters
            preparedStatement.setString(1, "Tony");
            preparedStatement.setString(2, "[email protected]");
            preparedStatement.setString(3, "US");
            preparedStatement.setString(4, "secret");

            // Execute the insert SQL statement
            int rowsInserted = preparedStatement.executeUpdate();
            System.out.println(rowsInserted + " row(s) inserted!");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output:

Closing the Connection

Using the try-with-resources statement ensures that the connection is closed automatically. This is important to free up database resources.

import java.sql.Connection;
import java.sql.SQLException;

public class CloseConnectionExample {
    public static void main(String[] args) {
        try (Connection connection = JDBCExample.getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the database!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Conclusion

In this tutorial, we have covered the basics of using the JDBC PreparedStatement interface to insert a record into a MySQL database table. We demonstrated how to establish a connection, execute an SQL query to insert data, and close the connection using the try-with-resources statement. This guide should help you get started with JDBC and understand how to use the PreparedStatement interface effectively with MySQL.