Gathering MetaData of A Table through the JDBC

When we are dealing with the ORM we don't even turn our eyes towards the table meta-data. But, on one fine day you would be waking up just to handle the tables through the meta-data. The tables can be accessed only through the JDBC interfaces and no other layer is crafted for you to do CURD operations. Yes, I faced the same scenario and the following are the few snippets that really helped me to gather meta-data from the DB schema.

The following are few aspects of Meta-data on which I'm interested in:

• Table organization
• Column Name
• Data type of a column
• Constraints 
• Non Null-able constraints 
• Check Constraints 
• Primary Key
• Child Tables Meta-data

I had the following POJO that was holding the data's that I required:

public class ColumnMetaData {

    private String columnName;
    private String dataType;
    private boolean nullable;
    private boolean autoIncrement;
}

public class TableMetaData {

    private String tableName;
    private Map<String, ColumnMetaData> columns;
    private String primaryKey;
    private boolean nonIDPrimaryKey;
    private Set<String> nonNullableColumns;
    private Map<String, ChildTableMetaData> childTables;
}

And the following are classes that I have used from java.sql package:

private Connection connection;
private DatabaseMetaData metadata;

above objects are set accordingly:

connection = jdbcTemplate.getDataSource().getConnection();
metadata = connection.getMetaData();

I would be running through the code snippets that helped me to collect the data on which I was interested.

Table Organization & Nullable Constraints: 

ResultSet columnsMetaData = metadata.getColumns(null, "VIVEK", "DEMO", null); **

while (columnsMetaData.next()) {

    ColumnMetaData metaData = new ColumnMetaData();
    String columnName = columnsMetaData.getString("COLUMN_NAME");
    metaData.setColumnName(columnName);
    metaData.setDataType(columnsMetaData.getString("DATA_TYPE"));
    metaData.setNullable(columnsMetaData.getBoolean("NULLABLE"));

    //nullableColumns are processed / used in 
TableMetaData     

    if (!metaData.isNullable()) {
        nullableColumns.add(metaData.getColumnName());
    }
}

Since, I'm aware of what data that I wanted to read form the ResultSet I inferred those data directly with getString/getBoolean. Probably you need to get the metadata of resultSet if you are interested in some thing else. 

Primary Key :

ResultSet tablePrimaryKey = metadata.getPrimaryKeys(null, "VIVEK", "DEMO"); **

while (tablePrimaryKey.next()) {

    primaryKey = tablePrimaryKey.getString("COLUMN_NAME");
    log.debug("{} is primary key for the table {}", primaryKey, table);

    //as we don't support composite columns for primary    break;
}


Child Table MetaData:

ResultSet exportedKeys = metadata.getExportedKeys(null, "VIVEK", "DEMO"); **

Map<String, ChildTableMetaData> childTablesMetaData = new HashMap<>();
while (exportedKeys.next()) {

    ChildTableMetaData childTableMetaData = new ChildTableMetaData();

    String childTableName = exportedKeys.getString("FKTABLE_NAME");

    childTableMetaData.setTableName(childTableName);
    childTableMetaData.setFkColumnName(exportedKeys.getString("FKCOLUMN_NAME"));
    childTableMetaData.setPkColumnName(exportedKeys.getString("PKCOLUMN_NAME"));
    childTablesMetaData.put(childTableName, childTableMetaData);
} 

** in this snippet "VIVEK" is the schema that I'm connecting and "DEMO" is the table name for

which I'm collecting the data.