9

When to use statement instead of prepared statement. i suppose statement is used in queries with no parameter but why not use prepared statement ? Which one is faster for queries with no params.

Neo
  • 165
  • 3
  • 11
  • 4
    There is hardly ever a reason to use normal statements, except maybe for DDL statements and other param-less statements. – Joachim Sauer Jun 14 '11 at 13:00
  • 1
    possible duplicate of [Difference between Statement and PreparedStatement](http://stackoverflow.com/questions/3271249/difference-between-statement-and-preparedstatement) – BalusC Jun 14 '11 at 13:03

2 Answers2

10

I suppose statement is used in queries with no parameter but why not use prepared statement ?

That's not even close. PreparedStatements are used in the case of INSERT, UPDATE and DELETE statements that return a ResultSet or an update count. They will not work for DDL statements as pointed out by Joachim, and neither will they work for invocation of stored procedures where a CallableStatement ought to be used (this is not a difference between the two classes). As far as queries with no bind parameters are concerned, PreparedStatements can turn out to be better than Statements (see below).

Which one is faster for queries with no params.

PreparedStatements will turn out to be faster in the long run, over extended use in a single connection. This is because, although PreparedStatements have to be compiled, which would take some time (this really isn't a lot, so don't see this as a drawback), the compiled version essentially holds a reference to the SQL execution plan in the database. Once compiled, the PreparedStatement is stored in a connection specific cache, so that the compiled version may be reused to achieve performance gains. If you are using JDBC Batch operations, using PreparedStatements will make the execution of the batch much faster than the use of plain Statement objects, where the plan may have to be prepared time and again, if the database has to do so.

Vineet Reynolds
  • 76,006
  • 17
  • 150
  • 174
  • FYI, *most* of the work of a typical query is in the parsing of the statement, not the data retrieval. PreparedStatements are a lot faster if you use the same statement multiple times. – Bohemian Jun 14 '11 at 13:17
  • Yes, that is when you compare it between the parsing and execution in across one execute, executeUpdate or executeQuery invocation. Against a DB that is heavily used, the precompiled statement is essentially what gives you a performance gain across multiple invocations, if you have a large enough statement cache. – Vineet Reynolds Jun 14 '11 at 13:22
4

That's depending on Your requirement.

If you have a SQL statement which runs in a loop or frequently with different parameters then PreparedStatement is the best candidate since it is getting pre-compiled and cache the execution plan for this parameterized SQL query. Each time it runs from the same PreparedStatement object it will use cached execution plan and gives the better performance.

Also SQL injection can be avoided using PreparedStatement .

But if you are sure that you run SQL query only once, sometimes Statement will be the best candidate since when you create PreparedStatement object sometimes it make additional db call, if the driver supports precompilation, the method Connection.prepareStatement(java.lang.String) will send the statement to the database for precompilation.

Read below article to understand "Statement Versus PreparedStatement"

Shyamal Madura Patabendige
  • 81
  • 6