Extracting ResultSetMetaData from Spring JdbcTemplate

Question

I am trying to fetch the resultsetmeta data using Spring jdbc template. It works fine if there is atleast one row returned.

The problem arises when there is no rows returned i.e. an empty resultSet.

I have tried a lot and still stuck up with the same. If there is any solution to this, please help me with this.

Also, I found ResultSetWrappingSqlRowSetMetaData class in spring. Is this of some use in my context?

Thanks for the help.

Answer 1

Finally I found the answer to my question. Below is the code:

template.query(builder.toString(),new ResultSetExtractor<Integer>() {

    @Override
    public Integer extractData(ResultSet rs) throws SQLException, DataAccessException {

        ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    for(int i = 1 ; i <= columnCount ; i++){
        SQLColumn column = new SQLColumn();
    column.setName(rsmd.getColumnName(i));
    column.setAutoIncrement(rsmd.isAutoIncrement(i));
    column.setType(rsmd.getColumnTypeName(i));
    column.setTypeCode(rsmd.getColumnType(i));
    column.setTableName(sqlTable.getName().toUpperCase());
    columns.add(column);
    }

    return columnCount;
}
});

For a detailed explanation you can visit here

Answer 2

You can also use JdbcUtils

@SuppressWarnings("unchecked")
public static List<TableColumnTypeMap> getTableColumns(DataSource dataSource,
    String tableName) {
    try {

        return (List<TableColumnTypeMap>) JdbcUtils.extractDatabaseMetaData(dataSource,
            new DatabaseMetaDataCallback() {
                @Override
                public Object processMetaData(DatabaseMetaData dbmd)
                    throws SQLException, MetaDataAccessException {
                    ResultSet rs = dbmd
                        .getColumns("", "%", tableName + "%", null);
                    List<TableColumnTypeMap> list = new ArrayList();
                    while (rs.next()) {

                        String tableCat = rs.getString("TABLE_CAT");
                        String tableSchem = rs.getString("TABLE_SCHEM");
                        String tableName = rs.getString("TABLE_NAME");
                        String columnName = rs.getString("COLUMN_NAME");
                        String typeName = rs.getString("TYPE_NAME");
                        String columnSize = rs.getString("COLUMN_SIZE");
                        String nullable = rs.getString("NULLABLE");
                        String remarks = rs.getString("REMARKS");
                        int dataType = rs.getInt("DATA_TYPE");

                        System.out.println(tableName);
                        TableColumnTypeMap tableColumnTypeMap = new TableColumnTypeMap()
                            .setColumnName(columnName)
                            .setColumnType(typeName)
                            .setJavaClassName(getColumnCLassName(dataType))
                            .setRemarks(remarks);
                        list.add(tableColumnTypeMap);
                    }
                    return list;
                }
            });
    } catch (MetaDataAccessException ex) {
        throw new RuntimeException("get table list failed", ex);
    }
}

for java class map:

private static String getColumnCLassName(int sqlType) {
    String className = String.class.getName();

    switch (sqlType) {

        case Types.NUMERIC:
        case Types.DECIMAL:
            className = java.math.BigDecimal.class.getName();
            break;

        case Types.BIT:
            className = java.lang.Boolean.class.getName();
            break;

        case Types.TINYINT:
            className = java.lang.Byte.class.getName();
            break;

        case Types.SMALLINT:
            className = java.lang.Short.class.getName();
            break;

        case Types.INTEGER:
            className = java.lang.Integer.class.getName();
            break;

        case Types.BIGINT:
            className = java.lang.Long.class.getName();
            break;

        case Types.REAL:
            className = java.lang.Float.class.getName();
            break;

        case Types.FLOAT:
        case Types.DOUBLE:
            className = java.lang.Double.class.getName();
            break;

        case Types.BINARY:
        case Types.VARBINARY:
        case Types.LONGVARBINARY:
            className = "byte[]";
            break;

        case Types.DATE:
            className = java.sql.Date.class.getName();
            break;

        case Types.TIME:
            className = java.sql.Time.class.getName();
            break;

        case Types.TIMESTAMP:
            className = java.sql.Timestamp.class.getName();
            break;

        case Types.BLOB:
            className = java.sql.Blob.class.getName();
            break;

        case Types.CLOB:
            className = java.sql.Clob.class.getName();
            break;
        default:
            break;
    }

    return className;
}

for result class

 /**
 * @author ryan
 * @date 19-7-15 下午6:05
 */
@Data
@Accessors(chain = true)
public class TableColumnTypeMap {

    private String columnName;
    private String columnType;
    private String javaClassName;
    private String remarks;

}