JDBC PreparedStatement Interface

Using Statement in JDBC you can execute SQL query on the connected DB but there is one limitation with using Statement, it doesn’t give you a chance to parameterize the SQL query so that the same query can be used multiple times by passing different parameters. There is a sub-interface of Statement, the PreparedStatement in JDBC that provides the option to write a parameterized query that can be executed multiple times.

Apart from giving you an option to write dynamic queries there is one more advantage of using JDBC PreparedStatement. In PreparedStatement you pass the SQL statement when it's instance is created so the SQL statement is pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times. Since the query is already compiled when execute is called so performance of PreparedStatement is better.

How to get PreparedStatement Object

You can get a PreparedStatement object by calling prepareStatement(String sql) method of the Connection interface. For example-

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET DEPARTMENT = ? WHERE ID = ?");

Here ‘?’ is a placeholder in parameterized statement. Values for these placeholders are provided through setter methods.

Methods in Java PreparedStatement

Some of the frequently used methods of the PreparedStatement interface are as follows-

execute()- Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement. This method returns a boolean; true if the first result is a ResultSet object; false if it is an update count or there are no results.
executeQuery()- Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
executeUpdate()- Executes the SQL statement in this PreparedStatement object which may be a DML statement like INSERT, UPDATE or DELETE or an SQL statement that returns nothing, such as an SQL DDL statement (Create, Drop).
addBatch()- Adds a set of parameters to this PreparedStatement object's batch of commands.

There are many setter methods too for setting values of different types of parameters, some of them are listed here-

setInt(int parameterIndex, int x)- Sets the parameter at the given index to the given Java int value.
setLong(int parameterIndex, long x)- Sets the parameter at the given index to the given Java long value.
setShort(int parameterIndex, short x)- Sets the parameter at the given index to the given Java short value.
setString(int parameterIndex, String x)- Sets the parameter at the given index to the given Java String value.
setDate(int parameterIndex, Date x)- Sets the parameter at the given index to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.
setTime(int parameterIndex, Time x)- Sets the parameter at the given index to the given java.sql.Time value.
setTimestamp(int parameterIndex, Timestamp x)- Sets the parameter at the given index to the given java.sql.Timestamp value.

PreparedStatement Java example

Here is an example showing the usage of JDBC PreparedStatement for CRUD operation. DB used is MySql, schema is knpcode and table used is EMPLOYEE with columns as id, FIRST_NAME, LAST_NAME and DEPARTMENT note that id is auto-generated so that it is not sent through the query.

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

public class PreparedStatementDemo {

  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Load driver
      Class.forName("com.mysql.cj.jdbc.Driver");
      // connection object
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", 
        "root", "admin");
      // create Statement object
      PreparedStatementDemo preparedStatement = new PreparedStatementDemo();
      preparedStatement.insertEmployee(connection, "Ranjeet", "Sharma", "Police");
      preparedStatement.updateEmployee(connection, 16, "Finance");
      preparedStatement.deleteEmployee(connection, 22);
      preparedStatement.getEmployeeById(connection, 16);
    } catch (ClassNotFoundException | SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){         
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } 
    }	      	     
  }

  private void insertEmployee(Connection connection, String fName, String lName, String dept) throws SQLException{
    String insertSQL = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, DEPARTMENT) values (?, ?, ?)";
    PreparedStatement prepStmt = null;
    try {
      prepStmt = connection.prepareStatement(insertSQL);
      prepStmt.setString(1, fName);
      prepStmt.setString(2, lName);
      prepStmt.setString(3, dept);
      int count = prepStmt.executeUpdate();
      System.out.println("Number of records inserted- " + count);
    }finally{
      if(prepStmt != null){
        prepStmt.close();
      }
    }
  }
	
  private void updateEmployee(Connection connection, int id, String department) throws SQLException{
    String updateSQL = "UPDATE EMPLOYEE SET DEPARTMENT = ? WHERE ID = ?";
    PreparedStatement prepStmt = null;
    try {
      prepStmt = connection.prepareStatement(updateSQL);
      prepStmt.setString(1, department);
      prepStmt.setInt(2, id);
      int count = prepStmt.executeUpdate();
      System.out.println("Number of records updated " + count);
    }finally{
      if(prepStmt != null){
        prepStmt.close();
      }
    }
  }
	 
  private void deleteEmployee(Connection connection, int id) throws SQLException {
    String deleteSQL = "DELETE FROM EMPLOYEE WHERE id = ?";
    PreparedStatement prepStmt = null;
    try {
      prepStmt = connection.prepareStatement(deleteSQL);
      prepStmt.setInt(1, id);
      int count = prepStmt.executeUpdate();
      System.out.println("Number of records deleted " + count);
    }finally{
      if(prepStmt != null){
        prepStmt.close();
      }
    }
  }
  private void getEmployeeById(Connection connection, int id) throws SQLException{
    String selectSQL = "SELECT * FROM EMPLOYEE WHERE id = ?";
    PreparedStatement prepStmt = null;
    try {
      prepStmt = connection.prepareStatement(selectSQL);
      prepStmt.setInt(1, id);
      ResultSet rs = prepStmt.executeQuery();
      while(rs.next()){
        System.out.println("id: " + rs.getInt("id"));
        System.out.println("First Name: " + rs.getString("FIRST_NAME"));
        System.out.println("Last Name: " + rs.getString("LAST_NAME"));
        System.out.println("Department: " + rs.getString("DEPARTMENT"));
      }
    }finally{
      if(prepStmt != null){
        prepStmt.close();
      }
    }
  }
}

That's all for the topic JDBC PreparedStatement Interface. If something is missing or you have something to share about the topic please write a comment.

You may also like

KnpCode

August 16, 2022