8

Often, in the network can be found code like this:

private static final String SQL = "SELECT * FROM table_name";
....

and for this SQL query is used PreparedStatement. Why?
As i know, PreparedStatement spend time to precompile SQL statement. It turns out so that the Statement is faster than a PreparedStatement. Or I'm mistaken?

Ifozest
  • 900
  • 2
  • 12
  • 22
  • Does this answer your question? [PreparedStatements and performance](https://stackoverflow.com/questions/687550/preparedstatements-and-performance) – rogerdpack Apr 06 '22 at 18:36

4 Answers4

16

Prepared statements are much faster when you have to run the same statement multiple times, with different data. Thats because SQL will validate the query only once, whereas if you just use a statement it will validate the query each time.

The other benefit of using PreparedStatements is to avoid causing a SQL injection vulnerability - though in your case your query is so simple you haven't encountered that.

For your query, the difference between running a prepared statement vs a statement is probably negligible.

EDIT: In response to your comment below, you will need to look closely at the DAO class to see what it is doing. If for example, each time the method is called it re-creates the prepared statement then you will lose any benefit of using prepared statements.

What you want to achieve, is the encapsulation of your persistence layer so that their is no specific call to MySQL or Postgres or whatever you are using, and at the same time take advantage of the performance and security benefits of things like prepared statements. To do this you need to rely on Java's own objects such as PreparedStatement,.

I personally would build my own DAO class for doing CRUD operations, using Hibernate underneath and the Java Persistence API to encapsulate it all, and that should use prepared statements for the security benefits. If you have a specific use-case for doing repeated operations, then I would be inclined to wrap that within its own object.

Hibernate can be configured to use whatever database vendor you are using via an XML file, and thus it provides really neat encapsulation of your persistence layer. However, it is quite a complicated product to get right!

christophmccann
  • 4,181
  • 7
  • 42
  • 66
  • I would like to clarify something. In my simple webApp i have DAO class. In this class i have method(take some info by ID) that uses preparedStatement. When calling this method multiple times, how many times preparedStatement will be pre-compile this SQL query? – Ifozest Mar 04 '12 at 18:41
  • I have edited my main answer for this - it is an interesting part of your question. – christophmccann Mar 04 '12 at 18:44
  • thanks for the detailed answer. Hibernate I have not yet studied, But I will learn soon. In my DAO class method i re-created preparedStatement >. – Ifozest Mar 04 '12 at 19:31
  • @christophmccann - SQL will validate the query. I am not sure i understand this. So DB will not check the correctness of SQL commands issued via PreparedStatement (except first query maybe) ? – david blaine Apr 25 '13 at 08:21
4

Most of the time queries are not as simple as your example. If there is any variation to the query, i.e. any parameters that are not known at compile time, you must use PreparedStatement to avoid SQL injection vulnerabilities. This trumps any performance concerns. If there is any difference between PreparedStatement and Statement, it would be highly dependent on the particular JDBC driver in question and most of the time the penalty will be negligible compare to the cost of going to the database, executing actual query and fetching results back.

MK.
  • 33,605
  • 18
  • 74
  • 111
  • yes, you say the obvious things. But in this query we dont need to set any parameter, and based on that - we will not have SQL injection. – Ifozest Mar 03 '12 at 17:32
  • 1
    So yeah, as others have said if there are no parameters and you only execute once, it might be slightly faster to do Statement instead of PreparedStatement, but I'd rather be consistent and use PreparedStatement always. – MK. Mar 03 '12 at 17:37
1

As Per the My knowledge PreparedStatement is much faster then statement. Here some reason why preparedstatement is faster then statement please read for more detail.
JDBC API is provide the functionality of connectivity with database. Then we try to execute the query with the use of statement and preparedstatement.
There are four step to execute the query.

Parsing of sql query.
Compile this Query.
optimization of data acquisition path.
execute the query.

Statement interface is suitable when we will not need to execute the query multiple time.

Disadvantages of Statement Interface. hacker can easily to hack the data. Like suppose we have one query which have the username and password is a parameters you can give the proper parameters is username='abc@example.com' and password ='abc123' actually this is current But hacker can do username='abc@example.com' or '1'=1 and password='' that means you can logged successfully. so that is happening possible in Statement.
And sql validate every time when we fetch the data from database.

So Java has the solution for this above problem that is PreparedStatement. This interface has many advantages. the main advantages of preparedstatement is sql is not validate the query every time. so you can get the result fast. please read the below more advantages of preparedstatement.

1) We can safely provide the value of query's parameters with setter method. 2) it prevent the SQL injection because it is automatically escapes the special characters. 3) When we use statement above four steps are execute every time But when we use the PreparedStatement only last steps is execute so this is faster then statement.

JegsVala
  • 1,789
  • 1
  • 19
  • 26
0

Faster is not the consideration here. Parsing of the sql will generally be a tiny part of overall execution. See more at When should we use a PreparedStatement instead of a Statement?

Community
  • 1
  • 1
Jayan
  • 18,003
  • 15
  • 89
  • 143