multiple resultsets into one pojo

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

multiple resultsets into one pojo

Roger Caplan
For various arcane reasons, we have (mysql) stored procedures that do
2 simple selects rather than one select/join for a one-to-many
relationship. I can't seem to find a way to map these to a single pojo
- here's an example:

SQL:

    select articleID, name from article where articleID=1234; -- returns 1 row
    select commentID, body from comment where articleID=1234; --
returns many rows

Java:

    class Article {
      private Integer articleID;
      private String name;
      private Set<Comment> comments;
    }

    class Comment {
      private Integer commentID;
      private String body;
    }


Is there any way to do this mapping without manually "gluing together"
the article and comments in custom java code?

--
-Roger
Reply | Threaded
Open this post in threaded view
|

Re: multiple resultsets into one pojo

Jon_E
Look "Nested Selects" in the MyBatis 3 User Guide, currently page
35-36.  There examples shown to do exactly this.

- jon

On Oct 21, 11:14 am, Roger Caplan <[hidden email]> wrote:

> For various arcane reasons, we have (mysql) stored procedures that do
> 2 simple selects rather than one select/join for a one-to-many
> relationship. I can't seem to find a way to map these to a single pojo
> - here's an example:
>
> SQL:
>
>     select articleID, name from article where articleID=1234; -- returns 1 row
>     select commentID, body from comment where articleID=1234; --
> returns many rows
>
> Java:
>
>     class Article {
>       private Integer articleID;
>       private String name;
>       private Set<Comment> comments;
>     }
>
>     class Comment {
>       private Integer commentID;
>       private String body;
>     }
>
> Is there any way to do this mapping without manually "gluing together"
> the article and comments in custom java code?
>
> --
> -Roger
Reply | Threaded
Open this post in threaded view
|

Re: multiple resultsets into one pojo

Roger-2
My impression is that nested selects will send a *new* query to the
database; how can I make them refer to an existing resultset instead?

This is what I see in the MyBatis manual:

 <resultMap id=”blogResult” type=”Blog”>
  <association property="author" column="blog_author_id"
javaType="Author"
    select=”selectAuthor”/>
 </resultMap>

 <select id=”selectAuthor” parameterType=”int” resultType="Author">
   SELECT * FROM AUTHOR WHERE ID = #{id}
 </select>

, in which case "selectAuthor" sends a *new* query to the db, right?


On Oct 22, 8:50 pm, Jon_E <[hidden email]> wrote:

> Look "Nested Selects" in the MyBatis 3 User Guide, currently page
> 35-36.  There examples shown to do exactly this.
>
> - jon
>
> On Oct 21, 11:14 am, Roger Caplan <[hidden email]> wrote:
>
> > For various arcane reasons, we have (mysql) stored procedures that do
> > 2 simple selects rather than one select/join for a one-to-many
> > relationship. I can't seem to find a way to map these to a single pojo
> > - here's an example:
>
> > SQL:
>
> >     select articleID, name from article where articleID=1234; -- returns 1 row
> >     select commentID, body from comment where articleID=1234; --
> > returns many rows
>
> > Java:
>
> >     class Article {
> >       private Integer articleID;
> >       private String name;
> >       private Set<Comment> comments;
> >     }
>
> >     class Comment {
> >       private Integer commentID;
> >       private String body;
> >     }
>
> > Is there any way to do this mapping without manually "gluing together"
> > the article and comments in custom java code?
>
> > --
> > -Roger
>
>
Reply | Threaded
Open this post in threaded view
|

Re: multiple resultsets into one pojo

Jon_E
Sorry, I thought you had two simple queries.  If you want one result
set, the only way I would know how to do this is to create one SQL
statement (which would be a join, obviously).  Your result map can get
it into one POJO.  The manual, "Nested Results for a Collection"
describes how to do this.  Just substitute the syntax for a collection
with the association you want.

You may or may not like this, though.  I used it on a greenfield
project.  At first it was great.  I was building a large, complex
object map with a result set using just one hit to the database.  Like
with all complex SQL joins, it got to be difficult to maintain and
debug.  Also, if we needed just one object (bean) out of the map, we
had to create another separate SQL statement for just that data *or*
call that big SQL statement just to get that one bit of data.  The
redundant SQL statements weren't so bad, but the troubleshooting was a
nightmare.  We found the slight performance hit of separate SQL
statements so slight as to be unnoticeable by the users (we are using
DB2/400, btw).

Good luck.



On Oct 22, 8:57 pm, Roger <[hidden email]> wrote:

> My impression is that nested selects will send a *new* query to the
> database; how can I make them refer to an existing resultset instead?
>
> This is what I see in the MyBatis manual:
>
>  <resultMap id=”blogResult” type=”Blog”>
>   <association property="author" column="blog_author_id"
> javaType="Author"
>     select=”selectAuthor”/>
>  </resultMap>
>
>  <select id=”selectAuthor” parameterType=”int” resultType="Author">
>    SELECT * FROM AUTHOR WHERE ID = #{id}
>  </select>
>
> , in which case "selectAuthor" sends a *new* query to the db, right?
>
> On Oct 22, 8:50 pm, Jon_E <[hidden email]> wrote:
>
>
>
> > Look "Nested Selects" in the MyBatis 3 User Guide, currently page
> > 35-36.  There examples shown to do exactly this.
>
> > - jon
>
> > On Oct 21, 11:14 am, Roger Caplan <[hidden email]> wrote:
>
> > > For various arcane reasons, we have (mysql) stored procedures that do
> > > 2 simple selects rather than one select/join for a one-to-many
> > > relationship. I can't seem to find a way to map these to a single pojo
> > > - here's an example:
>
> > > SQL:
>
> > >     select articleID, name from article where articleID=1234; -- returns 1 row
> > >     select commentID, body from comment where articleID=1234; --
> > > returns many rows
>
> > > Java:
>
> > >     class Article {
> > >       private Integer articleID;
> > >       private String name;
> > >       private Set<Comment> comments;
> > >     }
>
> > >     class Comment {
> > >       private Integer commentID;
> > >       private String body;
> > >     }
>
> > > Is there any way to do this mapping without manually "gluing together"
> > > the article and comments in custom java code?
>
> > > --
> > > -Roger