Accessing Database Metadata from a ResultSet

Now that we are able to insert data into and retrieve data from database tables, we can look at how we can actually get structural information about the database, which is referred to as metadata. Here is a simple program that will read all of the tables from the database and display the names of all of the columns in each of those tables.

Code Listing 16-8: Accessing database metadata

import java.sql.*;     public class MetaExample1 {     public static void main(String[] args)     {         try         {             Class.forName("org.gjt.mm.mysql.Driver");         }         catch(ClassNotFoundException e)         {             System.out.println(e);         }                                 try         {             Connection conn;                               System.out.println("Attempting to connect...\n");                           conn = DriverManager.getConnection("jdbc:mysql:                 //localhost/firsttest?user=root&password=");                          System.out.println("Connected\n");                         System.out.println("Getting Database Meta Data...\n");                         DatabaseMetaData metadata = conn.getMetaData();             String[] validTypes = {"TABLE"};             ResultSet theTables = metadata.getTables(null, null,                 null, validTypes);                         while(theTables.next())             {                 String tableName = theTables.getString("TABLE_NAME");                 System.out.println("Table Found: "+tableName);                                 // Now get the columns in that table                 ResultSet theColumns = metadata.getColumns(null,                     null, tableName, null);                                 while(theColumns.next())                 {                     String columnName = theColumns.getString                         ("COLUMN_NAME");                     System.out.println("\tColumn Found:                         "+columnName);                   }                 System.out.print("\n");             }                               System.out.println("\nAttempting to disconnect...\n");                         conn.close();                         System.out.println("Disconnected\n");           }         catch(SQLException e)         {             System.out.println(e);         }        } }

When we execute this console application, we can see that it will list all of the tables (of which there is only one) and columns in the table. In this example we are using the firsttest database that we created earlier in this chapter. Here is the output that we can expect in the console:

Figure 16-9: Output from our metadata example, using the firsttest database

Let's now look at how this is achieved. We connect to the database, as we have done in previous examples, but instead of using a query, we use the Connection object to retrieve the metadata from the database. The line of code used to do this is as follows:

DatabaseMetaData metadata = conn.getMetaData();

Once we have the metadata stored in a DatabaseMetaData object, we can then obtain a ResultSet containing information about our tables with the following code segment:

String[] validTypes = {"TABLE"}; ResultSet theTables = metadata.getTables(null, null, null,     validTypes);

Once we have our ResultSet, we can use the normal method of cycling through rows with a while loop. Notice, though, how we actually get the names of the tables:

String tableName = theTables.getString("TABLE_NAME");