{"id":18998,"date":"2015-01-27T15:00:00","date_gmt":"2015-01-27T13:00:00","guid":{"rendered":"http:\/\/examples.javacodegeeks.com\/?p=18998"},"modified":"2019-03-05T12:55:59","modified_gmt":"2019-03-05T10:55:59","slug":"jdbc-databasemetadata-example","status":"publish","type":"post","link":"https:\/\/examples.javacodegeeks.com\/java-development\/core-java\/sql\/jdbc-databasemetadata-example\/","title":{"rendered":"JDBC DatabaseMetaData Example"},"content":{"rendered":"

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<\/code> .<\/p>\n

1. What is the Metadata ?<\/h2>\n

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

2. How is this useful ?<\/h2>\n

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.<\/p>\n

3. What We Need ?<\/h2>\n
    \n
  1. JDBC Driver (We use MYSQL 5 Driver)<\/li>\n
  2. An IDE of our taste (We use Eclipse)<\/li>\n
  3. JDK 1.7 (Due to DBCP2 runs on Java 7)<\/li>\n
  4. An DBMS running with a valid Schema (In this example we named it \u201cTest\u201d in MYSQL 5 Engine)<\/li>\n<\/ol>\n

    4. The Example<\/h2>\n

    We use the traditional DBConnection.java<\/code> class to connect to the database.<\/p>\n

    DBConnection.java<\/em><\/span><\/p>\n

    package com.javacodegeeks.jdbc.metadata;\n\nimport java.sql.Connection;\nimport java.sql.DriverManager;\nimport java.sql.SQLException;\n\n\/**\n * @author Andres.Cespedes\n * @version 1.0 $Date: 24\/01\/2015\n * @since 1.7\n *\n *\/\npublic class DBConnection {\n\n\tprivate static String DB_URL = \"jdbc:mysql:\/\/localhost:3307\/test\";\n\tprivate static String DB_USER = \"admin\";\n\tprivate static String DB_PASSWORD = \"admin\";\n\n\tpublic static Connection getConnection() throws SQLException {\n\t\tConnection connection = DriverManager.getConnection(DB_URL, DB_USER,\n\t\t\t\tDB_PASSWORD);\n\t\tSystem.err.println(\"The connection is successfully obtained\");\n\t\treturn connection;\n\t}\n}\n<\/pre>\n
    With the connection<\/code>, now we can get all the comprehensive information about the schemas or the tables and everything else as a whole, using the getMetaData()<\/code> method.
     
    <\/div> <\/div><\/p>\n
    Metadata.java<\/em><\/span><\/p>\n
    package com.javacodegeeks.jdbc.metadata;\n\nimport java.sql.Connection;\nimport java.sql.DatabaseMetaData;\nimport java.sql.ResultSet;\nimport java.sql.SQLException;\nimport java.util.ArrayList;\n\n\/**\n * @author Andres.Cespedes\n * @version 1.0 $Date: 24\/01\/2015\n * @since 1.7\n *\/\npublic class Metadata {\n\n\tstatic Connection connection = null;\n\tstatic DatabaseMetaData metadata = null;\n\n\t\/\/ Static block for initialization\n\tstatic {\n\t\ttry {\n\t\t\tconnection = DBConnection.getConnection();\n\t\t} catch (SQLException e) {\n\t\t\tSystem.err.println(\"There was an error getting the connection: \"\n\t\t\t\t\t+ e.getMessage());\n\t\t}\n\n\t\ttry {\n\t\t\tmetadata = connection.getMetaData();\n\t\t} catch (SQLException e) {\n\t\t\tSystem.err.println(\"There was an error getting the metadata: \"\n\t\t\t\t\t+ e.getMessage());\n\t\t}\n\t}\n\n\t\/**\n\t * Prints in the console the general metadata.\n\t * \n\t * @throws SQLException\n\t *\/\n\tpublic static void printGeneralMetadata() throws SQLException {\n\t\tSystem.out.println(\"Database Product Name: \"\n\t\t\t\t+ metadata.getDatabaseProductName());\n\t\tSystem.out.println(\"Database Product Version: \"\n\t\t\t\t+ metadata.getDatabaseProductVersion());\n\t\tSystem.out.println(\"Logged User: \" + metadata.getUserName());\n\t\tSystem.out.println(\"JDBC Driver: \" + metadata.getDriverName());\n\t\tSystem.out.println(\"Driver Version: \" + metadata.getDriverVersion());\n\t\tSystem.out.println(\"\\n\");\n\t}\n\n\t\/**\n\t * \n\t * @return Arraylist with the table's name\n\t * @throws SQLException\n\t *\/\n\tpublic static ArrayList getTablesMetadata() throws SQLException {\n\t\tString table[] = { \"TABLE\" };\n\t\tResultSet rs = null;\n\t\tArrayList tables = null;\n\t\t\/\/ receive the Type of the object in a String array.\n\t\trs = metadata.getTables(null, null, null, table);\n\t\ttables = new ArrayList();\n\t\twhile (rs.next()) {\n\t\t\ttables.add(rs.getString(\"TABLE_NAME\"));\n\t\t}\n\t\treturn tables;\n\t}\n\n\t\/**\n\t * Prints in the console the columns metadata, based in the Arraylist of\n\t * tables passed as parameter.\n\t * \n\t * @param tables\n\t * @throws SQLException\n\t *\/\n\tpublic static void getColumnsMetadata(ArrayList tables)\n\t\t\tthrows SQLException {\n\t\tResultSet rs = null;\n\t\t\/\/ Print the columns properties of the actual table\n\t\tfor (String actualTable : tables) {\n\t\t\trs = metadata.getColumns(null, null, actualTable, null);\n\t\t\tSystem.out.println(actualTable.toUpperCase());\n\t\t\twhile (rs.next()) {\n\t\t\t\tSystem.out.println(rs.getString(\"COLUMN_NAME\") + \" \"\n\t\t\t\t\t\t+ rs.getString(\"TYPE_NAME\") + \" \"\n\t\t\t\t\t\t+ rs.getString(\"COLUMN_SIZE\"));\n\t\t\t}\n\t\t\tSystem.out.println(\"\\n\");\n\t\t}\n\n\t}\n\n\t\/**\n\t * \n\t * @param args\n\t *\/\n\tpublic static void main(String[] args) {\n\t\ttry {\n\t\t\tprintGeneralMetadata();\n\t\t\t\/\/ Print all the tables of the database scheme, with their names and\n\t\t\t\/\/ structure\n\t\t\tgetColumnsMetadata(getTablesMetadata());\n\t\t} catch (SQLException e) {\n\t\t\tSystem.err\n\t\t\t\t\t.println(\"There was an error retrieving the metadata properties: \"\n\t\t\t\t\t\t\t+ e.getMessage());\n\t\t}\n\t}\n}\n<\/pre>\n
    In the above example, we just used two methods from the DatabaseMetaData<\/code> interface, the first method getTables<\/code> returns a Resultset<\/code> 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<\/code> we can get the information looking for the some columns as:[ulp id=’TD36VgQCKfhTAygK’]<\/p>\n