CODEWITHSUNDEEP
javajdbccollections
Acid Rider
Acid Rider

Reputation: 1677

Java JDBC rowset to tocollection() method use

This is Java 8, Oracle JVM on Windows/Linux and using latest JDBC Java 8 compatible driver for MS-SQL (if it makes any difference).

I want to convert/cast/move data from a JDBC RowSet such as a CachedRowSet using the toCollection() method into a Collection of complete rows. This would allow me to use stream() and lambda to process the row set inside my Java program.

The single column toCollection("colname") method works fine for me.

I would like to know how to convert from JDBC row set into a collection containing all columns of the row set. I searched for examples but did not find any, any pointers on this will be much appreciated.

Upvotes: 0

Views: 1361

Answers (3)

Acid Rider
Acid Rider

Reputation: 1677

This may help someone else, I don't know. It turns out my code was actually OK. I had to change the IDE Eclipse setting to change the Restricted access messages from default setting of Error to Warning. This change permitted me to use Java Collection of Row (internal Sun class, imported) and to convert the JDBC row set to a Java collection suitable for use in Java 8 streams. I don't know if this is practice is supported or if its even a good practice to use the Sun internal Row class via import com.sun.rowset.internal.*; but the code does run correctly now.

Upvotes: 0

mv200580
mv200580

Reputation: 722

As mentioned above, you need to use toCollection() method, but that returns collection of com.sun.rowset.internal.Row which needs to be transformed to work with stream API. See the example:

ResultSetMetaData md = rowset.getMetaData();
List<Map<String, Object>> res = new ArrayList<>();
Collection<Row> c = (Collection<Row>) rowset.toCollection();
for(Row row: c) {
    Map<String, Object> m = new HashMap<String, Object>();
    for (int i = 1; i <= md.getColumnCount(); i++) {
        m.put(md.getColumnName(i), row.getColumnObject(i));
    }
    res.add(m);
}
res.stream().forEach(m -> System.out.println(m));

Upvotes: 2

Fayaz
Fayaz

Reputation: 471

Please have a look at the following snippet:

ResultSetMetaData rsmd = rs.getMetaData();
HashMap<String,Object> map;
while(rs.next()){
    int numColumns = rsmd.getColumnCount();
    map = new HashMap<String,Object>();
    for (int i=1; i<=numColumns; i++) {
        String colName = rsmd.getColumnName(i);
        if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){
            map.put(colName, rs.getArray(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.BIGINT){
            map.put(colName, rs.getInt(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){
            map.put(colName, rs.getBoolean(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.BLOB){
            map.put(colName, rs.getBlob(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.DOUBLE){
            map.put(colName, rs.getDouble(colName)); 
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.FLOAT){
            map.put(colName, rs.getFloat(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.INTEGER){
            map.put(colName, rs.getInt(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){
            map.put(colName, rs.getNString(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.VARCHAR){
            map.put(colName, rs.getString(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.TINYINT){
            map.put(colName, rs.getInt(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.SMALLINT){
            map.put(colName, rs.getInt(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.DATE){
            map.put(colName, rs.getDate(colName));
        }
        else if(rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){
            map.put(colName, rs.getTimestamp(colName));   
        }
        else{
            map.put(colName, rs.getObject(colName));
        }
    }
    //do something with the map here..
}

Upvotes: -1

Related Questions