Difference between CreateStatement and PreparedStatement in JDBC

Question

Hi I am trying to learn JDBC. and here is my question:-

What is the Use of PreparedStatement in JDBC Because We can achieve the same effect by using createStatement(); too.

I mean if there is a query like:

Select * from tbl_name where id = somevalue

Then We can achieve it by both PreparedStatement and createStatement(). As follows:

Using CreateStatement():

try {
      DataInputStream dis=new DataInputStream(System.in);
      System.out.println("Enter id :- ");
      int id=Integer.parseInt(dis.readLine());
      String q="Select * from tbl_name where id="+id;
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(q);
      while(rs.next()) {
         //fetching part
     }
} catch(Exception ex){  ...  }

Using PreparedStatement:

 try {
        PreparedStatement preStatement=conn.prepareStatement("Select * from tbl_name where id=?");
        DataInputStream dis=new DataInputStream(System.in);
        System.out.println("Enter id:- ");
        int id=Integer.parseInt(dis.readLine());
        preStatement.setInt(1, id);
        ResultSet result = preStatement.executeQuery();      
        while(result.next()){
          // fetch the data
        }
    }catch(Exception ex){ ... }

As both of these programs are capable of doing the same task.

Why there is provision of two different Methods? Also looping seems to be easy if avoid repeatation is the answer.
Can any one tell me which one is good to use ?
what is the provision of each of them?
What is the difference between them, and which one optimizes the code?

you will definitely get the answer from the above link, try that 9 answers. — Shahrzad, Jul 30 '13 at 14:59
Yeah ! Thanks @LuiggiMendoza and a_question.. Thanks for being so quick.. — user2204252, Jul 30 '13 at 15:02
For me, this: http://xkcd.com/327/ is the difference between a `PreparedStatement` and `Statement`. — jlordo, Jul 30 '13 at 15:07
@jlordo :D ! That means PreparedStatement is used to prevent injections ? — user2204252, Jul 30 '13 at 15:10
@user2204252 yes, that's stated in the accepted answer of the possible dup. Note that BalusC (Java/JEE expert) explains the benefits of using `PreparedStatement` vs `Statement`. IMO I doubt you can't find a better and simpler explanation. — Luiggi Mendoza, Jul 30 '13 at 15:21
Yeah !! Thats true. Thanks for explaining it with example :). — user2204252, Jul 30 '13 at 15:24

score 12 · Accepted Answer · edited Jul 30 '13 at 15:39

The prepared statement concept is not specific to Java, it is a database concept. Precompiling of statement means, when you execute a SQL query, database server will prepare a execution plan before executing the actual query, this execution plan will be cached at database server for further execution.

The advantages of Prepared Statements are:

As the execution plan get cached, performance will be better.
It is a good way to code against SQL Injection as escapes the input values.
When it comes to a Statement with no unbound variables, the database is free to optimize to its full extent. The individual query will be faster, but the down side is that you need to do the database compilation all the time, and this is worse than the benefit of the faster query.

score 3 · Answer 2 · answered Jul 30 '13 at 15:02

With the createStatement the underlying database has to parse and compile the passed select query every time the statement is executed. This can impact performance. You can kind of "save" the query logic in a prepared statement and just pass in the query parameters, which could be the variable part of your query, every time the statement is executed.

Difference between CreateStatement and PreparedStatement in JDBC

2 Answers2