MyBatis多对一关系的处理

多对一关系的处理

前期准备

  1. 数据库关系

MyBatis多对一关系的处理

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

  1. 实体类

    Department

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

    Employee

    @Data
    public class Employee {
    private Integer empid;
    private String last_name;
    private String email;
    private Integer gender;
    //private Integer deptid;
    private Department department;
    private Date birth;
    }
    
  2. 核心配置文件

    <?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>
        <typeAliases>
            <package name="com.maple.pojo"/>
        </typeAliases>
        <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>
    
  3. 测试类

    @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

    <!--  在核心配置文件中用<typeAliases>导入Employee所在包  -->
    <select id="queryEmpList" resultType="Employee">
        select * from mybatis.employee,mybatis.department 
        where employee.deptid=department.deptid
        # select * from mybatis.employee
    </select>

执行结果

MyBatis多对一关系的处理

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

解决方法

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

    • mapper文件

      <?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="Employee">
              <!-- 省略了能自动映射的列  -->
              <!-- 复杂的属性需要单独处理 对象:association 集合:collection -->
              <association property="department" column="deptid" javaType="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>
      
          <select id="queryEmpById" resultType="Employee">
      
          </select>
      
      </mapper>
      
    • 执行结果

      MyBatis多对一关系的处理

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

    mapper.xml核心代码

    	<select id="queryEmpById" resultMap="emp2" parameterType="int">
            select empid, last_name, email, gender, employee.deptid as edid, birth
                 ,department.deptid as ddid, department_name
            from mybatis.department ,mybatis.employee
            where employee.deptid=department.deptid and empid=#{empid}
        </select>
    
        <resultMap id="emp2" type="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="birth" column="birth"/>
            <association property="department" javaType="Department">
                <result property="deptid" column="ddid"/>
                <result property="department_name" column="department_name"/>
    
            </association>
        </resultMap>
    

    运行结果

MyBatis多对一关系的处理

上一篇:Java I/O流整理


下一篇:Mysql