轉載請注明：TheViperhttp://m.survivalescaperooms.com/TheViper

在之前的文章中說的是一個一對多情況下mybatis的自動分組，這篇說下多個一對多的情況。

比如QQ空間里的說說

可以看到，說說和評論是一對多，評論又和回復是一對多。

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表

moodcomment表

moodcommentreply表

user表

表數據

做法很容易想到，就是把上一篇resultmap改一下就可以了。

    <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>

可以看到在<collection>里面再加一個<collection>.不過，要注意在里面的<collection>加上<id>,否則mybatis不會自動進行第二次分組。

至于sql，還是把三個表數據一并取出就可以了，mybatis會自動分組。

        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

問題有來了，如果只要評論的前2條，但是那2條評論的所有回復要全部選出。

比如

還是用(譯)如何在sql中選取每一組的第一行/最后行/前幾行里面提到的user variables方法。

        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

注意幾個moodcomment的命名空間。還有需要在jdbcUrl中加上allowMultiQueries=true，因為set variable也是一次查詢。