How to Use ResultSetMetaData to Learn ResultSet Information?

To query an unknown result set for information about the columns that it contains, you need to use ResultSetMetaData methods to determine the characteristics of the ResultSets before you can retrieve data from them. ResultSetMetaData methods provide the following types of information:

• getColumnCount() method returns the number of columns in the ResultSet
• getTableName() method returns the qualifier for the underlying table of the ResultSet
• getSchemaName() method returns the the designated column's table's schema name
• Information about a column, getColumnName() returns column name, getColumnTypeName() method returns the data type, getColumnDisplaySize() method returns column display length, getPrecision() method returns the column precision, and getScale() method returns scale.
• Whether a column is read-only, nullability, automatically numbered, and so on.

In the following example, it shows various methods of the ResultSetMetaData object are used to display information about table and column information within the result set.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCResultSetMetaData {

    private static final String DBURL = 
           "jdbc:mysql://localhost:3306/mydb?user=usr&password=sql"+
           "&useUnicode=true&characterEncoding=UTF-8";
    private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
  
    static {
        try {
            Class.forName(DBDRIVER).newInstance();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    private static Connection getConnection() 
    {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(DBURL);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void createEmployees()
    {
        Connection con = getConnection();
        Statement stmt =null;
        String createString;
        createString = "CREATE TABLE  `mydb`.`employees` ("+
           "`EmployeeID` int(10) unsigned NOT NULL default '0',"+
           "`Name` varchar(45) collate utf8_unicode_ci NOT NULL default '',"+
         "`Office` varchar(10) collate utf8_unicode_ci NOT NULL default '',"+
           "`CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,"+
           "PRIMARY KEY  (`EmployeeID`)"+
           ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";            
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    private static void dropEmployees()
    {
        Connection con = getConnection();
        Statement stmt =null;
        String createString;
        createString = "DROP TABLE IF EXISTS `mydb`.`employees`;";            
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    
    private static void insertEmployee() 
    {
        Connection con = getConnection();
        Statement stmt = null;

        try {
            stmt = con.createStatement();  

            stmt.addBatch("INSERT INTO employees(EmployeeID, Name, Office) " 
                    + "VALUES(1001, 'David Walker', 'HQ101')");

            stmt.addBatch("INSERT INTO employees(EmployeeID, Name, Office) "  
                    + "VALUES(1002, 'Paul Walker', 'HQ202')");            

            stmt.addBatch("INSERT INTO employees(EmployeeID, Name, Office) " 
                    +  "VALUES(1003, 'Scott Warner', 'HQ201')");            

            int [] updateCounts = stmt.executeBatch();

        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    public static void showEmployeeInfo()
    {
        Connection con = getConnection();
        Statement stmt = null;

        try {
            stmt = con.createStatement();  
            ResultSet rs = stmt.executeQuery("SELECT * from Employees");
            ResultSetMetaData rsmd = rs.getMetaData();
            int cols = rsmd.getColumnCount();
            System.out.println("Column Count is " + cols);
            for (int i = 1; i <= cols; i++) {
               System.out.println("\nNAME: " + rsmd.getColumnName(i) + "\n" + 
                     "TYPE: " + rsmd.getColumnTypeName(i) + "\n" +
                      "TABLE: " + rsmd.getTableName(i)+"\n" + 
                      "Schema: " + rsmd.getSchemaName(i) + "\n" +
                      "Scale: " + rsmd.getScale(i) + "\n" +
                       "Length: " + rsmd.getColumnDisplaySize(i) + "\n" +
                       "Precision: " + rsmd.getPrecision(i));
            }
            rs.close();
        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    
    }
    public static void main(String[] args) {
        dropEmployees();
        createEmployees();
        insertEmployee();
        showEmployeeInfo();
    }

}

Printer-friendly version | Mail this to a friend