Saving/Updating collections with mybatis, what is the common practice?

Question

I've decided to try and use mybatis for a new project. I'm decently familiar with SQL, and I've had some bad experiences with hibernate recently so I'm looking for a more low-level approach to DAO.

Seems to be quite nice except for one thing, and that is handling collections.

I have two POJOs, group and user, that are many-to-many. I've decided on a design philosophy that a POJO that has a collection should only update the M-M relation between the tables when saved. So, for example, when I save a group object that has a collection of users, the design philosophy dictates that the users should already be saved, and I only have to save the group and the group_user relation in the database.

so, for the saveGroup function in the interface, I've made this XML mapping for mybatis:

    <insert id="saveGroup" keyColumn="id"
    parameterType="se.myapp.domain.Group">
    <choose>
        <when test="id == null">
        INSERT INTO myapp_group (name, description)
        VALUES
        (#{username}, #{password});
        </when>
        <otherwise>
        UPDATE myapp_group set name=#{name}, description=#{description}
        where id=#{id};
        </otherwise>
    </choose>

    <if test="users != null">
        create temporary table tmpnewgroups (group_id integer, user_id integer);

        insert into tmpnewgroups (group_id, user_id) values (
        <foreach collection="users" item="user" open="" close="" separator="),()">
             #{id},#{user.id}
        </foreach>
        );

        insert into myapp_user_group(group_id, user_id) 
        select tmp.group_id, tmp.user_id 
        from tmpnewgroups tmp 
        left outer join myapp_user_group ug 
            on ug.group_id = tmp.group_id and ug.user_id = tmp.user_id
        where ug.group_id is null;

        delete from myapp_user_group 
        where group_id = #{id} and user_id not in (select user_id from tmpnewgroups);
    </if>

</insert>

This does work as intended (insert/updates the group, saves the collection of users as relations in the database). But I don't really feel that this is best practice. The application is made so that I can switch to hibernate if needed, so the logic for saving collection preferably should be in the database layer. Is there some "magic" in mybatis that I'm not aware of that could streamline operations like this?

Any thoughts on how to improve this? Or should I rethink the application design and put the handling of collections further up in the model?

was there a problem using `` and its `` to describe many:many relations? — Gus, Mar 12 '13 at 23:59
Well afaik resultmaps are for getting data, not saving data. Getting a collection is no problem, it's saving them that I've found to be cumbersome. — Dytut, Mar 14 '13 at 11:56
Have you looked at using the [MyBatis Generator](https://code.google.com/p/mybatis/wiki/Generator)? It will generate the basic CRUD operations for you saving you a ton of hand coded SQL so you only have to write the more complex joins, etc. You can use the generated code in your DAO and move a lot of the selection logic you have in your XML into the DAO. — clav, Mar 27 '13 at 15:42

score 3 · Accepted Answer · edited May 23 '17 at 12:29

The second part of your saveGroup data mapping operation is indeed a reason to rethink your application design. Persisting your in-memory users collection to a temporary table to compare it to the persisted one in order to insert and delete the deltas is a fairly heavy operation that is not necessary at all if just the name or the description of the group needs updating, i.e. when there are no deltas. Whether or not this is the case can be decided by the database server, which is your current solution, or the database client, your application.

Apart from the case where the group and possibly it's users needs a first-time insert, if you want your application to decide whether or not the link table needs to be updated then your application needs to know whether or not the users collection has changed since it was retrieved from the database. Unfortunately MyBatis isn't going to help your application do that.

See, compared to Hibernate MyBatis is blissfully unaware of your objects and the state they carry after MyBatis has done its job, which is data mapping, not object relation mapping. Hibernate can automatically detect the so-called dirty state of your objects, MyBatis can not, as this was never part of its jobs description. So you are left to your own devices.

A super simple approach would be to store the users' hashcode after a select and check whether or not that hashcode has changed using a method called isUserDirty(). You could simply test that condition from within your mapping using <if test="isUserDirty">. This is of course not a very generic approach and depends on a decent hashCode() implementation. Have a look at the answer of leonbloy to a similar question for a more generic approach. Of course this might still be a bit too simple as well, especially since we are talking many to many relations. Which approach is best all depends on your case.

Now you should know what to do. Good luck!

PS instead of inserting and deleting the delta's I would advise a simple overwrite: delete all then insert all, in a transaction. Your temporary table strategy is an optimization strategy that might actually not improve the performance of your database at all, in fact my guess would be that it probably makes it worse. If you have properly profiled this strategy and know what you're doing you may ignore this postscript.

Saving/Updating collections with mybatis, what is the common practice?

1 Answers1