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 ?
- JDBC Driver (We use MYSQL 5 Driver)
- An IDE of our taste (We use Eclipse)
- JDK 1.7 (Due to DBCP2 runs on Java 7)
- 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
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
You can download the full source code of this example here: JDBCMetaDataExample
