MyBatipse XML Generate ResultSet from JPA Annotations

Oct 01, 2018 — Eclipse, Mybatis, JPA — 2 min read

My Batipse is a great Eclipse plugin that provides: Validation Generation Tools while working with Mybatis XML files. When I started at Standardbred Canada the DB library of choice was Mybatis (which I'd never used before) but quickly started to love it.

The primary reason that MyBatis was chosen due to it's ability to map legacy tables to updated domain models with ease. In order to quickly replicate the legacy functionality, the ability to write XML based SQL was a requirement.

The primary feature that I started using was Result Map generation option.

Review of MyBatipse Feature

The functionality was pretty basic, it would reflectively inspect the domain model fields, and create the result mapping for this item. One of the major problems was, that this did a direct mapping of name to column, which in our case (and most cases) wasn't generally the case. For example, if the object I was attempting to query was:

1public class Address {
2    private String address1;
3    private String address2;
4    ...
5}

with the matching result map:

1<resultMap>
2   <result property="address1" column="address1"/>
3   <result property="address2" column="address2"/>
4   ...
5</resulMap>

the issue is that our tables are defined a little differently:

1select ad_addr1, add_addr2, ... from address

the resultMap still required a bunch of manual edits. I could choose to either update the SQL to include aliases matching the Java Address but that would get hectic when working with collections and associations.

JPA Annotations

I figured it would be easier to add JPA annotations to my domain models, as this would provide: the ability to maybe switch away from MyBatis down the road in source documentation for easy lookup (until this point columns were "documented" by appending private String address; // ad_addr1 🙁 Plus it would give me a chance to take a look at how and what MyBatipse was doing - and learning something new is never bad. This is still a work in progress, it's crude and I want to make it better before submitting a pull request https://github.com/kenjdavidson/mybatipse/tree/jpa-mapping.

When installing and using this version of MyBatipse you have the following available:

1@Entity
2@Table(name="address")
3public class Address {
4    @Id
5    @Column(name="ad_id")
6    private long id;
7
8    @Column(name="ad_addr1")
9    private String address1;
10
11    @Column(name="ad_addr2")
12    private String address2;
13
14    ....
15}

which would be extracted to:

1<resultMap>
2   <id property="id" column="ad_id"/>
3   <result property="address1" column="ad_addr1"/>
4   <result property="address2" column="ad_addr2"/>
5   ...
6</resulMap>

As we can see, the resultMap is now updated with the appropriate column names. No more editing of resultMaps (at least for simple maps that is).

Collections and Associations

There's still work that needs to be done in order to get Collections and Associations working properly. On a basic level they work well - but to allow for some best practices I've noticed - we need to provide aliases and prefixes to the result map entries.

SQL Statement Column Generation

Another added feature was the ability to create <sql> blocks containing the columns for INSERT, UPDATE and SELECT statements. The following menu features are available when working with a <resultMap>: Source > ResultMap SQL > Select columns Source > ResultMap SQL > Insert columns Source > ResultMap SQL > Update columns which will create the appropriate SQL blocks (respectively):

1<sql id="addressMapSelectColumns>
2    ad_addr1,
3    ad_addr2,
4    ...
5</sql>

1<sql id="addressMapInsertColumns>
2    (ad_addr1,
3    ad_addr2,
4    ...)
5    values (#{address1},
6    #{address2},
7    ...
8    )
9</sql>

1<sql id="addressMapUpdateColumns>
2    ad_addr1 = #{address1},
3    ad_addr2 = #{address2},
4    ...
5</sql>

which can easily be used:

1<select id="selectAddress">
2    select <include ref="addressMapSelectColumns" />
3    from address
4</select>

1<insert id="insertAddress" parameterType="Address">
2    insert into address
3    <include ref="addressMapInsertColumns" />
4</insert>

1<insert id="updateAddress" parameterType="Address">
2    update address
3    set <include ref="addressMapUpdateColumns" />
4    where ...
5</insert>

Roadmap items

I'm currently using this in my day-to-day, but there are some things that need to get added/update:

Handling Class Extension with Prefixes

For example, right now base classes are not handled well. This probably won't affect many, but in our case our child objects contain prefixed column names in legacy, meaning that automating the resultMap still requires some manual intervention to update the column names coming from the parent object.

1public class Parent {
2    @Column(name="col1")
3    private String column1
4}
5
6public class Child extends parent {
7    @Column(name="c_col2")
8    private String column2;
9}

this will result in Child result map:

1<resultMap type="Child">
2    <result property="column1" column="col1"/>
3    <result property="column2" column="c_col2"/>
4</resultMap>

where we actually want

1<resultMap type="Child">
2    <result property="column1" column="c_col1"/>
3    <result property="column2" column="c_col2"/>
4</resultMap>

Collection and Association Mapping

This is pretty crude at the moment. It would be good if it could support: Aliases Prefixes Etc.