MyBatis-ResultMap处理映射关系 多对一和一对多

添加依赖

  <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.9</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.9</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
    </dependencies>

 添加日志输出

log4j.logger.com.chen=TRACE   com.chen 为自己包名 例如com.siwen

# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com.chen=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

log4j.rootLogger=DEBUG,Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.org.apache=INFO

 

1.jdbc配置

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/system?characterEncoding=UTF-8
jdbc.username=数据库账户
jdbc.password=密码

2.mybatis-config.xml配置

<?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="jdbc.properties"></properties>
<!--    <settings>-->
<!--&lt;!&ndash;        将下划线映射为驼峰&ndash;&gt;-->
<!--        <setting name="mapUnderscoreToCameCase" value="true"/>-->
<!--    </settings>-->
    <typeAliases>
        <package name="com.chen.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>

    </environments>
    <mappers>
        <mapper resource="mapper/EmpMapper.xml"></mapper>
    </mappers>
</configuration>

3.实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Emp implements Serializable {
    private Integer emp_id;
    private String emp_name;
    private Integer age;
    private String gender;
    private Dept dept;
}


@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept implements Serializable {
    private Integer dept_id;
    private String dept_name;
}

4.对应接口

public interface EmpDao {
    //分布查询
    //查询部门id
    Emp findById(@Param("emp_id") Integer emp_id);

    Dept findByDeptId(@Param("dept_id")Integer dept_id);
}

5.Mapper文件

ResultMap处理映射关系

<!--resultMap:设置自定义映射
id:映射的唯一标识
type：处理映射关系的实体类对象的类型
id:处理主键和实体类中属性的映射关系
column：设置映射关系字段名 必须是sql中查询出的字段名
property：设置映射关系属性的属性名 必须是实体类中的属性名
-->
    <resultMap id="RestultMapEmp" type="Emp">
        <id column="emp_id" property="emp_id"></id>
        <result column="emp_name" property="emp_name"></result>
        <result column="age" property="age"></result>
        <result column="gender" property="gender"></result>
    </resultMap>
    <select id="findById" resultMap="RestultMapEmp">
        select * from emp where emp_id=#{emp_id}
    </select>

1.处理多对一映射关系

1.1级联

    <resultMap id="RestultMapEmp" type="Emp">
        <id column="emp_id" property="emp_id"></id>
        <result column="emp_name" property="emp_name"></result>
        <result column="age" property="age"></result>
        <result column="gender" property="gender"></result>
        <result column="dept_id" property="dept.dept_id"></result>
        <result column="dept_name" property="dept.dept_name"></result>
    </resultMap>
    <select id="findById" resultMap="RestultMapEmp">
SELECT * from emp e LEFT JOIN dept d on e.dpet_id=d.dept_id
where e.emp_id=#{emp_id}
    </select>

1.2通过association

    <resultMap id="RestultMapEmp" type="Emp">
        <id column="emp_id" property="emp_id"></id>
        <result column="emp_name" property="emp_name"></result>
        <result column="age" property="age"></result>
        <result column="gender" property="gender"></result>
<!--        association:处理多对一的映射关系（处理实体类类型的属性）
            property：需要处理映射关系的属性名
            javaType：设置处理类型的属性-->
        <association property="dept" javaType="Dept">
            <id column="dpet_id" property="dept_id"></id>
            <result column="dept_name" property="dept_name"></result>
        </association>
    </resultMap>
    <select id="findById" resultMap="RestultMapEmp">
SELECT * from emp e LEFT JOIN dept d on e.dpet_id=d.dept_id
where e.emp_id=#{emp_id}
    </select>

1.3分布查询

    <resultMap id="RestultMapEmp" type="Emp">
        <id column="emp_id" property="emp_id"></id>
        <result column="emp_name" property="emp_name"></result>
        <result column="age" property="age"></result>
        <result column="gender" property="gender"></result>
<!--        property：设置需要处理映射关系的属性的属性名-->
<!--        select：设置分布查询的唯一标识-->
<!--        column：将查询的某个字段作为分布查询的字段sql-->
        <association property="dept"
                     select="com.chen.dao.EmpDao.findByDeptId"
                     column="dept_id">
        </association>
    </resultMap>
<!--    <select id="findById" resultMap="RestultMapEmp">-->
<!--SELECT * from emp e LEFT JOIN dept d on e.dpet_id=d.dept_id-->
<!--where e.emp_id=#{emp_id}-->
<!--    </select>-->
    <select id="findById" resultMap="RestultMapEmp">
        select * from emp where emp_id=#{emp_id}
    </select>
    <select id="findByDeptId" resultType="Dept">
        select * from dept where dept_id=#{dept_id}
    </select>

测试

public class EmpTest {
    public static SqlSessionFactory ssf=null;
    static {
        try {
            //读取配置文件
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            //通过配置文件读取创建sql会话工厂
            ssf = new SqlSessionFactoryBuilder().build(is);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void findById(){
        SqlSession sqlSession = ssf.openSession(true);
        EmpDao mapper = sqlSession.getMapper(EmpDao.class);
        //Dept emp = mapper.findByDeptId(1);
        Emp emp = mapper.findById(1);
        System.out.println(emp);
    }
}

2.处理一对多映射关系 

这里通过查询一个部门下对应的员工为例子

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept implements Serializable {
    private Integer dept_id;
    private String dept_name;
//一个部门下对应多个员工
    private List<Emp> emps;
}

collection   通过集合处理一对多的映射关系

方法一:联查

 1.部门接口中定义查询方法  通过查询获得部门id

Dept findDeptIdAndEmp(@Param("dept_id") Integer dept_id);

 2.通过多表联查

<resultMap id="ResultDept" type="Dept">
    <id column="dept_id" property="dept_name"></id>
    <result column="dept_name" property="dept_name"></result>
    <collection property="emps"
                select="com.chen.dao.EmpDao.findByEmp"
                column="dept_id"></collection>

<!--    ofType设置集合类型属性存储的数据的类型-->
    <collection property="emps" ofType="Emp">
        <id column="emp_id" property="emp_id"></id>
        <result column="emp_name" property="emp_name"></result>
       <result column="age" property="age"></result>
        <result column="gender" property="gender"></result>
   </collection>
</resultMap>
<select id="findDeptIdAndEmp" resultMap="ResultDept">
select * from dept  left JOIN emp  on dept.dept_id=emp.dept_id
where dept.dept_id=#{dept_id}
    </select>

方法二分布查询

先找部门id通过部门id找到对应员工信息

DeptMapper.xml

<resultMap id="ResultDept" type="Dept">
    <id column="dept_id" property="dept_name"></id>
    <result column="dept_name" property="dept_name"></result>
    <collection property="emps"
                select="com.chen.dao.EmpDao.findByEmp"
                column="dept_id"></collection>
</resultMap>

    <select id="findDeptIdAndEmp" resultMap="ResultDept">
        select * from dept where dept_id=#{dept_id}
    </select>

List<Emp> findByEmp(@Param("dept_id") Integer dept_id);

 EmpMapper.xml

    <select id="findByEmp" resultType="Emp">
        select * from emp where dept_id=#{dept_id}
    </select>

 测试

    @Test
    public void findDeptById(){
        SqlSession sqlSession = ssf.openSession(true);
        DeptDao mapper = sqlSession.getMapper(DeptDao.class);
        Dept dept = mapper.findDeptIdAndEmp(1);
        System.out.println(dept);
    }