Using the Preparedstatement object in JSP to operate the database

The Preparedstatement interface inherits the statement and is different from it in 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 was not specified when the SQL statement was created. Instead, the statement reserves 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. Therefore, SQL statements executed multiple times are often created as Preparedstatement objects to improve efficiency. As a subclass of statement, Preparedstatement inherits all the functions of statement. In addition, it adds a complete set of methods to set the value sent to the database to replace the placeholder for the in parameter. At the same time, the three methods execute, executeQuery, and executeupdate have been changed so that they no longer require parameters. The statement form of these methods (the form that accepts SQL statement parameters) should not be used for the Preparedstatement object. 1. Create the code segment below the Preparedstatement object (where con is the connection object). Create the Preparedstatement object containing the 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. 2. Pass the 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 the Java type long, the method used is setlong. The first parameter of setXXX method is the ordinal position of the parameter to be set, and the second parameter is the value set to the parameter. For example, the following code sets the first parameter to 123456789 and the second parameter to 100000000:

Once the parameter value of a given attributive sentence is set, it can be used to execute the statement multiple times until it is cleared by calling the clearparameters method. In the default mode of connection (enable auto commit), the statement will be submitted or restored automatically when it is completed. If the basic database and driver still keep these statements open after the statement is submitted, the same Preparedstatement can be executed multiple times. If this is not true, it is impossible to try to improve performance by using Preparedstatement object instead of statement object Meaningful. Using pstmt (the Preparedstatement object created earlier), the following code illustrates how to set the value of two parameter placeholders and execute pstmt 10 times. As mentioned above, in order to do this, the database cannot close pstmt. In this example, the first parameter is set to "Hi" And keep it constant. In the for loop, the second parameter is set to a different value each time: from 0 to 9.

3. Consistency of data type in in parameter XXX in setXXX method is Java type. It is an implicit JDBC type (general SQL type), because the driver will map the Java type to the corresponding JDBC type (follow § 8.6.2 "mapping Java and JDBC types" in the JDBC Guide) 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 is short: pstmt setShort(2,44); The driver sends 44 to the database as JDBC smallint, which is a standard mapping of Java short types. It is the programmer's responsibility 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 case where the database needs JDBC smallint. If the method setbyte is used, the driver sends JDBC tinyint to the database. This is feasible because many databases can be converted from one related type to another, and tinyint can usually be used to preprocess the statement object Preparedstatement wherever smallint is applicable. Use Preparedstatement to add data, update data, delete data and query data

Example: 1 Add data

Just a hint, don't use the wrong case. The red mark is because the letters in front of me are capitalized, which takes a long time

2. Update data

3. Delete data

4. Query data