PreparedStatement - advantages of PreparedStatement

What is the advantage of using a PreparedStatement?

A PreparedStatement Object would be faster than a Statement Object where repeated execution of SQL statements is required. The reason is that the creation of a PreparedStatement object causes it to be precompiled within the database. So, it does not have to be recompiled during the execution again and again.

Usually, these objects are used when SQL statements take parameters.

What is PreparedStatement?

PreparedStatement is called as “pre compiled statement”. An SQL statement is compiled beforehand and stored in the PreparedStatement object. The purpose of precompiled statement is that the statement can be executed repeatedly. The required parameter values are to be substituted from time to time.

The following example illustrates the PreparedStatement object usage.
PreparedStatement pStmt = con.prepareStatement("select * from ? where balance>?");

The first ? represents a table name and the second ? represents any numeric attribute. Now the values can be set as follows:
pStmt.setString(1,"emp"); // emp is the table name and substitutes first ?
pStmt.setInt(2,29809); // 29809 is a value for balance field of the database
pStmt.execute(); // executes select statement.

Now the same select statement can be used for another table say “BankAccounts”
The following statements are the examples which use the same PreparedStatement with another table and attribute value
pStmt.setString(1,”BankAccounts”); // BankAccounts is the table name for first ?
pStmt.setInt(2,600000); // 600000 is the value for the second ?
pStmt.execute(); // executes the select statement

Thus a “single select” statement for multiple tables is used. As the PreparedStatement is precompiled, it is used for several select statements instead of every time execution.

What is the difference between Statement and PreparedStatement?

- The parameter value is fixed
- Compiles and executes every time

- The parameter can be supplied at run time
- Precompiled ( compiled once ) and executes once for n number of parameter values.

What is the advantage of using PreparedStatement?

PreparedStatement objects are used to execute repetitive SQL statements. Compared to Statement object execution, Prepared Statement object creation is faster. The reason is the object is pre compiled, by eliminating the compilation task by DBMS. The PreparedStatement object can be used by just replacing the parameters.
TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE - Constant indicating that the type for a ResultSet object is scrollable but not sensitive to changes made by others...
Different types of RowSet
JDBC RowSet Types - Connected: A connected RowSet Object is permanent in nature. It doesn’t terminate until the application is terminated...
JDBC ResultSet
JDBC ResultSet - How can you retrieve data from the ResultSet?, What are the various means of accessing results from ResultSet objects?...
Post your comment