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:[EMAIL PROTECTED]
Sent: Monday, July 03, 2006 3:54 PM
To: [email protected]
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 <[EMAIL PROTECTED]> wrote:
> Bingo!
>
> Yep. Employee has a directReports list which is a list of Employees.
>
>
>
> -----Original Message-----
> From: Larry Meadors [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 03, 2006 3:19 PM
> To: [email protected]
> 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 <[EMAIL PROTECTED]> 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.
>
>
>
