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 Rick <ri...@arc-mind.com> on 2006/07/05 21:57:07 UTC
It worked (kinda) RE: One solution to self-join, new feature request.... RE: Employee self-join one to many relationship (iBatis)
I tried what you sent. It worked (sort of worked).
It did not work all the way. The issues is that it builds one level of the
tree and duplicates the employees.
I want a tree like this:
A----------> B --------> D
| |--> E
|
|-----> C
I got this:
A----------> B
|-----> C
B----------> D
|-----> E
C
D
E
Another way to put it is:
Employee A has direct reports B and C
Employee B has direct reports D and E
If you look at Employee A's employees you will see B and C, but A.B will not
have D and E.
If you look at Employee B's employees you will see D and E.
The issue is that Employee A's B (A.B) is a different object than B. A.B has
not children but B does.
B is not the same object as A.B.
The query returns
A (null boss)
B (boss A)
C (boss A)
D (boss B)
E (boss B)
I want to turn the query into:
A----------> B --------> D
| |--> E
|
|-----> C
Here is my map and my query.
<resultMap id="boss" class="qcom.cas.mysourcej.poc.model.Employee"
groupBy="emplid">
<result property="emplid" column="bossId" />
<result property="name" column="bossName" />
<result property="directReports"
resultMap="employee.employeeShort"/>
</resultMap>
<resultMap id="employeeShort"
class="qcom.cas.mysourcej.poc.model.Employee">
<result property="emplid" column="emplid" />
<result property="name" column="name" />
<result property="bossId" column="bossId"/>
</resultMap>
<select id="getMyOrg" resultMap="boss">
<![CDATA[
select
employee.emplid as emplid,
employee.name as name,
employee.N_SUPERVISOR as bossId,
boss.name as bossName
from ps_employees employee
join ps_employees boss
on employee.N_SUPERVISOR = boss.emplid
start with employee.emplid = #supervisorId#
connect by employee.n_supervisor = prior employee.emplid
order by level, name
]]>
</select>
_____
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Wednesday, July 05, 2006 9:54 AM
To: user-java@ibatis.apache.org
Subject: Re: One solution to self-join, new feature request.... RE: Employee
self-join one to many relationship (iBatis)
Hi Rick,
A self join can be accomplished through groupBy and column renaming. Here's
a simple example I just coded up for a test:
Table Definition:
create table employees(id int not null, bossId int, name varchar(50))
Class Definition:
public class Employee {
private Integer id;
private String name;
private List employees;
// getters/setters
}
iBATIS stuff:
<resultMap id="boss" class="test.Employee" groupBy="id">
<result property="id" column="bossId" />
<result property="name" column="bossName" />
<result property="employees" resultMap="employee"/>
</resultMap>
<resultMap id="employee" class="test.Employee">
<result property="id" column="employeeId" />
<result property="name" column="employeeName" />
</resultMap>
<select id="selectEmployees" resultMap="boss">
select a.id as employeedId, a.name as employeeName, b.id as bossId, b.name
as bossName
from employees a join employees b
on a.bossId = b.id
where a.bossId is not null
</select>
This returns all the data in one pass, so no N+1 problem.
Jeff Butler
On 7/5/06, Rick <ri...@arc-mind.com> wrote:
Someone sent me a solution to the self-join that I asked about earlier as
follows:
<resultMap id="employee" class=" sample01.Employee">
<result property="employeeId" column="employee_id"/>
<result property="bossId" column="boss_id" nullValue="-1"
select="getBoss"/>
<result property="firstName" column="first_name"/>
<result property="lastName" column="last_name"/>
</resultMap>
<statement id="getEmployees" resultMap="employee">
select * from Employee
</statement>
<statement id="getBoss" resultMap="employee">
select * from Employee where employee_id=#value#
</statement>
This is very helpful in understanding how iBatis works. I think I will stick
with the other approach b/c the code above would perform 1 query for each
boss (N+1). The approach I used (with the helper method) only hits the db
once. It gets all the data it needs in one swipe and then builds the
hierarchy.
Again, my former approach gets all of the employees in the tree (using
connect-by) and then constructs the hierarchy.
The above way is much cleaner in that it does not require custom Java code
to build the list. Its runtime performance would be problematic especially
since I am replacing code that does not have an N+1 issue (custom VB/ASP
app).
I'd like to have something like this (as a new feature in iBatis):
<resultMap id="employee" class="sample01.Employee">
<result property="employeeId" column="employee_id"/>
<result property="boss" fk_property="bossId"
adder-method="addDirectReport" self-join="true"
pk_property="emplId"/>
<result property="firstName" column="first_name"/>
<result property="bossId" column="n_supervisor"/>
<result property="lastName" column="last_name"/>
</resultMap>
<statement id="getEmployees" resultMap="employee">
select * from Employee e
start with n_supervisor = #supervisorId#
connect by n_supervisor = prior emplid
</statement>
See the:
<result property="boss" fk_property="bossId"
adder-method="addDirectReport" self-join="true"
pk_property="emplId"/>
Basically the data for the hierarchy is already retrieved from the db. Why
hit it a second time?
Thoughts?
-----Original Message-----
From: Larry Meadors [mailto:lmeadors@apache.org]
Sent: Monday, July 03, 2006 3:54 PM
To: user-java@ibatis.apache.org
Subject: Re: Employee self-join one to many relationship (iBatis)
Hmm, I generally don't build my object model that way, so my
experience with groupBy is sort of limited, sorry. ;-)
If you can't make groupBy work, I'd look at doing this with a
rowhandler. You'd get all the data in one hit to the database, and
then step through the results building the object graph the way you
want it.
Larry
On 7/3/06, Rick <ri...@arc-mind.com> wrote:
> Bingo!
>
> Yep. Employee has a directReports list which is a list of Employees.
>
>
>
> -----Original Message-----
> From: Larry Meadors [mailto: <ma...@apache.org>
lmeadors@apache.org]
> Sent: Monday, July 03, 2006 3:19 PM
> To: user-java@ibatis.apache.org
> Subject: Re: Employee self-join one to many relationship (iBatis)
>
> Hey Rick, I am not 100% sure I understand what you are trying to do,
> but let me try to restate it and see..
>
> Do you want a list of employees, each one with another list of
> employees (direct reports), and a list of contacts on each one - both
> the main employee list and the child employees?
>
> Larry
>
>
> On 7/3/06, Rick <ri...@arc-mind.com> wrote:
> >
> >
> >
> >
> > I just want to use iBatis in the cleanest way possible. I have a
> workaround
> > but wonder if iBatis support self-joined one to many relationships.
> >
> >
> >
> > Let me explain..
> >
> >
> >
> >
> >
> > I have a query that returns employees.
> >
> >
> >
> > Employees have Boss's who are Employees.
> >
> >
> >
> > Currently I have Employees have Contacts who have Phone Numbers.
> >
> >
> >
> > (Where Employee, Contact and Phone number are objects.)
> >
> >
> >
> > I'd like to have Employees have Employees (directReports) and Employees
> have
> > Contacts who have Phone Numbers.
> >
> >
> >
> > Does iBatis support a self join?
> >
> >
> >
> >
> >
> > Here is my current mapping and how I get around this lack of support (or
> is
> > this support lacking)...
> >
> >
> >
> > , i.e.,
> >
> >
> >
> > <resultMap id="employee"
> > class="qcom.cas.mysourcej.poc.model.Employee"
> > groupBy="emplid">
> >
> > <result property="emplid" column="emplid" />
> >
> > <result property="name" column="name" />
> >
> > <result property="contacts" resultMap="employee.contacts"/>
> >
> > <result property="bossId" column="bossId"/>
> >
> > </resultMap>
> >
> >
> >
> > <resultMap id="contacts"
> > class="qcom.cas.mysourcej.poc.model.Contact">
> >
> > <result property="name" column="contact_name"/>
> >
> > <result property="primaryContact" column="primary_contact"
> >
> >
> >
typeHandler="qcom.cas.commons.ibatis.typehandler.StringBooleanTypeHandler"
> > />
> >
> > <result property="relationship" column="relation"/>
> >
> > <result property="phoneNumber1.number" column="phone1"/>
> >
> > <result property="phoneNumber2.number" column="phone2"/>
> >
> > <result property="phoneNumber3.number" column="phone3"/>
> >
> > <result property="phoneNumber4.number" column="phone4"/>
> >
> > <result property="phoneNumber1.areaCode"
column="areaCode1"/>
> >
> > <result property="phoneNumber2.areaCode"
column="areaCode2"/>
> >
> > <result property="phoneNumber3.areaCode"
column="areaCode3"/>
> >
> > <result property=" phoneNumber4.areaCode"
column="areaCode4"/>
> >
> > </resultMap>
> >
> >
> >
> >
> >
> >
> >
> > <select
> > id="getEmployeeEmergencyContactsUsingDirectSort"
> > resultMap="employee">
> >
> >
> >
> >
> >
> > If so, any pointers or references where I can learn to do this..
> >
> >
> >
> > Currently I have a method that turns the list of employees into a
> hierarchy
> > of Employees as follows:
> >
> >
> >
> > /**
> >
> > * Turns a list of employees into a hierarchy of employees.
> >
> > * @param employees
> >
> > * @param removeChild
> >
> > */
> >
> > private void buildEmployeeObjectHierarchy(List
> > employees, boolean removeChild) {
> >
> > Map employeeIDMap = new HashMap(employees.size());
> >
> >
> >
> > for (Iterator iter = employees.iterator(); iter.hasNext();)
{
> >
> > Employee employee = (Employee) iter.next();
> >
> > employeeIDMap.put(employee.getEmplid(), employee);
> >
> > }
> >
> >
> >
> > for (Iterator iter = employees.iterator (); iter.hasNext();)
{
> >
> > Employee employee = (Employee) iter.next();
> >
> > Employee boss = (Employee)
> > employeeIDMap.get(employee.getBossId ());
> >
> > if (boss != null) {
> >
> > boss.addEmployee(employee);
> >
> > iter.remove(); //If a boss is found, remove this
> > employee from the list.
> >
> > }
> >
> > }
> >
> > }
> >
> >
> >
> > The above is a bit expensive and I'd like to get it out of my code if
> > possible. If not, no worries. I just want to use iBatis in the cleanest
> way
> > possible.
>
>
>
RE: No tree support in iBatis RE: It worked (kinda) RE: One solution to self-join, new feature request.... RE: Employee self-join one to many relationship (iBatis)
Posted by Rick <ri...@arc-mind.com>.
I'll take a look.
_____
From: Eric T. Blue [mailto:ericblue76@gmail.com]
Sent: Wednesday, July 05, 2006 1:56 PM
To: user-java@ibatis.apache.org
Subject: Re: No tree support in iBatis RE: It worked (kinda) RE: One
solution to self-join, new feature request.... RE: Employee self-join one to
many relationship (iBatis)
Rick,
For what it's worth, I also tried building a tree a while ago using a combo
of SQL and abstracting some code in the DAO (No RowHandler or anything like
that). Because some of these trees were going to be potentially very large
(a couple hundred thousand items), and I was not using a cache-model I opted
to do this in code. I basically execute a single query to return a List of
objects, each containing an id and parent id property. Then I used the
Jenkov TreeTag library (
http://java-source.net/open-source/jsp-tag-libraries/prize-tags) to iterate
over the collection and build the tree. This is primarily used for building
trees in JSP, but you can use the public API just fine.
P.S. It looks like the jenkov.com site is down right now. But if you're
interested in some sample code I'd be more than happy to mail off-list.
On 7/5/06, Larry Meadors <lm...@apache.org> wrote:
> It seems like dealing with trees is a fairly common case, so this could be
> generalized and put into iBatis.
IMO, I think you should add it so we have a reminder of it...even if
we never implement it.
I wonder if a RowHandler implementation would be faster. I guess if
you are bored you could try that, too. ;-)
Larry
Re: No tree support in iBatis RE: It worked (kinda) RE: One solution to self-join, new feature request.... RE: Employee self-join one to many relationship (iBatis)
Posted by "Eric T. Blue" <er...@gmail.com>.
Rick,
For what it's worth, I also tried building a tree a while ago using a combo
of SQL and abstracting some code in the DAO (No RowHandler or anything like
that). Because some of these trees were going to be potentially very large
(a couple hundred thousand items), and I was not using a cache-model I opted
to do this in code. I basically execute a single query to return a List of
objects, each containing an id and parent id property. Then I used the
Jenkov TreeTag library (
http://java-source.net/open-source/jsp-tag-libraries/prize-tags) to iterate
over the collection and build the tree. This is primarily used for building
trees in JSP, but you can use the public API just fine.
P.S. It looks like the jenkov.com site is down right now. But if you're
interested in some sample code I'd be more than happy to mail off-list.
On 7/5/06, Larry Meadors <lm...@apache.org> wrote:
>
> > It seems like dealing with trees is a fairly common case, so this could
> be
> > generalized and put into iBatis.
>
> IMO, I think you should add it so we have a reminder of it...even if
> we never implement it.
>
> I wonder if a RowHandler implementation would be faster. I guess if
> you are bored you could try that, too. ;-)
>
> Larry
>
RE: No tree support in iBatis RE: It worked (kinda) RE: One solution to self-join, new feature request.... RE: Employee self-join one to many relationship (iBatis)
Posted by Rick <ri...@arc-mind.com>.
Maybe I'll try the RowHandler first then if that does not work... I'll add
the JIRA request.
I am in JIRA request debt. I found a possible bug the HtmlTree.java
(myfaces) (with a solution) and need to add that as well.
-----Original Message-----
From: Larry Meadors [mailto:lmeadors@apache.org]
Sent: Wednesday, July 05, 2006 1:25 PM
To: user-java@ibatis.apache.org
Subject: Re: No tree support in iBatis RE: It worked (kinda) RE: One
solution to self-join, new feature request.... RE: Employee self-join one to
many relationship (iBatis)
> It seems like dealing with trees is a fairly common case, so this could be
> generalized and put into iBatis.
IMO, I think you should add it so we have a reminder of it...even if
we never implement it.
I wonder if a RowHandler implementation would be faster. I guess if
you are bored you could try that, too. ;-)
Larry
Re: No tree support in iBatis RE: It worked (kinda) RE: One solution to self-join, new feature request.... RE: Employee self-join one to many relationship (iBatis)
Posted by Larry Meadors <lm...@apache.org>.
> It seems like dealing with trees is a fairly common case, so this could be
> generalized and put into iBatis.
IMO, I think you should add it so we have a reminder of it...even if
we never implement it.
I wonder if a RowHandler implementation would be faster. I guess if
you are bored you could try that, too. ;-)
Larry
Re: It worked (kinda) RE: One solution to self-join, new feature request.... RE: Employee self-join one to many relationship (iBatis)
Posted by Clinton Begin <cl...@gmail.com>.
Another way to achieve B == A.B, is to enable a cache model for the
statements involved. That way, the first time B is looked up, it will be
cached and A.B will be set to the same instance. Of course, this can be
hit-or-miss depending on the cache implementation you use, and it will
likely only work for that single query (instances across queries may be
differnet).
That said, it has always been my opinion that your application logic should
never depend on instance equality. That is, everything should work just
fine either way. B == A.B should only be considered a memory optimization
with possible performance benefits. But really, if there's enough in memory
for that to be a problem, perhaps they shouldn't remain in memory for long.
Barring all of that....going forward in a future version of iBATIS I would
like to implement an always-on session level cache for such things to make
the resulting object graphs of such mappings more consistent and
predictable.
Cheers,
Clinton
On 7/5/06, Rick <ri...@arc-mind.com> wrote:
>
> I tried what you sent. It worked (sort of worked).
>
>
>
> It did not work all the way. The issues is that it builds one level of the
> tree and duplicates the employees.
>
>
>
> I want a tree like this:
>
>
>
> A----------> B --------> D
>
> | |--> E
>
> |
>
> |-----> C
>
>
>
>
>
> I got this:
>
>
>
> A----------> B
>
> |-----> C
>
> B----------> D
>
> |-----> E
>
> C
>
> D
>
> E
>
>
>
>
>
> Another way to put it is:
>
>
>
> Employee A has direct reports B and C
>
> Employee B has direct reports D and E
>
>
>
> If you look at Employee A's employees you will see B and C, but A.B will
> not have D and E.
>
> If you look at Employee B's employees you will see D and E.
>
>
>
> The issue is that Employee A's B (A.B) is a different object than B. A.Bhas not children but B does.
>
>
>
> B is not the same object as A.B.
>
>
>
> The query returns
>
> A (null boss)
>
> B (boss A)
>
> C (boss A)
>
> D (boss B)
>
> E (boss B)
>
>
>
> I want to turn the query into:
>
>
>
> A----------> B --------> D
>
> | |--> E
>
> |
>
> |-----> C
>
>
>
>
>
> Here is my map and my query.
>
>
>
> <resultMap id="boss" class="qcom.cas.mysourcej.poc.model.Employee"
> groupBy="emplid">
>
> <result property="emplid" column="bossId" />
>
> <result property="name" column="bossName" />
>
> <result property="directReports" resultMap="
> employee.employeeShort"/>
>
> </resultMap>
>
>
>
> <resultMap id="employeeShort" class="
> qcom.cas.mysourcej.poc.model.Employee">
>
> <result property="emplid" column="emplid" />
>
> <result property="name" column="name" />
>
> <result property="bossId" column="bossId"/>
>
> </resultMap>
>
>
>
> <select id="getMyOrg" resultMap="boss">
>
> <![CDATA[
>
> select
>
> employee.emplid as emplid,
>
> employee.name as name,
>
> employee.N_SUPERVISOR as bossId,
>
> boss.name as bossName
>
> from ps_employees employee
>
> join ps_employees boss
>
> on employee.N_SUPERVISOR = boss.emplid
>
> start with employee.emplid = #supervisorId#
>
> connect by employee.n_supervisor = prior employee.emplid
>
> order by level, name
>
> ]]>
>
> </select>
>
>
>
>
> ------------------------------
>
> *From:* Jeff Butler [mailto:jeffgbutler@gmail.com]
> *Sent:* Wednesday, July 05, 2006 9:54 AM
> *To:* user-java@ibatis.apache.org
> *Subject:* Re: One solution to self-join, new feature request.... RE:
> Employee self-join one to many relationship (iBatis)
>
>
>
> Hi Rick,
>
>
>
> A self join can be accomplished through groupBy and column renaming.
> Here's a simple example I just coded up for a test:
>
>
>
> Table Definition:
>
>
>
> create table employees(id int not null, bossId int, name varchar(50))
>
>
>
> Class Definition:
>
>
>
> public class Employee {
>
> private Integer id;
>
> private String name;
>
> private List employees;
>
> // getters/setters
>
> }
>
>
>
>
>
> iBATIS stuff:
>
> <resultMap id="boss" class="test.Employee" groupBy="id">
> <result property="id" column="bossId" />
> <result property="name" column="bossName" />
> <result property="employees" resultMap="employee"/>
> </resultMap>
>
> <resultMap id="employee" class="test.Employee">
> <result property="id" column="employeeId" />
> <result property="name" column="employeeName" />
> </resultMap>
>
> <select id="selectEmployees" resultMap="boss">
> select a.id as employeedId, a.name as employeeName, b.id as bossId,
> b.name as bossName
> from employees a join employees b
> on a.bossId = b.id
> where a.bossId is not null
> </select>
>
> This returns all the data in one pass, so no N+1 problem.
>
>
>
> Jeff Butler
>
>
>
>
> On 7/5/06, *Rick* <ri...@arc-mind.com> wrote:
>
> Someone sent me a solution to the self-join that I asked about earlier as
> follows:
>
> <resultMap id="employee" class=" sample01.Employee">
> <result property="employeeId" column="employee_id"/>
> <result property="bossId" column="boss_id" nullValue="-1"
> select="getBoss"/>
> <result property="firstName" column="first_name"/>
> <result property="lastName" column="last_name"/>
> </resultMap>
> <statement id="getEmployees" resultMap="employee">
> select * from Employee
> </statement>
> <statement id="getBoss" resultMap="employee">
> select * from Employee where employee_id=#value#
> </statement>
>
>
> This is very helpful in understanding how iBatis works. I think I will
> stick
> with the other approach b/c the code above would perform 1 query for each
> boss (N+1). The approach I used (with the helper method) only hits the db
> once. It gets all the data it needs in one swipe and then builds the
> hierarchy.
>
>
> Again, my former approach gets all of the employees in the tree (using
> connect-by) and then constructs the hierarchy.
>
>
> The above way is much cleaner in that it does not require custom Java code
> to build the list. Its runtime performance would be problematic especially
> since I am replacing code that does not have an N+1 issue (custom VB/ASP
> app).
>
> I'd like to have something like this (as a new feature in iBatis):
>
> <resultMap id="employee" class="sample01.Employee">
> <result property="employeeId" column="employee_id"/>
> <result property="boss" fk_property="bossId"
> adder-method="addDirectReport" self-join="true"
> pk_property="emplId"/>
> <result property="firstName" column="first_name"/>
> <result property="bossId" column="n_supervisor"/>
> <result property="lastName" column="last_name"/>
> </resultMap>
> <statement id="getEmployees" resultMap="employee">
> select * from Employee e
> start with n_supervisor = #supervisorId#
> connect by n_supervisor = prior emplid
> </statement>
>
>
> See the:
>
> <result property="boss" fk_property="bossId"
> adder-method="addDirectReport" self-join="true"
> pk_property="emplId"/>
>
> Basically the data for the hierarchy is already retrieved from the db. Why
> hit it a second time?
>
> Thoughts?
>
>
> -----Original Message-----
> From: Larry Meadors [mailto:lmeadors@apache.org]
> Sent: Monday, July 03, 2006 3:54 PM
> To: user-java@ibatis.apache.org
> Subject: Re: Employee self-join one to many relationship (iBatis)
>
> Hmm, I generally don't build my object model that way, so my
> experience with groupBy is sort of limited, sorry. ;-)
>
> If you can't make groupBy work, I'd look at doing this with a
> rowhandler. You'd get all the data in one hit to the database, and
> then step through the results building the object graph the way you
> want it.
>
> Larry
>
>
> On 7/3/06, Rick <ri...@arc-mind.com> wrote:
> > Bingo!
> >
> > Yep. Employee has a directReports list which is a list of Employees.
> >
> >
> >
> > -----Original Message-----
> > From: Larry Meadors [mailto: lmeadors@apache.org]
> > Sent: Monday, July 03, 2006 3:19 PM
> > To: user-java@ibatis.apache.org
> > Subject: Re: Employee self-join one to many relationship (iBatis)
> >
> > Hey Rick, I am not 100% sure I understand what you are trying to do,
> > but let me try to restate it and see..
> >
> > Do you want a list of employees, each one with another list of
> > employees (direct reports), and a list of contacts on each one - both
> > the main employee list and the child employees?
> >
> > Larry
> >
> >
> > On 7/3/06, Rick <ri...@arc-mind.com> wrote:
> > >
> > >
> > >
> > >
> > > I just want to use iBatis in the cleanest way possible. I have a
> > workaround
> > > but wonder if iBatis support self-joined one to many relationships.
> > >
> > >
> > >
> > > Let me explain..
> > >
> > >
> > >
> > >
> > >
> > > I have a query that returns employees.
> > >
> > >
> > >
> > > Employees have Boss's who are Employees.
> > >
> > >
> > >
> > > Currently I have Employees have Contacts who have Phone Numbers.
> > >
> > >
> > >
> > > (Where Employee, Contact and Phone number are objects.)
> > >
> > >
> > >
> > > I'd like to have Employees have Employees (directReports) and
> Employees
> > have
> > > Contacts who have Phone Numbers.
> > >
> > >
> > >
> > > Does iBatis support a self join?
> > >
> > >
> > >
> > >
> > >
> > > Here is my current mapping and how I get around this lack of support
> (or
> > is
> > > this support lacking)...
> > >
> > >
> > >
> > > , i.e.,
> > >
> > >
> > >
> > > <resultMap id="employee"
> > > class="qcom.cas.mysourcej.poc.model.Employee"
> > > groupBy="emplid">
> > >
> > > <result property="emplid" column="emplid" />
> > >
> > > <result property="name" column="name" />
> > >
> > > <result property="contacts" resultMap="employee.contacts
> "/>
> > >
> > > <result property="bossId" column="bossId"/>
> > >
> > > </resultMap>
> > >
> > >
> > >
> > > <resultMap id="contacts"
> > > class="qcom.cas.mysourcej.poc.model.Contact">
> > >
> > > <result property="name" column="contact_name"/>
> > >
> > > <result property="primaryContact" column="primary_contact"
>
> > >
> > >
> > >
> typeHandler="qcom.cas.commons.ibatis.typehandler.StringBooleanTypeHandler"
> > > />
> > >
> > > <result property="relationship" column="relation"/>
> > >
> > > <result property="phoneNumber1.number" column="phone1"/>
> > >
> > > <result property="phoneNumber2.number" column="phone2"/>
> > >
> > > <result property="phoneNumber3.number" column="phone3"/>
> > >
> > > <result property="phoneNumber4.number" column="phone4"/>
> > >
> > > <result property="phoneNumber1.areaCode"
> column="areaCode1"/>
> > >
> > > <result property="phoneNumber2.areaCode"
> column="areaCode2"/>
> > >
> > > <result property="phoneNumber3.areaCode"
> column="areaCode3"/>
> > >
> > > <result property=" phoneNumber4.areaCode"
> column="areaCode4"/>
> > >
> > > </resultMap>
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > <select
> > > id="getEmployeeEmergencyContactsUsingDirectSort"
> > > resultMap="employee">
> > >
> > >
> > >
> > >
> > >
> > > If so, any pointers or references where I can learn to do this..
> > >
> > >
> > >
> > > Currently I have a method that turns the list of employees into a
> > hierarchy
> > > of Employees as follows:
> > >
> > >
> > >
> > > /**
> > >
> > > * Turns a list of employees into a hierarchy of employees.
> > >
> > > * @param employees
> > >
> > > * @param removeChild
> > >
> > > */
> > >
> > > private void buildEmployeeObjectHierarchy(List
> > > employees, boolean removeChild) {
> > >
> > > Map employeeIDMap = new HashMap(employees.size());
> > >
> > >
> > >
> > > for (Iterator iter = employees.iterator(); iter.hasNext
> ();)
> {
> > >
> > > Employee employee = (Employee) iter.next();
> > >
> > > employeeIDMap.put(employee.getEmplid(), employee);
> > >
> > > }
> > >
> > >
> > >
> > > for (Iterator iter = employees.iterator (); iter.hasNext
> ();)
> {
> > >
> > > Employee employee = (Employee) iter.next();
> > >
> > > Employee boss = (Employee)
> > > employeeIDMap.get(employee.getBossId ());
> > >
> > > if (boss != null) {
> > >
> > > boss.addEmployee(employee);
> > >
> > > iter.remove(); //If a boss is found, remove
> this
> > > employee from the list.
> > >
> > > }
> > >
> > > }
> > >
> > > }
> > >
> > >
> > >
> > > The above is a bit expensive and I'd like to get it out of my code if
> > > possible. If not, no worries. I just want to use iBatis in the
> cleanest
> > way
> > > possible.
> >
> >
> >
>
>
>
No tree support in iBatis RE: It worked (kinda) RE: One solution to self-join, new feature request.... RE: Employee self-join one to many relationship (iBatis)
Posted by Rick <ri...@arc-mind.com>.
BTW I am not disappointed.
A -- I learned a lot of new things about iBatis (the journey was more
important than the goal)
B -- I already had a solution that worked; I just wanted to make sure it was
the best solution (since I am new to iBatis)
I am doing a POC project and what to use the tools in the best ways possible
since that usage could quite possibly be mimicked.
Thanks for all of the input. It has been really helpful.
Here is my solution to this problem of building a tree out of a list
returned from iBatis:
Here is what builds a tree of employees:
<resultMap id="employeeShort"
class="qcom.cas.mysourcej.poc.model.Employee">
<result property="emplid" column="emplid" />
<result property="name" column="name" />
<result property="bossId" column="bossId"/>
</resultMap>
<select id="getMyOrg" resultMap="employeeShort">
<![CDATA[
select
employee.emplid as emplid,
employee.name as name,
employee.N_SUPERVISOR as bossId,
boss.name as bossName
from ps_employees employee
join ps_employees boss
on employee.N_SUPERVISOR = boss.emplid
start with employee.emplid = #supervisorId#
connect by employee.n_supervisor = prior employee.emplid
order by level, name
]]>
</select>
Then one little helper method to take the list returned above and turn it
into a tree:
/**
* Turns a list of employees into a hierarchy of employees.
* @param employees
* @param removeChild
*/
private void buildEmployeeObjectHierarchy(List employees,
boolean removeChild) {
Map employeeIDMap = new HashMap(employees.size());
/* Create a map of employees to employee
ids */
for (Iterator iter = employees.iterator();
iter.hasNext();) {
Employee employee = (Employee)
iter.next();
employeeIDMap.put(employee.getEmplid(),
employee);
}
/* For each employee, check to see if boss is in
map, if it is
* then add the employee as a direct report of the
boss. */
for (Iterator iter = employees.iterator();
iter.hasNext();) {
Employee employee = (Employee)
iter.next();
Employee boss = (Employee)
employeeIDMap.get(employee.getBossId());
if (boss != null) {
boss.addEmployee(employee);
if (removeChild) {
iter.remove();
//If a boss is found, remove this employee from the list.
}
}
}
}
It seems like dealing with trees is a fairly common case, so this could be
generalized and put into iBatis.
Thoughts?
Should I bother adding this to JIRA as an enhancement request or is it too
far out of scope of iBatis vision?
_____
From: Rick [mailto:ricks_mailinglists@arc-mind.com]
Sent: Wednesday, July 05, 2006 12:57 PM
To: user-java@ibatis.apache.org
Subject: It worked (kinda) RE: One solution to self-join, new feature
request.... RE: Employee self-join one to many relationship (iBatis)
I tried what you sent. It worked (sort of worked).
It did not work all the way. The issues is that it builds one level of the
tree and duplicates the employees.
I want a tree like this:
A----------> B --------> D
| |--> E
|
|-----> C
I got this:
A----------> B
|-----> C
B----------> D
|-----> E
C
D
E
Another way to put it is:
Employee A has direct reports B and C
Employee B has direct reports D and E
If you look at Employee A's employees you will see B and C, but A.B will not
have D and E.
If you look at Employee B's employees you will see D and E.
The issue is that Employee A's B (A.B) is a different object than B. A.B has
not children but B does.
B is not the same object as A.B.
The query returns
A (null boss)
B (boss A)
C (boss A)
D (boss B)
E (boss B)
I want to turn the query into:
A----------> B --------> D
| |--> E
|
|-----> C
Here is my map and my query.
<resultMap id="boss" class="qcom.cas.mysourcej.poc.model.Employee"
groupBy="emplid">
<result property="emplid" column="bossId" />
<result property="name" column="bossName" />
<result property="directReports"
resultMap="employee.employeeShort"/>
</resultMap>
<resultMap id="employeeShort"
class="qcom.cas.mysourcej.poc.model.Employee">
<result property="emplid" column="emplid" />
<result property="name" column="name" />
<result property="bossId" column="bossId"/>
</resultMap>
<select id="getMyOrg" resultMap="boss">
<![CDATA[
select
employee.emplid as emplid,
employee.name as name,
employee.N_SUPERVISOR as bossId,
boss.name as bossName
from ps_employees employee
join ps_employees boss
on employee.N_SUPERVISOR = boss.emplid
start with employee.emplid = #supervisorId#
connect by employee.n_supervisor = prior employee.emplid
order by level, name
]]>
</select>
_____
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Wednesday, July 05, 2006 9:54 AM
To: user-java@ibatis.apache.org
Subject: Re: One solution to self-join, new feature request.... RE: Employee
self-join one to many relationship (iBatis)
Hi Rick,
A self join can be accomplished through groupBy and column renaming. Here's
a simple example I just coded up for a test:
Table Definition:
create table employees(id int not null, bossId int, name varchar(50))
Class Definition:
public class Employee {
private Integer id;
private String name;
private List employees;
// getters/setters
}
iBATIS stuff:
<resultMap id="boss" class="test.Employee" groupBy="id">
<result property="id" column="bossId" />
<result property="name" column="bossName" />
<result property="employees" resultMap="employee"/>
</resultMap>
<resultMap id="employee" class="test.Employee">
<result property="id" column="employeeId" />
<result property="name" column="employeeName" />
</resultMap>
<select id="selectEmployees" resultMap="boss">
select a.id as employeedId, a.name as employeeName, b.id as bossId, b.name
as bossName
from employees a join employees b
on a.bossId = b.id
where a.bossId is not null
</select>
This returns all the data in one pass, so no N+1 problem.
Jeff Butler
On 7/5/06, Rick <ri...@arc-mind.com> wrote:
Someone sent me a solution to the self-join that I asked about earlier as
follows:
<resultMap id="employee" class=" sample01.Employee">
<result property="employeeId" column="employee_id"/>
<result property="bossId" column="boss_id" nullValue="-1"
select="getBoss"/>
<result property="firstName" column="first_name"/>
<result property="lastName" column="last_name"/>
</resultMap>
<statement id="getEmployees" resultMap="employee">
select * from Employee
</statement>
<statement id="getBoss" resultMap="employee">
select * from Employee where employee_id=#value#
</statement>
This is very helpful in understanding how iBatis works. I think I will stick
with the other approach b/c the code above would perform 1 query for each
boss (N+1). The approach I used (with the helper method) only hits the db
once. It gets all the data it needs in one swipe and then builds the
hierarchy.
Again, my former approach gets all of the employees in the tree (using
connect-by) and then constructs the hierarchy.
The above way is much cleaner in that it does not require custom Java code
to build the list. Its runtime performance would be problematic especially
since I am replacing code that does not have an N+1 issue (custom VB/ASP
app).
I'd like to have something like this (as a new feature in iBatis):
<resultMap id="employee" class="sample01.Employee">
<result property="employeeId" column="employee_id"/>
<result property="boss" fk_property="bossId"
adder-method="addDirectReport" self-join="true"
pk_property="emplId"/>
<result property="firstName" column="first_name"/>
<result property="bossId" column="n_supervisor"/>
<result property="lastName" column="last_name"/>
</resultMap>
<statement id="getEmployees" resultMap="employee">
select * from Employee e
start with n_supervisor = #supervisorId#
connect by n_supervisor = prior emplid
</statement>
See the:
<result property="boss" fk_property="bossId"
adder-method="addDirectReport" self-join="true"
pk_property="emplId"/>
Basically the data for the hierarchy is already retrieved from the db. Why
hit it a second time?
Thoughts?
-----Original Message-----
From: Larry Meadors [mailto:lmeadors@apache.org]
Sent: Monday, July 03, 2006 3:54 PM
To: user-java@ibatis.apache.org
Subject: Re: Employee self-join one to many relationship (iBatis)
Hmm, I generally don't build my object model that way, so my
experience with groupBy is sort of limited, sorry. ;-)
If you can't make groupBy work, I'd look at doing this with a
rowhandler. You'd get all the data in one hit to the database, and
then step through the results building the object graph the way you
want it.
Larry
On 7/3/06, Rick <ri...@arc-mind.com> wrote:
> Bingo!
>
> Yep. Employee has a directReports list which is a list of Employees.
>
>
>
> -----Original Message-----
> From: Larry Meadors [mailto: lmeadors@apache.org
<ma...@apache.org> ]
> Sent: Monday, July 03, 2006 3:19 PM
> To: user-java@ibatis.apache.org
> Subject: Re: Employee self-join one to many relationship (iBatis)
>
> Hey Rick, I am not 100% sure I understand what you are trying to do,
> but let me try to restate it and see..
>
> Do you want a list of employees, each one with another list of
> employees (direct reports), and a list of contacts on each one - both
> the main employee list and the child employees?
>
> Larry
>
>
> On 7/3/06, Rick <ri...@arc-mind.com> wrote:
> >
> >
> >
> >
> > I just want to use iBatis in the cleanest way possible. I have a
> workaround
> > but wonder if iBatis support self-joined one to many relationships.
> >
> >
> >
> > Let me explain..
> >
> >
> >
> >
> >
> > I have a query that returns employees.
> >
> >
> >
> > Employees have Boss's who are Employees.
> >
> >
> >
> > Currently I have Employees have Contacts who have Phone Numbers.
> >
> >
> >
> > (Where Employee, Contact and Phone number are objects.)
> >
> >
> >
> > I'd like to have Employees have Employees (directReports) and Employees
> have
> > Contacts who have Phone Numbers.
> >
> >
> >
> > Does iBatis support a self join?
> >
> >
> >
> >
> >
> > Here is my current mapping and how I get around this lack of support (or
> is
> > this support lacking)...
> >
> >
> >
> > , i.e.,
> >
> >
> >
> > <resultMap id="employee"
> > class="qcom.cas.mysourcej.poc.model.Employee"
> > groupBy="emplid">
> >
> > <result property="emplid" column="emplid" />
> >
> > <result property="name" column="name" />
> >
> > <result property="contacts" resultMap="employee.contacts"/>
> >
> > <result property="bossId" column="bossId"/>
> >
> > </resultMap>
> >
> >
> >
> > <resultMap id="contacts"
> > class="qcom.cas.mysourcej.poc.model.Contact">
> >
> > <result property="name" column="contact_name"/>
> >
> > <result property="primaryContact" column="primary_contact"
> >
> >
> >
typeHandler="qcom.cas.commons.ibatis.typehandler.StringBooleanTypeHandler"
> > />
> >
> > <result property="relationship" column="relation"/>
> >
> > <result property="phoneNumber1.number" column="phone1"/>
> >
> > <result property="phoneNumber2.number" column="phone2"/>
> >
> > <result property="phoneNumber3.number" column="phone3"/>
> >
> > <result property="phoneNumber4.number" column="phone4"/>
> >
> > <result property="phoneNumber1.areaCode"
column="areaCode1"/>
> >
> > <result property="phoneNumber2.areaCode"
column="areaCode2"/>
> >
> > <result property="phoneNumber3.areaCode"
column="areaCode3"/>
> >
> > <result property=" phoneNumber4.areaCode"
column="areaCode4"/>
> >
> > </resultMap>
> >
> >
> >
> >
> >
> >
> >
> > <select
> > id="getEmployeeEmergencyContactsUsingDirectSort"
> > resultMap="employee">
> >
> >
> >
> >
> >
> > If so, any pointers or references where I can learn to do this..
> >
> >
> >
> > Currently I have a method that turns the list of employees into a
> hierarchy
> > of Employees as follows:
> >
> >
> >
> > /**
> >
> > * Turns a list of employees into a hierarchy of employees.
> >
> > * @param employees
> >
> > * @param removeChild
> >
> > */
> >
> > private void buildEmployeeObjectHierarchy(List
> > employees, boolean removeChild) {
> >
> > Map employeeIDMap = new HashMap(employees.size());
> >
> >
> >
> > for (Iterator iter = employees.iterator(); iter.hasNext();)
{
> >
> > Employee employee = (Employee) iter.next();
> >
> > employeeIDMap.put(employee.getEmplid(), employee);
> >
> > }
> >
> >
> >
> > for (Iterator iter = employees.iterator (); iter.hasNext();)
{
> >
> > Employee employee = (Employee) iter.next();
> >
> > Employee boss = (Employee)
> > employeeIDMap.get(employee.getBossId ());
> >
> > if (boss != null) {
> >
> > boss.addEmployee(employee);
> >
> > iter.remove(); //If a boss is found, remove this
> > employee from the list.
> >
> > }
> >
> > }
> >
> > }
> >
> >
> >
> > The above is a bit expensive and I'd like to get it out of my code if
> > possible. If not, no worries. I just want to use iBatis in the cleanest
> way
> > possible.
>
>
>