Difference Between Statement and Preparedstatement

When is it better to use a Statement over a PreparedStatement?

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.

Difference between PreparedStatement batch and Statement batch

The difference between batch execution of a Statement and PreparedStatement, is that a Statement batch can contain different statements (as long as they are statements that do not produce a result set), for example a single batch can contain all kinds of inserts into various tables, deletes, updates, and - not in all JDBC driver implementations AFAIK - even DDL statements.

On the other hand, a PreparedStatement batch execution concerns a single statement, and the batch contains the multiple sets of parameter values to execute for that statement. That is, each batch entry defines the values to use for the parameters of the prepared statement.

In short:

• Statement: batch can contain a lot of different statements
• PreparedStatement: single statement, multiple sets of parameter values

Difference between CreateStatement and PreparedStatement in JDBC

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.

which is faster? Statement or PreparedStatement

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!

What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?

All three "work the same" in that they execute the simple SQL statement:

INSERT INTO foo VALUES (3, 'ben');

The prepared statement is only good for a single prepared SQL statement (as the name suggests). And only DML commands. The manual:

Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

The function can contain any number of statements. DML and DDL. Only SQL for SQL functions. Plus some non-SQL procedural elements in PL/pgSQL.

The prepared statement is only visible inside the same session and gone at the end of the session, while the function persists and is visible to all - still only usable for those with the EXECUTE privilege.

The prepared statement is encumbered with the least overhead. (Not much difference.)

The SQL function is the only one of the three that cannot save the query plan (by itself). Read details about plan caching in PL/pgSQL functions in the manual here.

The SQL function is also the only one that could be inlined when used within a bigger query. (Not with an INSERT, though.)

A rather comprehensive list of differences between SQL and PL/pgSQL functions:

• Difference between language sql and language plpgsql in PostgreSQL functions

Starting with Postgres 11 there are also SQL procedures:

• When to use stored procedure / user-defined function?

---

Related Topics