Java JDBC PreparedStatement Interface Example with MySQL Database

In this article, we will learn what is JDBC PreparedStatement Interface and how to use it to perform different database operations with MySQL database.
The PreparedStatement interface is a subinterface of Statement. It is used to execute a parameterized query.
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
Let's see the simple example of a parameterized query:
String sql = "insert into users values(?,?,?)";  
As you can see, we are passing parameter (?) for the values. Its value will be set by calling the setter methods of PreparedStatement.

Overview of PreparedStatements

Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement.
The PreparedStatement's primary features are:
  • Easy to insert parameters into the SQL statement.
  • Easy to reuse the PreparedStatement with new parameters.
  • May increase performance of executed statements.
  • Enables easier batch updates.

Why use PreparedStatement?

Improves performance: The performance of the application will be faster if you use the PreparedStatement interface because a query is compiled only once.
PreparedStatement interface takes parameters and the advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. 

PreparedStatement interface Class Diagram

The below class diagram shows a list of PreparedStatement interface methods/APIs.

PreparedStatement interface Commonly used methods

The important methods of PreparedStatement interface are given below:
  • public void setInt(int paramIndex, int value) - sets the integer value to the given parameter index.
  • public void setString(int paramIndex, String value) - sets the String value to the given parameter index.
  • public void setFloat(int paramIndex, float value) - sets the float value to the given parameter index.
  • public void setDouble(int paramIndex, double value) - sets the double value to the given parameter index.
  • public int executeUpdate() - executes the query. It is used to create, drop, insert, update, delete etc.
  • public ResultSet executeQuery() - executes the select query. It returns an instance of ResultSet.
We will use a few of the above methods in upcoming examples.

PreparedStatement Interface Examples

Let's demonstrate how to insert, update and delete a record in a MySQL database using the PreparedStatement interface

Java JDBC PreparedStatement Insert a Record Example

In this example, we will use the users database table. Before inserting a record into a database, we need to first create a users table in a database.
Here is the article JDBC Statement Create Table Example will create a users table in a mysql_database database.
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
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, "[email protected]");
            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();
                }
            }
        }
    }
}

JDBC PreparedStatement Select Records Example

Here we have a users table in a database and we will query a list of users from database table by id.
Steps to process select 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
package com.javaguides.jdbc.preparestatement.examples;

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

/**
 * Select PreparedStatement JDBC Example
 * 
 * @author Ramesh Fadatare
 *
 */
public class SelectPStatementExample {
    private static final String QUERY = "select id,name,email,country,password from Users where id =?";

    public static void main(String[] args) {

        // using try-with-resources to avoid closing resources (boiler plate code)

        // 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(QUERY);) {
            preparedStatement.setInt(1, 1);
            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            ResultSet rs = preparedStatement.executeQuery();

            // Step 4: Process the ResultSet object.
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + "," + name + "," + email + "," + country + "," + password);
            }
        } catch (SQLException e) {
            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:
1,Ram,[email protected],US,secret

Reusing a PreparedStatement

Once a PreparedStatement is prepared, it can be reused after execution. You reuse a PreparedStatement by setting new values for the parameters and then execute it again. Here is a simple example:
String sql = "update people set firstname=? , lastname=? where id=?";

PreparedStatement preparedStatement =
        connection.prepareStatement(sql);

preparedStatement.setString(1, "Gary");
preparedStatement.setString(2, "Larson");
preparedStatement.setLong  (3, 123);

int rowsAffected = preparedStatement.executeUpdate();

preparedStatement.setString(1, "Stan");
preparedStatement.setString(2, "Lee");
preparedStatement.setLong  (3, 456);

int rowsAffected = preparedStatement.executeUpdate();
This works for executing queries too, using the executeQuery() method, which returns a ResultSet.

JDBC Statement Interface Examples

          Example to create a table using a Statement interface.
          Example to insert multiple records in a table using Statement interface.
          Example to update a record in a table using Statement interface.
          Example to retrieve records from a table using Statement interface.
          Example to delete a record from a table using a Statement interface.
          Example to insert records in a batch process via Statement interface.
          Example to update records in a batch process via Statement interface.

JDBC PreparedStatement Interface Examples

         Example to insert a record in a table using the PreparedStatement interface.
         Example to update a record in a table using the PreparedStatement interface.
          Example to retrieve records from a table using the PreparedStatement interface.
          Example to pass a list of values to IN clause using PreparedStatement interface.
          Example to insert records in a batch process via PreparedStatement interface.
          Example to update records in a batch process via PreparedStatement interface.

Comments