![]() |
|||
![]() ![]() |
![]() |
![]()
|
![]() |
Managing multiple result types As discussed earlier, the methods that send SQL queries and SQL updates are different. SQL queries return result sets; SQL updates return a count of the rows updated. SQL statements will normally execute using query and update methods. However, under some circumstances, it may be difficult to estimate the type of result. An application may not know whether a given SQL statement will return a result set or a counter until the statement executes, as, for example, in the case of an interactive query tool or when calling an unknown stored procedure. A mechanism is provided to accommodate these needs. It allows an application to execute statements and then process an arbitrary collection of sets of rows and single update counts. Figure 5-10 gives an idea of the steps performed in this scenario.
How is it possible to distinguish the difference between return types? The following methods give the answer. Statement: boolean execute(String sql); ResultSet getResultSet(); int getUpdateCount(); boolean getMoreResults(); boolean execute(String sql); The SQL string passed as parameter to this method is a statement that may return multiple results or a statement whose return type is unknown in advance, programmatically speaking. It gives an indication on the form of the result or of the first result, in case multiple results are returned. It will return true if a result set is available or false if it is an integer such as an update count. ResultSet getResultSet(); The method getResultSet() returns the current result as a ResultSet. It may only be called once per result. The result set is then scanned by the usual method. This method may also be used to verify that the current result is a result set, in which case it does not return null. A null return means that there are no more results or the result is an update count, in which case it should be fetched with the getUpdateCount() method. int getUpdateCount(); The method getUpdateCount() returns the current result, which should be an integer value, or -1 if it is a result set or if there are no more results. It should be called only once per result. boolean getMoreResults(); This method moves to a statements next result. In case it is a result set, it returns true. It returns false if it is an integer or there are no more results. This method implicitly closes a current result set obtained with getResultSet(). There are no more results when (!getMoreResults() && (getUpdateCount() == -1)). Listing 5-19 best illustrates the mechanism. Listing 5-19: How to discover the result type. ... Connection myConnection = DriverManager.getConnection(url, javauser, hotjava); Statement myStatement = myConnection.createStatement(); ResultSet rs; if (myStatement.execute(sqlStatement)) { // we have a ResultSet rs = myStatement.getResultSet(); while (rs.next()) { // process the rows } } else { // we have an update count System.out.println(myStatement.getUpdateCount()); } myStatement.close(); myConnection.close(); ... ... The example in Listing 5-20 is adapted to process the results of a statement that returns multiple results both result sets and update counts in arbitrary order. Listing 5-20: How to handle multiple result types. ... ... Connection myConnection = DriverManager.getConnection(url, javauser, hotjava); Statement myStatement = myConnection.createStatement(); boolean resultSetIsAvailable; boolean moreResultsAvailable; int i = 0; int res=0; resultSetIsAvailable = myStatement.execute(sqlText); ResultSet rs = null; for (moreResultsAvailable = true; moreResultsAvailable; ) { if (resultSetIsAvailable) { if ((rs = myStatement.getResultSet()) != null) { // we have a resultset ResultSetMetaData rsmd = rs.getMetaData(); int numCols = rsmd.getColumnCount(); // display column headers for (i = 1; i <= numCols; i++) { if (i > 1) System.out.print(, ); System.out.print( rsmd.getColumnLabel(i)); } System.out.println(); // step through the rows while (rs.next()) { // process the columns for (i = 1; i <= numCols; i++) { if (i > 1) System.out.print(, ); System.out.print( rs.getString(i)); } System.out.println(); } } } else { if ((res = curStmt.getUpdateCount()) != -1) { // we have an updatecount System.out.println(res + row(s) affected.); } // else no more results else { moreResultsAvailable = false; } } if (moreResultsAvailable) { resultSetIsAvailable = myStatement.getMoreResults(); } } if (rs != null) rs.close(); myStatement.close(); ... ... Canceling unwanted results It may happen that a statements result is no longer needed for one or another reason. In this case, closing the result set will usually be sufficient (see the close() method). However, a cancel() method exists and may be called on the statement object. It may be called from a thread to cancel a statement being executed within another thread. Statement: void cancel(); void cancel();
|
![]() |
|