MySQL Cookbook

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