JDBCTM Guide: Getting Started 4 - Statement
Abstract: JDBCTM Guide: Getting Started 4 - Statement
4 - Statement
This overview is from "JDBCTM Database Access from JavaTM: A Tutorial and Annotated Reference" to quote the book. JavaSoft is currently preparing this book. This is a tutorial, but also an important JDBC reference manual, which would serve as a component of the Java series in the spring of 1997 by Addison-Wesley Publishing Company published.
4.1 Summary
Statement will be targeted for SQL statement sent to the database. In fact there are three Statement object, as they have in a given SQL statement connected to the implementation of the Container: Statement, PreparedStatement (it inherited from the Statement) and CallableStatement (it inherited from the PreparedStatement). They are dedicated to sending a specific type of SQL statements: Statement object without parameters for the implementation of a simple SQL statement; PreparedStatement targets for the implementation with or without IN parameters of the pre-compiler SQL statements; CallableStatement object database has been used for the implementation of storage Calling the process.
Statement interface provides access to statements and the implementation of the outcome of the basic method. PreparedStatement IN interface add parameters to deal with the methods and CallableStatement added processing OUT parameters.
4.1.1 create Statement object to the establishment of a specific database connection, you can use the SQL statement sent connectivity. Statement clients in Connection methods createStatement create, as shown in the following code segment:
Connection con = DriverManager.getConnection (url, sunny,);
Statement stmt = con.createStatement ();
In order to implement Statement object, be sent to the database SQL statements will be provided to the Statement as a parameter of the method:
ResultSet rs = stmt.executeQuery (SELECT a, b, c FROM Table2);
4.1.2 implementation of the use of object statements Statement
Statement interface offers three methods of implementation of SQL statements: executeQuery, executeUpdate and execute. Which one of the ways used by the SQL statement generated by the content of decisions.
ExecuteQuery method used to produce a single result set of statements, such as SELECT statements.
ExecuteUpdate methods for the implementation of INSERT, UPDATE or DELETE statements and SQL DDL (Data Definition Language) statements, such as CREATE TABLE and DROP TABLE. INSERT, UPDATE or DELETE statements have the effect of amending the table, or to visit one or more of the out. ExecuteUpdate the return value is an integer instructions affected by the number of rows (ie updated count). The CREATE TABLE or DROP TABLE, which do not operate to the statement, executeUpdate the return value of zero.
Back to execute methods for the implementation of a number of result sets, a number of updated count or combination of the two statements. Because most programmers do not need the advanced features, so this will be outlined in the back of a separate its introduction.
All statements implementation method would be closed by the Statement object called the current open result set (if it exists). This means that in the re-implementation of Statement object before the completion of the current ResultSet object handling.
It should be noted that the inheritance of all Statement interface method has its own interface PreparedStatement executeQuery, executeUpdate and execute methods. Statement object itself does not contain SQL statements, which must be provided to the SQL statement Statement.execute method as a parameter. PreparedStatement SQL statement will not object as a parameter for these, because they already contain pre-compiled SQL statements. CallableStatement objects inherit these methods PreparedStatement form. For these methods PreparedStatement or CallableStatement version, with the query parameters will throw SQLException.
4.1.3 When completed sentences in connection to the automatic mode, the implementation of the sentence upon completion will be automatically submitted or reduction. Statements in the Executive and all the results have been returned, that is, that has been completed. For return a result set of executeQuery method, in the search End ResultSet object of all the statements trip completed. The method executeUpdate, when it is completed implementation statements. But in a few ways execute call in the case of all in the search results generated by its set or update statements after counting was completed.
Some DBMS will have been in the process of storage for each statement as an independent sentence, while others will be regarded as a composite whole process statement. In the opening automatically submitted, the difference becomes very important, because it affects when calling commit method. In the former case, and each separate statements submitted in the latter case, all statements at the same time.
4.1.4 Close Statement object
Statement targets Java garbage collection procedures will be shut down automatically. And as a good programming style, should the need Statement object to the closure of their explicit. This will be the immediate release of DBMS resources, help avoid potential memory problems.
4.1.5 Statement object syntax of SQL Escape
Statement escaped may include the use of SQL syntax of SQL statements. Grammar told the driver escaped code which should be handled differently. Drivers will scan any escaped grammar, and its conversion into a specific database understandable code. This makes escaped grammar has nothing to do with the DBMS, and allows programmers to use in the absence of escape must not use the grammar function.
Escape clause from flower brackets and defined keywords:
(Keyword.. Parameters..)
The keyword instructions escape clause types, as follows.
LIKE escaped characters that escape
Characters "%" and "_" similar to the SQL LIKE clause in the wildcard ( "%" matching zero or more characters, and "_" is a matching characters). In order to correctly interpret them, should be added to the front in his backslash ( "\"), it is the special string escaped characters. At the end include the following query syntax can be earmarked for the escape character of the characters:
(Escape escape-character)
For instance, the following for use as a backslash character escape characters to underscore the beginning of the search identifier:
Stmt.executeQuery (SELECT name FROM Identifiers
WHERE Id LIKE `\ _` escape% (\);
Fn said scalar function
Almost all have a scalar value DBMS Numerical, string, time, date, systems and transfer function. To use these functions can be used to escape the following syntax: Keyword fn heel for the function names and parameters. For example, the following code concat function call will be connected together two parameters:
(Fn concat (Hot, Java));
The following syntax can be used for current database users:
(Fn user ());
Scalar function may be slightly different from the grammar of DBMS support, and they may not be all driver support. DatabaseMetaData method will list all the support of the function. For example, the method getNumericFunctions return comma-separated list of numerical functions, and methods getStringFunctions will return to the string functions, and so on.
Driver Escape function call will be mapped to the corresponding syntax, or directly to the function.
D, t-ts and that the date and time text
DBMS for the date, time and time marker text syntax varies. JDBC escape clause to support the use of these words in grammar the ISO standard format. Drivers must be escape clauses into DBMS said.
For example, the following syntax can be used in the JDBC SQL statements in the specified dates:
(D `yyyy-mm-dd)
In the grammar, for the yyyy, mm for the month, compared with dd date. Drivers will use the equivalent of a specific DBMS in this escape clause that replacement. For example, if 28 - FEB-99 meet the basic database format, drivers will use it to replace (d) 1999-02-28.
For TIME, and TIMESTAMP has a similar escape clause:
(T `hh: mm: ss)
(Ts `yyyy-mm-dd hh: mm: ss.f..)
TIMESTAMP after the decimal point in the second (. F..) Can be neglected.
Call or? = Call that has been stored procedure
If the database support has been stored procedure can call them in from JDBC, grammar:
Call procedure_name ([(?,?,..)])
Or (return to the process which results parameters):
(? Call procedure_name = [(?,?,..)])
The instructions in square brackets is optional. They are not a necessary part of grammar.
Input parameters can be text or parameters. For more information, see JDBC Guide section 7, "CallableStatement."
DatabaseMetaData.supportsStoredProcedures method can check by calling the database has been whether to support the storage process.
Oj said external connections
The syntax for external connectivity
Oj outer-join ()
Which form the outer-join
Table LEFT OUTER JOIN (table / outer-join) ON search-condition
External connections are advanced features. On their interpretation can be found in SQL syntax. DatabaseMetaData JDBC offers three methods used to determine which driver support external connection type: supportsOuterJoins, supportsFullOuterJoins and supportsLimitedOuterJoins.
Statement.setEscapeProcessing method can be turned on or off escape; default status for the Open. When performance is extremely important, programmers may want to close it to reduce processing time. But usually it will open for the state. It should be noted: PreparedStatement setEscapeProcessing does not apply, because the statements before calling it may have been sent to the database. The pre-compiled information, see PreparedStatement.
4.1.6 execute use
We should only execute in a number of statements to return to ResultSet object, a number of updated count or counts ResultSet object and update the composition of use. When the implementation of a storage process has been the implementation of the unknown or dynamic SQL string (that is, application programmers unknown at compile time), there is risk of a number of results, even though such cases are rare. For example, a user may have a storage implementation process (using CallableStatement target - see the CallableStatement 135), and the storage process executable has been updated, and then the implementation of choice, to be updated to choose, and so on. Usually use the stored procedure returned to the people should know the contents of it.
Because dealing with unconventional methods execute, it needs access to the results and some special treatment is not surprising. For example, assume that the known results of a process to return two sets in use execute the implementation of the process, the method must be called getResultSet was a result of the first set, and then call the appropriate methods getXXX access to the value of one. For the second result set, the need to call getMoreResults method, and then call getResultSet methods. If a process known to return to two counts update, the first method getUpdateCount call, and then call getMoreResults, and once again call getUpdateCount.
Back to the contents do not know, the situation is more complicated. If the outcome is ResultSet object, execute method returns true if the result is Java int, it returns false. If int return, it means that the result is to update the count or implementation of the DDL statement is ordered. Methods execute after calling the first thing to do is call or getUpdateCount getResultSet. Call getResultSet method can be two or more objects in a ResultSet object method call or two or more getUpdateCount be updated count in the first count update the content.
When the SQL statement is not the result of result sets, then will return null getResultSet methods. This may mean that the result is an updated count or other results. In such circumstances, the real meaning of the judgement null method is the only way getUpdateCount call, it will return an integer. The statement called for integer affected by the number of rows; -1 if the result is that the result set or no results. If the methods getResultSet have returned to the null (that is not the result ResultSet object), the return value -1 that there is no other outcome. In other words, when the following conditions are true, that there are no results (or other results):
((Stmt.getResultSet () == null) & (stmt.getUpdateCount () == -1))
If you have already called getResultSet methods and handling the return of its ResultSet object, it is necessary to call methods getMoreResults to ascertain whether there are other results set or updated count. If true getMoreResults return, the need to once again call getResultSet a search result set. As noted above, if getResultSet return null, you need to check null getUpdateCount call is to update the results of that count or that there is no other outcome.
When getMoreResults return false, it said that the return of a SQL statement to update or other results count. Call getUpdateCount methods need to examine what it is a situation. In such circumstances, when the following conditions are true that there are no other results:
((Stmt.getMoreResults () == false) & & (stmt.getUpdateCount () == -1))
The following code demonstrates a method used to confirm the visit by calling methods execute the full results of the compilation and update counting:
Stmt.execute (queryStringWithUnknownResults);
While (true) (
Int rowCount = stmt.getUpdateCount ();
If (rowCount> 0) (/ / It is updated count
System.out.println (Rows changed = + count);
Stmt.getMoreResults ();
Continue;
)
If (rowCount == 0) (/ / DDL commands or 0 update
System.out.println (No rows changed or statement was DDL
Command);
Stmt.getMoreResults ();
Continue;
)
/ / Implementation here, proved to be a result set
/ / Or other results
ResultSet rs = stmt.getResultSet;
If (rs! = Null) (
.. / / Use of metadata access to the results set out on the information
While (rs.next ()) (
.. / / Results
Stmt.getMoreResults ();
Continue;
)
Break; / / No other results
↑ Back
Tags: Statement