Recipe 9.4. Using Metadata to Format Query Output
ProblemYou want to produce a nicely formatted result set display. SolutionLet the result set metadata help you. It provides important information about the structure and content of the results. DiscussionMetadata 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 | +----------+--------------------+----------+ Number of rows selected: 11 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 consideration other pieces of information:
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 ()); System.out.println ("Number of rows selected: " + nrows); } If you want to be more elaborate, you can also test whether a column contains numeric values, and format it as right-justified if so. In Perl DBI scripts, this is easy to check, because you can access the mysql_is_num metadata attribute. For other APIs, it is not so easy unless there is some equivalent "column is numeric" metadata value available. If not, you must look at the data-type indicator to see whether 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 Perl, Ruby, 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. If you have such a driver (MySQL Connector/J is one), the column-width calculation code in the displayResultSet( ) function can 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 result 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 | +--+-------+----------+ Number of rows selected: 11 See AlsoThe Ruby DBI::Utils::TableFormatter module has an ascii method that produces a formatted display much like that described in this section. Use it like this: dbh.execute(stmt) do |sth| DBI::Utils::TableFormatter.ascii(sth.column_names, sth.fetch_all) end |
