Jdbctm guide: Entry 4-Statement

4-Statement
This overview is taken from jdbctm database access from javatm: a tutorial and annotated reference. Javasoft is currently preparing this book. This is a tutorial and an important reference manual for JDBC, which will be published by Addison-Wesley in the spring of 1997 as part of the Java series.

4.1 Overview
The statement object is used to send SQL statements to the database. There are actually three statement objects, which are used as the package containers for executing SQL statements on a given connection: Statement, preparedstatement (inherited from statement), and callablestatement (inherited from preparedstatement ). They are dedicated to sending specific types of SQL statements: the statement object is used to execute simple SQL statements without parameters; The preparedstatement object is used to execute pre-compiled SQL statements with or without the in parameter; the callablestatement object is used to call stored procedures of the database.

The statement interface provides basic methods for executing statements and obtaining results. The preparedstatement interface adds a method to process in parameters, while the callablestatement interface adds a method to process out parameters.

4.1.1 create a statement object
After a connection is established to a specific database, the connection can be used to send SQL statements. The statement object is created using the connection method createstatement, as shown belowCodeSection:

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

To execute the statement object, the SQL statement sent to the database will be provided to the statement as a parameter:

Resultset rs = stmt.exe cutequery ("select a, B, c from Table2 ");

4.1.2 execute the statement using the statement object
The statement interface provides three methods for executing SQL statements: executequery, executeupdate, and execute. The method used is determined by the content generated by the SQL statement.

The executequery method is used to generate a single result set statement, such as the SELECT statement.

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 insert, update, or delete statement modifies one or more columns in zero or multiple rows of the table. The returned value of executeupdate is an integer indicating the number of affected rows (that is, the update count ). For statements that do not operate on rows such as create table or drop table, the return value of executeupdate is always zero.

Execute is used to execute a statement that returns multiple result sets, multiple update counts, or a combination of the two. Because mostProgramThis advanced feature is not required, so this overview will be described later in a separate section.

All the methods for executing the statement will disable the currently opened result set of the called statement object (if any ). This means that the current resultset object must be processed before the statement object is re-executed.

Note that the preparedstatement interfaces inherited from all methods in the statement interface have their own executequery, executeupdate, and execute methods. The statement object does not contain SQL statements. Therefore, the statement.exe cute method must be provided with SQL statements as parameters. Preparedstatement objects do not provide SQL statements as parameters to these methods because they already contain pre-compiled SQL statements. The callablestatement object inherits the preparedstatement form of these methods. For the preparedstatement or callablestatement versions of these methods, sqlexception is thrown using the query parameter.

4.1.3 statement completed
When the connection is in auto-submit mode, the statements executed in the connection are automatically submitted or restored when the connection is completed. When the statement is executed and all results are returned, the statement is deemed to have been completed. For the executequery method that returns a result set, the statement is completed after all rows of the resultset object are retrieved. For method executeupdate, the statement is completed when it is executed. However, in a few cases where the method execute is called, the statement is completed only after retrieving all result sets or the update count it generates.

Some DBMS regard each statement in the stored procedure as an independent statement, while others regard the entire process as a composite statement. When automatic submission is enabled, this difference becomes very important because it affects when to call the commit method. In the previous case, each statement is submitted separately. In the latter case, all statements are submitted simultaneously.

4.1.4 close the statement object
The statement object will be automatically closed by the Java garbage collection program. As a good programming style, you should explicitly close statement objects when they are not required. This will immediately release DBMS resources and help avoid potential memory problems.

4.1.5 SQL escape syntax in the statement object
Statement can contain SQL statements that use the SQL escape syntax. The escape syntax tells the driver that the Code should be processed in different ways. The driver will scan any escape syntax and convert it into code that is understandable to a specific database. This makes the escape syntax irrelevant to DBMS and allows programmers to use functions that are unavailable without the escape syntax.

Escape clauses are defined by curly brackets and keywords:

{Keyword... parameters ...}

This keyword indicates the type of the escape clause, as shown below.

Escape indicates like escape characters

The characters "%" and "_" are similar to the wildcards in the SQL like clause ("%" matches zero or multiple characters, while "_" matches one character ). To interpret them correctly, add a backslash ("\") before it, which is a special Escape Character in the string. The following syntax is included at the end of the query to specify the characters used as escape characters:

{Escape 'escape-character '}

For example, the following query uses the backslash character as the escape character to find the identifier name starting with the following line:

Stmt.exe cutequery ("Select name from identifiers
Where Id like '\ _ %' {escape '\'};

FN indicates a scalar function

Almost all DBMS have Scalar Value values, strings, time, date, system, and conversion functions. To use these functions, you can use the following escape Syntax: the keyword FN is followed by the required function name and its parameters. For example, the following code calls the Concat function to connect two parameters:

{Fn Concat ("hot", "Java ")};

You can use the following syntax to obtain the User Name of the current database:

{Fn user ()};

Scalar functions may be supported by DBMS with slightly different syntaxes, but they may not be supported by all drivers. Various databasemetadata methods will list supported functions. For example, the getnumericfunctions method returns a list of numeric functions separated by commas, while the getstringfunctions method returns string functions.

The driver maps the escape function call to the corresponding syntax or directly implements the function.

D, T, and TS indicate the date and time text

DBMS syntax for date, time, and time mark text is different. JDBC uses escape clauses to support the ISO standard format of the syntax of these texts. The driver must convert the escape clause into a DBMS representation.

For example, you can use the following syntax to specify a date in a jdbc SQL statement:

{D 'yyyy-mm-dd '}

In this syntax, YYYY is the time, mm is the month, and DD is the date. The driver replaces this escape clause with an equivalent DBMS-specific representation. For example, if '28-FEB-99 'is in the format of the basic database, the driver will replace {d} with it }.

There are similar escape clauses for time and timestamp:

{T' hh: mm: ss '}
{Ts 'yyyy-mm-dd hh: mm: Ss. F ...'}

The second (. F...) after the decimal point in Timestamp can be ignored.

Call or? = Call indicates the Stored Procedure

If the database supports stored procedures, you can call them from JDBC. Syntax:

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

Or (the process return result parameters ):

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

Square brackets indicate that the content is optional. They are not a necessary part of the syntax.

The input parameters can be text or parameters. For more information, see "callablestatement" in section 7th of the JDBC guide ".

You can call databasemetadata. supportsstoredprocedures to check whether the database supports stored procedures.

OJ indicates external connection

The external connection syntax is

{OJ outer-join}

The outer-join format is

Table left Outer Join {table/outer-join} on search-Condition

External connections are advanced functions. For their explanations, see SQL syntax. JDBC provides three databasemetadata methods to determine which external connection types the driver supports: supportsouterjoins, supportsfullouterjoins, and supportslimitedouterjoins.

The statement. setescapeprocessing method can be used to enable or disable escape processing. The default status is enable. When performance is critical, programmers may want to close it to reduce processing time. But usually it is out of the open state. Note: setescapeprocessing is not applicable to preparedstatement objects, because it may have been sent to the database before calling this statement. For pre-compilation information, see preparedstatement.

4.1.6 usage Execute
The execute method should be used only when the statement can return multiple resultset objects, multiple update counts, or a combination of resultset objects and update counts. When you execute a stored procedure or dynamically execute an unknown SQL string (that is, the application programmer is unknown during compilation), multiple results may occur, although this situation is rare. For example, a user may execute a stored procedure (using the callablestatement object-see callablestatement on page 1), and the stored procedure can be updated, and then selected and updated, then select. Generally, people who use stored procedures should know the content it returns.

Because the method execute handles Unconventional situations, it takes some special processing to obtain the results and it is not surprising. For example, if two result sets are returned in a known process, you must call the getresultset method to obtain the first result set after executing the process using method execute, call the appropriate getxxx method to obtain the value. To obtain the second result set, call the getmoreresults method and then call the getresultset method. If two update counts are returned for a specific process, call the getupdatecount method, call getmoreresults, and call getupdatecount again.

If you do not know the returned content, the situation is more complicated. If the result is a resultset object, the method execute returns true; if the result is a Java int, false is returned. If int is returned, it means that the result is an update count or the executed statement is a DDL command. The first thing to do after calling the method execute is to call getresultset or getupdatecount. Call the getresultset method to obtain the first object of two or more resultset objects, or call the getupdatecount method to obtain the first update count of two or more update counts.

If the SQL statement result is not a result set, the getresultset method returns NULL. This may mean that the result is an update count or no other results. In this case, the only way to determine the true meaning of null is to call the getupdatecount method, which returns an integer. This integer is the number of rows affected by the call statement. If it is-1, 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-1 indicates that no other results are returned. That is, when the following conditions are true, no results (or no other results) are returned ):

(Stmt. getresultset () = NULL) & (stmt. getupdatecount () =-1 ))

If you have already called the getresultset method and processed the resultset object returned by it, it is necessary to call the getmoreresults method to determine whether there are other result sets or update counts. If getmoreresults returns true, call getresultset again to retrieve the next result set. As mentioned above, if getresultset returns NULL, you need to call getupdatecount to check whether null indicates the result is an update count or no other results.

When getmoreresults returns false, it indicates that the SQL statement returns an update count or no other results. Therefore, you need to call the getupdatecount method to check which situation it is. In this case, when the following conditions are true, no other results are returned:

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

The following code demonstrates a method to confirm all the result sets and update counts generated by the Access Call method execute:

Stmt.exe cute (querystringwithunknownresults );
While (true ){
Int rowcount = 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 here to prove that there is a result set
// Or no other results

Resultset rs = stmt. getresultset;
If (RS! = NULL ){
... // Use metadata to obtain information about the result set Column
While (Rs. Next ()){
... // Processing result
Stmt. getmoreresults ();
Continue;
}
Break; // no other results