[ Team LiB ] Previous Section Next Section

Recipe 21.12 Finding Information about a ResultSet


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


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


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;
        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);


  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;
      java.io.PrintWriter out = response.getWriter( );

      "<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>");
          //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
          //print column index
          //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 {
                stmt.close( );
                conn.close( );
            } catch (SQLException sqle){ }
  } //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( )");
      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) +
      } 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) +

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:


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.

See Also

The JDBC specification: http://java.sun.com/products/jdbc/download.html; The ResultSetMetaData class: http://java.sun.com/j2se/1.4.1/docs/api/java/sql/ResultSetMetaData.html; Recipe 21.1 on accessing a database from a servlet without a connection pool; Recipe 21.2 and Recipe 21.3 on using a DataSource on Tomcat; Recipe 21.4-Recipe 21.6 on using DataSources with servlets and JSPs on WebLogic; Recipe 21.7 and Recipe 21.8 on calling stored procedures from servlets and JSPs; Recipe 21.10 and Recipe 21.11 on using transactions in servlets and JSPs.

    [ Team LiB ] Previous Section Next Section