Recent Posts

Tuesday, 13 November 2018

Steps to develop JDBC Application by using PreparedStatement

Step 1 : Prepare SQLQuery either with parameters or without parameters.
Eg: 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.
Eg: delete from employees where ename = 'Ashok Kumar'.
     The sql query with positional parameter(?) is called dynamic query.
Eg: select * from employees where esal > ?.

Example Program for PreparedStatement
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();
   }
}
Example 2
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();
   }
}

Next Tutorial : Advantages and Disadvantages of PreparedStatement

Previous Tutorial : About PreparedStatement

No comments:

Post a Comment