How Do You Retrieve the Column Name/Data/Type from a ResultSet? JDBC

Next we’ll see how to retrieve the column name from a result set as well as its associated type and data. To solve this problem, let’s return the result as a java.util.List, where each element of the list is a java.util.Map, which represents a retrieved row or record.

The Solution

We’ll use two methods:

  • getNameAndData(ResultSet rs): Retrieves the column name from a result set as well as its associated data.
  • getNameAndType(ResultSet rs): Retrieves the column name from a result set as well as its associated data type.

getNameAndData()

/**
* Get the column name from a result set as well as its associated data.
* @param rs a ResultSet object to process
* @throws SQLException Failed to get name and data from a ResultSet
* @return the result as a java.util.List (where each element of the
* list is a java.util.Map, which represents a retrieved row/record).
*/
public static java.util.List getNameAndData(ResultSet rs)
throws SQLException {
if (rs == null) {
return null;
}
ResultSetMetaData meta = rs.getMetaData();
if (meta == null) {
return null;
}
java.util.List rows = new java.util.ArrayList();
while(rs.next()) {
Map row = new HashMap();
for (int i = 1; i <= meta.getColumnCount(); i++){
String column = meta.getColumnName(i);
String value = rs.getString(i);
if (value == null){
value="";
}
row.put(column,value.trim());
}
rows.add(row);
}
return rows;
}

Discussion

  • The solution presented is a simplistic one: it assumes that all column types are either VARCHAR or CHAR. For getting values, we invoke ResultSet.getString(), which might not work for some data types such as BLOBs. The revised solution might be to get both the name and data type of each column, and then, based on the data type, invoke ResultSet.getXXX(), where XXX is derived from the data type of a column name.
  • ResultSetMetaData.getColumnCount() returns the number of columns returned from a ResultSet object.
  • ResultSetMetaData.getColumnName(i) returns the column name for the ith position (positions start from 1).

getNameAndType()

/**
* Get the column name from a result set as well as its associated data type.
* @param rs a ResultSet object to process
* @throws SQLException Failed to get name and data type from a ResultSet
* @return the result as a java.util.Map, where each element of the
* map is a pair of (name, data type).
*/
public static java.util.Map getNameAndType(ResultSet rs)
throws SQLException {
if (rs == null) {
return null;
}
ResultSetMetaData meta = rs.getMetaData();
if (meta == null) {
return null;
}
java.util.Map result = new HashMap();
for (int i = 1; i <= meta.getColumnCount(); i++){
String columnName = meta.getColumnName(i);
String columnType = meta.getColumnTypeName(i);
result.put(columnName, columnType);
}
return result;
}

Discussion

  • The solution I’ve presented is a general one, which means you can apply it to any ResultSet object.
  • ResultSetMetaData.getColumnName(i) returns the column name for the ith position (positions start from 1).
  • ResultSetMetaData.getColumnTypeName(i) returns the column data type name for the ith position (positions start from 1).

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics