ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick" <ricks_mailingli...@arc-mind.com>
Subject It worked (kinda) RE: One solution to self-join, new feature request.... RE: Employee self-join one to many relationship (iBatis)
Date Wed, 05 Jul 2006 19:57:07 GMT
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 <ricks_mailinglists@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 <ricks_mailinglists@arc-mind.com> wrote: 
> Bingo!
>
> Yep. Employee has a directReports list which is a list of Employees.
>
>
>
> -----Original Message-----
> From: Larry Meadors [mailto: <mailto:lmeadors@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 <ricks_mailinglists@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.
>
>
>



 


Mime
View raw message