Database Reference
In-Depth Information
Solution
Let the result set metadata help you. It provides important information about the struc‐
ture and content of the results.
Discussion
Metadata is valuable for formatting query results because it tells you several important
things about the columns, such as the names and display widths. For example, you can
write a general-purpose function that displays a result set in tabular format, even without
knowing what the query was. The following Java code shows one way to do this. It takes
a result set object and uses it to get the metadata for the result. Then it uses both objects
in tandem to retrieve and format the values in the result. The output is similar to that
produced by mysql : a row of column headers followed by the rows of the result, with
columns nicely boxed and lined up vertically. Here's a sample of function output, given
the result set generated by the query SELECT id, name, birth FROM profile :
+----------+--------------------+----------+
|id |name |birth |
+----------+--------------------+----------+
|1 |Sybil |1970-04-13|
|2 |Nancy |1969-09-30|
|3 |Ralph |1973-11-02|
|4 |Lothair |1963-07-04|
|5 |Henry |1965-02-14|
|6 |Aaron |1968-09-17|
|7 |Joanna |1952-08-20|
|8 |Stephen |1960-05-01|
|9 |Amabel |NULL |
+----------+--------------------+----------+
Number of rows selected: 9
The primary problem an application like this must solve is to determine the proper
display width of each column. The getColumnDisplaySize() method returns the col‐
umn width, but we must also factor in other pieces of information:
• The column name might be longer than the column width.
• We'll print the word “NULL” for NULL values, so if the column can contain NULL
values, the display width must be at least four.
The following Java function, displayResultSet() , formats a result set, taking those
factors into account. It also counts rows as it fetches them to determine the row count,
because JDBC doesn't provide that value in the metadata:
public static void displayResultSet ( ResultSet rs ) throws SQLException
{
ResultSetMetaData md = rs . getMetaData ();
int ncols = md . getColumnCount ();
Search WWH ::




Custom Search