MyBatis主子表查询

一、Mybatis查询语句

<select id="selectByProblemId" resultMap="findByProblemId">
      SELECT
          cp.id,
          cp.problem_id,
          cp.vote,
          cp.detail,
          cp.isdelete,
          cp.user_id,
          cp.islike,
          cp.headpic,
          cp.username,
          op.picture_url
      FROM
          co_reply AS cp
          LEFT JOIN co_picture AS op ON cp.id = op.attach_id
          AND op.attach_type = 1
      WHERE
          cp.problem_id = #{problemId,jdbcType=INTEGER}
          AND cp.isdelete = 0
      ORDER BY
          cp.vote DESC,
          cp.create_time DESC
  </select>

二、通过XML将查询结果转成主子表方式

  <resultMap id="findByProblemId" type="com.yunxue.entity.CoReply">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="problem_id" jdbcType="INTEGER" property="problemId" />
    <result column="vote" jdbcType="INTEGER" property="vote" />
    <result column="detail" jdbcType="VARCHAR" property="detail" />
    <result column="isdelete" jdbcType="INTEGER" property="isdelete" />
    <result column="user_id" jdbcType="INTEGER" property="userId" />
    <result column="headpic" jdbcType="INTEGER" property="headpic" />
    <result column="username" jdbcType="INTEGER" property="username" />
    <result column="islike" jdbcType="INTEGER" property="islike" />
    <collection property="coPictures" ofType="com.yunxue.entity.CoPicture">
      <id column="r_id" jdbcType="INTEGER" property="id" />
      <result column="picture_url" jdbcType="VARCHAR" property="pictureUrl" />
    </collection>
  </resultMap>

三、Mapper文件

 List<CoReply> selectByProblemId(@Param("problemId") Integer problemId,@Param("user_id") Integer user_id);

四、Vo类

import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonInclude;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Data
@ApiModel(value="CoReply对象", description="")
public class CoReply {
    @ApiModelProperty(value = "主键ID")
    private Integer id;
    @ApiModelProperty(value = "用户ID")
    private Integer userId = 0;
    @ApiModelProperty(value = "问题ID")
    private Integer problemId = 0;
    @ApiModelProperty(value = "点赞数")
    private Integer vote = 0;
    @ApiModelProperty(value = "答案详情")
    private String detail = "";
    @ApiModelProperty(value = "是否删除 0、否 1、是")
    private Boolean isdelete = false;
    @ApiModelProperty(value = "用户昵称")
    private String username = "";
    @ApiModelProperty(value = "头像地址")
    private String headpic = "";
    @ApiModelProperty(value = "是否点赞")
    private boolean islike = false;
    @ApiModelProperty(value = "图片地址")
    private List<CoPicture> coPictures = new ArrayList<>(0);
    @ApiModelProperty(value = "创建日期")
    @JsonInclude(JsonInclude.Include.NON_NULL)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date createTime;

五、实体类

@Data
@ApiModel(value="CoPicture对象", description="")
@JsonInclude(JsonInclude.Include.NON_NULL)
public class CoPicture {
    @ApiModelProperty(value = "主键ID")
    private Integer id;
    @ApiModelProperty(value = "图片地址")
    private String pictureUrl;
    @ApiModelProperty(value = "问题ID")
    private Integer attachId;
    @ApiModelProperty(value = "问题类型（0、问题 1、回答）")
    private Boolean attachType;