MySQL Cookbook, 2nd Edition

Using Metadata to Format Query Output

Problem

You want to produce a nicely formatted result set display.

Solution

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

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