MyBatis多对一和一对多关系的处理

多对一关系的处理

前期准备

1. 数据库关系

可以看到employee是参照department表的，是多对一的关系

2. 实体类

   com.maple.pojo.Department

   @Data
   public class Department {
   	private Integer deptid;
   	private String department_name;
   }
   

   com.maple.pojo.Employee

   @Data
   public class Employee {
   private Integer empid;
   private String last_name;
   private String email;
   private Integer gender;
   private Integer deptid;
   private Date birth;
   }
   

   com.maple.entity.Department

   
   @Data
   public class Department extends com.maple.pojo.Department {
   	List<Employee> employees;
   
   	@Override
   	public String toString(){
   		return super.toString()+"employees=" + employees;
   	}
   }
   

   com.maple.entity.Employee

   @Data
   public class Employee extends com.maple.pojo.Employee {
   	private Department department;
   
   	@Override
   	public String toString(){
   		return super.toString()+"department=" + department;
   	}
   }
   
   

3. 核心配置文件

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE configuration
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-config.dtd">
   <configuration>
       <properties resource="DB.properties"></properties>
       <settings>
           <setting name="logImpl" value="LOG4J"/>
       </settings>
       <environments default="development">
           <environment id="development">
               <transactionManager type="JDBC"/>
               <dataSource type="POOLED">
                   <property name="driver" value="${driver}"/>
                   <property name="url" value="${url}"/>
                   <property name="username" value="${username}"/>
                   <property name="password" value="${password}"/>
               </dataSource>
           </environment>
       </environments>
       <mappers>
           <mapper resource="com/maple/mapper/DepMapper.xml"/>
           <mapper resource="com/maple/mapper/EmpMapper.xml"/>
       </mappers>
   </configuration>
   

4. Mapper接口

   public interface EmpMapper {
   	List<Employee> queryEmpList();
   	Employee queryEmpById(Integer empid);
   }
   

5. 测试类

   @Test
   public void test2(){
       SqlSession sqlSession = MyBatisUtil.getSqlSession();
       EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
       List<Employee> empList = empMapper.queryEmpList();
       empList.forEach(emp-> System.out.println(emp));
   }
   @Test
   public void test3(){
       SqlSession sqlSession = MyBatisUtil.getSqlSession();
       EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
       Employee employee = empMapper.queryEmpById(1001);
       System.out.println(employee);
   }
   

使用resultType返回类型查询

mapper文件的select

    <select id="queryEmpList" resultType="com.maple.entity.Employee">
        select * from mybatis.employee,mybatis.department 
        where employee.deptid=department.deptid
        # select * from mybatis.employee
    </select>

执行结果

可以看到department并没有映射到结果

解决方法

1. 按照结果嵌套处理(使用resultMap返回类型，通过子查询获取department)

   • EmpMapper.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.maple.mapper.EmpMapper">
         <!--  在核心配置文件中用<typeAliases>导入Employee所在包  -->
         <resultMap id="emp" type="com.maple.entity.Employee">
             <!-- 省略了能自动映射的列  -->
             <!-- 复杂的属性需要单独处理 对象:association 集合:collection -->
             <association property="department" column="deptid" javaType="com.maple.pojo.Department" select="queryDept"/>
         </resultMap>
     
         <select id="queryEmpList" resultMap="emp">
             select *
             from mybatis.employee
         </select>
     
         <select id="queryDept" resultType="com.maple.pojo.Department">
             select *
             from mybatis.department
             where deptid = #{deptid}
         </select>
     </mapper>
     

   • 执行结果

     

2. 按照结果嵌套处理(使用连表查询)

   EmpMapper.xml核心代码

   	<select id="queryEmpById" resultMap="emp2" parameterType="int">
           select empid, last_name, email, gender, employee.deptid, birth
                , department_name
           from mybatis.department ,mybatis.employee
           where employee.deptid=department.deptid and empid=#{empid}
       </select>
   
       <resultMap id="emp2" type="com.maple.entity.Employee">
           <result property="empid" column="empid"/>
           <result property="last_name" column="last_name"/>
           <result property="email" column="email"/>
           <result property="gender" column="gender"/>
           <result property="deptid" column="deptid"/>
           <result property="birth" column="birth"/>
           <association property="department" javaType="com.maple.pojo.Department">
               <result property="deptid" column="deptid"/>
               <result property="department_name" column="department_name"/>
           </association>
       </resultMap>
   

   运行结果

一对多关系的处理

接口

public interface DepMappper {
	List<Department> queryDepList();
	List<Department> queryDepById(@Param("deptid") int deptid);
}

DepMappper.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.maple.mapper.DepMappper">
    <resultMap id="dept" type="com.maple.entity.Department">
        <result property="deptid" column="deptid"/>
        <result property="department_name" column="department_name"/>
        <collection property="employees" ofType="com.maple.pojo.Employee">
            <result property="empid" column="empid"/>
            <result property="last_name" column="last_name"/>
            <result property="email" column="email"/>
            <result property="gender" column="gender"/>
            <result property="deptid" column="deptid"/>
            <result property="birth" column="birth"/>
        </collection>
    </resultMap>

    <select id="queryDepList" resultMap="dept">
        select d.department_name,e.* from mybatis.department d,mybatis.employee e where e.deptid=d.deptid
    </select>

    <select id="queryDepById" resultMap="dept">
        select d.department_name,e.* from mybatis.department d,mybatis.employee e where e.deptid=d.deptid and d.deptid=#{deptid}
    </select>
</mapper>

测试类

@Test
	public void test3(){
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		DepMappper depMappper = sqlSession.getMapper(DepMappper.class);
		List<Department> deptList = depMappper.queryDepList();
		deptList.forEach(dept-> System.out.println(dept));
	}

	@Test
	public void test4(){
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		DepMappper depMappper = sqlSession.getMapper(DepMappper.class);
		List<Department> deptList = depMappper.queryDepById(102);
		deptList.forEach(dept-> System.out.println(dept));
	}

运行结果