PreparedStatement Interface in Java-JDBC

In the post Statement interface in Java we have already seen how you can create a Statement using connection object and execute SQL statements. One problem though is that Statement interface is used to execute static SQL statements with no option to send parameters. Though you can always use a StringBuilder to append parameters to the SQL and use it with Statement but there is a better option provided by JDBC itself in the form of PreparedStatement which is a sub-interface of Statement. In this post we'll see how to use PreparedStatement in Java with examples.

Obtaining JDBC PreparedStatement object

You can get the PreparedStatement object by calling the prepareStatement method of the Connection class.

PreparedStatement preparedStatement = connection.prepareStatement(sql);

Advantages of using PreparedStatement in JDBC

As stated above one advantage of PreparedStatement is that you can use PreparedStatement object for SQL statement with or without parameters. The advantage of using SQL statement with parameters is that you can use the same statement and supply it with different parameter values each time you execute it.

That brings us to the second advantage of PreparedStatement, it is more efficient. Unlike Statement object, PreparedStatement is given the SQL statement when it is created. So the SQL is sent to the DB right away where it is already compiled. When you come to execute() method to actually execute the SQL that SQL is pre-compiled making it more efficient for repeated executions.

Java PreparedStatement example

Let’s see an example using PreparedStatement in JDBC. DB used here is MySql, schema is netjs and table is employee with columns id, age and name, where id is auto-generated.

In the code there are methods for insert, update, delete and select from the table.

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

public class JDBCPrepStmt {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
    
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                         "root", "admin");
      JDBCPrepStmt prep = new JDBCPrepStmt();
      prep.insertEmployee(connection, "Kate", 24);
      prep.updateEmployee(connection, 22, 30);
      prep.displayEmployee(connection, 22);
    
      //prep.deleteEmployee(connection, 24);
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //closing connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } // if condition
    }// finally
  }
 
  // Method to insert
  private void insertEmployee(Connection connection, String name, int age) 
        throws SQLException{
    String insertSQL = "Insert into employee (name, age) values (?, ?)";
    PreparedStatement prepStmt = null;
    try {
      prepStmt = connection.prepareStatement(insertSQL);
      prepStmt.setString(1, name);
      prepStmt.setInt(2, age);
      int count = prepStmt.executeUpdate();
      System.out.println("Count of rows inserted " + count);
    }finally{
      if(prepStmt != null){
        prepStmt.close();
      }
    }
  }
 
 // Method to update
 private void updateEmployee(Connection connection, int id, int age) throws SQLException{
  String updateSQL = "Update employee set age = ? where id = ?";
  PreparedStatement prepStmt = null;
  try {
   prepStmt = connection.prepareStatement(updateSQL);
   prepStmt.setInt(1, age);
   prepStmt.setInt(2, id);
   int count = prepStmt.executeUpdate();
   System.out.println("Count of rows updated " + count);
  }finally{
    if(prepStmt != null){
     prepStmt.close();
    }
  }
 }
 
 // Method to delete
 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("Count of rows deleted " + count);
  }finally{
    if(prepStmt != null){
     prepStmt.close();
    }
  }
 }
 

 // Method to retrieve
 private void displayEmployee(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") + " Name : " 
                   + rs.getString("name") + " Age : " + rs.getInt("age")); 
   }
  }finally{
    if(prepStmt != null){
     prepStmt.close();
    }
  }
 }
 
}

Points to note here

Taking this example as reference let’s go through some of the points you will have to keep in mind when using PreparedStatement in JDBC.

Parameterized statement– In the example you can see that all the SQL statements are parameterized and ‘?’ is used as a placeholder in parameterized statements.
As example-
```
String insertSQL = "Insert into employee (name, age) values (?, ?)";
```
Setter methods– Values for these placeholders are provided through setter methods. PreparedStatement has various setter methods for different data types i.e. setInt(), setString(), setDate() etc.
General form of the setter method is setXXX(int parameterIndex, value)

Here parameterIndex is the index of the parameter in the statement, index starts from 1.

As example-
```
String insertSQL = "Insert into employee (name, age) values (?, ?)";
```
For this sql where the first parameter is String (name) and second parameter is of type int (age), you need to set the parameters on the PreparedStatement object as follows -
```
prepStmt.setString(1, name);
prepStmt.setInt(2, age);
```
Executing PreparedStatement objects– You can use execute methods for executing the queries.
1. boolean execute()- Executes the SQL statement in this PreparedStatement object, (it can be any kind of SQL query), which may return multiple results.
  Returns a boolean which is true if the first result is a ResultSet object; false if it is an update count or there are no results.
2. ResultSet executeQuery(String sql)- Executes the SQL statement in this PreparedStatement object, which returns a single ResultSet object. If you want to execute a Select SQL query which returns results you should use this method.
3. int executeUpdate()- Executes the SQL statement in this PreparedStatement object, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
  Returns an int denoting either the row count for the rows that are inserted, deleted, updated or returns 0 if nothing is returned.

That's all for this topic PreparedStatement Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page

Related Topics

You may also like-

Tech Tutorials

Saturday, November 21, 2020