mybatis的关联查询以及count

1.多表查询，1对1的时候，最简单的做法

<resultMap id="postInfo" type="postInfoEntity">
        <id property="postId" column="post_id"/>
        <result property="userName" column="user_name"/>
        <result property="postTitle" column="post_title"/>
        <result property="postContent" column="post_content"/>
        <result property="topPost" column="top_post"/>
        <result property="wonderfulPost" column="wonderful_post"/>
        <result property="createTime" column="create_time"/>
        <result property="count" column="num"/>
    </resultMap>

    <!--某个贴吧所有界面的帖子，通过置顶降序排序-->
    <select id="listAllPostInfos" resultMap="postInfo">
        SELECT A.post_id, COUNT(*) num, post_title, post_content, top_post, wonderful_post, user_name, A.create_time
        FROM post_info A, nf_user B, reply_post_info C
        WHERE post_status = 1 AND C.reply_status = 1
        AND post_bar_id = #{param1}
        AND A.user_id = B.user_id
        AND A.post_id = C.post_id
        GROUP BY post_id
        ORDER BY top_post DESC, create_time
    </select>

其中COUNT(*) 取了一个别名，目的是为了对应resultMap中的<result property="count" column="num"/>, 然而仅仅这样是不够的，因为虽然查得到，但是mybatis映射不出来，他底层的反射和动态代理还需要我们在实体类进行设置——我们加一个属性和property对应就可以了

 

@Data
@Document(indexName = "post_info")
public class PostInfoEntity {

    @Id
    private Long postId;
    @Field(type = FieldType.Long)
    private Long postBarId;
    @Field(type = FieldType.Text, analyzer = "ik_max_word")
    private String postTitle;
    @Field(type = FieldType.Text, analyzer = "ik_max_word")
    private String postContent;
    @Field(type = FieldType.Long)
    private Long userId;
    @Field(type = FieldType.Long)
    private Long topPost;
    @Field(type = FieldType.Long)
    private Long wonderfulPost;
    @Field(type = FieldType.Integer)
    private Integer audit;
    @Field(type = FieldType.Long)
    private Long visitCount;
    @Field(type = FieldType.Integer)
    private Integer postStatus;
    @Field(type = FieldType.Date)
    private Date createTime;
    private Integer count;
    private String userName;

}
// 除了@Data注解其他都无视，其他的是elasticsearch的

 

2.多表联合查询，可以像我们上面一样，和COUNT(*)同一种写法，直接写一个<result>标签然后实体类加一个属性对应。就是上面的userName.

但是实际上我们多表的时候一般每一张表都有POJO类的，也可以把String userName改成NfUser nfUser  然后mapper中对应修改成<result properties="nfUser.userName" colum="user_name"