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 :
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.
You can download the full source code of this example here : SqlDatabaseMetaDataExample.zip
