The selectAction( ) method executes a Transact-SQL select statement:
String sql = "select name, home from xmp where id=?";
The where clause uses a parameter marker (?) for the row to be selected. Using the JDBC setInt( ) method, selectAction( ) supplies a value for the parameter marker after preparing the SQL statement:
PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setInt(1, id);
selectAction( ) then executes the select statement:
ResultSet rs = pstmt.executeQuery();
For SQL statements that return no results, use doSQL(
) and updateAction( ). They execute
SQL statements with the executeUpdate( ) method.
For SQL statements that do return results, use the executeQuery(
) method, which returns a JDBC ResultSet object.
The ResultSet object is similar to a SQL cursor. Initially, it is positioned before the first row of results. Each call of the next( ) method advances the ResultSet object to the next row, until there are no more rows.
selectAction( ) requires that the ResultSet object have exactly one row. The selecter( ) method invokes the next method, and checks for the case where ResultSet has no rows or more than one row.
if (rs.next()) {
name = rs.getString(1);
home = (Address)rs.getObject(2);
if (rs.next()) {
throw new Exception("Error: Select returned multiple rows");
} else { // No action
}
} else { throw new Exception("Error: Select returned no rows");
}
In the above code, the call of methods getString( ) and getObject( ) retrieve the two columns of the first row of the result set. The expression “(Address)rs.getObject(2)” retrieves the second column as a Java object, and then coerces that object to the Address class. If the returned object is not an Address, then an exception is raised.
selectAction( ) retrieves a single row and checks for the cases of no rows or more than one row. An application that processes a multiple row ResultSet would simply loop on the calls of the next( ) method, and process each row as for a single row.
If you want to execute a batch of SQL statements, make sure that you use the execute( ) method. If you use executeQuery( ) for batch mode:
If the batch operation does not return a result set (contains no select statements), the batch executes without error.
If the batch operation returns one result set, all statements after the statement that returns the result are ignored. If getXXX( ) is called to get an output parameter, the remaining statements execute and the current result set is closed.
If the batch operation returns more than one result set, an exception is raised and the operation aborts.
Using execute( ) ensures that the complete batch executes for all cases.
