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 8i 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