Prepared Statement in JDBC

Back to: Java Tutorials For Beginners and Professionals

Prepared Statement in JDBC with Examples

In this article, I am going to discuss the Prepared Statement in JDBC with Examples. Please read our previous article where we discussed JDBC ResultSet in detail with Examples. At the end of this article, you will understand the following pointers in detail.

1. What happens in the Background within the database when an SQL Statement is Submitted to it?
2. What is the Limitation of java.sql.Statement?
3. Prepared Statement in JDBC
4. How the performance will be improved using PreparedStatement?
5. How to use PreparedStatement in Java?
6. What are the Positional Parameters?
7. Why Close the Connection object in a JDBC application?
8. Why Close Statement or PreparedStatement object in a JDBC application?
9. Why Close the ResultSet object in a JDBC application?

What happens in the Background within the database when an SQL Statement is Submitted to it?

When the DBMS receives an SQL statement it does the following things in order

1. Query compilation (it checks the syntax of whether the SQL statement is syntactically correct or not).
2. Query plan generation and caching (it selects the best way to execute the query means it selects the query optimization technique to improve the performance. Caching means it stores the query for later uses).
3. Query execution (it executes the query in the best way that is selected previously (in the previous step)).

What is the Limitation of java.sql.Statement?

The statement object is used to submit any kind of SQL statement from the JDBC to the DBMS. If the same query is required to be submitted repeatedly to the DBMS, we shouldn’t use the Statement object for performance reasons i.e. Statement object is used only for the one-time submission of any SQL statement. Java.sql.PreparedStatement object which is a child of statement object overcomes the limitations of Statement object.

Prepared Statement in JDBC

PreparedStatement is another statement object which is also used to execute all SQL select and non-select statements. The main advantage of Prepared Statement is, that it will improve the performance when we want to execute the same query multiple times compared to Statement object but if we send different queries using PreparedStatement then there is no difference between PreparedStatement and Statement but in this scenario Statement object is preferable one.

How the performance will be improved using PreparedStatement?

When we send a query to the database server then the database server will perform the following operations for every new query each time.

1. Query Tokenization: It means it will break our query into multiple tokens and the collection of tokens will be given for parsing.
2. Query Parsing: It means it will check all the tokens of the query whether they are valid database keywords or not, if valid it will convert the query into database understandable query format otherwise gives an error or exception.
3. Query Optimization: It means it will check all the algorithms and attach the appropriate algorithm with our query which takes less time and less memory.
4. Query Execution: Once optimization is completed then the query will be executed and the result will be sent to the Java program.

Conclusion :

So if we use the Statement object then the database server will perform the preceding four steps every time for every query. We may send the same query multiple times or different queries multiple times. But if we use the PrepredStatement object and sending the same query several times then the database server will perform the preceding four steps only for the first time but the second time onwards same query will be sent without doing any preceding four steps so that performance will be improved in PreparedStatement. But if we send different queries multiple times using the PreparedStatement object then the database server will perform the preceding four steps every time for every new query. So, in this case, no performance will be improved.

How to use PreparedStatement in Java?

Creating a prepared statement object: By calling the prepareStatement() method on the connection object, java.sql.PreparedStatement object is created. For example

PreparedStatement ps = con.prepareStatement(“INSERT INTO VALUES(?,?,?)”);

The Object-oriented representation of a pre-compiled or already prepared SQL statement is nothing but a PreparedStatement object.

Binding of parameters:

Supplying values to the placeholders (question marks) is nothing but binding of parameters. The binding of parameters is done by calling setXXX() methods on the PreparedStatement object. These setter methods take two arguments. The first argument is the position of the placeholders in the query and the second argument is the value that we want to supply. The setXXX methods are the mirror methods (opposite methods) of the getXXX methods of ResultSet. For example:
ps.setInt(1, 1001);
ps.setString(2. “pranaya”);
ps.setFloat(3.2345);

Submitting the SQL statement:

By calling executeUpdate() or executeQuery() method on the PreparedStatement object SQL statement is submitted to the DBMS. Let’s see the architecture for a better understanding:

Example:

First, create the following Account table in the Oracle database.

CREATE TABLE ACCOUNT (accnonumber(8) primary key, name varchar2(12), balance number(8,2));

Insert Operation using Prepared Statement:

import java.sql.*;
class AccountStoringApplication 
{
    public static void main (String[]args) throws ClassNotFoundException, SQLException 
    {  
        Class.forName ("oracle.jdbc.driver.OracleDriver");    
        Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe", "System", "pranaya");    
        PreparedStatement ps = con.prepareStatement ("INSERT INTO ACCOUNT VALUES(?,?,?,?)");
    
        ps.setInt (1, 1001);
        ps.setString (2, "ANURAG");
        ps.setFloat (3, 2345);
        ps.setString (4, "puri");
    
        int c = ps.executeUpdate ();
        System.out.println (c + "account created successfully");
     
        ps.setInt (1, 1002);    
        ps.setString (2, "PRANAYA");    
        ps.setFloat (3, 2345);   
        ps.setString (4, "JAJPUR");
     
        c = ps.executeUpdate ();
        System.out.println (c + " MORE account created successfully");
     
        ps.close ();
        con.close ();
    } 
}

Update Operation using Prepared Statement:

SQL Statement: UPDATE ACCOUNT SET BALANCE = BALANCE + 500 WHERE ACCNO = 1001;

Instead of writing this SQL statement, write this SQL statement with place holder (?) as shown in the below program.

import java.sql.*;
class AccountUpdatingApplication 
{
    public static void main (String[]args) throws ClassNotFoundException, SQLException 
    {  
        Class.forName ("oracle.jdbc.driver.OracleDriver");    
        Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe", "System", "pranaya");    
        PrepaaredStatement ps = con.prepareStatement("UPDATE ACCOUNT SET BALANCE = BALANCE + ? WHERE ACCNO = ?");
        
        ps.setFloat(1,500);
        ps.setInt(2,1001);
        ps.executeUpdate();
        
        ps.setFloat(1,500);
        ps.setInt(2,1002);
        ps.executeUpdate();
     
        ps.close ();
        con.close ();
    } 
}

Delete Operation using Prepared Statement in Java:

SQL Statement: DELETE FROM ACCOUNT WHERE ACCNO = 1001;

Instead of writing this SQL statement, write this SQL statement with a placeholder (?) as shown in the below program.

import java.sql.*;
class AccountClosingApplication 
{
    public static void main (String[]args) throws ClassNotFoundException, SQLException 
    {  
        Class.forName ("oracle.jdbc.driver.OracleDriver");    
        Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe", "System", "pranaya");    
        PrepaaredStatement ps = con.prepareStatement("DELETE FROM ACCOUNT WHERE ACCNO = ?");
        ps.setInt(1,1001);
        ps.executeUpdate();
        ps.setInt(2,1001);
        ps.executeUpdate();
     
        ps.close ();
        con.close ();
    } 
}

Retrieve Operation using Prepared Statement in Java:

SQL Statement: SELECT BALANCE FROM ACCOUNT WHERE ACCNO = 1001;

Instead of writing this SQL statement, write this SQL statement with a placeholder (?) as shown in the below program.

import java.sql.*;
import java.util.*;
class AccountDetailsInformation 
{  
    public static void main (String[]args) throws ClassNotFoundException, SQLException 
    {
        Class.forName ("oracle.jdbc.driver.OracleDriver"); 
        Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:xe", "System", "pranaya");  
        PreparedStatement ps = con.prepareStatement ("select balance from account where accno = ?");
    
        ps.setInt (1, 1001);
        ResultSet rs = ps.executeQuery ();
        if (rs.next ())
        {
            System.out.println ("A/C balance is Rs." + rs.getFloat (1));
        }
        else
            System.out.println ("A/C doesn't Exist");
    
        rs.close ();
        ps.setInt (1, 1004);
    
        ResultSet rs1 = ps.executeQuery ();   
        if (rs1.next ())
        {
            System.out.println ("A/C balance is Rs." + rs1.getFloat (1));
        }
        else
            System.out.println ("A/C doesn't Exist");
    
        rs1.close ();
        ps.close (); 
        con.close ();
    }
}

What are the Positional Parameters?

When we use PreparedStatement it should contain the query with positional parameters. Positional Parameters are specified using the “?” symbol. Positional Parameters are used to supply the values to the query. To supply the values for positional parameters we have to use the following setter methods:

Why Close the Connection object in a JDBC application?

To release the networking resources of the JDBC client held (with) by the database server, we need to close the connection.

Why Close Statement or PreparedStatement object in a JDBC application?

To release the JDBC resources of the JDBC client held (with) by the database server, we need to close the Statement/PreparedStatement.

Why Close the ResultSet object in a JDBC application?

To release the data of the JDBC client held (with) by the database server, we need to close ResultSet.

In the next article, I am going to discuss Transaction Management in JDBC. Here, in this article, I try to explain the Prepared Statement in JDBC with Examples. I hope you enjoy this Prepared Statement in the JDBC article.