Tutorial :Difference between Statement and PreparedStatement



Question:

The Prepared Statement is a slightly more powerful version of a Statement, and should always be at least as quick and easy to handle as a Statement.
The Prepared Statement may be parametrized

Most relational databases handles a JDBC / SQL query in four steps:

  1. Parse the incoming SQL query
  2. Compile the SQL query
  3. Plan/optimize the data acquisition path
  4. Execute the optimized query / acquire and return data

A Statement will always proceed through the four steps above for each SQL query sent to the database. A Prepared Statement pre-executes steps (1) - (3) in the execution process above. Thus, when creating a Prepared Statement some pre-optimization is performed immediately. The effect is to lessen the load on the database engine at execution time.

Now my question is that - "Is any other advantage of using Prepared Statement?"


Solution:1

Advantages of a PreparedStatement:

  • Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches.

  • Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters. Note that this requires that you use any of the PreparedStatement setXxx() methods to set the values

    preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)");  preparedStatement.setString(1, person.getName());  preparedStatement.setString(2, person.getEmail());  preparedStatement.setTimestamp(3, new Timestamp(person.getBirthdate().getTime()));  preparedStatement.setBinaryStream(4, person.getPhoto());  preparedStatement.executeUpdate();  

    and thus don't inline the values in the SQL string by string-concatenating.

    preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email) VALUES ('" + person.getName() + "', '" + person.getEmail() + "'");  preparedStatement.executeUpdate();  
  • Eases setting of non-standard Java objects in a SQL string, e.g. Date, Time, Timestamp, BigDecimal, InputStream (Blob) and Reader (Clob). On most of those types you can't "just" do a toString() as you would do in a simple Statement. You could even refactor it all to using PreparedStatement#setObject() inside a loop as demonstrated in the utility method below:

    public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {      for (int i = 0; i < values.length; i++) {          preparedStatement.setObject(i + 1, values[i]);      }  }  

    Which can be used as below:

    preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)");  setValues(preparedStatement, person.getName(), person.getEmail(), new Timestamp(person.getBirthdate().getTime()), person.getPhoto());  preparedStatement.executeUpdate();  


Solution:2

  1. They are pre-compiled (once), so faster for repeated execution of dynamic SQL (where parameters change)

  2. Database statement caching boosts DB execution performance

    Databases store caches of execution plans for previously executed statements. This allows the database engine to reuse the plans for statements that have been executed previously. Because PreparedStatement uses parameters, each time it is executed it appears as the same SQL, the database can reuse the previous access plan, reducing processing. Statements "inline" the parameters into the SQL string and so do not appear as the same SQL to the DB, preventing cache usage.

  3. Binary communications protocol means less bandwidth and faster comms calls to DB server

    Prepared statements are normally executed through a non-SQL binary protocol. This means that there is less data in the packets, so communications to the server is faster. As a rule of thumb network operations are an order of magnitude faster than disk operations which are an order of magnitude faster than in-memory CPU oeprations. Hence, any reduction in amount of data sent over the network will have a good effect on overall performance.

  4. They protect against SQL injection, by escaping text for all the parameter values provided.

  5. They provide stronger separation between the query code and the parameter values (compared to concatenated SQL strings), boosting readability and helping code maintainers quickly understand inputs and outputs of the query.

  6. In java, can call getMetadata() and getParameterMetadata() to reflect on the result set fields and the parameter fields, respectively

  7. In java, intelligently accepts java objects as parameter types via setObject, setBoolean, setByte, setDate, setDouble, setDouble, setFloat, setInt, setLong, setShort, setTime, setTimestamp - it converts into JDBC type format that is comprehendible to DB (not just toString() format).

  8. In java, accepts SQL ARRAYs, as parameter type via setArray method

  9. In java, accepts CLOBs, BLOBs, OutputStreams and Readers as parameter "feeds" via setClob/setNClob, setBlob, setBinaryStream, setCharacterStream/setAsciiStream/setNCharacterStream methods, respectively

  10. In java, allows DB-specific values to be set for SQL DATALINK, SQL ROWID, SQL XML, and NULL via setURL, setRowId, setSQLXML ans setNull methods

  11. In java, inherits all methods from Statement. It inherits the addBatch method, and additionally allows a set of parameter values to be added to match the set of batched SQL commands via addBatch method.

  12. In java, a special type of PreparedStatement (the subclass CallableStatement) allows stored procedures to be executed - supporting high performance, encapsulation, procedural programming and SQL, DB administration/maintenance/tweaking of logic, and use of proprietary DB logic & features


Solution:3

PreparedStatement is a very good defense (but not foolproof) in preventing SQL injection attacks. Binding parameter values is a good way to guarding against "little Bobby Tables" making an unwanted visit.


Solution:4

Some of the benefits of PreparedStatement over Statement are:

  1. PreparedStatement helps us in preventing SQL injection attacks because it automatically escapes the special characters.
  2. PreparedStatement allows us to execute dynamic queries with parameter inputs.
  3. PreparedStatement provides different types of setter methods to set the input parameters for the query.
  4. PreparedStatement is faster than Statement. It becomes more visible when we reuse the PreparedStatement or use it’s batch processing methods for executing multiple queries.
  5. PreparedStatement helps us in writing object Oriented code with setter methods whereas with Statement we have to use String Concatenation to create the query. If there are multiple parameters to set, writing Query using String concatenation looks very ugly and error prone.

Read more about SQL injection issue at http://www.journaldev.com/2489/jdbc-statement-vs-preparedstatement-sql-injection-example


Solution:5

nothing much to add,

1 - if you want to execute a query in a loop (more than 1 time), prepared statement can be faster, because of optimization that you mentioned.

2 - parameterized query is a good way to avoid SQL Injection, that is only available in PreparedStatement.


Solution:6

Statement is static and prepared statment is dynamic.

Statement is suitable for DDL and prepared statment for DML.

Statement is slower while prepared statement is faster.

more differences


Solution:7

Can't do CLOBs in a Statement.

And: (OraclePreparedStatement) ps


Solution:8

sql injection is ignored by prepared statement so security is increase in prepared statement


Solution:9

  • It's easier to read
  • You can easily make the query string a constant


Solution:10

Statement will be used for executing static SQL statements and it can't accept input parameters.

PreparedStatement will be used for executing SQL statements many times dynamically. It will accept input parameters.


Solution:11

Another characteristic of Prepared or Parameterized Query: Reference taken from this article.

This statement is one of features of the database system in which same SQL statement executes repeatedly with high efficiency. The prepared statements are one kind of the Template and used by application with different parameters.

The statement template is prepared and sent to the database system and database system perform parsing, compiling and optimization on this template and store without executing it.

Some of parameter like, where clause is not passed during template creation later application, send these parameters to the database system and database system use template of SQL Statement and executes as per request.

Prepared statements are very useful against SQL Injection because the application can prepare parameter using different techniques and protocols.

When the number of data is increasing and indexes are changing frequently at that time Prepared Statements might be fail because in this situation require a new query plan.


Solution:12

As Quoted by mattjames

The use of a Statement in JDBC should be 100% localized to being used for DDL (ALTER, CREATE, GRANT, etc) as these are the only statement types that cannot accept BIND VARIABLES. PreparedStatements or CallableStatements should be used for EVERY OTHER type of statement (DML, Queries). As these are the statement types that accept bind variables.

This is a fact, a rule, a law -- use prepared statements EVERYWHERE. Use STATEMENTS almost no where.


Solution:13

Statement interface executes static SQL statements without parameters

PreparedStatement interface (extending Statement) executes a precompiled SQL statement with/without parameters

  1. Efficient for repeated executions

  2. It is precompiled so it's faster


Solution:14

Dont get confusion : simply remember

  1. Statement is used for static queries like DDLs i.e. create,drop,alter and prepareStatement is used for dynamic queries i.e. DML query.
  2. In Statement, the query is not precompiled while in prepareStatement query is precompiled, because of this prepareStatement is time efficient.
  3. prepareStatement takes argument at the time of creation while Statement does not take arguments. For Example if you want to create table and insert element then :: Create table (static) by using Statement and Insert element (dynamic)by using prepareStatement.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »