MyBatis实现一对一、一对多查询

实现查询思路：

1. 嵌套查询方式: 通过执行另一条SQL语句映射语句来返回预期的复杂类型。

2. 嵌套结果方式：使用嵌套结果映射来处理复杂的联合结果的子集。
   一对多查询：

   嵌套结果使用双标签\<collection >\</collection >
   

一对一查询：

	嵌套结果使用双标签\<association>\</association>

就像下面这样：

//嵌套查询查询  使用标签<association>
 <association property="card" column="card_id" javaType="Pojo.IdCard"
                     select="Mapper.UserMapper.FindById"/>
                     
 //嵌套结果查询 使用标签 <association></association>
<association property="card" column="card_id" javaType="Pojo.IdCard">
 		 //查询到表二结果，并映射结果集
        <id property="id" column="card_id" />
        <result property="code" column="code"/>
 </association>

一对一具体应用时的步骤：

 1. 先查询表tb_person
<mapper namespace="Mapper.UserMapper">
<!--    一对一查询 begin-->
        <select id="FindPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
            SELECT * from tb_person where id=#{id}
</select>
2.使用resultMap 映射查询到表tb_person 的结果
  <resultMap id="IdCardWithPersonResult" type="Pojo.Person">
 
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
3.使用<association/>标签将通过Mapper包中的接口类UserMapper定义的方法查询表tb_idcard，从而使得两张表的查询链接起来。
4.根据 <id property="id" column="id"/>查询的id，传入下一个查询语句中，id=card_id,并将查询到的表二结果放到List<IDcard> card中；
<association property="card" column="card_id" javaType="Pojo.IdCard"
                     select="Mapper.UserMapper.FindById"/>
   </resultMap>
    <select id="FindById" parameterType="Integer" resultType="Pojo.IdCard" >
        SELECT * from tb_idcard where id=#{id}
 </select>

具体实现：

<!--    一对一查询 begin-->
        <select id="FindPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
            SELECT * from tb_person where id=#{id}
        </select>
    <resultMap id="IdCardWithPersonResult" type="Pojo.Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <association property="card" column="card_id" javaType="Pojo.IdCard"
                     select="Mapper.UserMapper.FindById"/>
    </resultMap>
    <select id="FindById" parameterType="Integer" resultType="Pojo.IdCard" >
        SELECT * from tb_idcard where id=#{id}
    </select>
<!--    一对一嵌套查询 end-->

<!--    一对一嵌套结果查询 begin-->
    <select id="FindPersonById2" parameterType="Integer"
            resultMap="IdCardWithPersonResult2">
        SELECT p.*,idcard.code
        from tb_person p,tb_idcard idcard
        where p.card_id=idcard.id
        and p.id=#{id}
    </select>

    <resultMap id="IdCardWithPersonResult2" type="Pojo.Person" >
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <association property="card" column="card_id" javaType="Pojo.IdCard">
        <id property="id" column="card_id" />
        <result property="code" column="code"/>
        </association>
    </resultMap>
    <!--    一对一嵌套结果查询 end-->

项目结构：

Mapper.UserMapper接口：

public interface UserMapper {
//这里时namespace的命名空间
	//一对一查询
    void FindPersonById();
    void FindPersonById2();
    //一对多查询
    void FindUserWithDept();
//    嵌套查询
    void FindEmpWithDept();
    void FindEmpWithDeptByOne();
}

下面直接实现一对多查询 的两种方式

1. 嵌套查询方式

Mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Mapper.UserMapper">

<!--一对多嵌套查询 begin-->

    <select id="FindEmpWithDept"  parameterType="Integer" resultMap="EmpWithDeptResult">
        SELECT * from dept where deptno=#{deptno}
    </select>

    <resultMap id="EmpWithDeptResult" type="Pojo.Dept">
        <id property="deptno" column="deptno"/>
        <result property="dname" column="dname"/>
        <result property="loc" column="loc"/>
        <collection property="empList" column="deptno" ofType="Pojo.Emp"
                     select="Mapper.UserMapper.FindEmpWithDeptByOne"/>
    </resultMap>

    <select id="FindEmpWithDeptByOne" parameterType="Integer" resultType="Pojo.Emp">
        SELECT * from emp where deptno=#{deptno}
    </select>

<!--   一对多 嵌套查询 end-->


<!--   一对多 嵌套结果查询 begin-->
<!--     SELECT d.*,e.*  查询两各表的所有需要的字段-->
<!--     from dept d,emp e 来自dept表和emp表，d，e是表名的缩写，用于区分两个表的deptno字段-->
<!--        SELECT d.*,e.deptno,e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm-->
    <select id="FindUserWithDept" parameterType="Integer"
            resultMap="UserWithOrdersResult">
        SELECT d.*,e.*
        from dept d,emp e
        where d.deptno=e.deptno
        and d.deptno=#{deptno}//传入参数
    </select>
    <resultMap id="UserWithOrdersResult" type="Dept" >
<!--        先查询dept表，然后再拼接empList-->
        <id property="deptno" column="deptno"/>
        <result property="dname" column="dname"/>
        <result property="loc"  column="loc"/>
        <collection property="empList" ofType="Emp" >
<!--   property="empList"  引入Dept对象指定实体类对象中的emplist泛型数组     -->
<!--    ofType="Emp" 指定实体类对象中的集合类属性        -->
            <id property="empno" column="empno"/>
            <result property="ename" column="ename"/>
            <result property="job" column="job"/>
            <result property="mgr" column="mgr"/>
            <result property="hiredate" column="hiredate"/>
            <result property="sal" column="sal"/>
            <result property="comm" column="comm"/>
            <result property="deptno" column="deptno"/>
        </collection>
    </resultMap>
    <!--   一对多 嵌套结果查询 end-->

</mapper>

Dept实体类；

package Pojo;

import java.util.List;

public class Dept {
    private Integer deptno;
    private String dname;
    private String loc;
    private List<Emp> empList;

    public List<Emp> getEmpList() {
        return empList;
    }

    public void setEmpList(List<Emp> empList) {
        this.empList = empList;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "deptno=" + deptno +
                ", dname='" + dname + '\'' +
                ", loc='" + loc + '\'' +
                ", empList=" + empList +
                '}';
    }
}

Emp实体类：

package Pojo;

public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private int mgr;
    private String hiredate;
    private int sal;
    private int comm;
    private Integer deptno;

    public Integer getEmpno() {
        return empno;
    }

    public void setEmpno(Integer empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public String getHiredate() {
        return hiredate;
    }

    public void setHiredate(String hiredate) {
        this.hiredate = hiredate;
    }

    public int getSal() {
        return sal;
    }

    public void setSal(int sal) {
        this.sal = sal;
    }

    public int getComm() {
        return comm;
    }

    public void setComm(int comm) {
        this.comm = comm;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hiredate='" + hiredate + '\'' +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }
}

测试：

import Pojo.Dept;
import Pojo.Person;
import Utils.Mybatis;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;

public class AppTest {
    @Test
    public void findPersonByIdTest(){//一对一查询
        SqlSession sqlSession = Mybatis.openSession();
        List<Person> person1 = sqlSession.selectList("Mapper.UserMapper.FindById",1);
        List<Person> person2 = sqlSession.selectList("Mapper.UserMapper.FindPersonById",1);
        List<Person> person3 = sqlSession.selectList("Mapper.UserMapper.FindPersonById2",1);
        System.out.println(person1.toString());
        System.out.println(person2.toString());
        System.out.println(person3.toString());
        sqlSession.close();
    }

    @Test
    public void FindUserWithDeptTest(){
//        一对多嵌套结果查询
        SqlSession sqlSession = Mybatis.openSession();
        List<Dept> deptList = sqlSession.selectList("Mapper.UserMapper.FindUserWithDept",20);
        System.out.println(deptList);
        sqlSession.close();
    }

    @Test
    public  void FindEmpWithDeptTest(){
        //    一对多嵌套查询
        SqlSession sqlSession = Mybatis.openSession();
        List<Dept> deptList = sqlSession.selectList("Mapper.UserMapper.FindEmpWithDept",10);
        System.out.println(deptList);
        sqlSession.close();
    }
}

注意：工具类和配置文件这里没有给出，需要自行配置。

查询结果

一对一

一对多查询：