Recipe 21.12 Finding Information about a ResultSet

Problem

You want to dynamically discover details about the rows and columns in a java.sql.ResultSet .

Solution

Use the ResultSetMetaData class obtained by calling the java.sql.ResultSet's getMetaData( ) method.

Discussion

Web developers sometimes need to work with database tables that have unknown column names and types. The java.sql package contains a very useful ResultSetMetaData interface that defines methods designed to provide information about a java.sql.ResultSet . A ResultSet encapsulates the rows returned by a SELECT SQL statement.

Example 21-16 shows a servlet that queries an Oracle 8 i database for a ResultSet , then displays the column names, the column index, the SQL type of the column, and the number of characters the column requires to display its values.

Example 21-16. A servlet uses the ResultSetMetaData class

 package com.jspservletcookbook;            import java.sql.*; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.*; import javax.servlet.*; import javax.servlet.http.*;  public class DbMetaServlet extends HttpServlet {  DataSource pool;  /*Initialize the DataSource in the servlet's init( ) method    which the servlet container calls once when it creates an instance of    the servlet */  public void init( ) throws ServletException {              Context env = null;              try{                     env = (Context) new InitialContext( ).lookup("java:comp/env");         pool  = (DataSource) env.lookup("jdbc/oracle-8i-athletes");         if (pool == null)             throw new ServletException(               "'oracle-8i-athletes' is an unknown DataSource");                   } catch (NamingException ne) {                   throw new ServletException(ne);     }          }//init   public void doGet(HttpServletRequest request,      HttpServletResponse response)     throws ServletException, java.io.IOException {            String sql = "select * from athlete";       Connection conn = null;       Statement stmt = null;  ResultSet rs = null;       ResultSetMetaData rsm = null;  response.setContentType("text/html");       java.io.PrintWriter out = response.getWriter( );       out.println(       "<html><head><title>Discover a ResultSet</title></head><body>");       out.println("<h2>Here is Info about the returned ResultSet</h2>");       out.println("<table border='1'><tr>");                try{  //Get a connection from the pool           conn = pool.getConnection( );                       //Create a Statement with which to run some SQL           stmt = conn.createStatement( );                  //Execute the SQL           rs = stmt.executeQuery(sql);                       //Get a ResultSetMetaData object from the ResultSet           rsm = rs.getMetaData( );                                int colCount =  rsm.getColumnCount( );                                   //print column names           printMeta(rsm,"name",out,colCount);                       //print column index           printMeta(rsm,"index",out,colCount);  //print column type           printMeta(rsm,"column type",out,colCount);                       //print column display size           printMeta(rsm,"column display",out,colCount);                              } catch (Exception e){                        throw new ServletException(e.getMessage( ));                    } finally {                          try{                                  stmt.close( );                 conn.close( );                              } catch (SQLException sqle){ }                  }       out.println("</table></body></html>");          } //doGet  private void printMeta(ResultSetMetaData metaData, String type,       java.io.PrintWriter out, int colCount) throws SQLException {  if (metaData == null  type == null  out == null)           throw new IllegalArgumentException(           "Illegal args passed to printMeta( )");                  out.println("<tr>");          if (type.equals("table")){  out.println("<td><strong>Table name</strong></td>");                 for (int i = 1; i <=colCount; ++i){                               out.println("<td>" + metaData.getTableName(i) + "</td>");          }  } else if (type.equals("name")){  out.println("<td><strong>Column name</strong></td>");                  for (int i = 1; i <=colCount; ++i){                                out.println("<td>" + metaData.getColumnName(i) + "</td>");            }  } else if (type.equals("index")){  out.println("<td><strong>Column index</strong></td>");                  for (int i = 1; i <=colCount; ++i){                                out.println("<td>" + i + "</td>");            }  } else if (type.equals("column type")){  out.println("<td><strong>Column type</strong></td>");                  for (int i = 1; i <=colCount; ++i){                                out.println("<td>" +  metaData.getColumnTypeName(i) +                "</td>");           }  } else if (type.equals("column display")){  out.println("<td><strong>Column display size</strong></td>");                  for (int i = 1; i <=colCount; ++i){                              out.println("<td>" +  metaData.getColumnDisplaySize(i) +             "</td>");            }  }         out.println("</tr>");              }//printMeta      }

Example 21-16 uses ResultSetMetaData methods to obtain information about each of the columns in the ResultSet . The code calls these methods inside its printMeta( ) method. For example, the code:

 metaData.getColumnName(1)

returns the name of the first column the table schema specifies, such as "USER_ID." Figure 21-9 shows the servlet's HTML output in a web browser.

Figure 21-9. A servlet displays meta information about a ResultSet

Use the java.sql.DatabaseMetaData interface to get a large amount of information about the database system associated with the java.sql.Connection the code is using. The Connection method getMetaData( ) returns an object that implements the DatabaseMetaData interface.