JDBC using PreparedStatement

JDBC using PreparedStatement

In this tutorial, we are going to discuss about JDBC using PreparedStatement. Using PreparedStatement in JDBC provides several advantages over plain Statement objects, particularly in terms of security, performance, and code maintainability.

Certainly! JDBC (Java Database Connectivity) is a Java API for executing SQL statements. It allows Java programs to interact with databases. JDBC using PreparedStatement provides a safer and more efficient way to execute parameterized SQL queries.

JDBC using PreparedStatement

Step 1 : Prepare SQLQuery either with parameters or without parameters.

E.g

insert into employees values(100,'Ashok Kumar',40000,'Bhimavaram');

insert into employees values(?, ?, ?, ?);

Here ? indicated Positional Parameter OR Place Holder OR IN Parameter

Step 2 : Create PreparedStatement object with our sql query.

PreparedStatement pst = con.prepareStatement(sqlQuery);

At this line only query will be compiled.

Step 3 : If the query is parameterized query then we have to set input values to these parameters by using corresponding setter methods. We have to consider these positional parameters from left to right and these are 1 index based. i.e., index of first positional parameter is 1 but not zero.

pst.setInt(1,100);
pst.setString(2,"Ashok Kumar");
pst.setDouble(3,40000);
pst.setString(4,"Bhimavaram");

Note

Before executing the query, for every positional parameter we have to provide input values otherwise we will get SQLException.

Step 4 : Execute SQL Query: PreparedStatement is the child interface of Statement and hence all methods of Statement interface are bydefault available to the PreparedStatement.Hence we can use same methods to execute sql query.

  1. executeQuery()
  2. executeUpdate()
  3. execute()

Note

  • We can execute same parameterized query multiple times with different sets of input values. In this case query will be compiled only once and we can execute multiple times.
  • We can use ? only in the place of input values and we cannot use in the place of SQL keywords,table names and column names.
Static Query vs Dynamic Query

The SQL query without positional parameter(?) is called static query.

E.g

delete from employees where ename = 'Ashok Kumar'.

The SQL query with positional parameter(?) is called dynamic query.

E.g

select * from employees where esal > ?

E.g

import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class PreparedStatementTest { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "delete from emp where ename = ?"; 
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      PreparedStatement pst = con.prepareStatement(sqlQuery);
      pst.setString(1,"Ashok Kumar");
      int updateCount = st.executeUpdate(sqlQuery);
      System.out.println("The number of rows deleted : "+ updateCount);

      System.out.println("Reusing PreparedStatement to delete one more record.");
      pst.setString(1,"Vinod Kumar");
      int updateCount1 = st.executeUpdate(sqlQuery);
      System.out.println("The number of rows deleted : "+ updateCount1);
      con.close();
   }
}

E.g2

import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class PreparedStatementTest { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "insert into emp values(?,?,?,?"; 
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      PreparedStatement pst = con.prepareStatement(sqlQuery);
      Scanner sc = new Scanner(System.in);
      while(true) {
         System.out.println("Employee Number:");
         int eno=sc.nextInt();
         System.out.println("Employee Name:"); 
         String ename=sc.next(); 
         System.out.println("Employee Sal:"); 
         double esal=sc.nextDouble();
         System.out.println("Employee Address:");
         String eaddr=sc.next(); 
         pst.setInt(1,eno);
         pst.setString(2,ename);
         pst.setDouble(3,esal); 
         pst.setString(4,eaddr);
         pst.executeUpdate(sqlQuery);
         System.out.println("Record Inserted Successfully"); 
         System.out.println("Do U want to Insert one more record[Yes/No]:"); 
         String option = sc.next(); 
         if(option.equalsIgnoreCase("No")) {
            break;
         }
      }
      con.close();
   }
}

JDBC using PreparedStatement helps prevent SQL injection attacks and improves performance by allowing the database to reuse the execution plan for the query.

That’s all about the JDBC using PreparedStatement. If you have any queries or feedback, please write us email at contact@waytoeasylearn.com. Enjoy learning, Enjoy Java.!!

JDBC using PreparedStatement
Scroll to top