Java JDBC: Metadata of database using DatabaseMetaData Interface

In this article, we will use DatabaseMetaData interface to extract database information like driver name and its version, product name and its version, number of tables and views inside that database

 

1. Pre-requisite:

• Java JDK 1.8.0_77
• MySQL database 5.5.16
• Eclipse Luna IDE 4.4.0
• mysql-connector-java-5.1.38.jar file

 

2. Database parameters:

Let us move on and code an example to connect MySQL database from Java application to extract database information using JDBC API. But before that, we will list down required things to connect database

• database server IP or address (localhost)
• sever port (3306)
• database name (PLAYER_INFO)
• username (root)
• password (root@123)

Note: All bold are database values to connect MySQL database

 

3. Metadata of database using JDBC DatabaseMetaData Interface

• As we are ready with required things to connect MySQL database from Java application
• We can retrieve below listed database information
  1. Driver name
  2. Driver version
  3. User name
  4. Product name
  5. Product version
  6. Table count
  7. View count
• Let us code a simple example using Statement Interface

DatabaseInfoUsingJDBCDatabaseMetaData.java

package in.bench.resources.mysql.db.metadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DatabaseInfoUsingJDBCDatabaseMetaData {

	public static void main(String[] args) {

		// variables
		Connection connection = null;
		ResultSet resultSet = null;

		// Step 1: Loading or registering MySQL JDBC driver class
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in loading MySQL JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			// Step 2.A: Create and get connection using DriverManager
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/PLAYER_INFO",
					"root", 
					"root@123"); 

			// get database metadata through connection
			DatabaseMetaData databaseMetaData = connection.getMetaData();

			System.out.println("=== Database Metdata information ===\n");

			// database information
			System.out.println("Database Product Name: \t\t" 
					+ databaseMetaData.getDatabaseProductName());
			System.out.println("Database Product Version: \t" 
					+ databaseMetaData.getDatabaseProductVersion());
			System.out.println("Database Major Version: \t" 
					+ databaseMetaData.getDatabaseMajorVersion());
			System.out.println("Database Minor Version: \t" 
					+ databaseMetaData.getDatabaseMinorVersion());

			System.out.println();

			// driver information
			System.out.println("Driver Name: \t\t\t" 
					+ databaseMetaData.getDriverName());
			System.out.println("Driver Version: \t\t" 
					+ databaseMetaData.getDriverVersion());
			System.out.println("Driver Major Version: \t\t" 
					+ databaseMetaData.getDriverMajorVersion());
			System.out.println("Driver Minor Version: \t\t" 
					+ databaseMetaData.getDriverMinorVersion());

			// retrieving table information
			String table[] = {"TABLE"};  
			resultSet = databaseMetaData
					.getTables(null, null, null, table);  

			while(resultSet.next()){  
				System.out.println("Table Information: \t\t"
						+ resultSet.getString(3));  
			} 

			// retrieving view information
			String views[] = {"VIEW"};  
			resultSet = databaseMetaData
					.getTables(null, null, null, views);  

			while(resultSet.next()){  
				System.out.println("View Information: \t\t"
						+ resultSet.getString(3));  
			} 

			System.out.println("\n== End of MetaData information ==");
		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
		finally {
			// Step 3: Closing database connection
			try {
				if(null != connection) {
					// and then finally close connection
					connection.close();
				}
			}
			catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
	}
}

Output:

=== Database Metdata information ===

Database Product Name: 		MySQL
Database Product Version: 	5.5.16
Database Major Version: 	5
Database Minor Version: 	5

Driver Name: 			MySQL Connector Java
Driver Version: 		mysql-connector-java-5.1.38 ( Revision: fe541c166cec739c74cc727c5da96c1028b4834a )
Driver Major Version: 		5
Driver Minor Version: 		1
Table Information: 		player

== End of MetaData information ==

 

4. Download:

• JDBC Database Metadata
• mysql-connector-java-5.1.38.jar

 

Read Also:

• JDBC Tutorial index
• Introduction to JDBC
• Different types of JDBC drivers
• JDBC Driver list for all leading databases
• Basic components of JDBC
• JDBC connection steps
• MySQL database connection steps
• Oracle database connection steps

 

References:

• https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html
• https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-connect-drivermanager.html
• http://www.benchresources.net/spring-jdbc-introduction-and-jdbc-example-without-spring/
• https://docs.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html
• https://docs.oracle.com/cd/E11882_01/java.112/e16548/overvw.htm#JJDBC28025
• http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
• https://en.wikipedia.org/wiki/JDBC_driver
• http://www.devx.com/tips/Tip/28818
• https://docs.oracle.com/javase/7/docs/api/java/lang/Class.html#forName(java.lang.String)

 

Happy Coding !!
Happy Learning !!