How To Get Database Meta Data

java.sql.DatabaseMetaData interface provide methods to get meta data of your connected database server. Below examples will show you how to do that one by one.

java.sql.DatabaseMetaData Instance

DatabaseMetaData object is retrieved from database Connection object.

DatabaseMetaData dbmd = dbConn.getMetaData();

DB Name And Version

String dbProductName = dbmd.getDatabaseProductName();
String dbProductVersion = dbmd.getDatabaseProductVersion();

int dbMajorVersion = dbmd.getDatabaseMajorVersion();
int dbMinorVersion = dbmd.getDatabaseMinorVersion();

JDBC Driver Version

int jdbcDriverMajorVersion = dbmd.getDriverMajorVersion();
int jdbcDriverMinorVersion = dbmd.getDriverMinorVersion();

DataBase List

ResultSet rs = dbmd.getCatalogs();
if(rs!=null)
{
      while(rs.next())
      {
          String catalogName = rs.getString(1);
	  System.out.println("Database catalog : " + catalogName);
      }
}

Table List

// Get all tables of this database.
ResultSet tblRs = dbmd.getTables(catalogName, "", "", null);				
if(tblRs!=null)
{
	while(tblRs.next())
	{
		// Get table
		String tmpDBName = tblRs.getString(1);
		String tmpTblName = tblRs.getString(3);
		System.out.println("DB : " + tmpDBName + " , table : " + tmpTblName);
         }
}

Table Column List

// Get columns
ResultSet columnRs = dbmd.getColumns(catalogName, "", tmpTblName, "");
						
if(columnRs!=null)
{
	while(columnRs.next())
	{
		// Get column type and name.
		String cType = columnRs.getString(6);
						
	        String cName = columnRs.getString(4);
		
		System.out.println("Column Name : " + cName + " , Column Type : " + cType);
	}
}

Full Example Code

Below example will use MySQL database server.

public class DatabaseMetaDataExample {

	public static void main(String[] args) {
		
		try
		{
			/* Below are db connection required data. */
			String ip = "localhost";
			int port = 3306;
			String dbName = "test";
			String userName = "root";
			String password = "";
	
			DatabaseMetaDataExample dbmdExample = new DatabaseMetaDataExample();
			
			// Get database connection.
			Connection dbConn = dbmdExample.getMySqlConnection(ip, port, dbName, userName, password);
			
			// Get database metadata. 
			DatabaseMetaData dbmd = dbConn.getMetaData();
			
			// Get db name.
			String dbProductName = dbmd.getDatabaseProductName();
			System.out.println("DB product name : " + dbProductName);
			
			// Get db product version.
			String dbProductVersion = dbmd.getDatabaseProductVersion();
			System.out.println("DB product version : " + dbProductVersion);
			
			// Get db major & minor version.
			int dbMajorVersion = dbmd.getDatabaseMajorVersion();
			System.out.println("DB major version : " + dbMajorVersion);
			int dbMinorVersion = dbmd.getDatabaseMinorVersion();
			System.out.println("DB minor version : " + dbMinorVersion);
			
			// Get jdbc driver major & minor version.
			int jdbcDriverMajorVersion = dbmd.getDriverMajorVersion();
			System.out.println("JDBC driver major version : " + jdbcDriverMajorVersion);
			int jdbcDriverMinorVersion = dbmd.getDriverMinorVersion();
			System.out.println("JDBC driver minor version : " + jdbcDriverMinorVersion);
			
			// Get all database catalogs.
			ResultSet rs = dbmd.getCatalogs();
			if(rs!=null)
			{
				while(rs.next())
				{
					String catalogName = rs.getString(1);
					System.out.println("Database catalog : " + catalogName);
					
					// Get all tables of this database.
					ResultSet tblRs = dbmd.getTables(catalogName, "", "", null);
					
					if(tblRs!=null)
					{
						while(tblRs.next())
						{
							// Get table
							String tmpDBName = tblRs.getString(1);
							String tmpTblName = tblRs.getString(3);
							System.out.println("DB : " + tmpDBName + " , table : " + tmpTblName);
							
							// Get columns
							ResultSet columnRs = dbmd.getColumns(catalogName, "", tmpTblName, "");
							
							if(columnRs!=null)
							{
								while(columnRs.next())
								{
									// Get column type and name.
									String cType = columnRs.getString(6);
									
									String cName = columnRs.getString(4);
									
									System.out.println("Column Name : " + cName + " , Column Type : " + cType);
								}
							}
							
						}
					}
					
				}
			}
			
			
			dbmdExample.closeDBResource(null, dbConn);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}
	
	/* This method return java.sql.Connection object from MySQL server. */
	public Connection getMySqlConnection(String ip, int port, String dbName, String userName, String password)
	{
		/* Declare and initialize a sql Connection variable. */
		Connection ret = null;
		
		try
		{
		
			/* Register for mysql jdbc driver class. */
			Class.forName("com.mysql.jdbc.Driver");
			
			/* Create mysql connection url. */
			String mysqlConnUrl = "jdbc:mysql://" + ip + ":" + port + "/" + dbName;
			
			/* Get the mysql Connection object. */
			ret = DriverManager.getConnection(mysqlConnUrl, userName , password);
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}finally
		{
			return ret;
		}
	}
	
	/* Close statement and connection after use, this can avoid resource waste. */
	public void closeDBResource(Statement stmt, Connection conn)
	{
		try
		{
			if(stmt!=null)
			{
				stmt.close();
				stmt = null;
			}
			
			if(conn!=null)
			{
				conn.close();
				conn = null;
			}
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}

}

Source Code:

1. [download id=”2567″]

Reference:

1. Interface DatabaseMetaData