A. The java.sql library is first imported, since we need to use some of its components to con-nect to and implement in our sample Oracle database.
B. First a class-level variable, con, is created. This variable is used to hold the connection instance to our sample Oracle database, CSE _ DEPT.
C. An ArrayList instance, result, is created and used to collect and store our query result and return to the consuming project. The reason we used an ArrayList and not a List is that the former is a concrete class, but the latter is an abstract class, and a runtime exception may be encountered if an abstract class is used as a returned object to the calling method.
D. The data query statement is created with a positional parameter as the dynamic parameter for the query criterion faculty _ name.
E. The user-defined method DBConnection() that will be built later is called to setup a connection between our Web service and our sample database. A connection instance, con, is returned after the execution of this method.
F. A new PreparedStatement instance, pstmt, is declared and created to perform the query.
G. The setString() method is used to setup the actual value that is our input faculty name for the positional parameter faculty _ name.
H. The query is performed by calling the executeQuery() method, and the query result is returned and stored in a ResultSet object, rs.
FIGURE 9.24 The code for the new operation, QueryFaculty().
I. To get more related information about the queried database, the getMetaData() method is executed, and the result is stored in a ResultSetMetaData instance, rsmd.
J. while() and for() loops are used to pick up each column from the queried result that is stored in the ResultSet object rs. In fact, the while() loop only runs one time since only one matching faculty row will be returned. The getColumnCount() method is used as the upperbound of the for() loop, but this upper bound must be decreased by 1 since totally there are eight (8) columns in the Faculty Table, but we only need to query and pick up the first seven (7) columns. The last column is the faculty image object, but it can-not be added into the ArrayList as a String object. Thus, this query only returns the first seven columns in a matching faculty row.
K. The close() method is executed to disconnect from our sample database.
L. The queried result is returned to the calling method.
M. The catch block is used to track and display any exception during the data query process, and a null will be returned if one occurs.
During the coding process, you may encounter some runtime compiling errors. The main reason for those errors is that some packages are missing. To fix these errors, just right-click on any space inside this code window, and select the Fix Imports item to add those miss-ing packages.
Now let’s build our user-defined method, DBConnection(), to setup a connection to our sam-ple database from our Web service project.
FIGURE 9.25 The code for the user-defined method DBConnection().