6.3.7.4 Use the Java execute() Method to Query the Faculty Table
As we discussed in Section 6.3.3.3, unlike the Java executeQuery() and Java execute-Update() methods, the Java execute() method is a more popular query operation, and it can be used for both selecting queries and data-manipulating queries to an Oracle database. The execute() method will not return any result itself, and one needs to use either getResult-Set() or getUpdateCount() method to pick up the results. Both methods belong to the Statement class. The key point is:
- 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 value.
In this section, we will discuss how to use this method to perform data query operation to our Faculty Table. In fact, we will add another if selection block with the code into the Select but-ton Click event handler to use the execute() method to perform this data query.
Open the Select button Click event handler and add the code highlighted in bold and shown in Figure 6.24 into this event handler.
Let’s have a closer look at this new piece of added code to see how it works.
A. If the user selected the Java execute() Method from the Query Method combo box, a try-catch block is used to create a prepared statement using the prepareS-tatement() method with the query string as the argument. Then the setString() method is used to set up the positional dynamic parameter, which is obtained from the ComboName combo box and selected by the user.
B. The Java execute() method is executed to perform this data query. The advantage of using this method is that both a query-related action and a non-query-related action can be performed by using this method. The disadvantage of using this method is that the run result cannot be determined when this method is used, since this method can only execute either a data query by returning a ResultSet object or a manipulating action, such as updat-ing, inserting and deleting, by returning an integer.
C. Suppose we do not know what kind of data will be returned by running the execute() method; we assume that a manipulating action has been performed by calling this method. So we try to use the getUpdateCount() method to pick up the run result, which is supposed to be an integer.
D. If the returned result of calling of the getUpdateCount() method is –1, this means that the result of the execute() method is not an integer; instead, it is a ResultSet object. The getResultSet() method should be used to pick up that result. The following code is identical to that used in the Java executeQuery() method to get the query result.
E. Otherwise, if the run result of this execute() method returns an integer, it means that a data manipulation action has been performed. Of course, this is impossible, since we used this method to perform a data query, not a data manipulation. Thus, an error would have occurred if that had really happened, and this error would be displayed with a message. F. The catch block is used to track and monitor any possible error during this query opera-
tion and display it if any error has occurred.
Next let’s take care of using the Java CallableStatement() method to query our Faculty Table.
FIGURE 6.24 The added code for the Java execute() method.