Handling Results
A DBMS usually returns results after executing a statement. The results are of different types. The executeQuery() method returns a result set object, and the executeUpdate() returns an integer. Of course, because the result of the executeQuery() method will always be used within the program, it would be useless to discard the result of a SQL query. In the case of an update, you should also use the result, at least to verify that everything happened as expected and to discover how many rows of data were affected by the update.
Fetchable result types
The SQL clauses listed in Table 6-3 return known result types: rows of data and/or an integer value.
| Type of SQL Statement | Type Returned |
|---|---|
| CREATE TABLE employees (...) | Nothing |
| SELECT * FROM employees | Rows and an integer |
| SELECT MAX(salary) FROM employees | Rows and an integer |
| UPDATE employees SET salary = 70000 | An integer WHERE name = "Jones" |
| INSERT INTO employees VALUES (...) | An integer |
| DELETE FROM employees WHERE salary > 150000 | An integer |
In summary, there are two possible result set return types: an integer and a result set composed of zero or more rows of table data. Of course, there are different methods for handling such results, and there is even a way to discover the result type of an unknown SQL query, as explained in the section “Managing multiple result types,” later in this chapter.
Getting the result set
SQL statements that return an integer such as DELETE, UPDATE, and INSERT don’t need additional processing. The method to send them returns an integer and is usually interpreted as a counter. Other SQL statements normally don’t return rows of data or a counter.
SELECT queries return normal rows of data. The result set is composed of zero or more rows coming from the database. The next operation is scanning this result set, row by row, until all rows have been fetched. This operation is done within a loop, as shown in Figure 6-6. I show you how to analyze the data that composes rows in the "Accessing columns" section, later in this chapter.
Figure 6-6: An overview of getting the result set.
The result set object is created when sending the statement to the DBMS. It is created by executing the statement object. Closing the result set releases all data associated with the result set.
Retrieving rows
Result sets are composed of rows. The ResultSet.next() method is used in the loop to access these rows, as shown in the following.
ResultSet’s next() Method
boolean next();
Figure 6-7 illustrates the mechanism used to scan the rows in a result set instance named resultSet.
Figure 6-7: The next() method is used to scan a result set.
It is important to position the cursor before the first row of the result set. The method next() needs to be called first to access the first row. After the first call, the first row becomes the current row, and it is ready to be processed. Successive calls to next() make the next rows current, row by row, of course.
Listing 6-14 shows how to scan a result set.
Listing 6-14: Scanning a Result Set
 |
... Connection myConnection = DriverManager.getConnection(url, "javauser", "hotjava"); Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery(sqlQuery); while (rs.next()) { // we got a row } myStatement.close(); myConnection.close(); ... ... |
Getting the number and label of columns
A row is usually composed of table data that may be organized in different columns of different types. It may be important to discover the properties of the result set’s rows, the number of columns, and the type of data in each column. I show in the following section, “Accessing columns,” how to get such information about result sets. Here, I’ll use only the column number and column labels.
ResultSet’s Metadata Information for Columns
int getMetaData().getColumnCount(); String getMetaData().getColumnLabel(int i);
The getMetaData() method returns a ResultSetMetaData object that is explained later (in Chapter 9) because it is quite complex. Calling getColumnCount() on this object returns the expected value.
The return type for the first line of the preceding is integer and is the number of columns in the rows composing this result set.
For the second line, String getMetaData().getColumnLabel(int i);, the parameter is the column index where a value of 1 indicates the first column. The method obviously returns the label for the column at this index.
It may be more efficient to store the ResultSetMetaData object once instead of calling the method to create it each time it is necessary to access a property. The
driver may provide caching, but it is often preferable not to abuse such features when not really needed.
Listing 6-15 shows how to call getColumnCount() and getColumnLabel().
Listing 6-15: Getting the Number and Label of Columns
|
... Connection myConnection = DriverManager.getConnection(url, "javauser", "hotjava"); Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery(sqlQuery); int maxColumns = rs.getMetaData().getColumnCount(); for (int i = 1; i <= maxColumns; i++) { if (i > 1) System.out.print(", "); System.out.print(rs.getMetaData().getColumnLabel(i)); } System.out.println("===================================="); while (rs.next()) { // we got a row } myStatement.close(); myConnection.close(); ... ... |
Accessing columns
As shown in Figure 6-8, columns must be fetched one by one, in left-to-right order. Fetching may be done in a loop using the column indexes or column names discovered by the ResultSetMetaData object.
Figure 6-8: An overview of accessing columns.
The row’s contents are accessible via getXXX() methods that allow the extraction of the various values of the columns of rows in the result set.
There are two ways of accessing columns: by column index or by column name. Accessing a column by name is more convenient but less efficient because it internally needs comparisons of strings before finding the column. Certain SQL statements return tables without column names or with multiple identical column names; it is absolutely necessary to use column numbers in these cases. Figure 6-9 illustrates the access to a row’s columns.
Figure 6-9: Accessing columns with getXXX().
All columns within a row must be read in left-to-right order, and each column must be read only once. This rule may not be true with some JDBC drivers, but it is preferable to observe it to ensure maximum portability.
By column indexes
The following is the list of getXXX() methods available to fetch columns in a row.
ResultSet’s Methods to Access Columns by Index
String getString(int columnIndex); boolean getBoolean(int columnIndex); byte getByte(int columnIndex); short getShort(int columnIndex); int getInt(int columnIndex); long getLong(int columnIndex); float getFloat(int columnIndex); double getDouble(int columnIndex); byte[] getBytes(int columnIndex); java.sql.Date getDate(int columnIndex); java.sql.Date getDate(int columnIndex, java.util.Calendar cal); java.sql.Time getTime(int columnIndex); java.sql.Timestamp getTimestamp(int columnIndex); java.sql.Timestamp getTimestamp(int columnIndex, java.util.Calendar cal); java.io.InputStream getAsciiStream(int columnIndex); java.io.InputStream getBinaryStream(int columnIndex); Object getObject(int columnIndex); Object getObject(int columnIndex, java.util.Map map); java.sql.Array getArray(int columnIndex); java.math.BigDecimal getBigDecimal(int columnIndex); java.sql.Blob getBlob(int columnIndex); java.sql.Clob getClob(int columnIndex); java.io.Reader getCharacterStream(int columnIndex); java.sql.Ref getRef(int columnIndex); java.net.URL getURL(int columnIndex); return_type getXXX(int columnIndex);
All these methods return the column value in the current row. Column indexes are integers.
The example in Listing 6-16 shows how to execute a SQL statement and retrieve the results using column indexes. Note that the rows are always read from left-to-right and that columns are read only once.
Listing 6-16: Using Column Indexes
|
... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery("SELECT name, title, salary FROM employees"); while (rs.next()) { // print the columns of the row that was retrieved String empName = rs.getString(1); String empTitle = rs.getString(2); long empSalary = rs.getLong(3); System.out.println("Employee " + empName + " is " + empTitle + " and earns $" + empSalary); } ... ... |
By column names
Column names may be more convenient to use than column indexes. The following are the getXXX() methods supporting column names.
ResultSet’s Methods to Access Columns by Name
String getString(String columnName); boolean getBoolean(String columnName); byte getByte(String columnName); short getShort(String columnName); int getInt(String columnName); long getLong(String columnName); float getFloat(String columnName); double getDouble(String columnName); byte[] getBytes(String columnName); java.sql.Date getDate(String columnName); java.sql.Date getDate(String columnName, java.util.Calendar cal); java.sql.Time getTime(String columnName); java.sql.Timestamp getTimestamp(String columnName); java.sql.Timestamp getTimestamp(String columnName, java.util.Calendar cal); java.io.InputStream getAsciiStream(String columnName); java.io.InputStream getBinaryStream(String columnName); Object getObject(String columnName); Object getObject(String columnName, java.util.Map map); java.sql.Array getArray(String columnName); java.math.BigDecimal getBigDecimal(String columnName); java.sql.Blob getBlob(String columnName); java.sql.Clob getClob(String columnName); java.io.Reader getCharacterStream(String columnName); java.sql.Ref getRef(String columnName); java.net.URL getURL(String columnName); return_type getXXX(String columnName);
The parameter should match exactly with the row’s column name that needs to be accessed.
Listing 6-17 contains the same example as Listing 6-16, except using column names instead of column indexes.
Listing 6-17: Using Column Names
|
... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery("SELECT name, title, salary FROM employees"); while (rs.next()) { // print the columns of the row that was retrieved String empName = rs.getString("name"); String empTitle = rs.getString("title"); long empSalary = rs.getLong("salary"); System.out.println("Employee " + empName + " is " + empTitle + " and earns $" + empSalary); } ... ... |
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 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 a 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 6-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 provide the answer.
Statement’s Methods for Dealing with Multiple Result Types
boolean execute(String sql); ResultSet getResultSet(); int getUpdateCount(); boolean getMoreResults(); boolean getMoreResults(int current);
Figure 6-10 provides an overview of the logic needed to manage multiple result types.
Figure 6-10: An overview of managing multiple result types.
The SQL string passed as a parameter to the method boolean execute(String sql); is a statement that may return multiple results or a statement whose return type is unknown in advance, programmatically speaking. Simple SQL statements usually return either an update count or a result set. However, certain stored procedures may return update counts or result sets, sometimes mixed together. This method gives an indication on the form of the result or of the first result, in case multiple results are returned. This method returns true if a result set is available or false if the result of the execution of the SQL string is an integer such as an update count.
The method getResultSet() in the second line of the preceding returns the current result as a ResultSet. It can be called only 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 doesn’t 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.
The method getUpdateCount() in the third line of the preceding 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.
The method in the following two lines moves to a statement’s next result:
boolean getMoreResults(); boolean getMoreResults(int current);
In case the statement’s next result is a result set, these methods return true. They return false if the result is an integer or there are no more results. The getMoreResults() method implicitly closes a current result set obtained with getResultSet(). When used with an integer argument, the getMoreResults() method can preserve the latest result set. There are three possible values for this argument:
-
CLOSE_CURRENT_RESULT: The current result set is closed as soon as a new result set is returned.
-
KEEP_CURRENT_RESULT: The current result set isn’t closed while fetching the next one.
-
CLOSE_ALL_RESULTS: All previous result sets will be closed when the next result is returned.
There are no more results after (!getMoreResults() && (getUpdateCount() == -1)).
Listing 6-18 best illustrates the mechanism used to discover the result type of a statement.
Listing 6-18: Discovering 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 6-19 is adapted to process the results of a statement that returns multiple results — both result sets and update counts in arbitrary order.
Listing 6-19: Handling 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(Statement.CLOSE_ALL_RESULTS); } } if (rs != null) rs.close(); myStatement.close(); ... ... |
Canceling unwanted results
It may happen that a statement’s 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 in the next section, "Closing the result set"). However, a cancel() method exists and may be called on the statement object, as shown in the following. It may be called from a thread to cancel a statement being executed within another thread.
Statement’s Cancel Method
void cancel();
The cancel() method, shown in Listing 6-20, cancels a statement being executed.
| Note | Note that the cancel() method is applicable only to statements that return either a single result set or multiple results — not to statements that are composed of a single SQL update, insert, or delete. |
Listing 6-20: Canceling Unwanted Results
|
... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery("SELECT name, title, salary FROM employees"); int i = 0; while (rs.next()) { // increment the counter i++; // print the columns of the row that was retrieved String empName = rs.getString("name"); String empTitle = rs.getString("title"); long empSalary = rs.getLong("salary"); System.out.println("Employee " + empName + " is " + empTitle + " and earns $" + empSalary); // cancel all results if 100 rows of data were already // retrieved if (i >= 100) myStatement.cancel(); } ... ... |
Closing the result set
As in the case with statements and connections, result sets must be closed when no longer needed.
ResultSet’s Close Method
void close();
This method closes the result set and releases database and JDBC resources associated with it. When a statement object is closed, re-executed, or used to retrieve the next result of multiple result sets, its result set is automatically closed.
Converting types
Depending on the situation, it may be necessary to perform automatic conversions between SQL types used in a specific result set column and Java types. For example, you can use getString() to access an element of SQL type DATE. The short example in Listing 6-21 shows how to force a conversion of SQL type DATE to Java String.
Listing 6-21: Type Conversion of DATE to String
|
... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery("SELECT name, datehired, salary FROM employees"); while (rs.next()) { // print the values of the row that was retrieved String empName = rs.getString("name"); String empDateHired = rs.getString("datehired"); long empSalary = rs.getLong("salary"); System.out.println("Employee " + empName + " was hired on the " + empDateHired + " and earns $" + empSalary); } ... ... |
Table 6-4 shows the supported conversions via the getXXX() methods. Unsupported conversions usually raise a SQLException when attempted.
| TINYINT | SMALLINT | INTEGER | BIGINT | REAL | FLOAT | ||
|---|---|---|---|---|---|---|---|
| getByte() | J | K | K | K | K | K | |
| getShort() | K | J | K | K | K | K | |
| getInt() | K | K | J | K | K | K | |
| getLong() | K | K | K | J | K | K | |
| getFloat() | K | K | K | K | J | K | |
| getDouble() | K | K | K | K | K | J | |
| getBigDecimal() | K | K | K | K | K | K | |
| getBoolean() | K | K | K | K | K | K | |
| getString() | K | K | K | K | K | K | |
| getBytes() | - | - | - | - | - | - | |
| getDate() | - | - | - | - | - | - | |
| getTime() | - | - | - | - | - | - | |
| getTimestamp() | - | - | - | - | - | - | |
| getAsciiStream() | - | - | - | - | - | - | |
| getCharacterStream() | - | - | - | - | - | - | |
| getBinaryStream() | - | - | - | - | - | - | |
| getObject() | K | K | K | K | K | K | |
| getClob() | - | - | - | - | - | - | |
| getBlob() | - | - | - | - | - | - | |
| getArray() | - | - | - | - | - | - | |
| getRef() | - | - | - | - | - | - | |
| getURL() | - | - | - | - | - | - | |
| DOUBLE | DECIMAL | NUMERIC | BIT | CHAR | VARCHAR | ||
| getByte() | K | K | K | K | K | K | |
| getShort() | K | K | K | K | K | K | |
| getInt() | K | K | K | K | K | K | |
| getLong() | K | K | K | K | K | K | |
| getFloat() | K | K | K | K | K | K | |
| getDouble() | J | K | K | K | K | K | |
| getBigDecimal() | K | J | J | K | K | K | |
| getBoolean() | K | K | K | J | K | K | |
| getString() | K | K | K | K | J | J | |
| getBytes() | - | - | - | - | - | - | |
| getDate() | - | - | - | - | K | K | |
| DOUBLE | DECIMAL | NUMERIC | BIT | CHAR | VARCHAR | ||
| getTime() | - | - | - | - | K | K | |
| getTimestamp() | - | - | - | - | K | K | |
| getAsciiStream() | - | - | - | - | K | K | |
| getCharacterStream() | - | - | - | - | K | K | |
| getBinaryStream() | - | - | - | - | - | - | |
| getObject() | K | K | K | K | K | K | |
| getClob() | - | - | - | - | - | - | |
| getBlob() | - | - | - | - | - | - | |
| getArray() | - | - | - | - | - | - | |
| getRef() | - | - | - | - | - | - | |
| getURL() | - | - | - | - | - | - | |
| LONGVARCHAR | BINARY | VARBINARY | LONGVARBINARY | ||||
| getByte() | K | - | - | - | |||
| getShort() | K | - | - | - | |||
| getInt() | K | - | - | - | |||
| getLong() | K | - | - | - | |||
| getFloat() | K | - | - | - | |||
| getDouble() | K | - | - | - | |||
| getBigDecimal() | K | - | - | - | |||
| getBoolean() | K | - | - | - | |||
| getString() | K | K | K | K | |||
| getBytes() | - | J | J | K | |||
| getDate() | K | - | - | - | |||
| getTime() | K | - | - | - | |||
| getTimestamp() | K | - | - | - | |||
| getAsciiStream() | J | K | K | K | |||
| getCharacterStream() | J | K | K | K | |||
| getBinaryStream() | - | K | K | J | |||
| getObject() | K | K | K | K | |||
| getClob() | - | - | - | - | |||
| getBlob() | - | - | - | - | |||
| getArray() | - | - | - | - | |||
| getRef() | - | - | - | - | |||
| getURL() | - | - | - | - | |||
| DATE | TIME | TIMESTAMP | |||||
| getByte() | - | - | - | ||||
| getShort() | - | - | - | ||||
| getInt() | - | - | |||||
| getLong() | - | - | - | ||||
| getFloat() | - | - | - | ||||
| getDouble() | - | - | - | ||||
| getBigDecimal() | - | - | - | ||||
| getBoolean() | - | - | - | ||||
| getString() | K | K | K | ||||
| getBytes() | - | - | - | ||||
| getDate() | J | - | K | ||||
| getTime() | - | J | K | ||||
| getTimestamp() | K | - | J | ||||
| getAsciiStream() | - | - | - | ||||
| getCharacterStream() | - | - | - | ||||
| getBinaryStream() | - | - | - | ||||
| getObject() | K | K | K | ||||
| getClob() | - | - | - | ||||
| getBlob() | - | - | - | ||||
| DATE | TIME | TIMESTAMP | |||||
| getArray() | - | - | - | ||||
| getRef() | - | - | - | ||||
| getURL() | - | - | - | ||||
| CLOB | BLOB | ARRAY | REF | DATALINK | STRUCT | JAVA_OBJECT | |
| getByte() | - | - | - | - | - | - | - |
| getShort() | - | - | - | - | - | - | - |
| getInt() | - | - | - | - | - | - | - |
| getLong() | - | - | - | - | - | - | - |
| getFloat() | - | - | - | - | - | - | - |
| getDouble() | - | - | - | - | - | - | - |
| getBigDecimal() | - | - | - | - | - | - | - |
| getBoolean() | - | - | - | - | - | - | - |
| getString() | - | - | - | - | - | - | - |
| getBytes() | - | - | - | - | - | - | - |
| getDate() | - | - | - | - | - | - | - |
| getTime() | - | - | - | - | - | - | - |
| getTimestamp() | - | - | - | - | - | - | - |
| getAsciiStream() | - | - | - | - | - | - | - |
| getCharacterStream() | - | - | - | - | - | - | - |
| getBinaryStream() | - | - | - | - | - | - | - |
| getObject() | - | - | - | - | - | J | J |
| getClob() | J | - | - | - | - | - | - |
| getBlob() | - | J | - | - | - | - | - |
| getArray() | - | - | J | - | - | - | - |
| getRef() | - | - | - | J | - | - | - |
| getURL() | - | - | - | - | J |
J means that the corresponding method is recommended.
K means that the corresponding get method can be used.
Note that it may be convenient to convert all SQL types to a string when retrieving data to display it in tabular format. The getString() method accepts any type and always does the implicit conversion to a Java string. Chapter 7 provides more information on SQL data types and Java types.
Receiving BLOBs
Retrieving pictures, sounds, and movies from a database is an expected JDBC function. It makes sense in the context of applets delivered via Web pages, but because Java has a fair set of multimedia facilities, it makes sense in standalone Java applications as well.
As for sending such binary large objects, Java streams are used to retrieve LONGVARBINARY or LONGVARCHAR data. However, you can also retrieve the data in fixed-size chunks. The limits are imposed by the Statement.getMaxFieldSize() value. Another limitation due to underlying implementation constraints is that each stream must be accessed immediately after the get method. Indeed, the streams will be closed on successive get calls on the result set.
Three separate methods support the retrieval of streams:
-
getBinaryStream(): The returned stream supports raw bytes. It doesn’t perform any conversion.
-
getAsciiStream(): Returns a stream providing 1-byte wide ASCII characters.
-
getUnicodeStream(): Returns a stream providing 2-byte wide Unicode characters.
These methods are listed in the earlier section “Accessing columns.”
Listing 6-22 shows how to retrieve binary large objects from a database. The fields containing a BLOB are emp_pict and emp_welcome.
Listing 6-22: Retrieving BLOBs
|
... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery("SELECT name, emp_pict, emp_welcome FROM employees"); // we retrieve in 4K chunks byte[] buffer = new byte[4096]; int size; while (rs.next()) { // fetch employee’s name String empName = rs.getString("name"); // fetch employee’s picture java.io.InputStream strin = rs.getBinaryStream("emp_pict"); for (;;) { size = strin.read(buffer); if (size == 0) { break; } // Send the buffer to some output stream output.write(buffer, 0, size); } // fetch employee’s voicemail welcome message java.io.InputStream strin2 = rs.getBinaryStream("emp_welcome"); for (;;) { size = strin2.read(buffer); if (size == 0) { break; } // Send the buffer to some output stream output.write(buffer, 0, size); } } ... ... |
Adjusting properties
Properties that affect result sets may be set or queried through various methods. Always verify that the settings are as independent of the data source as possible.
Data truncation on reads
Data truncation may happen when reading data from a database. How it is handled depends on the circumstances. Normally, data truncation results in a warning. However, if the maximum field size is set to a certain value and if the application attempts to read a field larger than the limit, the data will be silently truncated to the maximum limit. (The setMaxFieldSize() and getMaxFieldSize() are explained in the "Sending SQL Statements" section, earlier in this chapter.)
Statement’s Methods to Specify a Maximum Field Size
void setMaxFieldSize(int max); int getMaxFieldSize();
Listing 6-23 shows how to manage data truncation on reads.
Listing 6-23: Managing Data Truncation on Reads
|
... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery("SELECT name, title, salary FROM employees"); myStatement.setMaxFieldSize(128); while (rs.next()) { // print the columns of the row that was retrieved String empName = rs.getString("name"); String empTitle = rs.getString("title"); long empSalary = rs.getLong("salary"); System.out.println("Employee " + empName + " is " + empTitle + " and earns $" + empSalary); } ... ... |
Limiting the number of rows
Under some circumstances, it may not be useful or preferable to retrieve thousands or more of rows of data. One such circumstance is if the data destination is the user’s screen. Although you should always build and send queries that make sense to be able to exploit their result, it may happen that the number of returned rows is unpredictable. In this case, it is possible to set a limit for the number of rows returned. The following methods are used to set and get this limit.
Statement’s Limit for the Number of Rows in a Result Set
void setMaxRows(int max); int getMaxRows();
The method in the first line of the preceding sets the maximum limit to max. In other words, max is the maximum number of rows that a result set can contain. If the limit is exceeded, the excess rows will be silently dropped from the result set.
The code in the second line of the preceding returns the current value of the limit. A value of zero means no limit at all.
Listing 6-24 shows how to limit the number of rows returned by a query.
Listing 6-24: Limiting the Number of Rows Returned by a Query
|
... ... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery("SELECT name, title, salary FROM employees"); // we do not want more than 1000 rows of data retrieved myStatement.setMaxRows(1000); while (rs.next()) { // print the columns of the row that was retrieved String empName = rs.getString("name"); String empTitle = rs.getString("title"); long empSalary = rs.getLong("salary"); System.out.println("Employee " + empName + " is " + empTitle + " and earns $" + empSalary); } ... ... |
Examining the results
The following two methods are useful for examining and navigating through a resulting row of data.
ResultSet’s Methods for Examining Results
boolean wasNull(); int findColumn(String columnName);
In case a column has the value of SQL NULL, the method in the first line of the preceding returns true. Note that it should be called after calling the getXXX() method to access the column.
The method in the second line of the preceding attempts to find the index of the column name passed in a parameter. It returns the column index as an integer. If the column name doesn’t exist, either a -1 value is returned or a SQLException is thrown by the driver. The specification doesn’t enforce one or the other approach.
Listing 6-25 shows how to examine the columns of a result set.
Listing 6-25: Examining the Columns of a Result Set
|
... ... java.sql.Statement myStatement = myConnection.createStatement(); ResultSet rs = myStatement.executeQuery("SELECT name, title, salary FROM employees"); while (rs.next()) { // print the columns of the row that was retrieved String empName = rs.getString("name"); if rs.wasNull() System.out.println("Ooops, column " + rs.findColumn("name") + " is NULL!"); String empTitle = rs.getString("title"); if rs.wasNull() System.out.println("Ooops, column " + rs.findColumn("title") + " is NULL!"); long empSalary = rs.getLong("salary"); if rs.wasNull() System.out.println("Ooops, column " + rs.findColumn("salary") + " is NULL!"); System.out.println("Employee " + empName + " is " + empTitle + " and earns $" + empSalary); } ... ... |
Creating SQL cursors
Cursors are often used when programming database applications. They offer a practical way of scanning the result sets and perform positioned delete and updates. Cursors are also quite useful when navigating a result set: They allow for results to be transferred per chunks from the database engine to the driver instead of the complete set of rows returned by a particular query. The following method is used to obtain the name of the cursor for a given result set:
ResultSet’s Method for Cursor Support
String getCursorName();
When a result table is retrieved, a named cursor is created. This cursor is used when stepping through the result set rows and can be used to update or delete data pointed to by the cursor. This use of cursors is called positioned update/positioned delete. JDBC supports this feature by giving the name of the SQL cursor used by a result set. Note that if positioned update/delete isn’t supported by the DBMS, a SQLException will be thrown.
Putting it all together again
Figure 6-11 summarizes all the steps covered in this chapter. They are the essential steps for communicating with a database, sending queries and updates, and retrieving the results from Java.
Listing 6-26 provides an example of all the steps discussed so far in this chapter.
Figure 6-11: An overview of all the steps for communicating with a database.
Listing 6-26: Retrieving Results
|
// retrieving results // the SQL statement is taken from the standard input import java.sql.*; import java.io.*; class SimpleExample { public static void main(String argv[]) { String url = "jdbc:odbc:mysource"; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection myConnection = DriverManager.getConnection(url, "javauser", "hotjava"); Statement myStatement = myConnection.createStatement(); boolean resultSetIsAvailable; boolean moreResultsAvailable; int i = 0; int res=0; String sqlText = (new DataInputStream(System.in)).readLine(); 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 = myStatement.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(); myConnection.close(); } catch(java.lang.Exception ex) { ex.printStackTrace(); } } } |
EAN: 2147483647
Pages: 148