The Statement object is used to send SQL statements to 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 of executing statements and acquisition results; the PreparedStatement interface adds a method of processing the IN parameter; while CallableStatement adds a method of processing OUT parameters.
1. Create a Statement object
After the connection to a specific database is established, the connection can be used 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 ();
In order to execute the statement object, the SQL statement sent to the database will be supplied as a parameter to the statement:
ResultSet RS = Stmt.executeQuery ("SELECT A, B, C from Table2);
2. Using the Statement object to execute a statement
The STATEMENT interface provides three ways to perform SQL statements: 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.
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 contain a SQL statement, so you must provide the SQL statement as a parameter to the Statement.execute method. The PreparedStateMent object does not require a 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. 3. Follow the statement
When the connection is in an automatic submission mode, the executed statement will be submitted or restored when the statement is 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. Close Statement object
The 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.
5. Usage Execute
The Execute method should only be used when the statement can return multiple RESULTSET objects, multiple update counts, or a combination of RESULTSET objects and update counts. 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, the user may perform a stored procedure, and the stored procedure can perform updates, then perform 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 Javaint, return false. If Int is returned, it means that the result is the update count or execution statement is a DL 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, when the following conditions are true, there is no result (or no other results): ((Stmt.getResultSet () == null) && () == - 1))
If the method getResultset has been called and the RESULTSET object it returns, it is necessary 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 that NULL is that the result is that the 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. In this case, when the following conditions are true, no other results:
((Stmt.getmoreresults () == false && (Stmt.getUpdateCount () == - 1))
The following code demonstrates a method to confirm that all result sets and update counts generated by the access call method Execute:
