JDBCTM Guide: Getting Started 4-statement_jsp programming

4-statement
This overview is quoted in the book Jdbctm Database Access from Javatm:a Tutorial and annotated Reference. JavaSoft is currently preparing the book. This is a tutorial, as well as an important reference manual for JDBC, which will be published as part of the Java family in the spring of 1997 by Addison-wesley Publishing Company.


4.1 Overview
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 PreparedStatement inherited from). 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; The PreparedStatement object is used to execute a precompiled SQL statement with or without an in parameter; The CallableStatement object is used to hold A row's call to a stored procedure in a database.

The Statement interface provides the basic method of executing statements and obtaining results. The PreparedStatement interface adds methods for processing in parameters, while CallableStatement adds methods to handle out parameters.


4.1.1 Create 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");

4.1.2 use Statement object to execute statement
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.

Method execute is used to execute statements that return multiple result sets, multiple update counts, or combinations of both. Since most programmers do not need this advanced functionality, this overview is described later in a separate section.

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. The PreparedStatement object does not provide SQL statements 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.


4.1.3 Statement Complete
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.1.4 Close 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.


4.1.5 The SQL escape syntax in the Statement object
Statement can contain SQL statements that use SQL escape syntax. The escape syntax tells the driver that the code in it should be handled in a different way. The driver scans any escape syntax and converts it into code that is understandable to a particular database. This makes the escape syntax independent of the DBMS and allows the programmer to use features that are not available when there is no escape syntax.

The conversion clauses are defined by curly braces and keywords:

{keyword ... parameters ...}

This keyword indicates the type of the goto sentence, as shown below.


Escape represents the like escape character


The characters "%" and "_" are similar to the wildcard characters in the SQL like clause ("%" matches 0 or more characters, while "_" matches one character). To properly interpret them, precede them with a backslash ("\"), which is a special escape character in the string. You can specify the characters to use as escape characters at the end of the query by including the following syntax:

{Escape ' Escape-character '}


For example, the following query uses the backslash character as an escape character, looking for an identifier name that begins with an underscore:

Stmt.executequery ("Select name from Identifiers
WHERE Id like ' \_% ' {escape '};


FN represents scalar functions


Almost all DBMS have numeric, string, time, date, system, and transformation functions for scalar values. To use these functions, you can use the following escape syntax: The keyword FN is followed by the desired function name and its parameters. For example, the following code calls the function concat to concatenate two parameters together:

{fn concat ("Hot", "Java")};


You can obtain the current database username using the following syntax:

{fn user ()};


Scalar functions may be supported by a slightly different DBMS for syntax, and they may not be supported by all drivers. The various DatabaseMetaData methods will list the supported functions. For example, method getnumericfunctions returns a comma-delimited list of numeric functions, and method Getstringfunctions returns string functions, and so on.

The driver maps an escape function call to the appropriate syntax, or implements the function directly.


D, T, and TS represent date and time text


The syntax used by the DBMS for date, time, and time markup text varies. JDBC uses a conversion clause to support the syntax of these words in the ISO standard format. The driver must convert the conversion clause into a DBMS representation.

For example, you can specify a date in a JDBC SQL statement with the following syntax:

{d ' yyyy-mm-dd '}


In this syntax, YYYY is the age, MM is the month, and DD is the date. The driver replaces the conversion clause with an equivalent DBMS-specific representation. For example, if ' 28-feb-99 ' conforms to the basic database format, the driver replaces {D 1999-02-28} with it.

There are similar clauses for time and TIMESTAMP:

{t ' hh:mm:ss '}
{ts ' yyyy-mm-dd hh:mm:ss.f ... '}


TIMESTAMP seconds after the decimal point (. f ...). ) part can be ignored.


Call or? = call indicates stored procedure



If the database supports stored procedures, you can call them from JDBC, which is:

{Call procedure_name[(?,?,...)]}


or (where the procedure returns the result parameter):

{? = Call procedure_name[(?,?,...)]}


The square brackets indicate that the contents are optional. They are not a necessary part of the grammar.

Input parameters can be text or parameters. For more information, see "CallableStatement" in section 7th of the JDBC Guide.

You can check whether the database supports stored procedures by calling method Databasemetadata.supportsstoredprocedures.



OJ represents an external connection



The syntax for external joins is

{OJ Outer-join}


Where the Outer-join form is

Table left OUTER JOIN {table/outer-join} on Search-condition


External connections are advanced features. For an explanation of them, see SQL syntax. JDBC provides three DatabaseMetaData methods to determine which external connection types are supported by the driver: Supportsouterjoins, Supportsfullouterjoins, and Supportslimitedouterjoins.


Method statement.setescapeprocessing can turn on or off the escape processing, and the default state is open. When performance is extremely important, programmers may want to close it to reduce processing time. But usually it will be out of the open state. Note that setescapeprocessing does not apply to the PreparedStatement object because it may have been sent to the database before the statement was invoked. For information about precompilation, see PreparedStatement.


4.1.6 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 (using the CallableStatement object-see callablestatement on page 135th), 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 a Java int. If int is returned, it means that the result is an update count or that the statement being executed is a DDL 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) {
int rowcount = Stmt.getupdatecount ();
if (RowCount > 0) {//It is update count
System.out.println ("Rows changed =" + count);
Stmt.getmoreresults ();
Continue
}
if (rowcount = = 0) {//DDL command or 0 update
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 result set columns
while (Rs.next ()) {
..//Processing results
Stmt.getmoreresults ();
Continue
}
Break No other results