JDBC PreparedStatement Interface

PreparedStatement is the object of a JDBC driver software supplied Java class that implements java.sql.PreparedStatement(I). The java.sql.PreparedStatement(I) is extended from java.sql.Statement(I), and it is used for precompiling SQL statements that might contain input parameters.

Using java.sql.Statement(I) object, the same SQL query goes to database software for multiple times from Java application, and the same SQL query will be parsed in Database software for multiple times. It reduces the performance of the application and takes a longer time. We can avoid this problem by using “pre-compiled SQL query”

The SQL query that goes to database software without input values and becomes parsed or compiled SQL query in database software irrespective of whether it will be executed or not is called pre-compiled SQL query. PreparedStatement object of the Java application represents this pre-compiled SQL query of database software. We can use that object to set input values to SQL query, to execute SQL query and to fetch the output of SQL query for one or multiple times. PreparedStatement object is good to execute the same SQL query for multiple times either with input values or without input values.

Advantages of JDBC PreparedStatement over Statement

• It allows working with a pre-compiled/dynamic SQL query. PreparedStatement object can deal with both static and dynamic SQL queries because the pre-compiled SQL query can be a static query with or without inputs or can be a dynamic query with parameters.
• It allows specifying in parameter/place holders/place resolvers (?) in the SQL query.
• PreparedStatement allows setting input values to SQL queries in Java-style without any kind of conversions. The setXxx() method of PreparedStatement object converts the given Java inputs to SQL inputs internally. So, programmers need not give the same conversion explicitly.
• It doesn’t raise the SQL injection problem.
• It is suitable for inserting date values in the database table by collecting date values from end-user in different patterns.
• JDBC PreparedStatement is suitable for inserting large objects like images, audio, video, files.
• It reduces network traffic between Java application and database software while executing the same query multiple times with either the same values or different values.

Important Methods of PreparedStatement

Methods to set the parameter values,

• setShort(int parameterIndex, short x)
• setInt(int parameterIndex, int x)
• setLong(int parameterIndex, long x)
• setFloat(int parameterIndex, float x)
• setDouble(int parameterIndex, double x)
• setString(int parameterIndex, String x)
• setNull(int parameterIndex, int sqlType)
• setBoolean(int parameterIndex, boolean x)
• setByte(int parameterIndex, byte x)

• setTime(int parameterIndex, Time x):- It sets the designated parameter to the given java.sql.Time value.
• setDate(int parameterIndex, Date x):- It sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.
• setClob(int parameterIndex, Clob x) Sets the designated parameter to the given java.sql.Clob object.
• setBlob(int parameterIndex, Blob x) Sets the designated parameter to the given java.sql.Blob object.

Methods to execute the SQL query,

Method	Use
executeQuery()	It is used to execute the select queries/statement and returns the ResultSet object.
executeUpdate()	It is used to execute the non-select queries (like insert, update, delete), and returns int values.
execute()	It executes all kind of SQL statement in the PreparedStatement object, and returns boolean value.

In Java Documentation you can get all the methods of PreparedStatement.

JDBC PreparedStatement Example

Now, let us develop a simple JDBC program using PreparedStatement. We will insert some records into the table using the PreparedStatement object. For this, we need a table. Here we are working with the Oracle database, but you can work with any database with the required information (jar file, URL, username, password).

Create a table,

create table student
(
  sno number(5) primary key,
  sname varchar2(15),
  sadd varchar2(15),
  avg float
);

Task:- Write a JDBC application to gather details of the N number of students from the end-user and insert them in the database table as records using JDBC PreparedStatement.

Sample Code to Work with PreparedStatement

1. Prepare SQL query with positional parameter/place holder/place resolver (?)

// prepare SQL query
private static final String QUERY =
 "INSERT INTO STUDENT VALUES(?, ?, ?, ?)";

Here ? symbol represents the parameter for which values can be set later. The first ? represents the first column sno, the second ? represents sname, the third ? represents sadd, and fourth ? represents the fourth column avg.

The SQL query will remain the same for the entire application so, we generally take SQL query as string constants at the top of the class. The other reasons are:-
a) To identify them easily and to modify them easily.
b) To see them un-modifiable in other parts of the code,
c) To access them in other parts of code without an object.

The static final variables are also called as constants. Generally, these variables name will be taken in uppercase letters so to differentiate them from other variables.

2. Send SQL query to Database software.

Make that SQL query as pre-compiled SQL query in database software and get PreparedStatement object representing the SQL query.

PreparedStatement ps = 
     con.prepareStatement(query);

The pre-compiled SQL query is ready and now, it will go to database only for execution.

3. Set input values to pre-compiled SQL query parameters(?) using setXxx(-) methods.

Note that here index value start from 1 not from 0.

ps.setInt(1,1001); // sno
ps.setString(2, “rajesh”); // sname
ps.setString(3, “hyd”); // sadd
ps.setFloat(4, 75); // avg

4. Execute the pre-compiled SQL query in database software,

int result = ps.executeUpdate();

5. Process the result,

if(result == 0) 
System.out.println(“Not inserted”);
else
System.out.println(“record inserted”);

6. Execute the above pre-compiled SQL query for multiple times either with same or difference values (repeat 3 to 5 steps)

7. Close the JDBC objects

ps.close();
con.close();

JDBC Program Using PreparedStatement

package com.know.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class InsertTest {

   // values specific to each database
   private static final String URL = 
      "jdbc:oracle:thin:@localhost:1521:knowprogram";
   private static final String USERNAME = "scott";
   private static final String PASSWORD = "tiger"; 
   
   // SQL Query
   private static final String INSERT_STUDENT_QUERY =
                "INSERT INTO STUDENT VALUES (?,?,?,?)";

   // main method
   public static void main(String[] args ) {

     // declare variables
     Scanner scan = null;
     int n = 0;
     int sno = 0;
     String sname = null, sadd = null;
     float avg = 0.0f;
     String query = null;
     Connection con = null;
     PreparedStatement ps = null;
     int result = 0;

     try {
        // create Scanner class object
        scan = new Scanner(System.in);
        
        // take number of students
        if(scan != null) {
            System.out.print("Enter Number of students: ");
            n = scan.nextInt();
        }

        // establish the connection
        con = DriverManager.getConnection(
                    URL, USERNAME, PASSWORD);

        // compile SQL query and store it in
        // PreparedStatement object
        if(con != null) {
           ps = con.prepareStatement(INSERT_STUDENT_QUERY);
        }
        
        if(scan != null && ps != null) {
           for(int i=0; i<n; i++) {

              // read input values
              System.out.println("\nEnter Student-"+(i+1)+" details,");
              System.out.print("Number: ");
              sno = scan.nextInt();
              System.out.print("Name: ");
              sname = scan.next();
              System.out.print("Address: ");
              sadd = scan.next();
              System.out.print("Average: ");
              avg = scan.nextFloat();

              // set parameters values
              ps.setInt(1, sno);
              ps.setString(2, sname);
              ps.setString(3, sadd);
              ps.setFloat(4, avg);

              // execute the query
              result = ps.executeUpdate();
           }
        }
        
        // process the result
        if(result == 0) {
           System.out.println("\nRecords insertion failed");
        } else {
           System.out.println("\nRecords inserted successfully.");
        }

     } catch(SQLException se) {
        se.printStackTrace();
     } catch(Exception e) {
        e.printStackTrace();
     } // try-catch block 

     finally {
        // close JDBC objects
        try {
           if(ps != null) ps.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(scan != null) scan.close();
        } catch(Exception e) {
           e.printStackTrace();
        }
     } // finally
   } // main()
} // class

Output:-

Enter Number of students: 2

Enter Student-1 details,
Number: 1000
Name: Alex
Address: Boise
Average: 80

Enter Student-2 details,
Number: 1005
Name: Amelia
Address: Washington
Average: 59

Records inserted successfully.

Also learn another JDBC programs using PreparedStatement:-

• Create a table using the JDBC program
• JDBC program to insert the record into a given table
• JDBC program to update records
• Select and display records of table based on given conditions
• Create Database in MySQL

Limitation of PreparedStatement

=> Using one simple statement object we can execute different types of SQL queries but one after another.
=> Using one PreparedStatement object we can execute only one type of query for which it is confined. One PreparedStatement can have only one compiled query at a time. To execute multiple different types of queries we need to take multiple prepared statement objects.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search