MyBatis result maps Grouping Results 2

Reprint Please specify: theviper http://www.cnblogs.com/TheViper

In the previous article is a one-to-many case of MyBatis automatic grouping, this article said more than a couple of cases.

For example, in the QQ space to say

As you can see, saying say and comments is one-to-many, and comments and replies are one-to-many.

Mood

 Public class Mood {    privateint  mood_id;     Private String mood_content;     Private String mood_time;     Private user user;     Private List<moodcomment> moodcomments;        // getter,setter..}

Mood Comment

 Public class moodcomment {    privateint  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 {    privateint  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, that is, to change the last article Resultmap.

    <ResultmapID= "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 a <collection> in the <collection>. However, be aware of the <collection> plus <id> inside, otherwise MyBatis will not automatically make a second grouping.

As for SQL, it is possible to take out three of the table data together, and MyBatis will automatically group.

        SELECTU1.name asMood_user,u2.name asComment_user,u3.name asReply_user, Mood.mood_id,mood.id asMood_userid,mood_content,mood_time, Moodcomment.moodcommentid,moodcomment.id asComment_userid,moodcomment.comment_content,moodcomment.comment_time, Moodcommentreply.moodcommentid, Moodcommentreply.id asReply_userid,reply_content,reply_time fromMood Left JOINMoodcomment onmoodcomment.mood_id=mood.mood_id Left JOINMoodcommentreply onMoodcommentreply.moodcommentid=Moodcomment.moodcommentid Left JOIN USER  asU1 onMood.id=u1.id Left JOIN USER  asU2 onMoodcomment.id=u2.id Left JOIN USER  asU3 onMoodcommentreply.id=u3.idORDER  byMood_timeDESC, Moodcomment.comment_timeDESC, Reply_timeDESC

The question is, if only the first 2 of the comments, but all the replies to the 2 comments are to be selected.

Like what

Or how to select the user variables method mentioned in the first row/last row/previous row of each group in SQL.

        SET @num:= 0,@type:= "'; SELECTU1.name asMood_user,u2.name asComment_user,u3.name asReply_user, Mood.mood_id,mood.id asMood_userid,mood_content,mood_time, Moodcomment2.moodcommentid,moodcomment2.id asComment_userid,moodcomment2.comment_content,moodcomment2.comment_time, Moodcommentreply.moodcommentid, Moodcommentreply.id asReply_userid,reply_content,reply_time fromMood Left JOIN         (SELECT *          from (          SELECTmoodcommentid,comment_content,comment_time,id,mood_id,@num:= IF(@type =MOOD_ID,@num + 1,1) asRow_number,@type:=mood_id as Dummy           frommoodcommentORDER  bymood_id) asMoodcomment1WHEREMoodcomment1.row_number&Lt= 2) asMoodcomment2 onmoodcomment2.mood_id=mood.mood_id Left JOINMoodcommentreply onMoodcommentreply.moodcommentid=Moodcomment2.moodcommentid Left JOIN USER  asU1 onMood.id=u1.id Left JOIN USER  asU2 onMoodcomment2.id=u2.id Left JOIN USER  asU3 onMoodcommentreply.id=u3.idORDER  byMood_timeDESC, Moodcomment2.comment_timeDESC, Reply_timeDESC

Note a few moodcomment namespaces. There is also a need to add allowmultiqueries=true to the Jdbcurl because set variable is also a query.

MyBatis result maps Grouping Results 2