JDBCTM Guide: Getting Started 6-preparedstatement

6-preparedstatement
This overview is quoted in the book Jdbctm Database Access from Javatm:a Tutorial and annotated Reference. JavaSoft is currently preparing the book. This is a tutorial, as well as an important reference manual for JDBC, which will be published as part of the Java family in the spring of 1997 by Addison-wesley Publishing Company.


6.1 Overview
The PreparedStatement interface inherits Statement and differs from the other in two ways:


The PreparedStatement instance contains compiled SQL statements. This is to make the statement "ready".
The SQL statement contained in the PreparedStatement object can have one or more in parameters. The value of the in parameter was not specified when the SQL statement was created. Instead, the statement retains a question mark for each in parameter ("? ") as a placeholder. The value of each question mark must be provided by the appropriate Setxxx method before the statement is executed.

Because the PreparedStatement object has been precompiled, it executes faster than the Statement object. As a result, SQL statements executed multiple times are often created as preparedstatement objects to improve efficiency.

As a subclass of Statement, PreparedStatement inherits all the functionality of Statement. It also adds a set of methods for setting the value that is sent to the database to replace the in parameter placeholder. Also, three methods execute, ExecuteQuery, and executeupdate have been changed so that the parameters are no longer needed. The Statement form of these methods (in the form of accepting SQL statement parameters) should not be used for PreparedStatement objects.


6.1.1 Create PreparedStatement Object
The following code snippet (where con is the Connection object) creates a PreparedStatement object that contains an SQL statement with two in parameter placeholders:

PreparedStatement pstmt = con.preparestatement (
"UPDATE table4 SET m =?" WHERE x =? ");

Pstmt object contains statement "UPDATE table4 SET m =? where x =? ", which has been sent to the DBMS and ready for execution.


6.1.2 Pass in Parameter
Before executing the PreparedStatement object, you must set each? The value of the parameter. This can be done by calling the Setxxx method, where XXX is the type corresponding to the parameter. For example, if the parameter has a Java type long, the method used is setlong. The first parameter of the Setxxx method is the ordinal position of the parameter to set, and the second parameter is the value set to the parameter. For example, the following code sets the first argument to 123456789 and the second parameter to 100000000:

Pstmt.setlong (1, 123456789);
Pstmt.setlong (2, 100000000);

Once you set the parameter value for a given statement, you can use it to execute the statement multiple times until you call the Clearparameters method to clear it.

In the default mode of the connection (enable autocommit), the statement is automatically committed or restored when the statement completes.

The same preparedstatement can be executed multiple times if the base database and the driver maintain the open state of the statements after the statement has been committed. If this is not true, there is no point in trying to improve performance by using the PreparedStatement object instead of the Statement object.

Using Pstmt (The PreparedStatement object created earlier), the following code illustrates how to set the value of a two-parameter placeholder and execute pstmt 10 times. As mentioned above, to do this, the database cannot close pstmt. In the example, the first parameter is set to "Hi" and persisted as a constant. In the For loop, the second argument is set to a different value each time: starting at 0 and ending with 9.

Pstmt.setstring (1, "Hi");
for (int i = 0; i < i++) {
Pstmt.setint (2, I);
int rowcount = Pstmt.executeupdate ();
}

Consistency of data types in the 6.1.3 in parameter
XXX in the Setxxx method is a Java type. It is an implied type of JDBC (generic SQL type) because the driver will map the Java type to the appropriate JDBC type (following the mapping specified in the "Mapping Java and JDBC type" table in the JDBC Guide) and send the JDBC type to the §8.6.2 to the database. For example, the following snippet sets the second argument of the PreparedStatement object pstmt to the 44,java type as short:

Pstmt.setshort (2, 44);

The driver sends 44 as a JDBC SMALLINT to the database, which is the standard mapping of the Java short type.

It is the responsibility of the programmer to ensure that the Java type of each in parameter is mapped to a JDBC type compatible with the JDBC data type required by the database. Consider the situation where the database requires JDBC SMALLINT. If you use method SetByte, the driver sends the JDBC TINYINT to the database. This is possible because many databases can be converted from one related type to another, and usually TINYINT can be used anywhere SMALLINT applies. However, for applications that apply to as many databases as possible, it is best to use the Java type that corresponds to the exact type of JDBC required for the database. If the desired JDBC type is SMALLINT, then using Setshort instead of SetByte will make the application more portable.


6.1.4 Use SetObject
Programmers can use the SetObject method to explicitly convert input parameters to specific JDBC types. The method can accept a third parameter that specifies the target JDBC type. The driver converts Java Object to the specified JDBC type before it is sent to the database.

If you do not specify a JDBC type, the driver maps Java Object to its default JDBC type (see the table in section 8.6.4) and sends it to the database. This is similar to a regular Setxxx method in which the driver maps the Java type of the value to the appropriate JDBC type before sending the value to the database. The difference is that the Setxxx method uses standard mappings from Java types to JDBC types (see the table in section 8.6.2), whereas the SetObject method uses mappings from Java Object type to JDBC type (see table in section 8.6.4).

Method SetObject allows the ability to accept all Java objects to make applications more generic and to accept input of parameters at run time. In this case, the application does not have a clear input type at compile time. By using SetObject, an application can accept all Java object types as input and convert them to the type of JDBC required by the database. The table in section 8th 6.5 shows all possible transformations that setobject can perform.


6.1.5 to send JDBC NULL as an in parameter
The SetNull method allows programmers to send JDBC NULL values as in parameters to the database. Note, however, that you must still specify the JDBC type of the parameter.

When a Java null value is passed to the Setxxx method (if it accepts a Java object as a parameter), JDBC NULL is also sent to the database. However, the method setobject can accept null values only if the JDBC type is specified.


6.1.6 send large in Parameter
SetBytes and SetString methods can send unlimited amounts of data. However, sometimes programmers prefer to use smaller chunks to pass large data. This can be done by setting the in parameter to a Java input stream. When the statement executes, the JDBC driver calls the input stream repeatedly, reads its contents, and transmits them as actual parameters.

JDBC provides three ways to set the in parameter to an input stream: Setbinarystream is used for streams that contain unspecified bytes, setasciistream for streams that contain ASCII characters, and setunicodestream for a string containing Unicode characters. Flow. Because the total length of the stream must be specified, these methods take one more argument than the other setxxx methods. This is necessary because some databases need to know their total transfer size before sending data.

The following code example shows the use of a stream as an in parameter to send the contents of a file:

Java.io.File File = new Java.io.File ("/tmp/data");
int filelength = File.length ();
Java.io.InputStream fin = new Java.io.FileInputStream (file);
Java.sql.PreparedStatement pstmt = con.preparestatement (
"UPDATE Table5 SET stuff =?" WHERE index = 4 ");
Pstmt.setbinarystream (1, Fin, filelength);
Pstmt.executeupdate ();

When the statement executes, the input stream fin is called repeatedly to pass its data.