Spring整合Mybatis之关联查询示例

Spring整合Mybatis之关联查询示例

目录结构：

Dept表：

employee表：

pojo包

Dept.java(实体类):

package com.xmm.springboot_lab.pojo;

import java.util.List;

public class Dept {
    private int id;
    private String name;
    private List<Employee> employees;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", employees=" + employees +
                '}';
    }
}

Employee.java(实体类)

package com.xmm.springboot_lab.pojo;

import org.springframework.stereotype.Repository;

import java.sql.Date;

public class Employee {
    private  String id;
    private String name;
    private Date hireDate;
    private float salary;
    private int deptId;
    private Dept dept;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public Date getHireDate() {
        return hireDate;
    }

    public void setHireDate(Date hireDate) {
        this.hireDate = hireDate;
    }

    public float getSalary() {
        return salary;
    }

    public void setSalary(float salary) {
        this.salary = salary;
    }

    public int getDeptId() {
        return deptId;
    }

    public void setDeptId(int deptId) {
        this.deptId = deptId;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", hireDate=" + hireDate +
                ", salary=" + salary +
                ", deptId=" + deptId +
                ", dept=" + dept +
                '}';
    }
}

Dao包

DeptDao.java

package com.xmm.springboot_lab.dao;

import com.xmm.springboot_lab.pojo.Dept;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;

@Repository
public interface DeptDao {
    public List<Dept> getDeptWithEmployees(); //主要看这个
    public List<Dept> getDepts();
    public int addDept(Dept dept);
}

DeptMapper.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="com.xmm.springboot_lab.dao.DeptDao">

	<insert id="addDept" parameterType="Dept">
		insert into dept (deptname) values(#{name});
	</insert>

	<!-- ===一对多查询（一个部门有多个员工）,这里使用的是左外连接==== -->
	<resultMap type="Dept" id="deptWithEmployeesMap">
		<id property="id" column="D_ID" />
		<result property="name" column="D_NAME" />
		<collection property="employees" javaType="java.util.ArrayList"
			ofType="Employee">
			<id property="id" column="E_ID" />
			<result property="name" column="E_NAME" />
			<result property="salary" column="E_SALARY" />
		</collection>
	</resultMap>
	<select id="getDeptWithEmployees" resultMap="deptWithEmployeesMap">
		select D.deptno
		D_ID,D.deptname D_NAME, E.empno E_ID,E.empname E_NAME,E.salary E_SALARY
		from Dept  D
		left join employee E on D.deptno = E.deptno;
	</select>

    <!-- ====只获取部门信息 -->
	<resultMap id="deptMap" type="Dept">
		<id property="id" column="deptno" />
		<result property="name" column="deptname" />
	</resultMap>
	<select id="getDepts" resultMap="deptMap">
		select * from dept;
	</select>


</mapper>

service包

package com.xmm.springboot_lab.service.impl;

import com.xmm.springboot_lab.dao.DeptDao;
import com.xmm.springboot_lab.pojo.Dept;
import com.xmm.springboot_lab.service.DeptService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    private DeptDao deptDao;
    @Override
    public List<Dept> getDeptWithEmployees() {  //主要看这个
        return deptDao.getDeptWithEmployees();
    }

    @Override
    public List<Dept> getDepts() {
        return deptDao.getDepts();
    }

    @Override
    public int addDept(Dept dept) {
        return deptDao.addDept(dept);
    }
}

Junit测试

package com.xmm.springboot_lab.service.impl;

import com.xmm.springboot_lab.pojo.Dept;
import com.xmm.springboot_lab.service.DeptService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;


@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class DeptServiceImplTest {

    @Autowired
    private DeptService deptService;

//    @Test
//    public void addDeptTest(){
//        Dept dept = new Dept();
//        dept.setName("行政部");
//        int r = deptService.addDept(dept);
//        System.out.println("受影响行数：" + r);
//    }

    @Test
    public void getDeptWithEmployeesTest(){  //T1
       List<Dept>  depts = deptService.getDeptWithEmployees();
       for (Dept d :depts){
           System.out.println(d);
       }
    }
    @Test
    public void getDeptsTest(){ //T2
        List<Dept>  depts = deptService.getDepts();
        for (Dept d :depts){
            System.out.println(d);
        }
    }
}

测试结果

T1结果：

Dept{id=1, name='财务部', employees=[Employee{id='1', name='李四', hireDate=null, salary=4000.0, deptId=0, dept=null}, Employee{id='2', name='张三', hireDate=null, salary=12000.0, deptId=0, dept=null}, Employee{id='3', name='赵六', hireDate=null, salary=7000.0, deptId=0, dept=null}]}
Dept{id=2, name='人事部', employees=[Employee{id='4', name='刘洪', hireDate=null, salary=5000.0, deptId=0, dept=null}]}
Dept{id=3, name='行政部', employees=[]}
Dept{id=4, name='大娃', employees=[]}

T2结果：

Dept{id=1, name='财务部', employees=null}
Dept{id=2, name='人事部', employees=null}
Dept{id=3, name='行政部', employees=null}
Dept{id=4, name='大娃', employees=null}