You want to produce a nicely formatted result set display.

Let the result set metadata help you. It provides important information about the structure and content of the results.

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      |
+----------+--------------------+----------+
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

The 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