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.

JDBC ResultSet to JSON transformation.

With a bunch ORM frameworks(especially for JVM languages) out there and each one of us sticking to our favorite ORM in the applications that we develop and when there is a necessary or need to handle the data in JDBC level even small stuff like converting ResultSet to Json seems to be a complex task. Here I'll be giving a gist on how to convert ResultSet to JSON object.

While querying the data through JDBC we either look for one tuple or a list of tuple (i.e. one or N rows).  In other words, We either query for Map (key representing column name and value representing the actual value in the table) or a List of Map. In technical terms we would invoke queryForList or queryForMap. The ResultSet can then be transformed to a Map from which we can easily transform to JSON object.

The following is the code I have implemented to convert the ResultSet List to a JSON:

List<Map<String, Object>> mapperList = new ArrayList<Map<String, Object>>();
List<Map<String, Object>> transformObject = (List) resultSet;

// Result Set might be empty so validate it before processing 

if (transformObject.size() < 1) {
    log.warn("No Results found");
    throw new NoEntityFoundException("Data not found");
}

//Iterate through each row in resultSet (Basically a Map) 

transformObject.forEach(result -> {

    Map<String, Object> transformMap = new HashMap<String, Object>();
    transformData(result, transformMap);
    mapperList.add(transformMap);
});

//print the transformed data 

System.out.println(mapper.writeValueAsString(mapperList)); 

The following is the Object Mapper configuration:

ObjectMapper objectMapper = new ObjectMapper();
//as we don't need to send NULL values in the JSON response
objectMapper.setSerializationInclusion(JsonInclude.Include.NON_NULL);
objectMapper.configure(SerializationFeature.WRITE_NULL_MAP_VALUES, false);