6.3.3.3 Create and Manage the Statement and PreparedStatement Objects
The Statement class contains three important query methods with different functions: execute-Query(), executeUpdate() and execute(). For each method, different operations can be performed and different results can be returned. Generally, the execute methods can be divided into two categories: 1) execute methods that need to perform a data query, such as executeQuery(), which returns an instance of ResultSet that contains the queried results, and 2) execute methods that do not perform a data query and only return an integer, such as executeUpdate(). An inter-esting method is execute(), which can be used either way.
- The executeQuery() method performs a data query and returns a ResultSet object that contains the queried results.
- The executeUpdate() method does not perform a data query; instead, it only performs either a data update, insert or delete action against the database and returns an integer that equals the number of rows that have been successfully updated, inserted or deleted.
- The execute() method is a special method, and it can be used either way. All different data actions can be performed by using this method, such as data query, data insertion, data updating and data deleting. The most important difference between the execute()
method and the other two methods is that the former can be used to execute Oracle state-ments that are unknown at compile time or return multiple results from stored procedures. Another difference is that the execute() method does not return any result itself, and one needs to use the getResultSet() or getUpdateCount() method to pick up the results. Both methods belong to the Statement interface.
Confusion may arise when using the execute() method. As we mentioned, since any Oracle statement, either known or unknown at compile time, can be used with the execute() method, how do we know the execution results? Yes, that is indeed a problem. However, fortunately, we can solve this problem by using some testing methods indirectly.
In fact, we can call either the getResultSet() or getUpdateCount() method to try to pick up the run results from execution of the execute() method. The key point is that:
- The getResultSet() method will return a null if the run result is an integer, which is the number of rows that have been affected, either inserted, updated or deleted.
- The getUpdateCount() method will return a –1 if the run result is a ResultSet.
Based on these two key points, we can easily determine whether a result is a ResultSet or an integer.
As we mentioned, a static statement does not contain any parameters passing into or from the database; therefore, this kind of statement does not meet our requirements, since we need to pass parameters, such as username and password, into our sample database to perform the login process. To make a data query to our LogIn Table to perform the login process, we need to use the second type of statement, PreparedStatement.
The advantages of using a PreparedStatement object to build and perform a dynamic query are that both the query flexibility can be increased and the query execution speed and efficiency can be significantly improved since the prepared statement can be pre-compiled and re-run again for a multiple query situation.