JDBC series tutorial (5)-prepare statements

JDBC series tutorial (5) --- prepare statements

Preparedstatement

This overview is taken from jdbctm database access from javatm: a tutorial and annotated reference. Javasoft is currently preparing this book. This is a tutorial and an important reference manual for JDBC, which will be published by Addison-Wesley in the spring of 1997 as part of the Java series.

6.1 Overview
This preparedstatement interface inherits Statement, which is different from the following two aspects:

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 is not specified when the SQL statement is created. On the contrary, this statement reserves a question mark ("?") for each in parameter. As a placeholder. The value of each question mark must be provided through the appropriate setxxx method before the statement is executed.

Because the preparedstatement object has been pre-compiled, its execution speed is faster than the statement object. Therefore, SQL statements executed multiple times are often created as preparedstatement objects to improve efficiency.

As a subclass of statement, preparedstatement inherits all functions of statement. In addition, it also adds a complete set of methods to set the values sent to the database to replace the in parameter placeholder. At the same time, the three methods execute, executequery, and executeupdate have been changed so that they no longer need parameters. The statement form of these methods (the form that accepts SQL statement parameters) should not be used for the preparedstatement object.

6.1.1 create a 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 =? ");

The pstmt object contains the statement "Update table4 Set M =? Where X =? ", It has been sent to the DBMS and is ready for execution.

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

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

Once the parameter value of a given statement is set, it can be used to execute the statement multiple times until the clearparameters method is called to clear it.

In the default connection mode (enable automatic submission), the statement is automatically submitted or restored when the statement is completed.

If the basic database and driver remain open after the statement is submitted, the same preparedstatement can be executed multiple times. If this is not true, it is meaningless to try to use the preparedstatement object instead of the statement object to improve performance.

The following code uses pstmt (the preparedstatement object created earlier) to demonstrate how to set the values of Two Parameter placeholders and execute pstmt for 10 times. To do this, the database cannot close pstmt. In this example, the first parameter is set to "hi" and kept as a constant. In the for loop, the second parameter is set to a different value each time: from 0 to 9.

Pstmt. setstring (1, "Hi ");
For (INT I = 0; I <10; I ++ ){
Pstmt. setint (2, I );
Int rowcount = pstmt.exe cuteupdate ();
}

6.1.3 consistency of data types in the in Parameter
In the setxxx method, XXX is of the Java type. It is an implicit JDBC Type (generally an SQL type), because the driver maps the Java type to the corresponding JDBC Type (according to section 8 in the JDBC guide. 6.2 ing specified in the "ing Java and JDBC types" table) and send the JDBC Type to the database. For example, the following code snippet sets the second parameter of the preparedstatement object pstmt to 44 and the Java type to short:

Pstmt. setshort (2, 44 );

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

The programmer's responsibility is to ensure that the Java type of each in parameter is mapped to the JDBC Type compatible with the JDBC data type required by the database. Consider the situation where the database needs JDBC smallint. If setbyte is used, the driver sends the JDBC tinyint to the database. This is feasible because many databases can be converted from one type to another, and tinyint can usually be used wherever smallint applies. However, for applications that apply to as many databases as possible, it is best to use the Java type corresponding to the exact JDBC Type required by the database. If the required JDBC Type is smallint, 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. This method can accept the third parameter to specify the target JDBC Type. Before sending a Java object to a database, the driver converts it to the specified JDBC Type.

If the JDBC Type is not specified, the driver maps the 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 the conventional setxxx method. In both cases, the driver maps the Java type of the value to the appropriate JDBC Type before sending it to the database. The difference between the two is that the setxxx method uses the standard ing from the Java type to the JDBC Type (see the table in section 8.6.2 ), the setobject method uses ing from the Java object type to the JDBC Type (see the table in section 8.6.4 ).

The setobject method allows applications to be more generic by accepting all Java objects and can accept parameter input at runtime. In this case, the application is not clear about the input type during compilation. By using setobject, the application can accept all Java object types as input and convert them to the JDBC types required by the database. The table in section 8.6.5 shows all possible conversions that setobject can perform.

6.1.5 send JDBC null as the in Parameter
The setnull method allows the programmer to send the JDBC null value to the database as the in parameter. However, you must specify the JDBC Type of the parameter.

When the 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 setobject method can accept null values only when the JDBC Type is specified.

6.1.6 send large in Parameters
The setbytes and setstring methods can send an unlimited amount of data. However, sometimes programmers prefer to use smaller blocks to transmit large data. This can be done by setting the in parameter to a JAVA input stream. When a statement is executed, the JDBC driver repeatedly calls the input stream, reads its content, and transmits them as actual parameter data.

JDBC provides three methods to set the in parameter to the input stream: setbinarystream is used for streams containing unspecified bytes, setasciistream is used for streams containing ASCII characters, and setunicodestream is used for streams containing Unicode characters. Because the total length of the stream must be specified, the parameters used by these methods are one more parameter than other setxxx methods. This is necessary because some databases need to know the total transfer size before sending data.

The following code uses a stream as the in parameter to send file content:

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.exe cuteupdate ();

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