JDBC PreparedStatement

PreparedStatement interface inherits from java.sql.Statement and differs from it in two ways:

1. Instances of java.sql.PreparedStatement contain an SQL statement that has already been compiled. This is what makes a statement “prepared.”

2. The SQL statement contained in a java.sql.PreparedStatement object may have one or more IN parameters. An IN parameter is a parameter whose value is not specified when the SQL statement is created. Instead the statement has a question mark (“?“) as a placeholder for each IN parameter. A value for each question mark must be supplied by the appropriate setXXX() method before the statement is executed.

As because PreparedStatement objects are pre-compiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency.

Being a subclass of Statement, PreparedStatement inherits all the functionality of Statement. In addition, it adds a whole set of methods which are needed for setting the values to be sent to the database in place of the placeholders for IN parameters. Also, the three methods execute(), executeQuery(), and executeUpdate() are modified so that they take no argument. The Statement forms of these methods (the forms that take an SQL statement parameter) should never be used with a PreparedStatement object.

The methods to create a PreparedStatement object are as follows:

Creating PreparedStatement Objects

The first argument in each method is a SQL string. The SQL string can have placeholders (variables) that represent data that will be set at a later time. The placeholder is represented by the question mark symbol (?). Let’s take the SQL command presented above and change it so that it could be used as part of a prepared statement:

insert into students values(?,?)

Placeholders are referred to by their index in the SQL command. Placeholders are consecutively indexed starting with index 1 at the beginning of the SQL string. When the SQL in the prepared statement is sent to the database, the database compiles the SQL. Before we execute a prepared statement, we must set the placeholders with data. The driver sends the data to the database when the prepared statement is executed. Then, the database sets the variables with the data, and executes the SQL.

Using a Prepared Statement

After creating the PreparedStatement object, but before the SQL command can be executed, the placeholders in the command must be set. The PreparedStatement interface defines various methods for doing this. We can also use the PreparedStatement object for setting null values in a table.

The other advantage of using a prepared statement is that the values we set do not need to be reset every time we want to execute the SQL command; that is, the values we set are persistent. Finally, we can perform batch updating with a prepared statement.

Setting Placeholders

The methods for setting placeholders take the form of setXXX() where XXX is a Java type name. Here is the method for setting a String:

void setString (int parameterIndex, String x)

There are other setXXX() methods available, one for each Java primitive, and methods for many object types such as Date, or BigDecimal. We should consult the JavaDoc for information on all the available methods.

The first argument in the setXXX() method will be the index of the placeholder in the SQL command. Each placeholder is referenced by its position in the SQL string. Starting from the beginning of the string, the first placeholder is at index 1, the second at 2, and so on.

The second argument is the data value that replaces the placeholder. So, using the same SQL INSERT from above, here’s how the data values would be set:

String sql = "insert into students values(?, ?)"; 
             // Placeholder index:        1  2  

//creating the PreparedStatement object
PreparedStatement pstmt = con.prepareStatement(sql);

pstmt.setInt(1, 60); //1 indicates the 1st parameter in the query 
pstmt.setString(2, "Ranjan Mustafi"); //2 indicates the 2nd parameter in the query

//returns the number of data rows affected after insert operation
ps.executeUpdate();

If we do not set all the parameters before executing the SQL, the driver will throw a SQLException. When the values have all been set, we execute the SQL command by calling the executeUpdate() method as shown above. If we call any of the executeQuery(String), executeUpdate(String), or execute(String) methods, the driver will throw a SQLException. We must call the “no parameter” versions of those methods with a prepared statement.

Coding Example 1

The example below shows the use of PreparedStatement using “insert into” query.

prepare-stmt.jsp

<%@ page import="java.sql.*" %>
<%!
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String url = "jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true";
    String user = "root";
    String pass = "system";
    String sql = null;
    int count;
%>
<%
    try {
        //loading the MySQL driver
        Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        
        //creating the Connection object for MySQL
        con = DriverManager.getConnection(url, user, pass);
        if (con != null) {
                out.println("Successfully connected to " + "MySQL server using TCP/IP..." + "<br><br>");
        }
        
        //parameterized query statement
        sql = "insert into students values(?,?)"; 
        
        //creating the PreparedStatement object
        PreparedStatement pstmt = con.prepareStatement(sql);
        
        pstmt.setInt(1, 60); //1 indicates the 1st parameter in the query  
        pstmt.setString(2, "Ranjan Mustafi");  //2 indicates the 2nd parameter in the query  
        
        //returns the number of data rows affected after insert operation
        count = pstmt.executeUpdate(); //don't use any parameter here
        
        if(count==1)
            out.println("Successfully added one record.." + "<br><br>");
        else
            out.println("Test unsuccessful!" + "<br>");
    } 
    catch (Exception e) {
        out.println("Exception: " + e.getMessage());
    } 
    finally {
        try {
            if (con != null) {
                con.close();
            }
        } 
        catch (SQLException e) {  }
    }
%>

Output is given below:

JDBC PreparedStatement – Batch Update

A JDBC batch update is a batch of updates grouped together, and sent to the database in one batch, rather than sending the updates one by one.

Sending a batch of updates to the database in one go, is faster than sending them one-by-one, waiting for each one to finish. There is less network traffic involved in sending one batch of updates (involves only 1 round-trip), and the database might be able to execute some of the updates in parallel. The speed up compared to executing the updates one-by-one, can be quite big.

We can batch both SQL inserts, updates and deletes. It does not make sense to batch select statements.

Coding Example 2

The example below shows the use of PreparedStatement for batch updates.

prepare-batch.jsp

<%@ page import="java.sql.*" %>
<%!
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String url = "jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true";
    String user = "root";
    String pass = "system";
    String sql = null;
%>
<%
    try {
        //loading the MySQL driver
        Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        
        //creating the Connection object for MySQL
        con = DriverManager.getConnection(url, user, pass);
        if (con != null) {
                out.println("Successfully connected to " + "MySQL server using TCP/IP..." + "<br><br>");
        }
        
        //parameterized query statement
        sql = "update students set name=? where roll=?"; 
                                                
        //creating the PreparedStatement object
        PreparedStatement pstmt = con.prepareStatement(sql);
        
        pstmt.setString(1, "Shampa Paul"); //1 indicates 'name' here
        pstmt.setInt(2, 20); //2 indicates 'roll' here
        pstmt.addBatch(); //adds the parameter values to the batch internally
                
        pstmt.setString(1, "Krishna Nag");
        pstmt.setInt(2, 30);   
        pstmt.addBatch();
        
        //executeBatch() executes all the batch updates and returns an array of update counts on success
        int[] affectedRecords = pstmt.executeBatch(); 
        out.println("Successfully updated " + affectedRecords.length + " records.." + "<br><br>");
        
    } 
    catch (Exception e) {
        out.println("Exception: " + e.getMessage());
    } 
    finally {
        try {
            if (con != null) {
                con.close();
            }
        } 
        catch (SQLException e) {  }
    }
%>

Output is given below: