Java ResultSet copy to generic object or list

Question

I'm aware of the issues with using ResultSet and have seen examples about extracting data into an object (or list), however, I'm wanting to do this in a generic manner i.e. without having to be aware of the class.

Is there a way to do this such that the result would be an Object or List that I could then iterate using Reflection?

The solutions I've examined that either don't seem to work or require class awareness of the target class include:

I've also tried ResultSetMapper (http://resultsetmapper.sourceforge.net/) and it seems to require the class as well. I really just want an Object or List that I can act on.

More examples requiring lines of code specific to each of the object's properties; stackoverflow.com/questions/17206523/… and stackoverflow.com/questions/9440482/… - any way to do this where the output is a List<Object> instead? — joelc
– joelc, Commented May 24, 2014 at 1:52

Gord Thompson · Accepted Answer · 2016-12-09 16:54:28Z

8

This is pretty generic:

Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM Clients WHERE ID <= 2");

// collect column names
List<String> columnNames = new ArrayList<>();
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
    columnNames.add(rsmd.getColumnLabel(i));
}

int rowIndex = 0;
while (rs.next()) {
    rowIndex++;
    // collect row data as objects in a List
    List<Object> rowData = new ArrayList<>();
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        rowData.add(rs.getObject(i));
    }
    // for test purposes, dump contents to check our results
    // (the real code would pass the "rowData" List to some other routine)
    System.out.printf("Row %d%n", rowIndex);
    for (int colIndex = 0; colIndex < rsmd.getColumnCount(); colIndex++) {
        String objType = "null";
        String objString = "";
        Object columnObject = rowData.get(colIndex);
        if (columnObject != null) {
            objString = columnObject.toString() + " ";
            objType = columnObject.getClass().getName();
        }
        System.out.printf("  %s: %s(%s)%n",
                columnNames.get(colIndex), objString, objType);
    }
}

For my test data (in SQL Server) it prints the following to the console:

Row 1
  ID: 1 (java.lang.Integer)
  LastName: Thompson (java.lang.String)
  FirstName: Gord (java.lang.String)
  DOB: (null)
Row 2
  ID: 2 (java.lang.Integer)
  LastName: Loblaw (java.lang.String)
  FirstName: Bob (java.lang.String)
  DOB: 1966-09-12 16:03:00.0 (java.sql.Timestamp)

edited Dec 9, 2016 at 16:54

answered May 24, 2014 at 14:11

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

joelc Over a year ago

Thanks for providing this Gord - I'll certainly give it a try. I'm a bit constrained in that I need a List<Object> to pass to another function that I don't control. Do you know of a solution that produces a List<Object> or will convert Maps cleanly?

GreyBeardedGeek Over a year ago

Use a List<Map> instead of Map<Integer, Map<Integer, Object>>

Gord Thompson Over a year ago

@haxor I have updated my answer to use List<Object>.

joelc Over a year ago

Hi Gord - thanks. In your example is each of the rows a separate Object or a separate List<Object>? I would assume each row to be an object but that's not what I get from your code output. Nevertheless this is really, really helpful (marking as answer). Is your code easily modifiable to have each row be an Object and a List<Object> containing all of them?

Gord Thompson Over a year ago

@haxor re: "Is your code easily modifiable to have each row be an Object and a List<Object> containing all of them?" - Well, an ArrayList object is an Object itself, so you could conceivably add each rowData object to another ArrayList named tblData. Then, depending on how precise you want your terminology to be you could say that each row is an Object (an ArrayList object) and the List of row "Objects" (named tblData) is a List<Object>.

|

Collectives™ on Stack Overflow

Java ResultSet copy to generic object or list

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related