mybatis的一对一和一对多查询

一对一

需求：查询出员工的部门+员工的信息（一个员工对应一个部门）

1、建表

　　　　　　　　　部门表（department）

 

 

 　　　　　　　　　　员工表（employee）

 

 

 2、新建两个类（Department和Employee）和接口

 

 

 

public class Department {
    private Integer deptId;
    private String deptName;
    private LocalDate createDate = LocalDate.now();
    private String deptLoc;

　　

public class Employee {
    private Integer empId;
    private String username;
    private String password;
    private String realName;
    private LocalDate bornDate;
    private LocalDate hireDate=LocalDate.now();
    private float salary;
　　 private Department department;

　　

public interface EmployeeMapper {
    /**
     * create by: 
     * description:根据id查询员工信息
     * create time: 2020/8/11
     *
      * @Param: null
     * @return
     */
    Employee efindById(int id);
}

 

3、配置映射表、

对于结果的处理有两种方式：嵌套结果、嵌套查询

嵌套结果

<!--嵌套结果-->
    <resultMap id="employee" type="Employee">
        <id property="empId" column="emp_id"></id>
        <result property="hireDate" column="entry_date"></result>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
        <result property="bornDate" column="born_date"></result>
        <result property="salary" column="salary"></result>
        <!--assocaition来映射一对一的关联关系-->
        <association property="department" column="dept_id" javaType="Department">
            <id property="deptId" column="dept_id"></id>
            <result property="deptName" column="dept_name"></result>
            <result property="createDate" column="create_Date"></result>
            <result property="deptLoc" column="dept_loc"></result>
        </association>
    </resultMap>
    <select id="efindById" parameterType="int" resultMap="employee">
       select d.* ,e.* from department d,employee e where d.dept_id=e.dept_id and e.emp_id=#{empId};
    </select>

嵌套查询

   <!--嵌套查询-->
    <select id="efindById" resultMap="employee2">
        select emp_id,entry_date,username,password,realname,dept_id from employee where emp_id = #{id}
    </select>
    <resultMap id="employee2" type="Employee">
        <id property="empId" column="emp_id"></id>
        <result property="hireDate" column="entry_date"></result>
        <association property="department" select="edu.cduestc.book.Dao.DepartmentDao.findById" column="dept_id"> //这个column表示传递的参数。因为findById查询要使用dept_id这个值
        </association>
    </resultMap>
    <select id="findById" resultMap="employee2">
        select * from department where dept_id = #{dept_id}
    </select>

 4、测试

 

一对多

需求：查询一个部门下有多少员工

1、表和上面一样不变

2、建立类和接口方法

　

public class Department {
    private Integer deptId;
    private String deptName;
    private LocalDate createDate = LocalDate.now();
    private String deptLoc;
    private List<Employee> employees;

public class Employee {
    private Integer empId;
    private String username;
    private String password;
    private String realName;
    private LocalDate bornDate;
    private LocalDate hireDate=LocalDate.now();
    private float salary;

public interface DepartmentDao {
         Department findAllEmployee(int dept_id);
         Department findAllEmployee2(int dept_id);
4 }    

3、配置xml文件

　　嵌套结果

<!--嵌套结果查询-->
    <select id="findAllEmployee" resultMap="getEmployeelist">
        select e.*,d.dept_name 
            from employee e,department d 
                where e.dept_id=d.dept_id 
                    and e.dept_id=#{dept_id}
    </select>
    <resultMap id="getEmployeelist" type="Department">
        <id property="deptId" column="dept_id"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="employees" ofType="Employee">
            <id property="empId" column="emp_id"></id>
            <result property="username" column="username"></result>
            <result property="password" column="password"></result>
            <result property="realName" column="realname"></result>
            <result property="hireDate" column="entry_date"></result>
            <result property="bornDate" column="born_date"></result>
            <result property="salary" column="salary"></result>
        </collection>
    </resultMap>

嵌套查询

<!--    嵌套查询-->
    <select id="findAllEmployee2" resultMap="getEmployeelist2" parameterType="int">
        select dept_id,dept_name
            from department
                where dept_id=#{dept_id} 
    </select>
    <select id="getEmployees" resultType="Employee" parameterType="int">
        select *
            from employee
                where dept_id=#{dept_id}
    </select>
    <resultMap id="getEmployeelist2" type="Department">
        <id property="deptId" column="dept_id"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="employees" ofType="Employee" column="dept_id" select="getEmployees">

        </collection>
    </resultMap>

 

如果不太记得resultMap的具体使用可以参考以下的介绍

　　

<!--column不做限制，可以为任意表的字段，而property须为type 定义的pojo属性-->
<resultMap id="唯一的标识" type="映射的pojo对象">
  <id column="表的主键字段，或者可以为查询语句中的别名字段" jdbcType="字段类型" property="映射pojo对象的主键属性" />
  <result column="表的一个字段（可以为任意表的一个字段）" jdbcType="字段类型" property="映射到pojo对象的一个属性（须为type定义的pojo对象中的一个属性）"/>
  <association property="pojo的一个对象属性" javaType="pojo关联的pojo对象">
    <id column="关联pojo对象对应表的主键字段" jdbcType="字段类型" property="关联pojo对象的主席属性"/>
    <result  column="任意表的字段" jdbcType="字段类型" property="关联pojo对象的属性"/>
  </association>
  <!-- 集合中的property须为oftype定义的pojo对象的属性-->
  <collection property="pojo的集合属性" ofType="集合中的pojo对象">
    <id column="集合中pojo对象对应的表的主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" />
    <result column="可以为任意表的字段" jdbcType="字段类型" property="集合中的pojo对象的属性" />  
  </collection>
</resultMap>

collection标签使用的是嵌套查询
 <collection column="传递给嵌套查询语句的字段参数" property="pojo对象中集合属性" ofType="集合属性中的pojo对象" select="嵌套的查询语句" > 
 </collection>

 

参考：

https://blog.51cto.com/wuqinglong/1726152

https://blog.51cto.com/wuqinglong/1726099

https://www.cnblogs.com/kenhome/p/7764398.html