How to Dynamically Retrieve JDBC ResultSet Data

Question

The requirement is :-

The application will run dynamic SQLs and show the results in table format in JSP. The SQL passed to the application will change, which means the number, name, datatype of selected columns will change and so the result set will also change. The SQL is stored in a config.properties file, everytime we need to run a different SQL, we will just change the SQL in config.properties file. After the SQL is executed, from the ResultSet's Metadata object I have retrieved the column names and column datatypes by :-

ResultSetMetaData rsmd = rs.getMetaData(); // rs is the ResultSet
HashMap<String , String> hmap = new LinkedHashMap<String , String>();
for(int i=1;i<=rsmd.getColumnCount();i++)
{
   hmap.put(rsmd.getColumnName(i), rsmd.getColumnTypeName(i));
}
hmap.entrySet().forEach(entry ->{System.out.println(entry.getKey() + " : " + entry.getValue());});

Output :-

TRADER : VARCHAR2 
TRAN_NUM : NUMBER 
STARTTIME : DATE 
ERROR_DETAILS : CLOB

In JDBC, we have specific methods eg. rs.getString(columnName), rs.getInt(columnIndex), rs.getTimestamp(), rs.getClob() to get data of different data types. But in this scenario everything is dynamic, as columnName and columnDatatype will change everytime.

The ResultSet contains around 2000 rows.

How to write the logic, to check the column's datatype and apply the correct rs.getXXX() method to retrieve the ResultSet's data dynamically ?

Thanks & Regards Saswata Mandal

Answer 1

I am able to do it by :-

while(rs.next()) 
{ 
  JsonObject jsonRow = new JsonObject();    
                  
  for(String colName : ResultSetColumnNames)
   {
    jsonRow.addProperty(colName, rs.getObject(colName)==null ? "NULL": rs.getObject(colName).toString()); 
   }              
  jsonArry.add(jsonRow);              
}

Thanks and Regards Saswata Mandal