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.