Summary of preparedstatement usage

The 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.

A preparedstatement is obtained from the java. SQL. connection object and the provided SQL string. The SQL string contains the question mark (?), These question marks indicate the location of the variable, then provide the value of the variable, and finally execute the statement, for example:

Stringsql = "select * from people P where P. ID =? And P. Name =? "; <Br/> preparedstatement PS = connection. preparestatement (SQL); <br/> ps. setint (1, ID); <br/> ps. setstring (2, name); <br/> resultset rs = ps.exe cutequery (); <br/>
Another advantage of using preparedstatement is that strings are not dynamically created. The following is an example of dynamically creating strings:
Stringsql = "select * from people P where p. I =" + ID;
This allows JVM (javavirtual machine, Java Virtual Machine) and driver/database cache statements and strings and improves performance.
Preparedstatement also provides database independence. When less declared SQL statements are displayed, the database dependency of potential SQL statements is smaller.
Because preparedstatement has many advantages, developers may usually use it, and use the normal statement only when it is completely because of performance or when there is no variable in a row of SQL statements.

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.

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); <br/> pstmt. setlong (2, 100000000); <br/> pstmt. setint (3, 6); <br/> pstmt. setstring (4, "logo"); <br/> pstmt. setdouble (5, 32.5); <br/> pstmt. setdate (6, new date ());

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.

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 ing specified in the corresponding JDBC Type "ing Java and JDBC types" table, and sends 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 (1, 44 ); 

The specific usage is as follows:

Package jstarproject; <br/> Import Java. SQL. *; <br/> public class mypreparedstatement {<br/> private final string db_driver = "com. microsoft. JDBC. sqlserver. sqlserverdriver "; <br/> private final string url =" JDBC: Microsoft: sqlserver: // 127.0.0.1: 1433; databasename = pubs "; <br/> Public mypreparedstatement () <br/>{< br/>}< br/> Public void query () throws sqlexception {<br/> connection conn = This. getconnection (); <Br/> string strsql = "select emp_id from employee where emp_id =? "; <Br/> preparedstatement pstmt = Conn. preparestatement (strsql); <br/> pstmt. setstring (1, "pma42628m"); <br/> resultset rs = pstmt.exe cutequery (); </P> <p> while (RS. next () {<br/> string fname = Rs. getstring ("emp_id"); <br/> system. out. println ("The fname is" + fname); <br/>}< br/> Rs. close (); <br/> pstmt. close (); <br/> Conn. close (); <br/>}< br/> private connection getconnection () throws sqlexception {<br/> // class. <br/> connection conn = NULL; <br/> try {<br/> class. forname (db_driver); <br/> conn = drivermanager. getconnection (URL, "sa", "sa"); <br/>}< br/> catch (classnotfoundexception ex) {}< br/> return conn; <br/>}< br/> // main <br/> Public static void main (string [] ARGs) throws sqlexception {<br/> mypreparedstatement jdbctest1 = new mypreparedstatement (); <br/> jdbctest1.query (); <br/>}< br/>