DatabaseMetaData

Java.sql.DatabaseMetaData Example

In this example we will see how we can make use of class java.sql.DatabaseMetaData to extract the meta data of the database. We can view details like table names, primary keys, database name, DB driver version, etc. Lets see few examples of it.

In order to run this code we need to make sure that a DB server is installed and is running. We would also need to create a test schema for our examples. Lets do it step by step.

We will use MySQL Database for our example. To download and install MySql Server and configure it go here. We will need MySQL JDBC driver in order to talk to this database. To download MySQL Driver go here. You can also find the jar in the lib folder of this example, if you download it.

For our example we will use a db named “taskmaster” which has the following schema :
Screen Shot 2014-10-08 at 3.31.40 pm

Now, Lets see the example to see the metadata of this schema :

SqlDatabaseMetaDataExample.java

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

public class SqlDatabaseMetaDataExample {

	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DATABASE_URL = "jdbc:mysql://localhost/taskmaster";

	// Database credentials
	static final String USER_NAME = "root";
	static final String PASSWORD = "";

	public static void main(String[] args) {

		Connection conn = null;
		try {
			//Register driver and get a connection object from driver manager
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DATABASE_URL, USER_NAME, PASSWORD);

			DatabaseMetaData databaseMetaData = conn.getMetaData();
			
			//Data base name and version
			System.out.println(databaseMetaData.getDatabaseProductName());
			System.out.println(databaseMetaData.getDatabaseMajorVersion());
			System.out.println(databaseMetaData.getDatabaseMinorVersion());
			

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}

Output:

MySQL
5
6

In the example above, After registering jdbc driver and getting a Connection object from DriverManager class, I used the method getMetaData() of the java.util.Connection class to get a DatabaseMetadata object.

This DatabaseMetadata object can be used to get the meta data of the database. We used databaseMetaData.getDatabaseProductName() to get the DB Product Name and getDatabaseMajorVersion(), getDatabaseMinorVersion() for major and minor versions.

Lets see few code snippets which can be used to find more details:

Table Names

The method databaseMetaData.getTables(catalog, schemaPattern, tableNamePattern, types);. These 4 parameters are used to narrow down the results based on these. If we pass null it ignores these search parameters.

SqlDatabaseMetaDataExample.java

			//Find table names
			ResultSet result = databaseMetaData.getTables(
			    null, null, null, null );

			while(result.next()) {
			    String tableName = result.getString(3);
			    System.out.println(tableName);
			}

Output :

meal
task
user
userFriendMapping

The ResultSet returned from the getTables() method contains a list of table names matching the 4 given parameters (which were all null). This ResultSet contains 10 columns, which each contain information about the given table. The column with index 3 contains the table name itself. More details can be seen in the Java Docs.

Column Names of a table

Lets try to find the column names for a particular table, here we will use the method databaseMetaData.getColumns() for this. Here we will pass the parameter tableNamePattern with the value as name of the table of which we want to find the columns.

SqlDatabaseMetaDataExample.java

// See Column names of the table user
			String tableNamePattern = "user";
			ResultSet col_result = databaseMetaData.getColumns(null, null,
					tableNamePattern, null);

			while (col_result.next()) {
				System.out.println(col_result.getString(4));
			}

Output :

id
emailAddress
firstName
lastName
mobileNumber
password

Here passing the string “user” as the value for tableNamePattern resulted in a result set consisting of all the columns of the table “user”. We used the column index 4 as it contains the column name. More details can be seen in the Java Docs of the method.

So, here we saw few examples of how to use DatabaseMetaData to view the metadata of a database.

Download
You can download the full source code of this example here : SqlDatabaseMetaDataExample.zip

Anirudh Bhatnagar

Anirudh is a Java programmer with extensive experience in building Java/J2EE applications. He has always been fascinated by the new technologies and emerging trends in software development. He has been involved in propagating these changes and new technologies in his projects. He is an avid blogger and agile enthusiast who believes in writing clean and well tested code.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button