ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: One solution to self-join, new feature request.... RE: Employee self-join one to many relationship (iBatis)
Date Wed, 05 Jul 2006 16:54:13 GMT
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.nameas 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: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