HOWTO: using multi column keys in MyBatis Sql maps

When you have to deal with a Third normal form (3NF) relational database, you almost always fall in two scenarios:

  • a DB with natural keys as the primary keys of your data
  • a DB with surrogate keys with auto generated integer identities or GUIDs
Said that I prefer to model with the “more normalized” first approach, this results in a more complicated way to query the DB with multiple condition ON clauses in Joins, more and best designed indexes to have good performance, higher skill to develop, maintain and query the DB.
With this approach you can however manage historical data (with the change of the date part of a composite key), you can easily implement inheritance and extension and so on.
Another issue to be aware of,  is that when you opt for the first approach you may have to deal with Object Relational libraries such as MyBatis(Net) I’m talking about here.
When you use MyBatis you can express your relation through the SELECT attribute in the RESULT tag of the RESULTMAP definition.
Suppose we have the following master/detail table definition:
MASTER(M_KEY1, M_KEY2, M_ATTR1, M_ATTR2)
DETAIL(D_KEY1, D_ATTR1, D_FK_M_KEY1, D_FK_M_KEY2)
And you have the following two mapping Classes:
class Master {
    string key1;
    string key2;
    string attr1;
    string attr2;
    List<Detail> details;
}
class Detail {
    string key1;
    string atttr1;
    Master master;
}
Then you can define two result maps:
<resultMaps>
  <resultMap id="masterResult" class="Master">
    <result property="key1" column="M_KEY1" />
    <result property="key2" column="M_KEY2" />
    <result property="attr1" column="M_ATTR1" />
    <result property="attr2" column="M_ATTR2" />
  </resultMap>
  <resultMap id="detailResult" class="Detail">
    <result property="key1" column="D_KEY1" />
    <result property="attr1" column="D_ATTR1" />
  </resultMap>
</resultMaps>
Now you should use the SELECT attribute of the RESULT tag to make MyBatis automatically load the detail list related to a master or a master related to a details and eventually use the LAZYLOAD attribute to defer the execution of the subquery.
The SELECT attribute is used in conjunction with a COLUMN attribute in which you can specify the column to use as the parameter for selection; in our case we have TWO columns, so how this affect the use of SELECT, COLUMN and PARAMETERCLASS?
The multi column mapping of SELECT subqueries is based on Maps/Dictionaries so the mechanism is to build a Map/Dictionary from more than one column to use as a parameter for another query; to the masterResult map we can add:
<result property="details" column="MAP_KEY1=M_KEY1,MAP_KEY2=M_KEY2" select="details.byMaster" />
And in the detailResult map we can similarly add:
<result property="master" column="MAP_KEY1=D_FK_M_KEY1,MAP_KEY2=D_FK_M_KEY2" select="master.byKey" />
The two select statements master.byKey and details.byMaster should be something like this:
<select id="master.byKey" resultMap="masterRes" { parameterClass="System.Collections.IDictionary" (for .NET) OR parameterClass="java.util.Map" (for Java) } >
  SELECT *
  FROM MASTER
  WHERE M_KEY1=#MAP_KEY1# AND M_KEY2=#MAP_KEY2#
</select>
<select id="details.byMaster" resultMap="detailRes" { parameterClass="System.Collections.IDictionary" (for .NET) OR parameterClass="java.util.Map" (for Java) } >
  SELECT *
  FROM DETAIL
  WHERE D_FK_M_KEY1=#MAP_KEY1# AND D_FK_M_KEY2=#MAP_KEY2#
</select>
And that’s it!
Hope some of you may find this helpful.

Un pensiero su “HOWTO: using multi column keys in MyBatis Sql maps

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...