MyBatis result maps Grouping Results 2

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.