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 con-tains the queried results, and 2) execute methods that do not perform a data query and only return an integer, such as executeUpdate(). An interesting method is execute(), which can be used either way. In conclusion, the following points should be noted when using any of these execute methods:
- 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 the differ-ent 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 two previous methods is that the former can be used to execute some Oracle statements 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 with the use of 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 indeed is 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:
- The getResultSet() method will return a null if the run result is an integer, which is a 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. Figure 4.11 shows a piece of example code to illustrate how to distinguish what kind of result is returned by using these two methods.
FIGURE 4.11 A code example to distinguish the returned result.
A PreparedStatement object is created, the input parameter is initialized using the setString() method and then the execute() method is called to run the Oracle statement. In order to distinguish the run result, first we use the getUpdateCount() method to pick up the returned result. A ResultSet object is returned if a –1 is returned for the execution of the getUpdateCount() method. Otherwise, an integer is returned to indicate that a data update, insert or delete action has been executed, and the integer value is equal to the number of rows that have been affected.
Now that we know how to create and execute different execute methods, let’s have a closer look at the creation and execution of Oracle statements using those methods.