JDBC DatabaseMetaData Example
Andres CespedesJanuary 27th, 2015Last Updated: March 5th, 2019In 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 JDBCjava.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 anddescriptive 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 traditionalDBConnection.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 theconnection, now we can get all the comprehensive information about the schemas or the tables and everything else as a whole, using thegetMetaData() 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 initializationstatic {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 tablefor (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// structuregetColumnsMetadata(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 theDatabaseMetaData interface, the first methodgetTables returns aResultset 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 theResultset we can get the information looking for the some columns as:
Thank you!
We will contact you soon.
- TABLE_CAT String => table catalog (may be null)
- TABLE_SCHEM String => table schema (may be null)
- TABLE_NAME String => table name
With the second methodgetColumns we can obtain the information for each table, and with aforloop 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 obtainedDatabase Product Name: MySQLDatabase Product Version: 5.6.22-logLogged User: admin@localhostJDBC Driver: MySQL Connector JavaDriver Version: mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om )CITYidcity INT 10name VARCHAR 45population INT 10department INT 10COUNTRYidcountry INT 10name VARCHAR 45pib INT 10DEPARTMENTidDepartment INT 10name 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
Thank you!
We will contact you soon.
Andres CespedesJanuary 27th, 2015Last Updated: March 5th, 2019
