16.1 Overview of database meta data

Meta Data is data about data which means information about data. In previous chapter we discussed how to get meta data of table from the ResultSet. With the help of JDBC, we can get the meta data at a database level like jdbc driver name with which the connection is obtained, username, database product name, database version name, table details etc.

We can get Database Meta Data using connection object.

In this chapter we will discuss how to get the database meta data from Connection  object.

 

16.2 DatabaseMetaData API

JDBC provides an DatabaseMetaData interface in java.sql package and its object can be obtained from Connection  Object by calling getMetaData() method like below

DatabaseMetaData metadata= conn.getMetaData();

Following are the useful methods of DatabaseMetaData object.

1. int getDatabaseMajorVersion() – This method returns the major version of  connected database.
2. int getDatabaseMinorVersion() – This method returns the minor version of  connected database.
3. String  getDatabaseProductName()- This method returns the database product name.
4. String  getDatabaseProductVersion()- This method returns the database product version.
5. int getDriverMajorVersion() – This method returns the major version of  JDBC driver used to connect to database.
6. int getDriverMinorVersion() – This method returns the major version of  JDBC driver used to connect to database.
7. String getDriverVersion() – This method returns the version of  JDBC driver used to connect to database.
8. String getDriverName() – This method returns the name of  JDBC driver used to connect to database.
9. String getUserName() – This method returns the username of database used to  connect to database.
10. ResultSet  getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)- This method returns the list of tables in a database based on the catalog, schema, table name pattern and types. Passing all null will return null will return all tables. Returned parameter is ResultSet and it contains 10 columns containing details of table and as many object as tables. We can get the table name from column index 3 . The table type can be TABLE, VIEW, ALIAS, SYNONYM etc.
11. ResultSet  getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)- This method returns the list of column details on the catalog, schema, table name pattern and column name pattern. Index number 4 and 5 gives column name and data type respectively.  

There are other methods available but these are the most commonly used.    

16.3 Examples of database meta data

We will use the MySQL database .

1.  Use the below sql statement to create a schema

       Create schema JDBCTutorial;

2. Create table User with 4 columns using below SQL

       CREATE  TABLE `User` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NULL ,  `age` VARCHAR(45) NULL ,  `email` VARCHAR(45) NULL ,  PRIMARY KEY (`id`) );

16.3.1 – Code example to determine the meta data of the Database

Solution –

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

import java.sql.DatabaseMetaData;

public class DatabaseMetaDataExample {

   public static void main(String args[])
    {
        try
        {

           Connection conn= getConnection();
           DatabaseMetaData metaData= conn.getMetaData();

           int    majorVersion   = metaData.getDatabaseMajorVersion();
           int    minorVersion   = metaData.getDatabaseMinorVersion();

           String productName    = metaData.getDatabaseProductName();
           String productVersion = metaData.getDatabaseProductVersion();

           int driverMajorVersion = metaData.getDriverMajorVersion();
           int driverMinorVersion = metaData.getDriverMinorVersion();

           String username = metaData.getUserName();
           String driverName= metaData.getDriverName();        
           String driverVersion= metaData.getDriverVersion();

           System.out.println("Database Major Version = " + majorVersion);            
           System.out.println("Database Minor Version = " + minorVersion);

           System.out.println("Database Product Name = " + productName);
           System.out.println("Database Product Version = " + productVersion);
           System.out.println("Driver Major Version = " + driverMajorVersion);
           System.out.println("Driver Minor Version = " + driverMinorVersion);
           System.out.println("Database Username = " + username);
           System.out.println("Driver name = " + driverName);        
           System.out.println("Driver Version = " + driverVersion);

           String table[]={"TABLE"};  
           ResultSet rs = metaData.getTables(null, null , null , table);
            
           while(rs.next())
           {
            System.out.println("Table Name is =" + rs.getString(3));  
           }

           ResultSet rsCol = metaData.getColumns(null, null, "User", null);

           System.out.println("Columns in User table are-");
             while(rsCol.next())
             {
            String name = rsCol.getString(4);
            String type = rsCol.getString(5);
            System.out.println("Col Name is =" + name + " type is " + type);  
                    
             }
             rs.close();
             rsCol.close();
             conn.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    private static Connection getConnection() {

        Connection con = null;
        String dbDriver = "com.mysql.jdbc.Driver";
        String dbUsername = "root";
        String dbPassword="password";
        String dbHostname="localhost";
        String dbPort="3306";
        String schema="JdbcTutorial";
        try {
              String     url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
            Class.forName(dbDriver);
            con = DriverManager.getConnection(url, dbUsername, dbPassword);
        }
        catch (ClassNotFoundException ex1)
        {
            System.out.println("Failed to find driver class " + ex1.getMessage());
            System.exit(1);
        }
        catch (SQLException ex2) {
            System.out.println("Connection failed " + ex2.getMessage());
            System.exit(2);
        }
        return con;
    }
}

On Running above program, you will see below output.

Database Major Version = 5

Database Minor Version = 5

Database Product Name = MySQL

Database Product Version = 5.5.33

Driver Major Version = 5

Driver Minor Version = 1

Database Username = root@localhost

Driver name = MySQL-AB JDBC Driver

Driver Version = mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq )

Table Name is =user

Columns in User table are-

Col Name is =id type is 4

Col Name is =name type is 12

Col Name is =age type is 12

Col Name is =email type is 12