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;}@Overridepublic String toString() {return "Dept{" +"id=" + id +", name='" + name + '\'' +", employees=" + employees +'}';}
}

java关联关系、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;}@Overridepublic 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 mapperPUBLIC "-//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.deptnoD_ID,D.deptname D_NAME, E.empno E_ID,E.empname E_NAME,E.salary E_SALARYfrom Dept  Dleft 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 {@Autowiredprivate DeptDao deptDao;@Overridepublic List<Dept> getDeptWithEmployees() {  //主要看这个return deptDao.getDeptWithEmployees();}@Overridepublic List<Dept> getDepts() {return deptDao.getDepts();}@Overridepublic 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 {@Autowiredprivate DeptService deptService;//    @Test
//    public void addDeptTest(){
//        Dept dept = new Dept();
//        dept.setName("行政部");
//        int r = deptService.addDept(dept);
//        System.out.println("受影响行数：" + r);
//    }@Testpublic void getDeptWithEmployeesTest(){  //T1List<Dept>  depts = deptService.getDeptWithEmployees();for (Dept d :depts){System.out.println(d);}}@Testpublic void getDeptsTest(){ //T2List<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=[]}

Mybatis框架，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}