Executing a select statement: the selectAction( ) method

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();

NoteFor 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.

Executing in batch mode

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:

Using execute( ) ensures that the complete batch executes for all cases.