Mybatis Result Maps for Result group 2, mybatismaps

Mybatis Result Maps for Result group 2, mybatismaps

Reprinted Please note:TheViper http://www.cnblogs.com/TheViper

In the previous article, we talked about an automatic group of mybatis in One-to-multiple cases. This article describes multiple one-to-multiple cases.

For example, in the QQ space

We can see that the comment and comment are one-to-many, and the comment and reply are one-to-many.

Mood

public class Mood {    private int mood_id;    private String mood_content;    private String mood_time;    private User user;    private List<MoodComment> moodComments;       //getter,setter..}

Mood comment

public class MoodComment {    private int moodcommentid;    private String comment_content;    private String comment_time;    private User user;    private Mood mood;    private List<MoodCommentReply> moodCommentReplys;        //getter,setter}

Mood reply

public class MoodCommentReply {    private int moodreplyid;    private String reply_content;    private String reply_time;    private User user;    private MoodComment moodComment;        //getter,setter}

Mood table

Moodcomment table

Moodcommentreply table

User table

Table Data

It is easy to think of, that is, to change the resultmap in the previous article.

    <resultMap id="MoodResult" type="Mood">        <id property="mood_id" column="mood_id" />        <association property="user" javaType="User">               <id property="id" column="mood_userid"/>            <result property="name" column="mood_user"/>        </association>        <collection property="moodComments" ofType="MoodComment">            <id property="moodcommentid" column="moodcommentid" />            <association property="user" javaType="User">                <id property="id" column="comment_userid" />                <result property="name" column="comment_user"/>            </association>            <collection property="moodCommentReplys" ofType="MoodCommentReply">                <association property="user" javaType="User">                    <id property="id" column="reply_userid" />                    <result property="name" column="reply_user"/>                </association>            </collection>        </collection>    </resultMap>

You can see that a <collection> is added to <collection>. However, note that <collection> is added with <id>. Otherwise, mybatis will not be automatically grouped for the second time.

As for SQL, you can retrieve the data from the three tables. mybatis is automatically grouped.

        SELECT u1.name AS mood_user,u2.name AS comment_user,u3.name AS reply_user,        mood.mood_id,mood.id AS mood_userid,mood_content,mood_time,        moodcomment.moodcommentid,moodcomment.id AS comment_userid,moodcomment.comment_content,moodcomment.comment_time,        moodcommentreply.moodcommentid,moodcommentreply.id AS reply_userid,reply_content,reply_time FROM mood         LEFT JOIN moodcomment ON moodcomment.mood_id=mood.mood_id         LEFT JOIN moodcommentreply ON moodcommentreply.moodcommentid=moodcomment.moodcommentid         LEFT JOIN USER AS u1 ON mood.id=u1.id         LEFT JOIN USER AS u2 ON moodcomment.id=u2.id         LEFT JOIN USER AS u3 ON moodcommentreply.id=u3.id         ORDER BY mood_time DESC,moodcomment.comment_time DESC,reply_time DESC

The problem arises. If you only need the first two comments, all the replies of the two comments should be selected.

For example

Or how to select the first/last/first rows of each group in SQLUser variablesMethod.

        SET @num := 0, @type := '';        SELECT u1.name AS mood_user,u2.name AS comment_user,u3.name AS reply_user,        mood.mood_id,mood.id AS mood_userid,mood_content,mood_time,        moodcomment2.moodcommentid,moodcomment2.id AS comment_userid,moodcomment2.comment_content,moodcomment2.comment_time,        moodcommentreply.moodcommentid,moodcommentreply.id AS reply_userid,reply_content,reply_time FROM mood         LEFT JOIN         (SELECT *         FROM (          SELECT moodcommentid,comment_content,comment_time,id,mood_id,              @num := IF(@type = mood_id, @num + 1, 1) AS row_number,              @type := mood_id AS dummy          FROM moodcomment          ORDER BY mood_id        ) AS moodcomment1 WHERE moodcomment1.row_number <= 2) AS moodcomment2        ON moodcomment2.mood_id=mood.mood_id         LEFT JOIN moodcommentreply ON moodcommentreply.moodcommentid=moodcomment2.moodcommentid         LEFT JOIN USER AS u1 ON mood.id=u1.id         LEFT JOIN USER AS u2 ON moodcomment2.id=u2.id         LEFT JOIN USER AS u3 ON moodcommentreply.id=u3.id         ORDER BY mood_time DESC,moodcomment2.comment_time DESC,reply_time DESC

Pay attention to several moodcomment namespaces. You also need to add allowMultiQueries = true in jdbcUrl, because set variable is also a query.