Statement, PreparedStatement, CallableStatement

There are three kinds of statement in SQL: Statement, PreparedStatement and CallableStatement
The relationship between these three interfaces is that:

PreparedStatement extends Statement and CallableStatement extends PreparedStatement.

1. Statement: 
Example:

01 package com.company.tests;
02 import java.util.*;
03 import java.sql.*;
04 import com.company.utils.JDBCUtil;
05 
06 public class OracleTest2 {
07 
08     public static void main(String[] args) {
09         
10         java.util.Scanner scanner = new Scanner(System.in);
11         System.out.print("Input name:");
12         String name = scanner.nextLine();
13         try {
14             Connection conn = JDBCUtil.getConnection();
15             String sql = "select * from Sample where Name = '" + name + "'";
16             Statement st = conn.createStatement();
17             ResultSet rs = st.executeQuery(sql);
18             while (rs.next()) {
19                 System.out.println(rs.getString("Name") + "\t"
20                         + rs.getInt("Age"));
21             }
22             rs.close();
23         } catch (Exception e) {
24             e.printStackTrace();
25         }
26     }
27 }
In this code, there is a problem and it makes the code easy to be hiked.
For example, if the user inputs abc' or '1' = '1 . What the output will be?
It'll show all the columns in the table. It's dangerous. This is called inline sql which should be prevented.
The interface PreparedStatement gives a way to solve this problem.

PS: JDBCUtil.getConnection() please refer to my last post:
http://bosbluebluesky.blogspot.com/2012/04/jdbc-configuration-and-implementation.html


2. PreparedStatement: 
   Difference between PreparedStatement and Statement:
   1. PreparedStatement allows parameters in the sql statement, so it can prevent inline sql.
   2. PreparedStatement is precompiled. It compiles once and only once. So it has better performance.


Example:


01 package com.company.tests;
02 import java.util.*;
03 import java.sql.*;
04 import com.company.utils.JDBCUtil;
05 
06 public class OracleTest2 {
07 
08     public static void main(String[] args) {
09         
10         java.util.Scanner scanner = new Scanner(System.in);
11         System.out.print("Input name:");
12         String name = scanner.nextLine();
13         try {
14             Connection conn = JDBCUtil.getConnection();
15             String sql = "select * from Sample where Name = ?";
16             PreparedStatement ps = conn.prepareStatement(sql);
17             ps.setString(1, name); 
18             ResultSet rs = ps.executeQuery();
19             while (rs.next()) {
20                 System.out.println(rs.getString("Name") + "\t"
21                         + rs.getInt("Age"));
22             }
23             rs.close();
24         } catch (Exception e) {
25             e.printStackTrace();
26         }
27     }
28 }


3. CallableStatement: 
It inherits all the features of Statement and PreparedStatement. In the mean time, there are differences between PreparedStatement and CallableStatement. CallableStatement is designed for stored procedure.


Difference between PreparedStatement and CallableStatement:


Both CallableStatement and PreparedStatement can call stored procedure, BUT
The CallableStatement can have both input and output parameters in the statement, while the PreparedStatement can only have input parameters in the statement.


Example:
First we create functions in the Oracle:

1. Save a record
   create or replace function SAVEUSER(
name in VARCHAR2,
age in NUMBER)
   return NUMBER
   is
   begin
insert into Sample values(name, age);
return 10;
   end;


2. Retrieve a result set
   Step 1:
   create or replace package TYPES as
TYPE ref_cursor IS REF CURSOR;
   end;

   Step 2:
   create or replace function QUERYUSER
return Types.ref_cursor
   as 
mycursor Types.ref_cursor;
   begin
open mycursor for
select * from Sample;
return mycursor;
   end;

Java Code:

01 /**
02 * Test callableStatement and stored procedure
03 */
04 
05 package com.company.tests;
06 import java.sql.*;
07 import com.company.utils.*;
08 import oracle.jdbc.OracleTypes;
09 
10 public class OracleTest3 {
11 
12     public static void main(String[] args) {
13         
14         try{
15             Connection conn = JDBCUtil.getConnection();
16             String sp = "{? = call saveUser(?,?)}";
17             CallableStatement cs = conn.prepareCall(sp);
18             cs.registerOutParameter(1,Types.INTEGER); // output
19             cs.setString(2,"Frank");  // input
20             cs.setInt(3, 58);
21             cs.execute();
22             System.out.println("Return value: " + cs.getInt(1));
23             sp = "{? = call queryUser()}";
24             cs = conn.prepareCall(sp);
25             cs.registerOutParameter(1, OracleTypes.CURSOR);
26             cs.execute();
27             ResultSet rs = (ResultSet)cs.getObject(1);
28             while(rs.next()){
29                 System.out.println(rs.getString("Name") + "\t" + rs.getInt("Age"));    
30             }
31             rs.close();
32         }catch(Exception e){
33             e.printStackTrace();
34         }
35 
36     }
37 
38 }