4 - Statement4.1 Overview Statement objects are used to send SQL statements into the database. There is actually three statement objects, which are all packed containers that perform SQL statements on a given connection: Statement, PreparedStatement (it comes from Statement) and CallableStatement (it is inherited from PreparedStatement). They are dedicated to sending specific types of SQL statements: Statement objects are used to perform simple SQL statements without parameters; PreparedStatement objects are used to execute the precompiled SQL statement with the IN parameter; the callablestatement object is used to perform the database stored Call of the process. The STATEMENT interface provides the basic method for performing statements and get results. The PreparedStateMent interface adds a method of processing the IN parameter; and CallableStatement adds a method of processing an OUT parameter. 4.1.1 Creating a Statement object After the connection to a specific database, you can use this connection to send SQL statements. The Statement object creates CreateStatement with Connection, as shown in the following code segment: Connection Con = DriverManager.getConnection (URL, "Sunny", "); statement stmt = con.createstatement (); to perform a Statement object, sent The SQL statement to the database will be supplied as a parameter to statement: ResultSet RS = Stmt.executeQuery ("SELECT A, B, C from Table2"); 4.1.2 Using the Statement Object Execute Statement interface to provide three execution SQL Method of statement: ExecuteQuery, ExecuteUpdate and Execute. Which method is used by the content generated by the SQL statement. Method EXECUTEQUERY is used to generate a single result set statement, such as a SELECT statement. Methods EXECUTEUPDATE are used to perform INSERT, UPDATE, or DELETE statements, and SQL DDL (data definition language) statements, such as Create Table, and Drop Table. The effect of INSERT, UPDATE or DELETE statement is to modify a column or multiple columns in a table in a table. The return value of ExecuteUpdate is an integer that indicates the number of rows affected (ie update counts). For CREATE TABLE or DROP TABLE, the return value of ExecuteUpdate is always zero. Methods EXECUTE is used to perform statements that return multiple result sets, multiple update counts, or two combinations. Because most programmers do not need this advanced feature, this overview will be described in separate section. All methods of performing statements will close the current open result set of the called Statement object (if present). This means that the processing of the current Resultset object is required before re-executing the Statement object. It should be noted that inherits all the preparedStatement interfaces of all methods in the Statement interface have their own ExecuteQuery, ExecuteUpdate, and Execute methods. The STATEMENT object itself does not include the SQL statement, so you must provide the SQL statement to the statement.execute method as a parameter.
The PreparedStatement object does not provide the SQL statement as a parameter to these methods because they already contain the precompiled SQL statement. The CallableStatement object inherits the preparedStatement form of these methods. For these methods of PreparedStatement or CallableStatement version, use the query parameter will throw SQLException. 4.1.3 Statements Complete When the connection is automatically submitted mode, the executed statements will be submitted or restored when completed. The statement is considered to be completed when the statement is executed and all results are returned. For the EXECUTEQUERY method that returns a result set, the statement is completed when all rows of the ResultSet object is retrieved. For methods executeUpdate, when it is executed, the statement is complete. However, in the case of a few calling methods Execute, the statement is completed after retrieving all result sets or its generated update count. Some DBMS regard each statement in the stored procedure as a separate statement; while others treat the entire process as a composite statement. This difference becomes very important when automatic commit is enabled, because it affects when to call your COMMIT method. In the previous case, each statement is submitted separately; in the latter case, all statements are submitted simultaneously. 4.1.4 Closing the Statement object Statement object will automatically shut down by the Java garbage collection program. As a good programming style, it should be explicitly shut down when there is no statement object. This will immediately release DBMS resources to help avoid potential memory issues. 4.1.5 SQL Swords in the Statement object STATEMENT can include SQL statements that use the SQL escape syntax. The escape syntax tells the driver's code to be handled in different ways. The driver will scan any essential syntax and convert it to a specific database understandable code. This makes the escape syntax independent of DBMS and allows programmers to use features that are not available when there is no syntax. The escape clause is defined by the curly brackets and keywords: {keyword... Parameters..} The keyword indicates the type of the escape clause, as shown below. Escape represents the LIKE escape character "%" and "_" similar to wildcards in the SQL Like clause ("%" match zero or more characters, and "_" matches a character). In order to correctly explain them, it should be added to the backslash (""), which is a special escape character in a string. In the end of the query, you can specify the following syntax to specify the character: {escape 'escape-character'}, for example, the following query uses a backslaptric character as an escape character, find the identifier of the following scribe: STMT. ExecuteQuery ("Select Name from Identifier WHERE ID LIKE` / _% '{escape `'}; fn Indicates that almost all DBMs has values, strings, time, date, system, and conversion functions. To use These functions can use the following syntax: the keyword fn with the desired function name and its parameters. For example, the following code call function Concat connects two parameters together: {Fn Concat ("Hot", "java") }; You can use the following syntax to get the current database username: {fn user ()}; scalar function may have a slightly different DBMS support, and they may not be supported by all drivers. Various DatabaseMetadata methods will list the support function.
For example, method getNumericFunctions returns a list of numeric functions separated by commas, and method getStringFunctions will return a string function, and so on. The driver will map the escape function to the corresponding syntax, or directly implement the function. D, T and TS represent the date and time text DBMS used for the syntax of the date, time, and time tag text. JDBC uses the escape subsee to support the ISO standard format of the syntax of these texts. The driver must convert the escape clause to DBMS. For example, the following syntax can be specified in the JDBC SQL statement in the JDBC SQL statement: {d `YYYY-mm-dd '} In this grammat, YYY is an era, MM is month, while DD is a date. The driver will replace this escape clause with the equivalent DBMS representation. For example, if the '28 - Feb-99 'is in line with the format of the basic database, the driver will replace {D 1999-02-28}. There is also a similar escape clause for Time and TimeStamp: {T `hh: mm: ss '} {TS` YYYY-MM-DD HH: mm: ss.f.'} Seconds in the decimal point in TimeStamp ( Partially negligible. Call or? = CALL indicates that the stored procedure If the database supports the stored procedure, they can call them from the JDBC: {call procedure_name [(?,?,..)]} or (where the process returns the result parameters) : {? = Call procedure_name [(?,?,..)]} Square brackets indicate that the content is optional. They are not the necessary part of the syntax. The input parameter can be text or parameters. For more information, see Section 7, "Callablestatement" in the JDBC Guide. You can check if the database has been stored by calling method DatabaseMetadata.supportsStoreMetadata.supportsStoreMetadata.supportsStoredProcedTaTaDures. OJ means the grammar of the external connection external connection to {Oj Outer-join} where the outer-join form is a high-level function for the Table Left Outer Join {Table / Outer-Join} on search-condition. See SQL syntax for their explanation. JDBC provides three DatabaseMetadata methods for determining which external connection types support support for drivers: SupportsouterJoint, SupportsFullouterJoint, and SupportslimitedouterJoints. Method Statement.SeteScapeProcessing opens or closes the escape process; 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 on the open state. Note: SteeScapeProcessing does not apply to the PreparedStateMent object, because it may have been sent to the database before calling the statement. See PreparedStatement for pre-compiled information. 4.1.6 Using Method The executeExecute method should only be used only when the statement can return multiple RESULTSET objects, multiple update counts, or a combination of the RESULTSET object with the update count. When a stored procedure or dynamically execute unknown SQL string (that is, the application programmer is unknown when compiling), there may be a number of results, although this is rare.
For example, a user may perform a stored procedure (see CallableStatement object - see the CallableStatement on page 135), and the stored procedure can perform updates, then perform the selection, then update, select, and so on. People usually use the stored procedure should know the content it returns. Because the method Execute handles unconventional situation, it is not enough to get some special processing is not enough. For example, suppose a certain process returns two result sets, then after using the method Execute, you must call the method getResultSet to get the first result set, then call the appropriate Getxxx method to get the value. To get the second result set, you need to call the getMoreResults method before calling the GetResultSet method. If a process is known to return two update counts, first call the method getUpdateCount, then call getMoreResults, and call GetUpdateCount again. For unknown content, the situation is more complicated. If the result is a ResultSet object, the method EXECUTE returns true; if the result is Java Int, return false. If Int is returned, it means that the result is the update count or execution statement is the DDL command. The first thing you want to do after the call method Execute is to call getResultSet or getUpdateCount. The calling method getResultSet can get the first object in two or more ResultSet objects; or call method getUpdateCount to get the first update count in two or more update counts. When the result of the SQL statement is not a result set, the method getResultSet will return NULL. This may mean that the result is an update count or no results. In this case, it is the only way to determine the true meaning of NULL to call the method getUpdateCount, which will return an integer. This integer is the number of rows affected by the calling statement; if the -1 indicates that the result is the result set or no results. If the method getResultSet has returned NULL (indicating the result is not the Resultset object), the return value -1 means no other results. That is to say, when the following conditions are true, there is no result (or no other results): (Stmt.getResultSet () == null) && () "If the method getResultset is called Handling the RESULTSET object it returns, there is a need to call the method getMoreResults to determine if there are other result sets or update counts. If getMoreResults returns true, you will need to call getResultSet again to retrieve the next result set. As mentioned above, if getResultSet returns null, you need to call getUpdateCount to check NULL is that the result is that the update count or does not have other results. When getMoreResults returns false, it indicates that the SQL statement returns an update count or no results. Therefore, you need to call the method getUpdateCount to check which situation it is.
