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 Zoran Avtarovski <zo...@sparecreative.com> on 2006/02/02 11:10:50 UTC
Multiple n+1
I've been trying to get a n+1 example to work without much luck and I think
that I may have misunderstood the wiki article. I have intentionally kept
the example simple so I can fully understand the concepts and then apply it
to the real world app which uses the same principles.
I have three object tables
User, which has {user_id, name, type} columns
Group, which has {group_id, name, description} columns
Category, which has {cat_id, name, description} columns
I also have two relationship tables
UserGroup, which has {user_id, group_id} columns
UserCat, which has {user_id, group_id} columns
Each User can have multiple Groups and Categories. This is represented
through a User POJO which includes two Collection properties {groups,
categories} with appropriate setters and getters.
I currently use multiple SQL calls to populate the n+1 properties, I use a
<result property="groups" column="user_id" select="getUserGroups" /> and
the same for categories. You get the Idea. Ideally, I'd really like to use a
single SQL query to improve system performance.
Can I resolve multiple n+1 relationships in a single call and if so how. I'd
appreciate any help. I'm sure that once I get my head around the concept of
n+1 selects it will be easy.
Zoran
Re: Multiple n+1
Posted by Tony Qian <da...@aol.com>.
Nils,
Have you tried groupBy by more than one key(groupBy="key1, key2)?
If you see my previous post. During the test, I turned ibatis and sql
log to debug level. I saw the queried data in result set. Somehow,
groupBy cannot pick it up and always set the list to null.
thx,
Tony
Nils Winkler wrote on 2/3/2006, 2:51 AM:
> I have something like this working. Here's roughly what I'm doing:
>
> - Create a single select using joins across the tables.
> - Make sure that each of the selected columns has a unique alias, e.g.
> "category_name"
> - Test the query in a SQL tool to make sure you get what you want. You
> should have multiple rows for each user.
> - Create result maps for User, Group and Category.
> - Link the Group and Category result maps into the User result map:
> <result property="groups" column="user_id" resultMap="userGroups" />
> - Each of the result maps should have the groupBy attribute set to its
> own primary key attribute:
>
> class User { private int userId;}
> class Group { private int groupId;}
> class Category {private int catId;}
>
> <resultMap id="user" class="User" groupBy="userId">...</resultMap>
> <resultMap id="group" class="Group" groupBy="groupId">...</resultMap>
> <resultMap id="category" class="Category"
> groupBy="catId">...</resultMap>
>
> - For testing, start with only one relationship, e.g. Group, leave out
> Category. Once this works, add Category again.
>
> The example in the Wiki is too simple IMHO, we should try to add a more
> complex one...
>
> Nils
>
> On Thu, 02 Feb 2006 21:10:50 +1100, "Zoran Avtarovski"
> <zo...@sparecreative.com> said:
> > I've been trying to get a n+1 example to work without much luck and I
> > think
> > that I may have misunderstood the wiki article. I have intentionally
> kept
> > the example simple so I can fully understand the concepts and then
> apply
> > it
> > to the real world app which uses the same principles.
> >
> > I have three object tables
> > User, which has {user_id, name, type} columns
> > Group, which has {group_id, name, description} columns
> > Category, which has {cat_id, name, description} columns
> >
> > I also have two relationship tables
> > UserGroup, which has {user_id, group_id} columns
> > UserCat, which has {user_id, group_id} columns
> >
> > Each User can have multiple Groups and Categories. This is represented
> > through a User POJO which includes two Collection properties {groups,
> > categories} with appropriate setters and getters.
> >
> > I currently use multiple SQL calls to populate the n+1 properties, I
> use
> > a
> > <result property="groups" column="user_id" select="getUserGroups"
> /> and
> > the same for categories. You get the Idea. Ideally, I'd really like to
> > use a
> > single SQL query to improve system performance.
> >
> > Can I resolve multiple n+1 relationships in a single call and if so
> how.
> > I'd
> > appreciate any help. I'm sure that once I get my head around the
> concept
> > of
> > n+1 selects it will be easy.
> >
> > Zoran
> >
> >
> --
> ==================================
> nils@nilswinkler.com
>
Re: [Abator] selectByExample not working?
Posted by Jeff Butler <je...@gmail.com>.
iBATIS and Abator support sequences directly - there's no need to alter the
generated inserts if you configure the abator_config file properly. To use,
specify something like this in your Abator configuration:
<table schema="someschema" tableName="someTable">
<generatedKey column="some_column" sqlStatement="select
sequence.nextvalfrom someSequence"
identity="false"/>
</table>
You'll need to replace all the values in the above example to the actual
values in your database.
When you run the DAO insert method, it will return the newly generated key -
so you can use the value in other queries.
Jeff Butler
On 2/6/06, Rashmi Dave <ra...@persistent.co.in> wrote:
>
> Hi Jeff,
>
>
>
> Thanks for this. It has worked.
>
>
>
> I have one more question. In my table, the primary key is to be generated
> using a sequence (I am using Oracle). Now, I have altered the Insert in the
> sql map file so that it does not take the ID from the parameter Class. I
> need the value of the sequence that gets generated for the newly inserted
> record so that I can use it to populate other related tables. Can you
> suggest a good method of doing this using iBATIS?
>
>
>
> Thanks so much
>
>
>
> Regards
>
> ~Rashmi
>
>
> ------------------------------
>
> *From:* Jeff Butler [mailto:jeffgbutler@gmail.com]
> *Sent:* Monday, February 06, 2006 7:44 PM
> *To:* user-java@ibatis.apache.org
> *Subject:* Re: [Abator] selectByExample not working?
>
>
>
> Add this line of code before calling the DAO method:
>
>
>
> roleExl.setIsActive_Indicator(RolesExample.EXAMPLE_EQUALS);
>
>
>
> HTH -
>
> Jeff Butler
>
>
>
> On 2/5/06, *Rashmi Dave* <ra...@persistent.co.in> wrote:
>
> Hi Jeff,
>
> I am using Abator generated DAO classes to fire queries against the
> tables.
>
> I would like to select those rows in a table where the ACTIVE column is
> "Y".
> For this my code reads
>
> RolesExample roleExl = new RolesExample();
> roleExl.setIsActive("Y");
>
> List roleGrps = roleDAO.selectByExample(roleExl);
>
> I would like this to generate a WHERE ACTIVE = 'Y' in the select query.
>
> However, I find that the list being returned has all the rows in the
> table.
> Not sure if I am using this right. Can you please help?
>
> Thanks in advance
>
> Cheers!
> Rashmi
>
>
>
RE: [Abator] selectByExample not working?
Posted by Rashmi Dave <ra...@persistent.co.in>.
Hi Jeff,
Thanks for this. It has worked.
I have one more question. In my table, the primary key is to be generated
using a sequence (I am using Oracle). Now, I have altered the Insert in the
sql map file so that it does not take the ID from the parameter Class. I
need the value of the sequence that gets generated for the newly inserted
record so that I can use it to populate other related tables. Can you
suggest a good method of doing this using iBATIS?
Thanks so much
Regards
~Rashmi
_____
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Monday, February 06, 2006 7:44 PM
To: user-java@ibatis.apache.org
Subject: Re: [Abator] selectByExample not working?
Add this line of code before calling the DAO method:
roleExl.setIsActive_Indicator(RolesExample.EXAMPLE_EQUALS);
HTH -
Jeff Butler
On 2/5/06, Rashmi Dave <ra...@persistent.co.in> wrote:
Hi Jeff,
I am using Abator generated DAO classes to fire queries against the tables.
I would like to select those rows in a table where the ACTIVE column is "Y".
For this my code reads
RolesExample roleExl = new RolesExample();
roleExl.setIsActive("Y");
List roleGrps = roleDAO.selectByExample(roleExl);
I would like this to generate a WHERE ACTIVE = 'Y' in the select query.
However, I find that the list being returned has all the rows in the table.
Not sure if I am using this right. Can you please help?
Thanks in advance
Cheers!
Rashmi
Re: [Abator] selectByExample not working?
Posted by Jeff Butler <je...@gmail.com>.
Add this line of code before calling the DAO method:
roleExl.setIsActive_Indicator(RolesExample.EXAMPLE_EQUALS);
HTH -
Jeff Butler
On 2/5/06, Rashmi Dave <ra...@persistent.co.in> wrote:
>
> Hi Jeff,
>
> I am using Abator generated DAO classes to fire queries against the
> tables.
>
> I would like to select those rows in a table where the ACTIVE column is
> "Y".
> For this my code reads
>
> RolesExample roleExl = new RolesExample();
> roleExl.setIsActive("Y");
>
> List roleGrps = roleDAO.selectByExample(roleExl);
>
> I would like this to generate a WHERE ACTIVE = 'Y' in the select query.
>
> However, I find that the list being returned has all the rows in the
> table.
> Not sure if I am using this right. Can you please help?
>
> Thanks in advance
>
> Cheers!
> Rashmi
>
>
[Abator] selectByExample not working?
Posted by Rashmi Dave <ra...@persistent.co.in>.
Hi Jeff,
I am using Abator generated DAO classes to fire queries against the tables.
I would like to select those rows in a table where the ACTIVE column is "Y".
For this my code reads
RolesExample roleExl = new RolesExample();
roleExl.setIsActive("Y");
List roleGrps = roleDAO.selectByExample(roleExl);
I would like this to generate a WHERE ACTIVE = 'Y' in the select query.
However, I find that the list being returned has all the rows in the table.
Not sure if I am using this right. Can you please help?
Thanks in advance
Cheers!
Rashmi
Re: Multiple n+1
Posted by Zoran Avtarovski <zo...@sparecreative.com>.
Thanks Nils,
I apreciate the pointer.
I've changed my SQL statement to read:
SELECT * FROM Users
left outer join UserGroup on Users.user_id = UserGroup.user_id
left outer join UserCat on Users.user_id = UserCat.user_id
left outer join nGroup on nGroup.group_id = UserGroup.group_id
left outer join Category on Category.cat_id = UserCat.cat_id
WHERE Users.user_id = #user_id#
Which gives a result set with all the data I need,
I didn't realise the groupBy attribute was available in the resultMap tag
for the child properties (eg Group, Category). That makes it so much easier.
Do you know if it works? I'll implement it in the morning (too tired now),
but I'm interested to know if anybody else has had any success with this
method. There whole section on n+1 is a little sparse.
Zoran
> I have something like this working. Here's roughly what I'm doing:
>
> - Create a single select using joins across the tables.
> - Make sure that each of the selected columns has a unique alias, e.g.
> "category_name"
> - Test the query in a SQL tool to make sure you get what you want. You
> should have multiple rows for each user.
> - Create result maps for User, Group and Category.
> - Link the Group and Category result maps into the User result map:
> <result property="groups" column="user_id" resultMap="userGroups" />
> - Each of the result maps should have the groupBy attribute set to its
> own primary key attribute:
>
> class User { private int userId;}
> class Group { private int groupId;}
> class Category {private int catId;}
>
> <resultMap id="user" class="User" groupBy="userId">...</resultMap>
> <resultMap id="group" class="Group" groupBy="groupId">...</resultMap>
> <resultMap id="category" class="Category"
> groupBy="catId">...</resultMap>
>
> - For testing, start with only one relationship, e.g. Group, leave out
> Category. Once this works, add Category again.
>
> The example in the Wiki is too simple IMHO, we should try to add a more
> complex one...
>
> Nils
>
> On Thu, 02 Feb 2006 21:10:50 +1100, "Zoran Avtarovski"
> <zo...@sparecreative.com> said:
>> I've been trying to get a n+1 example to work without much luck and I
>> think
>> that I may have misunderstood the wiki article. I have intentionally kept
>> the example simple so I can fully understand the concepts and then apply
>> it
>> to the real world app which uses the same principles.
>>
>> I have three object tables
>> User, which has {user_id, name, type} columns
>> Group, which has {group_id, name, description} columns
>> Category, which has {cat_id, name, description} columns
>>
>> I also have two relationship tables
>> UserGroup, which has {user_id, group_id} columns
>> UserCat, which has {user_id, group_id} columns
>>
>> Each User can have multiple Groups and Categories. This is represented
>> through a User POJO which includes two Collection properties {groups,
>> categories} with appropriate setters and getters.
>>
>> I currently use multiple SQL calls to populate the n+1 properties, I use
>> a
>> <result property="groups" column="user_id" select="getUserGroups" /> and
>> the same for categories. You get the Idea. Ideally, I'd really like to
>> use a
>> single SQL query to improve system performance.
>>
>> Can I resolve multiple n+1 relationships in a single call and if so how.
>> I'd
>> appreciate any help. I'm sure that once I get my head around the concept
>> of
>> n+1 selects it will be easy.
>>
>> Zoran
>>
>>
> --
> ==================================
> nils@nilswinkler.com
>
Re: Multiple n+1
Posted by Nils Winkler <ni...@nilswinkler.com>.
I have something like this working. Here's roughly what I'm doing:
- Create a single select using joins across the tables.
- Make sure that each of the selected columns has a unique alias, e.g.
"category_name"
- Test the query in a SQL tool to make sure you get what you want. You
should have multiple rows for each user.
- Create result maps for User, Group and Category.
- Link the Group and Category result maps into the User result map:
<result property="groups" column="user_id" resultMap="userGroups" />
- Each of the result maps should have the groupBy attribute set to its
own primary key attribute:
class User { private int userId;}
class Group { private int groupId;}
class Category {private int catId;}
<resultMap id="user" class="User" groupBy="userId">...</resultMap>
<resultMap id="group" class="Group" groupBy="groupId">...</resultMap>
<resultMap id="category" class="Category"
groupBy="catId">...</resultMap>
- For testing, start with only one relationship, e.g. Group, leave out
Category. Once this works, add Category again.
The example in the Wiki is too simple IMHO, we should try to add a more
complex one...
Nils
On Thu, 02 Feb 2006 21:10:50 +1100, "Zoran Avtarovski"
<zo...@sparecreative.com> said:
> I've been trying to get a n+1 example to work without much luck and I
> think
> that I may have misunderstood the wiki article. I have intentionally kept
> the example simple so I can fully understand the concepts and then apply
> it
> to the real world app which uses the same principles.
>
> I have three object tables
> User, which has {user_id, name, type} columns
> Group, which has {group_id, name, description} columns
> Category, which has {cat_id, name, description} columns
>
> I also have two relationship tables
> UserGroup, which has {user_id, group_id} columns
> UserCat, which has {user_id, group_id} columns
>
> Each User can have multiple Groups and Categories. This is represented
> through a User POJO which includes two Collection properties {groups,
> categories} with appropriate setters and getters.
>
> I currently use multiple SQL calls to populate the n+1 properties, I use
> a
> <result property="groups" column="user_id" select="getUserGroups" /> and
> the same for categories. You get the Idea. Ideally, I'd really like to
> use a
> single SQL query to improve system performance.
>
> Can I resolve multiple n+1 relationships in a single call and if so how.
> I'd
> appreciate any help. I'm sure that once I get my head around the concept
> of
> n+1 selects it will be easy.
>
> Zoran
>
>
--
==================================
nils@nilswinkler.com