2

There's lots of Q&A as to when and why it's better to use a PreparedStatement over a Statement in JDBC but not for the opposite? In other words when and why is it better to use Statement over PreparedStament? I'm struggling to find a use case where this is better. I've seen some comments that seem to indicate that in some cases it's better performance wise, but again it quickly goes back to PreparedStatement. So when is it better to use a Statement over a PreparedStatement?

As in:

connection.createStatement().executeQuery(sql);

vs

PreparedStatement preparedStatement = connection.preparedStatement(sql);
preparedStatement.execute();

With whatever additional code is needed.

Stephane Grenier
  • 15,527
  • 38
  • 117
  • 192
  • [`PreparedStatement`](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html) extends `Statement`. So, whenever you use a `PreparedStatement` you are using a `Statement`. – Elliott Frisch Aug 31 '17 at 01:46
  • 1
    `Statement` is an interface, not an implementation. When you use `Statement` you are getting some implementation which does not support positional parameters, and therefore doesn't offer the safety which `PreparedStatement` has. This is a major difference between the two. – Tim Biegeleisen Aug 31 '17 at 01:48
  • When is it better to buy an Animal than a Dog? – 4castle Aug 31 '17 at 01:48
  • That's my question, is there any time it's better? If not then why is it there at all? – Stephane Grenier Aug 31 '17 at 01:52
  • @StephaneGrenier Read the comments again. `PreparedStatement` is-a `Statement` and `Statement` is an interface. So, you are using `Statement` when you are using `PreparedStatement`, and it is not actually meaningful to say "using `Statement`". You have to be clear on what you mean by _"using Statement"_ – Adrian Shum Aug 31 '17 at 02:02
  • I suspect you mean using plain `conn.createStatement()` + `stmt.executeQuery(query)` vs `conn.prepareStatement() + stmt.executeQuery()`? – Adrian Shum Aug 31 '17 at 02:07
  • @AdrianShum Yes that's correct. I just edited the question – Stephane Grenier Aug 31 '17 at 02:13
  • I think the answer is straight-forward: you know the benefit of using prepared statement. If there is any case the benefits are irrelevant, then using plain `Statement.executeQuery(query)` will probably gain you (very minimal) performance gain and shorter code, because you do not need to create a separate `PreparedStatement` object for each query you are going to run. – Adrian Shum Aug 31 '17 at 02:14
  • Then what's the point of the having connection.createStatement() ? – Stephane Grenier Aug 31 '17 at 02:16
  • You can have one `Statement` object to invoke multiple queries – Adrian Shum Aug 31 '17 at 08:10
  • @AdrianShum One could argue that the fact `PreparedStatement` extends `Statement` is a design flaw in JDBC (which is reinforced by the fact that the `executeXXX(String)` methods are explicitly documented that they should always throw an `SQLException` in `PreparedStatement` and `CallableStatement`) – Mark Rotteveel Aug 31 '17 at 14:12
  • I don't understand why this question is closed as a duplicate. The questions that are shown as duplicate actually are not, they all say why you want to use PreparedStatement, none say when you want to use connection.createStatement() over PreparedStatement – Stephane Grenier Sep 01 '17 at 16:36

3 Answers3

4

In most real-life case, there is rarely case that createStatement is better than preparedStatement.

I believe almost everyone are aware of benefit of using prepared statement, just to name a few:

  • Less vulnerable to sql injection
  • better performance as to avoid SQL parsing same SQL with different parameter
  • etc

Most of these benefits comes from reusing the same statement, and setting parameters separately (instead of embedding in the query itself) when you are using prepared statement.

The only benefit (that I am aware of) by using createStatement() is you can use same statement object to execute different SQLs, while when using prepared statement, you will need to create PrepredStatement for each query.

In real life this is seldom meaningful. However, when you are developing an application that will allow user to input arbitrary query (which means you cannot set parameter separately), then there is no obvious benefit of using prepared statement. And then, if such application is going to query a lot of times using different query string (for example, you are building a SQL client), then doing createStatement once, and reuse the Statement object to execute different queries may gain you some (marginal) performance gain with less object allocation.

Adrian Shum
  • 38,812
  • 10
  • 83
  • 131
1

Your question is clearly answered in this link : https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

In short word I can say that both Statement and PreparedStatement is good based on the case for what they're used. For example, if you just want to get all rows in table-A, you can use Statement, for example:

String sql = "SELECT name, age, address FROM table-A";
Statement stat = connection.createStatement(sql);
ResultSet rs = stat.executeQuery();

But, in case of use SQL using where condition, you're better use PreparedStatement, for example:

String sql = "SELECT name, age, address FROM table-A WHERE name=? AND age=?";
PreparedStatement stat = connection.prepareStatement(sql);
stat.setString(1, "johny");
stat.setInt(2, 25);
ResultSet rs = stat.executeQuery();
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
adityasrucitra
  • 21
  • 2
  • So if I'm reading your answer correctly you're basically saying the only time you want to do connection.createStatement is if you're doing a select all for a table? That seems extremely rare, and therefore my question is why would that be in the API at all? – Stephane Grenier Sep 01 '17 at 16:38
  • For every SQL syntax passed to DBMS via java runtime, java compile it first and then execute this syntax. By using `Statement` , if we request 100 row from table, java will compile 100 time. Not like `Statement` , `PreparedStatement` compile it once, then for each row, java use precompiled syntax then passing it to DBMS. – adityasrucitra Sep 02 '17 at 22:34
-2

For selecting all rows from a table, you can use statement. If your requirement is to get a particular data out of a table, using statement is a bad idea. Because you retrieve data based on user input. If Statement is used, User input at where condition will become part of the query. Like below.

String selectQuery = "Select * from table where condition="+user_input;

Here if user input is like "something or 5=5", then the query string rendered will be like,

Select * from table where condition=something or 5=5

5=5 will always return true and hence all the rows from that particular table gets returned. This is so dangerous when it comes to dealing with tables where sensitive data gets stored.

But if you use prepared statement, the input will not be the part of the query. It is just a string. Query string rendered using preparedStatement will looks like 

Select * from table where condition=everything_user_posted_as_a_single_value.

So now condition='some_condition or 5=5'. It will not be treated as a part of the query instead everything to that parameter is considered as a single value.

If you need to secure your data and never care about performance, you are free to use PreparedStatements. When it comes to security PreparedStatement outperforms Statement. So if you think your query doesn't get affected from SQL injection, you can use Statements straight away.

pvkcse
  • 99
  • 1
  • 11
  • it is not answering the question. OP obvious is aware of the benefit of PreparedStatement. He want to know in what case using plain `statement.query(xxxx)` is better than using prepared statement. – Adrian Shum Aug 31 '17 at 08:12