mybatis的多表查询

mybatis的多表查询

今天项目涉及到多表查询，原本可以根据mybayis的逆向工程的每一个mapper查询数据库封装对象，但为了效率能够提高，使用mybatis的多表查询直接返回封装对象
具体如下：

1、首先，先在mybatis的xml配置文件中配置你需要返回的分装对象，大体如下：
返回的封装类UserVO

package com.zhongkai.volunteer.VO;

import com.zhongkai.volunteer.DTO.UserVolunteerDTO;
import com.zhongkai.volunteer.pojo.VolunteerInformation;

import java.util.List;

/**
 * @author 17715
 * 管理员端搜索用户显示的数据
 */
public class UserVO {
    private String stuId;
    private String name;
    private String academy;
    private String profession;
    private String sex;
    private String birth;
    private String face;
    private String tele;
    private String email;
    private Double workTime;
    private Integer state;
    private Integer vip;
    /**
     * 志愿时长记录
     */
    private List<UserVolunteerDTO> volunteerInformationList;


    @Override
    public String toString() {
        return "UserVO{" +
                "stuId='" + stuId + '\'' +
                ", name='" + name + '\'' +
                ", academy='" + academy + '\'' +
                ", profession='" + profession + '\'' +
                ", sex='" + sex + '\'' +
                ", birth='" + birth + '\'' +
                ", face='" + face + '\'' +
                ", tele='" + tele + '\'' +
                ", email='" + email + '\'' +
                ", workTime=" + workTime +
                ", state=" + state +
                ", vip=" + vip +
                ", volunteerInformationList=" + volunteerInformationList +
                '}';
    }

    public String getStuId() {
        return stuId;
    }

    public void setStuId(String stuId) {
        this.stuId = stuId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAcademy() {
        return academy;
    }

    public void setAcademy(String academy) {
        this.academy = academy;
    }

    public String getProfession() {
        return profession;
    }

    public void setProfession(String profession) {
        this.profession = profession;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getBirth() {
        return birth;
    }

    public void setBirth(String birth) {
        this.birth = birth;
    }

    public String getFace() {
        return face;
    }

    public void setFace(String face) {
        this.face = face;
    }

    public String getTele() {
        return tele;
    }

    public void setTele(String tele) {
        this.tele = tele;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Double getWorkTime() {
        return workTime;
    }

    public void setWorkTime(Double workTime) {
        this.workTime = workTime;
    }

    public Integer getState() {
        return state;
    }

    public void setState(Integer state) {
        this.state = state;
    }

    public Integer getVip() {
        return vip;
    }

    public void setVip(Integer vip) {
        this.vip = vip;
    }

    public List<UserVolunteerDTO> getVolunteerInformationList() {
        return volunteerInformationList;
    }

    public void setVolunteerInformationList(List<UserVolunteerDTO> volunteerInformationList) {
        this.volunteerInformationList = volunteerInformationList;
    }
}

UserVolunteerDTO

package com.zhongkai.volunteer.DTO;

/**
 * @author 17715
 * 用于后台搜索时的部分对象展示
 */
public class UserVolunteerDTO {
    private String volunteerName;
    private String volunteerTime;
    private String time;

    @Override
    public String toString() {
        return "UserVolunteerDTO{" +
                "volunteerName='" + volunteerName + '\'' +
                ", volunteerTime='" + volunteerTime + '\'' +
                ", time='" + time + '\'' +
                '}';
    }

    public String getVolunteerName() {
        return volunteerName;
    }

    public void setVolunteerName(String volunteerName) {
        this.volunteerName = volunteerName;
    }

    public String getVolunteerTime() {
        return volunteerTime;
    }

    public void setVolunteerTime(String volunteerTime) {
        this.volunteerTime = volunteerTime;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }
}

xml中配置返回对象

<resultMap id="UserVO" type="com.zhongkai.volunteer.VO.UserVO">
    <id column="stu_id" property="stuId"  jdbcType="VARCHAR"/>
    <result column="name" property="name" jdbcType="VARCHAR"/>
    <result column="academy" property="academy" jdbcType="VARCHAR"/>
    <result column="profession" property="profession" jdbcType="VARCHAR"/>
    <result column="sex" property="sex" jdbcType="VARCHAR"/>
    <result column="birthday" property="birth" jdbcType="VARCHAR"/>
    <result column="face" property="face" jdbcType="VARCHAR"/>
    <result column="telephone" property="tele" jdbcType="VARCHAR"/>
    <result column="email" property="email" jdbcType="VARCHAR"/>
    <result column="state" property="state" jdbcType="INTEGER"/>
    <result column="vip" property="vip" jdbcType="INTEGER"/>
    <result column="sum(B.work_time)" jdbcType="DOUBLE" property="workTime"/>
    <collection property="volunteerInformationList" ofType="com.zhongkai.volunteer.DTO.UserVolunteerDTO" javaType="java.util.List">
      <result column="volunteer_time" jdbcType="VARCHAR" property="volunteerTime"/>
      <result column="volunteer_name" jdbcType="VARCHAR" property="volunteerName"/>
      <result column="work_time" jdbcType="DOUBLE" property="time"/>
    </collection>
  </resultMap>

xml中配置sql语句

<select id="selectUserVO" resultMap="UserVO" parameterType="java.lang.String"> 
 select A.stu_id,A.name,A.academy,A.profession,A.sex,A.birthday,A.face,A.telephone,A.email,A.state,A.vip,sum(B.work_time),B.volunteer_name,B.volunteer_time,B.work_time  from student_information A left join volunteer_information B on A.stu_id=B.stu_id where A.stu_id=#{name} or A.name=#{name}
</select>

补充：当数据库存在同名的字段时，可以使用别名来填写resultMap的column字段，如下

<resultMap type="Vote" id="VoteMapper">
          <id column="id" property="id"/>
          <result column="theme" property="theme"/>
          <result column="isuse" property="isuse"/>
          <collection property="Options" ofType="VoteOption" >
              <id column="vid" property="id"/>
              <result column="vote_id" property="voteId"/>
              <result column="option" property="option"/>
              <result column="poll" property="poll"/>
          </collection>
  </resultMap>

   <select id="findById" parameterType="int" resultMap="VoteMapper">
          SELECT 
              v.*,vt.id vid,vt.vote_id,vt.option,vt.poll 
          FROM 
              vote v join vote_option vt 
          on v.id=vt.vote_id 
         WHERE v.id=#{id}
  </select>

两个id字段同名， sql语句联合查询时使用字段别名，resultMap中对应的column属性使用相应的别名

参考博客：

Mybatis 查询一个对象包含多个子对象 (List 包含 List)

mybatis多表联合查询字段重复问题

mybatis使用group by返回sum，count数据