MetaData

Accessing database meta data

Defined : metadata is data about data
In Java you can retrieve meta data about a connection, tables, and resultsets.

Accessing ResultSetMetaData

First ask the result set for its meta data object, e.g. if rs is a ResultSet object, then you would get its metadata by doing the following:

ResultSetMetaData rsmd = rs.getMetaData(); // get result set meta data

Then you can retrieve various meta data items using the object

the number of columns - int ncolumns = rsmd.getColumnCount();
the name of a column - String label = rsmd.getColumnLabel(i); // where i is an integer from 1 to ncolumns
the data type of a column - String type = rsmd.getColumnTypeName(i); // where i is an integer from 1 to ncolumns
reference the help text on ResultSetMetaData for more methods

Accessing DatabaseMetaData

First ask the connection object for its metadata (DatabaseMetaData)

DatabaseMetaData
dbmd = dbConnection.getMetaData();

To get information on each table, ask the DatabaseMetaData object to "getTables", this will return a ResultSet with the table information

String[] tabletypes = {"TABLE"};

ResultSet rs =
dbmd.getTables(null,null,null,tabletypes);

Then you can go through the result set line by line and get information on each table.

The table name is in Column 3 of the result set.
This can then be used to ask for all the column names in the table using getColumns

This will return a result set with the name in column 4 and the data type in column 6.

      while(rs.next()) {

           String
tablename = rs.getString(3); // get the table name

          
metadataField.append(tablename+": \n");

           ResultSet
rs3 = dbmd.getColumns(null,null,tablename,"%");

          
while(rs3.next())

           {

           
String name = rs3.getString(4);

           
String type = rs3.getString(6);

           
metadataField.append("  "+name+":"+type+"\n");

           }

           
metadataField.append("\n");

           
rs3.close();

            

         }

refererence help text on DatabaseMetaData for more methods

Given a Connection object dbConnection, display its meta data in a JTextArea named metadataField.






 public void displaySchema()

    {

     try{

         String[] tabletypes =
{"TABLE"};

         

         DatabaseMetaData dbmd =
dbConnection.getMetaData();

          

         ResultSet rs =
dbmd.getTables(null,null,null,tabletypes);

  //

 // process each table

 //

         while(rs.next()) {

           String
tablename = rs.getString(3); // get the table name

          
metadataField.append(tablename+": \n");

           ResultSet
rs3 = dbmd.getColumns(null,null,tablename,"%");

          
while(rs3.next())

           {

           
String name = rs3.getString(4);

           
String type = rs3.getString(6);

           
metadataField.append("  "+name+":"+type+"\n");

           }

           
metadataField.append("\n");

           
rs3.close();

            

         }

         

         rs.close();

       }

       catch(SQLException sqle) {

          
errorLog.append("Sql Exception :"+sqle.getMessage()+"\n");

           return;

        }

        catch(Exception ge) {

         
errorLog.append("Exception :" + ge.getMessage()+"\n\n");

          return;

       }  

    }