Is it possible to map a single object from multiple rows?

[Erin Drummond]

Hello,

I am trying to use a ResultSetMapper to build an object that requires multiple rows for a single object. However, it appears that ther has to be a 1:1 relationship between a row in the resultset and a mapped object (ie, 1 row = 1 object).

I am calling it like so:

Handle db = Database.getHandle();
FormGroup fg = db.createQuery("SELECT * FROM vw_form_group WHERE id=:group_id")
                  .bind("group_id", id)
                  .map(new FormGroupMapper())
                  .first();   

I want to get a single FormGroup object from the entire result set. However, first() only returns the first row to the FormGroupMapper, and if I change it to list() then the result type is List<FormGroup> where I just want a single FormGroup.

How can I achieve this?

Thanks,
Erin

[Brian McCallister]

Try fold() if you want to accumulate across rows.

--
 
 

[Erin Drummond]

Is there some documentation on using this? (or some example code somewhere?) I see the method takes an Object as an accumulator and a Folder3 instance as a 'folder' but I am unsure exactly what the accumulator object needs to be and how to implement a Folder3

[Brian McCallister]

Here is an example which accumulates into an ArrayList:

https://github.com/brianm/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/TestQueries.java#L325

A fold basically traverses the result set, calling a function for each row. It passes the result of the last call as an argument to the next call, returning the final result at the end. You can pass an initial value in to the call to fold().

The Folder3 interface expcects to be called *after* any per-row mappers.

-Brian

--
 
 

[Erin Drummond]

Hi Brian,

Thanks for pointing me to that, it was very helpful.
However, I have another question - basically, I want to use the
ResultSet interface in my Folder3 function, so as far as I understand
in order to do that, I need to make the map() function return a
ResultSet. My question is, is there a built-in way to do this or do I
have to do something like:

db.createQuery("SELECT * FROM vw_form WHERE id=:form_id")
.bind("form_id", id)
.map(new ResultSetMapper<ResultSet>() {
public ResultSet map(int i, ResultSet rs, StatementContext sc)
throws SQLException {
return rs;
}
}).fold(new Form(), new Folder3<Form, ResultSet>() {
public Form fold(Form at, ResultSet mt, FoldController fc,
StatementContext sc) throws SQLException {
//BUILD FORM OBJECT HERE throw new
UnsupportedOperationException("Not supported yet.");
}
});

(I noticed that if I omit the map() function entirely, I can get a
Map<String, Object> for each row, but I like the convieniance methods
in the ResultSet interface for getInt(), getString() etc)

Cheers,
Erin

> --
>
>

[Brian McCallister]

On Tue, Dec 25, 2012 at 2:48 AM, Erin Drummond <erin...@gmail.com> wrote:

Hi Brian,

Thanks for pointing me to that, it was very helpful.
However, I have another question - basically, I want to use the
ResultSet interface in my Folder3 function, so as far as I understand
in order to do that, I need to make the map() function return a
ResultSet. My question is, is there a built-in way to do this or do I
have to do something like:

You can use Folder2 (or even Folder, but it is deprecated as I forgot to pass the StatementContext as an argument) to accomplish this today, and it is perfectly fine. I use it all over and it works well.

 

db.createQuery("SELECT * FROM vw_form WHERE id=:form_id")
.bind("form_id", id)
.map(new ResultSetMapper<ResultSet>() {
   public ResultSet map(int i, ResultSet rs, StatementContext sc)
throws SQLException {
      return rs;
   }
}).fold(new Form(), new Folder3<Form, ResultSet>() {
   public Form fold(Form at, ResultSet mt, FoldController fc,
StatementContext sc) throws SQLException {
      //BUILD FORM OBJECT HERE throw new
UnsupportedOperationException("Not supported yet.");
    }
});

(I noticed that if I omit the map() function entirely, I can get a
Map<String, Object> for each row, but I like the convieniance methods
in the ResultSet interface for getInt(), getString() etc)

I would like the mapped type to start with ResultSet instead of Map<String, Object> but it is too late to change that now. I had thought Map<String, Object> was useful as it was the default in jdbi1, in Java 1.4. No one uses that Map<String, Object> today though, except maybe from JRuby, so it turns out to have been a pretty poor decision. Oops.

In jdbi3 I'd like to start with ResultSet, though whether to start with a proxy which prevents calling mutators (next() etc) or to create a ResultSet implementation which genuinely captures the state, immutably, of the current row in the result set, is unsure. The first seems less likely to have bugs, the second feels more correct.

-Brian

 

--

[Erin Drummond]

Hi Brian,

Thanks for explaining that, the Folder2 interface was exactly what I
needed. However, the method Query.fold(AccumulatorType,
Folder2<AccumulatorType>) appears to be marked as deprecated. This
isn't too much of an issue, just my IDE puts nice strikeout lines
through the method call :)

The Folder2 interface is definitely much simpler for my use case,
however I understand that the Folder3 interface offers much more
flexibility and works in conjunction with Query.map() which is why its
the non-deprecated solution.

Cheers,
Erin

> --
>
>

[Brian McCallister]

On Thu, Jan 3, 2013 at 3:01 PM, Erin Drummond <erin...@gmail.com> wrote:

Hi Brian,

Thanks for explaining that, the Folder2 interface was exactly what I
needed. However, the method Query.fold(AccumulatorType,
Folder2<AccumulatorType>) appears to be marked as deprecated. This
isn't too much of an issue, just my IDE puts nice strikeout lines
through the method call :)

The Folder2 interface is definitely much simpler for my use case,
however I understand that the Folder3 interface offers much more
flexibility and works in conjunction with Query.map() which is why its
the non-deprecated solution.

It is, but it doesn't provide all the functionality of Folder2, so I'll undeprecate it :-)

 

This will create some confusion, but is best for now :-/

Just cut 2.43.1 (should sync to central shortly) which un-deprecates Folder2 usage. I cannot mock Sun/Oracle for undeprecating Thread stuff anymore :-D

-Brian

--