JDBC – More About Columns in an SQL Query

Learn how to get details of columns in an SQL query with JDBC.

1. Introduction

An earlier article covered the basics of JDBC (Java DataBase Connectivity) and how to perform SQL queries on a MySQL database. Frequently, it is useful to obtain more information about the columns in a query. These include: the number of columns, column names and types, etc. Maybe you want to build a GUI table showing the results of a query, which requires the names of the columns and more. This article will show you how to retrive the information using JDBC.

We use a MySQL database with the MySQL Connector/J driver to interact with the database.

2. Perform the Query

See here for details of connecting to the database and performing the query. We use the ResultSet.getMetaData() method to obtain more details of the query, including the columns and types. For the query, we select all the columns in a table for illustration.

ResultSetMetaData rmd = rset.getMetaData();
int ncols = rmd.getColumnCount();
for (int i = 1 ; i <= ncols ; i++) {
    System.out.println("Column "+i+" - "+rmd.getColumnName(i)+"\n"+
                       "  Catalog Name: "+rmd.getCatalogName(i)+"\n" +
                       "  Schema Name: "+rmd.getSchemaName(i)+"\n" +
                       "  Table Name: "+rmd.getTableName(i)+"\n" +
                       "  Display Size: "+rmd.getColumnDisplaySize(i)+"\n" +
                       "  Label: "+rmd.getColumnLabel(i)+"\n" +
                       "  Name: "+rmd.getColumnName(i)+"\n" +
                       "  Type: "+rmd.getColumnTypeName(i)+"\n" +
                       "  Precision: "+rmd.getPrecision(i)+"\n" +
                       "  Scale: "+rmd.getScale(i)+"\n" +
                       "  AutoIncrement? "+rmd.isAutoIncrement(i)+"\n" +
                       "  Nullable? "+rmd.isNullable(i)+"\n");
}

We use the ResultSetMetaData object to lookup details of the query, including number of columns, column names, etc. Here is a partial output:

Column 1 - Host
  Catalog Name: mysql
  Schema Name:
  Table Name: user
  Display Size: 60
  Label: Host
  Name: Host
  Type: CHAR
  Precision: 60
  Scale: 0
  AutoIncrement? false
  Nullable? 0

Column 2 - User
  Catalog Name: mysql
  Schema Name:
  Table Name: user
  Display Size: 16
  Label: User
  Name: User
  Type: CHAR
  Precision: 16
  Scale: 0
  AutoIncrement? false
  Nullable? 0
...

Conclusion

This brief article demonstrates how to extract more information about a query from the result set.