ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Clinton Begin" <clinton.be...@gmail.com>
Subject Re: 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 20:32:33 GMT
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 <ricks_mailinglists@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* <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