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