Preparestatement Detailed Introduction

The SQL statement that is passed to the database returns the result through a procedure that contains two steps. Prepare them first, then process them. With the Statement object, these two phases become a phase for the application. PreparedStatement allows the two steps to be separated. The preparation step occurs when the object is created, and the processing step occurs when the ExecuteQuery, executeupdate, or Execute method is invoked on the PreparedStatement object.

If you do not add parameter markers, it makes no sense to be able to split the SQL processing into separate phases. The parameter tag is placed in the application so that it can tell the database that it does not have a specific value when it is prepared, but it provides a value before processing. In an SQL statement, a parameter marker is represented using a question mark.

By using parameter markers, it is possible to create a generic SQL statement for a particular request. For example, given the following SQL query statement:

SELECT * from employee_table WHERE LASTNAME = ' Dettinger '

This is a specific SQL statement that returns only one value, that is, information about an employee named Dettinger. You can make statements more flexible by adding parameter markers:

SELECT * from employee_table WHERE LASTNAME =?

By simply setting the parameter marker to a value, you can get information about any employee in the table.

Because the previous Statement example can be processed repeatedly with a single preparatory phase and then with different parameter values, PreparedStatement can provide higher performance than Statement.

Note: to support statement pooling for native JDBC drivers, you must use PreparedStatement.

The Preparestatement method is used to create a new PreparedStatement object. Unlike the Createstatement method, you must provide an SQL statement when you create a PreparedStatement object. At that time, the SQL statements were precompiled for use. For example, assuming that a Connection object named Conn already exists, the following example creates a PreparedStatement object and prepares an SQL statement to be processed in the database.

PreparedStatement PS = conn.preparestatement ("select * from employee_table
                                                  WHERE LASTNAME =?");

As with the Createstatement method, the purpose of the overloaded Preparestatement method is to provide support for the specified ResultSet feature. The Preparestatement method also has variants that can be used with automatically generated keys. Here are some examples of valid Preparestatement method calls:

Example:preparestatement method

Note: Please read the code example does not guarantee the declaration to understand important legal information.

New in JDBC 2.0

     preparedstatement PS2 = conn.preparestatement ("select * from
         employee_table WHERE LASTNAME =?" ,

     resultset.type_scroll_insensitive,
     resultset.concur_updateable);

     New in JDBC 3.0

     preparedstatement ps3 = conn.preparestatement ("select * from
         employee_table WHERE LASTNAME =?" ,
         resultset.type_scroll_insensitive, resultset.concur_updateable,
         resultset.hold_cursor_over_commit);

     PreparedStatement PS4 = conn.preparestatement ("select * from
         employee_table WHERE LASTNAME =?", Statement.return_ Generated_keys);

You must set each parameter marker to a number of values before you can work with the PreparedStatement object. The PreparedStatement object provides a number of methods for setting parameters. All of these methods are formatted as SET<TYPE>, where <Type> is the Java data type. Some examples of these methods include Setint, Setlong, SetString, Settimestamp, SetNull, and Setblob. Almost all of these methods have two parameters: the first parameter is the index of the parameter in the statement. The parameter marker has a number starting from 1. The second parameter is the value you want to set for the first argument. There are several set<type> methods that have additional parameters, such as length parameters on Setbinarystream.

For more information, consult the Javadoc for the java.sql package. By giving PS to the SQL statements prepared in the previous example, the following code shows how to specify the parameter values before processing:

Ps.setstring (1, ' Dettinger ');

If an attempt is made to process a preparedstatement with a parameter marker that has not yet been set, SQLException is thrown.

Note: After you set the parameter marker, the parameter marker will remain with the same value unless the following conditions occur. The value is changed by another set method call. The value was dropped when the Clearparameters method was invoked.

The Clearparameters method marks all parameters as not set. After the clearparameters call is made, the set method must be called again on all parameters before the next procedure is executed.

The new Parametermetadata interface allows you to retrieve information about parameters. This support is consistent with ResultSetMetaData and is similar. Provides comprehensive information such as precision, scale, data type, data type name, and whether the parameter allows null values.