6 - PreparedStatement 6.1 Overview This preparedStatement interface inherits Statement and differ from it in two: PreparedStatement instances contain compiled SQL statements. This is to make the statement "ready". The SQL statement included 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. Conversely, the statement retains a question mark ("?") As a placeholder for each IN parameter. The value of each question mark must be provided by the appropriate setxxx method before this statement is executed. Since the PreparedStatement object has been precompiled, its execution speed is faster than the Statement object. Therefore, the SQL statement that is executed multiple times is often created as a PreparedStatement object to increase efficiency. As a STATEMENT subclass, PreparedStatement inherits all features of Statement. In addition, it also adds a set of methods to set the value sent to the database to replace the IN parameter placeholder. At the same time, three methods Execute, ExecuteQuery and ExecuteUpdate have been changed to make it no longer need parameters. The Statement form of these methods (the form of accepting the SQL statement parameters) should not be used for PreparedStatement objects. 6.1.1 Creating a PREPAREDStateMent Object The following code segment (where the CON is the Connection object) Creates the preparedStatement object containing the SQL statement with two IN parameter placeholders: PreparedState PSTMT = Con. PrepareStatement ("Update Table4 set m =? Where X =? "); PSTMT object contains statement" Update Table4 Set M =? WHERE X =? ", it has been sent to DBMS and prepare it for execution. 6.1.2 Passing the In parameter Before executing the PreparedStateMent object, you must set the value of each parameter. This can be done by calling the setxxx method, where xxx is a type corresponding to this 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 order position of the parameters 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 is set to 100000000: PSTMT.SETLONG (1, 123456789); PSTMT.SETLONG (2, 100000000); once the parameter value of the given statement is set, This statement can be performed multiple times until it is called to call the ClearParameterS method. This statement is automatically submitted or restored when the statement is completed when the statement is completed. If the basic database and driver remains in the open state of these statements after the statement is submitted, the same preparedStatement executes multiple times. If this is not true, it is not necessary to improve the performance by replacing the Statement object by using the PreparedStatement object. With PSTMT (previously created preparedStatement object), the following code exemplifies how to set the value of two parameter placeholders and perform PSTMT 10 times. As mentioned above, in order to do this, the database cannot close the PSTMT.
In this example, the first parameter is set to "hi" and remains a constant. In the For loop, the second parameter is set to different values each time: starting from 0, to 9 end. PSTMT.SetString (1, "hi"); for (int i = 0; i <10; i ) {PSTMT.Setint (2, i); int RowCount = pstmt.executeUpdate ();} 6.1.3 in parameter The consistency of the data type The xxx in the setXXX method is a Java type. It is an implied JDBC type (general SQL type) because the driver maps the Java type to the corresponding JDBC type (following the 8.6.2 "Mapping Java and JDBC Type" table specified in the JDBCGUIDE table ), And send the JDBC type to the database. For example, the following code segment sets the second parameter of the PreparedStatement object PSTMT to 44, the Java type is short: pstmt.setshst (2, 44); the driver sends 44 as a JDBC Smallint to the database, it is a standard for Java Short type Mapping. The programmer's responsibility is to ensure that the Java type of each IN parameter is mapped to the JDBC type compatible with the database required by the database. Consider the case where the database needs JDBC Smallint. If you use the method setByte, the driver sends the JDBC Tinyint to the database. This is feasible because many databases can be converted from one related type to another, and typically Tinyint can be used anywhere in Smallint applicable. However, for applications to be applied 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, use Setshort instead of SetByte will make the application's portability better. 6.1.4 Using the SETObject programmer can explicitly convert the input parameters to a specific JDBC type using the SetObject method. This method can accept the third parameter to specify the target JDBC type. Before sending Java Object to the database, the driver converts it to the specified JDBC type. If you do not specify a JDBC type, the driver maps Java Object to its default JDBC type (see Table 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 the value to the database. The difference between the two is that the SetXXX method uses standard mappings from Java types to JDBC types (see Sections 8.6.2), while the SETObject method uses mappings from Java Object types to JDBC types (see Section 8.6.4) Table). Method SetObject allows you to accept the ability of all Java objects to make your application more generous and you can accept the input of parameters at runtime. In this case, the application is not clear when compiling. By using SETOBJECT, the application accepts all Java object types as input and converts it to the JDBC type required by the database. Section 8.6.5 The table shows all possible conversions that setObject executable.
6.1.5 Sending the JDBC NULL as the IN parameter Allow programmers to send JDBC NULL values as the IN parameter to the database. But note that the JDBC type of the parameter must still be specified. When the Java Null value is passed to the SetXXX method (if it accepts the Java object as a parameter), JDBC NULL will also be sent to the database. However, when specifying the JDBC type, method setObject can accept NULL values. 6.1.6 Sending large IN parameters setbytes and setstring methods can send unlimited data. However, sometimes programmers prefer to deliver large data with smaller blocks. This can be done by setting the IN parameter to the Java input stream. When the statement is executed, the JDBC driver will repeat the input stream to read its contents and use them as actual parameter data transmission. JDBC provides three ways to set the IN parameter to input stream: SetBINARYSTREAM is used to contain a stream that is not indicated bytes, and setASciistream is used to contain the stream of ASCII characters, while SetUnicODestream is used to contain a stream containing Unicode characters. Because the total length of the stream must be specified, the parameters used in these methods are more than one of the other setxxx methods. This is necessary, because some databases need to know their total transmission size before sending data. The following code exemplifies the use of streams 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. EXECUTEUPDATE (); When the statement is executed, the input stream Fin repeatedly is called to pass its data. Related resources: with sound access new year fireworks animation effects - html-code
