9.5 Formatting Query Results for Display
9.5.1 Problem
You want to produce a nicely
formatted result set display.
9.5.2 Solution
Let the result set metadata help you.
It can give you important information about the structure and content
of the results.
9.5.3 Discussion
Metadata information is valuable for formatting query results,
because it tells you several important things about the columns (such
as the names and display widths), even if you don't
know what the query was. For example, you can write a general-purpose
function that displays a result set in tabular format with no
knowledge about what the query might have been. 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 what the
function displays, given the result set generated by the query
SELECT id,
name, birth
FROM profile:
+----------+--------------------+----------+
|id |name |birth |
+----------+--------------------+----------+
|1 |Fred |1970-04-13|
|2 |Mort |1969-09-30|
|3 |Brit |1957-12-01|
|4 |Carl |1973-11-02|
|5 |Sean |1963-07-04|
|6 |Alan |1965-02-14|
|7 |Mara |1968-09-17|
|8 |Shepard |1975-09-02|
|9 |Dick |1952-08-20|
|10 |Tony |1960-05-01|
|11 |Juan |NULL |
+----------+--------------------+----------+
11 rows selected
The primary problem an application like this must solve is to
determine the proper display width of each column. The
getColumnDisplaySize(
) method returns the column width, but we
actually need to take into account other pieces of information:
The length of the column name has to be considered (it 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 the
preceding factors into account. It also counts rows as it fetches
them to determine the row count, because JDBC
doesn't make that value available directly from the
metadata.
public static void displayResultSet (ResultSet rs) throws SQLException
{
ResultSetMetaData md = rs.getMetaData ( );
int ncols = md.getColumnCount ( );
int nrows = 0;
int[ ] width = new int[ncols + 1]; // array to store column widths
StringBuffer b = new StringBuffer ( ); // buffer to hold bar line
// calculate column widths
for (int i = 1; i <= ncols; i++)
{
// some drivers return -1 for getColumnDisplaySize( );
// if so, we'll override that with the column name length
width[i] = md.getColumnDisplaySize (i);
if (width[i] < md.getColumnName (i).length ( ))
width[i] = md.getColumnName (i).length ( );
// isNullable( ) returns 1/0, not true/false
if (width[i] < 4 && md.isNullable (i) != 0)
width[i] = 4;
}
// construct +---+---... line
b.append ("+");
for (int i = 1; i <= ncols; i++)
{
for (int j = 0; j < width[i]; j++)
b.append ("-");
b.append ("+");
}
// print bar line, column headers, bar line
System.out.println (b.toString ( ));
System.out.print ("|");
for (int i = 1; i <= ncols; i++)
{
System.out.print (md.getColumnName (i));
for (int j = md.getColumnName (i).length ( ); j < width[i]; j++)
System.out.print (" ");
System.out.print ("|");
}
System.out.println ( );
System.out.println (b.toString ( ));
// print contents of result set
while (rs.next ( ))
{
++nrows;
System.out.print ("|");
for (int i = 1; i <= ncols; i++)
{
String s = rs.getString (i);
if (rs.wasNull ( ))
s = "NULL";
System.out.print (s);
for (int j = s.length ( ); j < width[i]; j++)
System.out.print (" ");
System.out.print ("|");
}
System.out.println ( );
}
// print bar line, and row count
System.out.println (b.toString ( ));
if (nrows == 1)
System.out.println ("1 row selected");
else
System.out.println (nrows + " rows selected");
}
If you want to be more elaborate, you can also test whether a column
contains numeric values, and format it right-justified if so. In DBI
and PHP scripts, this is easy to check, because you can access the
mysql_is_num or numeric
metadata attributes provided by those APIs. In DB-API and JDBC,
it's not so easy. There is no
"column is numeric" metadata value
available, so you'd have to look at the column type
indicator to see if it's one of the several possible
numeric types.
Another shortcoming of the displayResultSet(
) function is that it prints columns using
the width of the column as specified in the table definition, not the
maximum width of the values actually present in the result set. The
latter value is often smaller. You can see this in the sample output
that precedes the listing for displayResultSet( ).
The id and name columns are 10
and 20 characters wide, even though the widest values are only two
and seven characters long, respectively. In DBI, PHP, and DB-API, you
can get the maximum width of the values present in the result set. To
determine these widths in JDBC, you must iterate through the result
set and check the column value lengths yourself. This requires a JDBC
2.0 driver that provides scrollable result sets. Assuming that to be
true, the column-width calculation code in the
displayResultSet( ) function could be modified as
follows:
// calculate column widths
for (int i = 1; i <= ncols; i++)
{
width[i] = md.getColumnName (i).length ( );
// isNullable( ) returns 1/0, not true/false
if (width[i] < 4 && md.isNullable (i) != 0)
width[i] = 4;
}
// scroll through result set and adjust display widths as necessary
while (rs.next ( ))
{
for (int i = 1; i <= ncols; i++)
{
byte[ ] bytes = rs.getBytes (i);
if (!rs.wasNull ( ))
{
int len = bytes.length;
if (width[i] < len)
width[i] = len;
}
}
}
rs.beforeFirst ( ); // rewind result set before displaying it
With that change, the result is a more compact query output display:
+--+-------+----------+
|id|name |birth |
+--+-------+----------+
|1 |Fred |1970-04-13|
|2 |Mort |1969-09-30|
|3 |Brit |1957-12-01|
|4 |Carl |1973-11-02|
|5 |Sean |1963-07-04|
|6 |Alan |1965-02-14|
|7 |Mara |1968-09-17|
|8 |Shepard|1975-09-02|
|9 |Dick |1952-08-20|
|10|Tony |1960-05-01|
|11|Juan |NULL |
+--+-------+----------+
11 rows selected
| 
No comments:
Post a Comment