JSP Learning--Comprehensive analysis of JDBC (4)

How does JS use JDBC to send SQL statements?

The statement object is used to send SQL statements to the database. There are actually three kinds of Statement objects that act as an inclusion for executing SQL statements on a given connection: Statement, PreparedStatement (it inherits from statement) and CallableStatement (it inherits from PreparedStatement). They are all dedicated to sending a specific type of SQL statement: The statement object is used to execute a simple SQL statement with no parameters, and the PreparedStatement object is used to execute a precompiled SQL statement with or without an in parameter The CallableStatement object is used to perform a call to the stored procedure of the database.

The statement interface provides a basic way to execute statements and obtain results; The PreparedStatement interface adds methods for handling in parameters, and CallableStatement adds methods for handling out parameters.

1. Create a statement object

Once you have established a connection to a specific database, you can use the connection to send the SQL statement. The statement object is created using the connection method createstatement as shown in the following code snippet:

Connection con = drivermanager.getconnection (URL, "Sunny", "");
Statement stmt = Con.createstatement ();


In order to execute the statement object, the SQL statement sent to the database will be supplied as a parameter to the statement method:

ResultSet rs = stmt.executequery ("Select A,b,c from Table2");

2. Executing statements using the statement object

The statement interface provides three ways to execute SQL statements: ExecuteQuery, Executeupdate, and execute. Which method to use is determined by what the SQL statement produces.

Method ExecuteQuery The statement used to produce a single result set, such as a SELECT statement. Method Executeupdate is used to execute INSERT, UPDATE, or DELETE statements and SQL DDL (data definition language) statements, such as CREATE table and drop TABLE. The effect of an INSERT, update, or DELETE statement is to modify one or more columns in 0 or more rows in a table. The return value of the executeupdate is an integer indicating the number of rows affected (that is, the update count). For statements that do not manipulate rows such as CREATE table or drop table, the executeupdate return value is always zero.

All methods that execute the statement will close the current open result set (if any) of the statement object that is invoked. This means that the processing of the current ResultSet object needs to be completed before the statement object is executed again. It should be noted that the PreparedStatement interfaces that inherit all the methods in the statement interface have their own executequery, Executeupdate, and Execute methods. The statement object itself does not contain SQL statements, so the Statement.execute method must be supplied with an SQL statement as an argument. PreparedStatement objects do not require SQL statements to be supplied as arguments to these methods, because they already contain precompiled SQL statements.

The CallableStatement object inherits the PreparedStatement form of these methods. For PreparedStatement or callablestatement versions of these methods, use query parameters to throw SqlException.

3. Statement completion

When the connection is in autocommit mode, the statement that executes is automatically committed or restored when it finishes. Statement is considered complete when it is executed and all results are returned. For a ExecuteQuery method that returns a result set, the statement completes after retrieving all rows of the ResultSet object. For method Executeupdate, the statement completes when it executes. However, in a few cases where method execute is invoked, the statement completes after retrieving all the result sets or the update count that it generates.

Some DBMS treat each statement in a stored procedure as a separate statement, while others treat the entire process as a compound statement. This difference becomes very important when autocommit is enabled, because it affects when the commit method is invoked. In the former case, each statement is submitted separately, and in the latter case all statements are committed at the same time.

4. Close the Statement object

The statement object will be automatically closed by the Java garbage collector. As a good programming style, you should explicitly close them when you do not need to statement objects. This frees the DBMS resources immediately, helping to avoid potential memory problems.

5. Use method Execute

The Execute method should be used only if the statement can return multiple ResultSet objects, multiple update counts, or a combination of the ResultSet object and the update count. When executing a stored procedure or dynamically executing an unknown SQL string (that is, an application programmer is unknown at compile time), it is rare that a number of results may occur. For example, a user might execute a stored procedure, and the stored procedure could perform an update, then perform a selection, update it, make a selection, and so on. People who typically use stored procedures should know what they are returning.

Because method execute handles unconventional situations, it is not surprising that obtaining the results requires some special treatment. For example, assuming that a procedure is known to return two result sets, after you use method execute to execute the procedure, you must call method Getresultset to get the first result set, and then call the appropriate GetXXX method to get the value in it. To get a second result set, you need to call the Getmoreresults method first, and then call the Getresultset method. If a procedure is known to return two update counts, the method Getupdatecount is called first, then the getmoreresults is invoked, and the getupdatecount is called again.

The situation is more complex for those that do not know what to return. If the result is a ResultSet object, method execute returns True, or False if the result is javaint. If you return int, it means that the result is the update count or the statement being executed is a DL command. The first thing to do after invoking method execute is to invoke Getresultset or Getupdatecount. Call method Getresultset can get the first object in two or more ResultSet objects, or call method Getupdatecount can get the contents of the first update count in two or more update counts.

The method Getresultset returns NULL when the result of the SQL statement is not a result set. This may mean that the result is an update count or no other result. In this case, the only way to determine the true meaning of NULL is to invoke the method Getupdatecount, which returns an integer. This integer is the number of rows affected by the calling statement, or 1 to indicate that the result is a result set or no result. If the method Getresultset has returned null (indicating that the result is not a ResultSet object), the return value of 1 indicates no other result. This means that there is no result (or no other result) when the following conditions are true:

((Stmt.getresultset () ==null) && (Stmt.getupdatecount () ==-1))

If you have called the method Getresultset and processed the ResultSet object it returns, it is necessary to invoke the method getmoreresults to determine whether there are other result sets or update counts. If Getmoreresults returns True, the Getresultset will need to be called again to retrieve the next result set. As noted above, if Getresultset returns NULL, you need to call Getupdatecount to check that NULL indicates whether the result is an update count or that there are no other results.

When Getmoreresults returns false, it means that the SQL statement returns an update count or no other result. Therefore, you need to invoke the method Getupdatecount to check which case it is. In this case, there is no other result when the following conditions are true:

((Stmt.getmoreresults () ==false) && (Stmt.getupdatecount () ==-1))

The following code shows a method used to confirm that all result sets and update counts are generated by accessing the calling method execute:

Stmt.execute (Querystringwithunknownresults);
while (true) {
Introwcount=stmt.getupdatecount ();
if (rowcount>0) {//It is the update count
System.out.println ("Rows changed=" +count);
Stmt.getmoreresults ();
Continue
}
if (rowcount==0) {//DDL command or 0 updates
System.out.println ("No rows changed or statement was DDL command");
Stmt.getmoreresults ();
Continue
}
Execute to here and prove there's a result set
or no other result
ResultSet Rs=stmt.getresultset ();
if (rs!=null) {
...//use meta data to get information about the result set column
while (Rs.next ()) {
.//Processing results
Stmt.getmoreresults ();
Continue
}
break;//no other result.