Load entity relations with dynamic joins on business demand

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Load entity relations with dynamic joins on business demand

Guillermo Gefaell
Hi!

Where are developing a medium size app using using a private framework  that uses mybatis 3.1.1, but after reading a bit around here i'm going to test 3.2.8.

Well, to the point. Our app has a complex business logic and the database model is a mess (It's a project requiriment not to change, or keep the changes to a minimum). One of the problems we had on the beggining, beeing every developer new to mybatis, was getting the entities with its relationships and avoiding the N+1 select problem. Following examples over the internet we started adding querys with joins over the xml mapper.

But was a mess. The PROJECT entity, that is the most used and has the more complex relation. This entity is used in almost every page and almost weverytime you need to bring some of its relationships. But not only it's direct relations, but maybe a 2º or 3º degree relation. Furthermore, ther are other entities, like COMPANY, that may appear in different places entities of the final tree. For example

PROJECT
  - STAGE (1..N)
        -COMPANY
        -COMPANY
        -REQUEST (1..N)
             -COMPANY
 -SUBPROCESS
       -PROCESS
 -REGION
 
There where problems and the XML files where getting a nightmare. So we came up with a dynamic joins. The idea was to be able to query the entities from the web layer, but beeing able to tell with relationships are needed so the final query would be kept to just the neccesary.


The model uses columPrefixes concatenated, alias for each and unique relation. Each entity has it's join that's looks something like this.

public JoinInstituciones(String prefix, String alias, String joinOn) {
super(prefijoAlias, alias,joinOn);
this.sentenciaColumnas ="INST.CLAVE as INCLAVE,rest of columns";
this.sentenciaJoin =" LEFT JOIN  INSTITUCIONES "+alias+ joinOn;
agregarPrefijo(); //ads the prefix to all the columns on sentenciaColumnas.
agregarAlias("INST"); //replaces the table name with it's alias on the columns and join string.
}
}

The base  interface for the model it's a Joinable interface

public interface Joinable{
String getSentenceColumns();

String getSentenceJoin();
        //Get the nestd joins of this
List<Joinable> getNestedJoin();
//returns the nested joins plus this join
List<Joinable> getJoins();

}

All the relationships in the ResultMap get a prefix:
<collection property="etapas" javaType="ArrayList" ofType="cl.mds.bip.business.model.EtapasBO" resultMap="cl.mds.bip.business.model.EtapasBOMapper.ResultMapPrefijo"
columnPrefix="P">

In the Example whe added a property List<Joinable> joins, In the selecByExample whery then added this.

        <sql id="dynamicColumns">
<foreach collection="joins" item="join" separator=",">
${join.sentenceColumns}
</foreach>
</sql>

<sql id="dynamicJoins">
<foreach collection="joins" item="join">
${join.sentenceJoin}
</foreach>
</sql>


<select id="selectByExampleConJoins" parameterType="cl.mds.bip.business.model.ProyectosBOExample" resultMap="BaseResultMapPrefix">
select
<if test="distinct">
distinct
</if>
'true' as QUERYID,
<include refid="Base_Column_List" />
<if test="_parameter != null">
<if test="joins.size() > 0">
,
<include refid="dynamicColumns" />
</if>
</if>
from PROYECTOS PROYECTOS
<if test="_parameter != null">
<if test="joins.size() > 0">
<include refid="dynamicJoins" />
</if>
</if>
<if test="_parameter != null">
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null">
order by ${orderByClause}
</if>
</select>

We use the pass an instance of the same entitty we want to query as the relations filter. For example, to query Proyectos and get it with etapas, and regiones whe do somthing like this.

ProyectosBO relations = new ProyectosBO();
relations.setEtapas(new ArrayList<EtapasBO>());
relations.setRegiones( new RegionesBO());

ProyectosBO result = proyectosManager.getByIdLoaded(id, relations).

Down the layer we do something like this:
               List<Joinable> joinsToQuery = new ArrayList<Joinable>();
               if (relation.getRegiones() != null)
joinsToQuery.addAll(new JoinRegion("P", Alias.PROYECTOS_REGION, "PROYECTOS.REG_CLAVE").getJoins());
if (relation.getEtapas()!=null && pro.getEtapas().isEmpty())
joinsToQuery.addAll(new JoinEtapas("P",Alias.PROYECTOS_ETAPAS, clave).getJoins());


This model works quite well, but has its limits:
 - if you want to add the same relation (for example, a stage with two companies) you have to creat an specific prefix for the second relationship.
 - It doesnt limit the deepth of the query. 
 - The prefixes get concatenated, so you have to be careful with the lenght of the columns names. Oracle has a 30 chars limit for aliases.
 - If you add a nested join to an existing join you are impacting the performance somewhere else, as your new join will be added in other queries.


I was thinking in tuning this model up and have a couple of ideas, but before investing time I wanted to know if the idea is worth the effort or i'm loosing my time here. I'll be glad to explain more.

Greetings!

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Load entity relations with dynamic joins on business demand

Ray Sprinkle
I have had similar issues and the way that I handled it was totally
different.  I had 2 different approaches depending on the situation:

1. Cache each level of objects individually and then build then assemble
them after retrieving them from MyBatis, or

2. Read all of the data all of the time and depend upon the caching
layer to make it happen efficiently.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.