你的位置:首页 > Java教程

[Java教程]mybatis Result Maps对结果分组2


转载请注明: TheViper http://www.cnblogs.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 &lt;= 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也是一次查询。