sql

JDBC DatabaseMetaData Example

In this example will talk about how to get the Database’s Metadata and what is the usefulness of that information for our development, through the Java API JDBC java.sql.DatabaseMetaData .

1. What is the Metadata ?

The Database’s Metadata is information about the data, what? Yes, is data about data, of which are two types of data, structural metadata about the design and specification of data structures and descriptive metadata about the identification of the resources and own instance information.
 
 

2. How is this useful ?

Imagine that you don’t have a database client IDE, and you only have the credentials, but you don’t have any information about the database and need to manage them. This is a common scenario for a developer and here is the usefulness of the metadata, with a brief code, we can get all the information about the database for start to develop our DML and DDL queries.

3. What We Need ?

  1. JDBC Driver (We use MYSQL 5 Driver)
  2. An IDE of our taste (We use Eclipse)
  3. JDK 1.7 (Due to DBCP2 runs on Java 7)
  4. An DBMS running with a valid Schema (In this example we named it “Test” in MYSQL 5 Engine)

4. The Example

We use the traditional DBConnection.java class to connect to the database.

DBConnection.java

package com.javacodegeeks.jdbc.metadata;

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

/**
 * @author Andres.Cespedes
 * @version 1.0 $Date: 24/01/2015
 * @since 1.7
 *
 */
public class DBConnection {

	private static String DB_URL = "jdbc:mysql://localhost:3307/test";
	private static String DB_USER = "admin";
	private static String DB_PASSWORD = "admin";

	public static Connection getConnection() throws SQLException {
		Connection connection = DriverManager.getConnection(DB_URL, DB_USER,
				DB_PASSWORD);
		System.err.println("The connection is successfully obtained");
		return connection;
	}
}

With the connection, now we can get all the comprehensive information about the schemas or the tables and everything else as a whole, using the getMetaData() method.

Metadata.java

package com.javacodegeeks.jdbc.metadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author Andres.Cespedes
 * @version 1.0 $Date: 24/01/2015
 * @since 1.7
 */
public class Metadata {

	static Connection connection = null;
	static DatabaseMetaData metadata = null;

	// Static block for initialization
	static {
		try {
			connection = DBConnection.getConnection();
		} catch (SQLException e) {
			System.err.println("There was an error getting the connection: "
					+ e.getMessage());
		}

		try {
			metadata = connection.getMetaData();
		} catch (SQLException e) {
			System.err.println("There was an error getting the metadata: "
					+ e.getMessage());
		}
	}

	/**
	 * Prints in the console the general metadata.
	 * 
	 * @throws SQLException
	 */
	public static void printGeneralMetadata() throws SQLException {
		System.out.println("Database Product Name: "
				+ metadata.getDatabaseProductName());
		System.out.println("Database Product Version: "
				+ metadata.getDatabaseProductVersion());
		System.out.println("Logged User: " + metadata.getUserName());
		System.out.println("JDBC Driver: " + metadata.getDriverName());
		System.out.println("Driver Version: " + metadata.getDriverVersion());
		System.out.println("\n");
	}

	/**
	 * 
	 * @return Arraylist with the table's name
	 * @throws SQLException
	 */
	public static ArrayList getTablesMetadata() throws SQLException {
		String table[] = { "TABLE" };
		ResultSet rs = null;
		ArrayList tables = null;
		// receive the Type of the object in a String array.
		rs = metadata.getTables(null, null, null, table);
		tables = new ArrayList();
		while (rs.next()) {
			tables.add(rs.getString("TABLE_NAME"));
		}
		return tables;
	}

	/**
	 * Prints in the console the columns metadata, based in the Arraylist of
	 * tables passed as parameter.
	 * 
	 * @param tables
	 * @throws SQLException
	 */
	public static void getColumnsMetadata(ArrayList tables)
			throws SQLException {
		ResultSet rs = null;
		// Print the columns properties of the actual table
		for (String actualTable : tables) {
			rs = metadata.getColumns(null, null, actualTable, null);
			System.out.println(actualTable.toUpperCase());
			while (rs.next()) {
				System.out.println(rs.getString("COLUMN_NAME") + " "
						+ rs.getString("TYPE_NAME") + " "
						+ rs.getString("COLUMN_SIZE"));
			}
			System.out.println("\n");
		}

	}

	/**
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			printGeneralMetadata();
			// Print all the tables of the database scheme, with their names and
			// structure
			getColumnsMetadata(getTablesMetadata());
		} catch (SQLException e) {
			System.err
					.println("There was an error retrieving the metadata properties: "
							+ e.getMessage());
		}
	}
}

In the above example, we just used two methods from the DatabaseMetaData interface, the first method getTables returns a Resultset object with the information about the type we send as a parameter, the typical types are “TABLE”, “VIEW”, “SYSTEM TABLE”, “GLOBAL TEMPORARY”, “LOCAL TEMPORARY”, “ALIAS”, “SYNONYM”. In the Resultset we can get the information looking for the some columns as:

  • TABLE_CAT String => table catalog (may be null)
  • TABLE_SCHEM String => table schema (may be null)
  • TABLE_NAME String => table name

With the second method getColumns we can obtain the information for each table, and with a forloop just get all the information from the database if we pass as a paremeter the table name retrieved in the previous method.

5. Running the Example

The connection is successfully obtained
Database Product Name: MySQL
Database Product Version: 5.6.22-log
Logged User: admin@localhost
JDBC Driver: MySQL Connector Java
Driver Version: mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om )


CITY
idcity INT 10
name VARCHAR 45
population INT 10
department INT 10


COUNTRY
idcountry INT 10
name VARCHAR 45
pib INT 10


DEPARTMENT
idDepartment INT 10
name VARCHAR 6
Tip
Some Driver vendors doesn’t implements all the methods from the API, and doesn’t retrieve all the columns, so you must avoid a NullPointerException with a try/catch block while you do either of these operations.

6. Download the Eclipse Project

Download
You can download the full source code of this example here: JDBCMetaDataExample

Andres Cespedes

Andres is a Java Software Craftsman from Medellin Colombia, who strongly develops on DevOps practices, RESTful Web Services, Continuous integration and delivery. Andres is working to improve software process and modernizing software culture on Colombia.
Subscribe
Notify of
guest

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

0 Comments
Inline Feedbacks
View all comments
Back to top button