Introduction to Java Programming-Comprehensive Version (6th Edition)
32.6. Retrieving Metadata
JDBC provides the DatabaseMetaData interface for obtaining database-wide information and the ResultSetMetaData interface for obtaining information on the specific ResultSet , such as column count and column names .
32.6.1. Database Metadata
The Connection interface establishes a connection to a database. It is within the context of a connection that SQL statements are executed and results are returned. A connection also provides access to database metadata information that describes the capabilities of the database, supported SQL grammar, stored procedures, and so on. To obtain an instance of DatabaseMetaData for a database, use the getMetaData method on a connection object like this:
DatabaseMetaData dbMetaData = connection.getMetaData();
If your program connects to a local MySQL database, the following statements display the database information, as shown in Figure 32.21:
Listing 32.4. TestDatabaseMetaData.java
(This item is displayed on pages 1116 - 1117 in the print version)
| 1 import java.sql.*; 2 3 public class TestDatabaseMetaData { 4 public static void main(String[] args) 5 throws SQLException, ClassNotFoundException { 6 // Load the JDBC driver 7 Class.forName( "com.mysql.jdbc.Driver" ); 8 System.out.println( "Driver loaded" ); 9 10 // Establish a connection 11 Connection connection = DriverManager.getConnection 12 ( "jdbc:mysql://localhost/test" ); 13 System.out.println( "Database connected" ); 14 15 DatabaseMetaData dbMetaData = connection.getMetaData(); 16 System.out.println( "database URL: " + dbMetaData.getURL() ); 17 System.out.println( "database username: " + 18 dbMetaData.getUserName() ); 19 System.out.println( "database product name : " + 20 dbMetaData.getDatabaseProductName()); 21 System.out.println( "database product version: " + 22 dbMetaData.getDatabaseProductVersion()); 23 System.out.println( "JDBC driver name: " + 24 dbMetaData.getDriverName()); 25 System.out.println( "JDBC driver version: " + 26 dbMetaData.getDriverVersion()); 27 System.out.println( "JDBC driver major version: " + 28 dbMetaData.getDriverMajorVersion()); 29 System.out.println( "JDBC driver minor version: " + 30 dbMetaData.getDriverMinorVersion()); 31 System.out.println( "Max number of connections: " + 32 dbMetaData.getMaxConnections()); 33 System.out.println( "MaxTableNameLength: " + 34 dbMetaData.getMaxTableNameLength()); 35 System.out.println( "MaxColumnsInTable: " + 36 dbMetaData.getMaxColumnsInTable()); 37 38 // Close the connection 39 connection.close(); 40 } 41 }
|
Figure 32.21. The DatabaseMetaData interface enables you to obtain database information.
32.6.2. Obtaining Database Tables
You can find all the tables in the database through database metadata using the getTables method. Listing 32.5 displays all the user tables in the test database on a local MySQL. Figure 32.22 shows a sample output of the program.
Figure 32.22. You can find all the tables in the database.
Listing 32.5. FindUserTables.java
(This item is displayed on pages 1117 - 1118 in the print version)
| 1 import java.sql.*; 2 3 public class FindUserTables { 4 public static void main(String[] args) 5 throws SQLException, ClassNotFoundException { 6 // Load the JDBC driver 7 Class.forName( "com.mysql.jdbc.Driver" ); 8 System.out.println( "Driver loaded" ); 9 10 // Establish a connection 11 Connection connection = DriverManager.getConnection 12 ( "jdbc:mysql://localhost/test" ); 13 System.out.println( "Database connected" ); 14 15 DatabaseMetaData dbMetaData = connection.getMetaData(); 16 17 ResultSet rsTables = dbMetaData.getTables( null , null , null , 18 new String[] { "TABLE" }); 19 System.out.print( "User tables: " ); 20 while (rsTables. next ()) 21 System.out.print( rsTables.getString( "TABLE_NAME" ) + " " ); 22 23 // Close the connection 24 connection.close(); 25 } 26 }
|
Line 17 obtains table information in a result set using the getTables method. One of the columns in the result set is TABLE_NAME. Line 21 retrieves the table name from this result set column.
32.6.3. Result Set Metadata
The ResultSetMetaData interface describes information pertaining to the result set. A ResultSetMetaData object can be used to find the types and properties of the columns in a ResultSet . To obtain an instance of ResultSetMetaData , use the getMetaData method on a result set like this:
ResultSetMetaData rsMetaData = resultSet.getMetaData();
You can use the getColumnCount() method to find the number of columns in the result and the getColumnName(int) method to get the column names. For example, Listing 32.6 displays all the column names and contents resulting from the SQL SELECT statement select * from Enrollment . The output is shown in Figure 32.23.
Figure 32.23. The ResultSetMetaData interface enables you to obtain resultset information.
Listing 32.6. TestResultSetMetaData.java
(This item is displayed on pages 1118 - 1119 in the print version)
| 1 import java.sql.*; 2 3 public class TestResultSetMetaData { 4 public static void main(String[] args) 5 throws SQLException, ClassNotFoundException { 6 // Load the JDBC driver 7 Class.forName( "com.mysql.jdbc.Driver" ); 8 System.out.println( "Driver loaded" ); 9 10 // Establish a connection 11 Connection connection = DriverManager.getConnection 12 ( "jdbc:mysql://localhost/test" ); 13 System.out.println( "Database connected" ); 14 15 // Create a statement 16 Statement statement = connection.createStatement(); 17 18 // Execute a statement 19 ResultSet resultSet = statement.executeQuery 20 ( "select * from Enrollment" ); 21 22 ResultSetMetaData rsMetaData = resultSet.getMetaData(); 23 for ( int i = 1 ; i <= rsMetaData.getColumnCount() ; i++) 24 System.out.printf( "%-12s\t" , rsMetaData.getColumnName(i) ); 25 System.out.println(); 26 27 // Iterate through the result and print the student names 28 while (resultSet.next()) { 29 for ( int i = 1 ; i <= rsMetaData.getColumnCount() ; i++) 30 System.out.printf( "%-12s\t" , resultSet.getObject(i)); 31 System.out.println(); 32 } 33 34 // Close the connection 35 connection.close(); 36 } 37 }
|