Execute query using PreparedStatement in DB2 with example code in java

       Some times you may need to execute the same SQL statement many times with different parameters (input values). In this situation the best option is to use a PreparedStatement object. PreparedStatement in java is an interface that define the methods and properties that enable you to send SQL statements and receive data from database server. JDBC Drivers developed by database vendors provide classes that implement the PreparedStatement interface. Without a driver you cannot create objects on this interface. 

      As PreparedStatement interface extends Statement, methods of Statement objects such as execute(), executeQuery(), executeUpdate(), and executeBatch() work with the PreparedStatement object. But these methods are implemented to take input the parameters also. To bind values to input (IN) parameters, you can use the setXXX() methods where XXX represents the Java data type of the value. setXXX is used to convert the Java data type to the appropriate SQL data type for your target database. 

Use of the above methods are as follows

1. executeUpdate() is used to execute DML Statements like INSERT, UPDATE, DELETE, or DDL SQL statements

2. excuteQuery() is used to execute SELECT statements to query database.

3. excute() is used to process any DDL or DML statements

4. executeBatch() is used execute group of SQL statements (DDL, DML statements)

The following code snippet describes how the methods executeUpdate(), setInt(), setString() are used with PreparedStatement object. 

 
 String strSql  = "UPDATE student  SET MobileNo = ?  WHERE studentNo = ?"; 
       PreparedStatement pstmt = conn.prepareStatement(strSql);
       pstmt.setString(1,"9876543579");
       pstmt.setInt(2,5321);
       pstmt.executeUpdate(); 

PreparedStatement object has advantages over a generic Statement object.

1. Quicker than the generic Statement object because the SQL statement is precompiled and stored in a PreparedStatement object.  It can improve performance of frequently used SQL statements because this compiled statement is in memory and remains ready to use during different iterations.

2. Gives flexibility to supply input parameters at runtime

3. PreparedStatement object can also be used for statements that take no parameters.

4. PreparedStatement helps in defending against SQL Injection

Let us see an example of using PreparedStatement object, suppose you have a STUDENT table , which is having Student No, Student Name, Class, Address1, Address2 , City , State , Mobile No . Now you have the list of classes, suppose if you want to print the student details of MCA, MBA and B.Com . The following example shows how to use a PreparedStatement object to execute a SELECT statement for the above said example which has one input parameter: Note : either you can use classname or classcode as a parameter . Always better to use classcode.

public void loadStudentDetails() throws CDRCException, CDRCFatalException {
   String strQuery;
   Connection conn = null;
   ResultSet rs = null;
   PreparedStatement pstmt = null;
   ArrayList className = new ArrayList();

   try {
    conn = ConnectionFactory.getConnection();
    className.add("MCA");
    className.add("MBA");
    className.add("B.Com");
   
    strQuery = "select StudentNo, StudentName , Address1, Address2, City , State , MobileNo from student where className=?   ";

    pstmt = conn.prepareStatement(strQuery);  
    
    Iterator it=className.iterator();
    while (it.hasNext())
    {
     String clname=it.next().toString();
    pstmt.setString(1,clname);
    rs = pstmt.executeQuery();

      System.out.println("Class Name :" + clname ); 
    while(rs.next())
    {
     System.out.println(rs.getInt("StudentNo"));
     System.out.println(rs.getString("StudentName"));
     System.out.println(rs.getString("Address1"));
     System.out.println(rs.getString("City"));
     System.out.println(rs.getString("MobileNo"));
    }
     
    }

   } catch (SQLException e) {
    System.out.println( "Error :" + e.toString());
    
   } finally {
    try {
   if (rs != null) {
      rs.close();
      rs = null;
     }
     if (pstmt != null) {
      pstmt.close();
      pstmt = null;
     }
  
     if (conn != null) {
      conn.close();
      conn = null;
     }
    } catch (SQLException e) {
     System.out.println( "Error :"  + e.toString());
    }
       }
   } 

In the above example, same query is used three times with different input values (parameters). When you use a PreparedStatement object to execute an SQL statement, Don't forget to use the PreparedStatement.close() method to explicitly close the PreparedStatement object when you finish processing the results. This frees (deallocates) the resources that were allocated to the SQL statement.

For more methods on PreparaedStatement, you can visit  http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html