You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Eric Andres <er...@gmail.com> on 2008/03/13 10:54:56 UTC
Discriminator and Table per subclass
Hello,
I have a problem writing a polymorphic query. As I am new to iBatis, I
started digging the list archive and stumbled upon the discriminator
and submap tags, and I also found this in the archives: http://www.mail-archive.com/user-java@ibatis.apache.org/msg00070.html
, my situation is pretty close to Niels', but there was no solution in
that thread...
The situation I'm in is this: I have a type hierarchy representing
events consisting of a base class (call it Event) and a subclass for
each event type (30+ currently). It's representation in the database
was done with a table-per-subclass strategy.
I want to write a query that fetches an event based on it's ID. The
problem is that I don't know the event type I'm going to fetch in
advance, and I want to avoid a 30-table join. Is there a way a
delaying the join to a second query after the 'discrimination'?
Something like
<resultMap id="Event" class="BaseEvent">
...
<result property="ts" column="timestamp/>
<discriminator javaType="string" column="type">
<subMap value="userLoggedIn" select="addUserLoggedInProps"/>
...
</discriminator>
</resultMap>
...
<select id="addUserLoggedInProps" parameterClass="int"
resultMap="userLoggedInEvent">
select * from event, userloggedinevent
where event.id=userloggedin.eventid and event.id=#value#
</select>
...
<resultMap id="userLoggedInEvent" extends="Event">
...
<result property="userId" column="userid"/> <!-- specific to
userLoggedInEvent -->
...
</resultMap>
Sorry for the lengthy post. Any help greatly appreciated...
Thanks for your time,
Eric
Re: Discriminator and Table per subclass
Posted by Ryan Shelley <12...@gmail.com>.
Seems ok to me (but I'm not an expert). The only thing I might say is that
if "remoteAddress" and "userAgent" do not have a 1-to-many relationship, you
might as well do a single join (which it seems you want to avoid if you've
got 30 tables to join), otherwise, your 1 query turns into 30+ queries
(times the number of rows returned in the original query). I know you want
to avoid it, but there may be no simple solution to that, regardless of the
framework you use. One "drawback" to a highly normalized database is a
large number of joins. You could always use the "dynamic" clause in your
SQL to dynamically apply joins as necessary (so it's not 30 joins ALWAYS,
but a limited amount based upon criteria supplied in the parameter map).
-Ryan
On Fri, Mar 14, 2008 at 6:11 AM, Eric Andres <er...@gmail.com> wrote:
> Thank you for the hint, Ryan.
>
> I tried using the subselect, and it works. However, the way I made it work
> seems awkward, I think I did not fully understand your proposal. What I
> managed to do is this:
>
> I made a result map for the base class:
>
> <resultMap id="eventsMap" class="EventBaseClass">
> .....
> <discriminator javaType="string" column="type" jdbcType="VARCHAR">
> <subMap value="UserLoggedIn"
> resultMap="userLoggedInMap"></subMap>
> </discriminator>
> </resultMap>
>
> and here's the submap for a concrete event type:
>
> <resultMap id="userLoggedInMap" class="UserLoggedInEvent"
> extends="eventsMap">
> <result property="remoteAdress" select="fetchRemoteAddr"
> column="eventid"/>
> <result property="userAgent" select="fetchUserAgent"
> column="eventId"/>
> </resultMap>
>
> The select queries are all analogous, they look like this:
>
> <select id="fetchRemoteAddr" parameterClass="int" resultClass="string">
> select ip from eventuserloggedin where eventid=#value#
> </select>
>
> What I would like to do is, like you described, to have the subselect add
> the parameters to the result map in one shot... Am I on the right track or
> did I miss it completely? Sorry for being slow off the mark...
>
> On Mar 13, 2008, at 7:50 PM, Ryan Shelley wrote:
>
> Could you use a nested select? Check out page 34, under "Complex
> Properties":
> http://ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2_en.pdf
>
> The idea is that one ResultMap contains a complex type that consists of
> the results from another select. So if tableA has a primary key, and tableB
> has a foreign key to the primary key in tableA, you'd have a ResultMap of
> tableA results, and in each result from tableA exists a List (or Object) of
> ResultMap(s) that match the foreign key in tableB. One iBATIS query would
> execute the primary select to gather tableA results, and then automatically,
> a separate select will execute to gather the results for each tableA result
> and insert them into tableA's ResultMap.
>
> I might be incorrect, but I think the docs might be wrong. It says:
> <result property="category" column="PRD_CAT_ID" select="getCategory"/>
> and it should be:
> <result property="category" column="PRD_ID" select="getCategory"/>
> because you want to map PRD_ID from PRODUCT into the getCategory select.
>
> -Ryan
>
> On Thu, Mar 13, 2008 at 2:54 AM, Eric Andres <er...@gmail.com>
> wrote:
>
> > Hello,
> >
> > I have a problem writing a polymorphic query. As I am new to iBatis, I
> > started digging the list archive and stumbled upon the discriminator
> > and submap tags, and I also found this in the archives:
> > http://www.mail-archive.com/user-java@ibatis.apache.org/msg00070.html
> > , my situation is pretty close to Niels', but there was no solution in
> > that thread...
> > The situation I'm in is this: I have a type hierarchy representing
> > events consisting of a base class (call it Event) and a subclass for
> > each event type (30+ currently). It's representation in the database
> > was done with a table-per-subclass strategy.
> >
> > I want to write a query that fetches an event based on it's ID. The
> > problem is that I don't know the event type I'm going to fetch in
> > advance, and I want to avoid a 30-table join. Is there a way a
> > delaying the join to a second query after the 'discrimination'?
> > Something like
> >
> > <resultMap id="Event" class="BaseEvent">
> > ...
> > <result property="ts" column="timestamp/>
> > <discriminator javaType="string" column="type">
> > <subMap value="userLoggedIn"
> > select="addUserLoggedInProps"/>
> > ...
> > </discriminator>
> > </resultMap>
> >
> > ...
> >
> > <select id="addUserLoggedInProps" parameterClass="int"
> > resultMap="userLoggedInEvent">
> > select * from event, userloggedinevent
> > where event.id=userloggedin.eventid and event.id=#value#
> > </select>
> >
> > ...
> > <resultMap id="userLoggedInEvent" extends="Event">
> > ...
> > <result property="userId" column="userid"/> <!-- specific to
> > userLoggedInEvent -->
> > ...
> > </resultMap>
> >
> >
> > Sorry for the lengthy post. Any help greatly appreciated...
> >
> > Thanks for your time,
> >
> > Eric
> >
> >
>
>
Re: Discriminator and Table per subclass
Posted by Eric Andres <er...@gmail.com>.
Thank you for the hint, Ryan.
I tried using the subselect, and it works. However, the way I made it
work seems awkward, I think I did not fully understand your proposal.
What I managed to do is this:
I made a result map for the base class:
<resultMap id="eventsMap" class="EventBaseClass">
.....
<discriminator javaType="string" column="type"
jdbcType="VARCHAR">
<subMap value="UserLoggedIn"
resultMap="userLoggedInMap"></subMap>
</discriminator>
</resultMap>
and here's the submap for a concrete event type:
<resultMap id="userLoggedInMap" class="UserLoggedInEvent"
extends="eventsMap">
<result property="remoteAdress" select="fetchRemoteAddr"
column="eventid"/>
<result property="userAgent" select="fetchUserAgent"
column="eventId"/>
</resultMap>
The select queries are all analogous, they look like this:
<select id="fetchRemoteAddr" parameterClass="int" resultClass="string">
select ip from eventuserloggedin where eventid=#value#
</select>
What I would like to do is, like you described, to have the subselect
add the parameters to the result map in one shot... Am I on the right
track or did I miss it completely? Sorry for being slow off the mark...
On Mar 13, 2008, at 7:50 PM, Ryan Shelley wrote:
> Could you use a nested select? Check out page 34, under "Complex
> Properties":
> http://ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2_en.pdf
>
> The idea is that one ResultMap contains a complex type that consists
> of the results from another select. So if tableA has a primary key,
> and tableB has a foreign key to the primary key in tableA, you'd
> have a ResultMap of tableA results, and in each result from tableA
> exists a List (or Object) of ResultMap(s) that match the foreign key
> in tableB. One iBATIS query would execute the primary select to
> gather tableA results, and then automatically, a separate select
> will execute to gather the results for each tableA result and insert
> them into tableA's ResultMap.
>
> I might be incorrect, but I think the docs might be wrong. It says:
> <result property="category" column="PRD_CAT_ID" select="getCategory"/>
> and it should be:
> <result property="category" column="PRD_ID" select="getCategory"/>
> because you want to map PRD_ID from PRODUCT into the getCategory
> select.
>
> -Ryan
>
> On Thu, Mar 13, 2008 at 2:54 AM, Eric Andres <er...@gmail.com>
> wrote:
> Hello,
>
> I have a problem writing a polymorphic query. As I am new to iBatis, I
> started digging the list archive and stumbled upon the discriminator
> and submap tags, and I also found this in the archives: http://www.mail-archive.com/user-java@ibatis.apache.org/msg00070.html
> , my situation is pretty close to Niels', but there was no solution in
> that thread...
> The situation I'm in is this: I have a type hierarchy representing
> events consisting of a base class (call it Event) and a subclass for
> each event type (30+ currently). It's representation in the database
> was done with a table-per-subclass strategy.
>
> I want to write a query that fetches an event based on it's ID. The
> problem is that I don't know the event type I'm going to fetch in
> advance, and I want to avoid a 30-table join. Is there a way a
> delaying the join to a second query after the 'discrimination'?
> Something like
>
> <resultMap id="Event" class="BaseEvent">
> ...
> <result property="ts" column="timestamp/>
> <discriminator javaType="string" column="type">
> <subMap value="userLoggedIn"
> select="addUserLoggedInProps"/>
> ...
> </discriminator>
> </resultMap>
>
> ...
>
> <select id="addUserLoggedInProps" parameterClass="int"
> resultMap="userLoggedInEvent">
> select * from event, userloggedinevent
> where event.id=userloggedin.eventid and event.id=#value#
> </select>
>
> ...
> <resultMap id="userLoggedInEvent" extends="Event">
> ...
> <result property="userId" column="userid"/> <!-- specific to
> userLoggedInEvent -->
> ...
> </resultMap>
>
>
> Sorry for the lengthy post. Any help greatly appreciated...
>
> Thanks for your time,
>
> Eric
>
>
Re: Discriminator and Table per subclass
Posted by Ryan Shelley <12...@gmail.com>.
Could you use a nested select? Check out page 34, under "Complex
Properties":
http://ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2_en.pdf
The idea is that one ResultMap contains a complex type that consists of the
results from another select. So if tableA has a primary key, and tableB has
a foreign key to the primary key in tableA, you'd have a ResultMap of tableA
results, and in each result from tableA exists a List (or Object) of
ResultMap(s) that match the foreign key in tableB. One iBATIS query would
execute the primary select to gather tableA results, and then automatically,
a separate select will execute to gather the results for each tableA result
and insert them into tableA's ResultMap.
I might be incorrect, but I think the docs might be wrong. It says:
<result property="category" column="PRD_CAT_ID" select="getCategory"/>
and it should be:
<result property="category" column="PRD_ID" select="getCategory"/>
because you want to map PRD_ID from PRODUCT into the getCategory select.
-Ryan
On Thu, Mar 13, 2008 at 2:54 AM, Eric Andres <er...@gmail.com> wrote:
> Hello,
>
> I have a problem writing a polymorphic query. As I am new to iBatis, I
> started digging the list archive and stumbled upon the discriminator
> and submap tags, and I also found this in the archives:
> http://www.mail-archive.com/user-java@ibatis.apache.org/msg00070.html
> , my situation is pretty close to Niels', but there was no solution in
> that thread...
> The situation I'm in is this: I have a type hierarchy representing
> events consisting of a base class (call it Event) and a subclass for
> each event type (30+ currently). It's representation in the database
> was done with a table-per-subclass strategy.
>
> I want to write a query that fetches an event based on it's ID. The
> problem is that I don't know the event type I'm going to fetch in
> advance, and I want to avoid a 30-table join. Is there a way a
> delaying the join to a second query after the 'discrimination'?
> Something like
>
> <resultMap id="Event" class="BaseEvent">
> ...
> <result property="ts" column="timestamp/>
> <discriminator javaType="string" column="type">
> <subMap value="userLoggedIn"
> select="addUserLoggedInProps"/>
> ...
> </discriminator>
> </resultMap>
>
> ...
>
> <select id="addUserLoggedInProps" parameterClass="int"
> resultMap="userLoggedInEvent">
> select * from event, userloggedinevent
> where event.id=userloggedin.eventid and event.id=#value#
> </select>
>
> ...
> <resultMap id="userLoggedInEvent" extends="Event">
> ...
> <result property="userId" column="userid"/> <!-- specific to
> userLoggedInEvent -->
> ...
> </resultMap>
>
>
> Sorry for the lengthy post. Any help greatly appreciated...
>
> Thanks for your time,
>
> Eric
>
>