Mybatis之返回封装类型数据

明湖起风了

已于 2022-10-25 16:15:47 修改

阅读量1k

点赞数

分类专栏： myBatis 文章标签： mybatis 数据库 mysql

于 2022-10-25 16:04:56 首次发布

原文链接：https://frxcat.fun/pages/c86777/

版权

myBatis 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

建表

CREATE TABLE tbl_employee(
	id INT(11) PRIMARY KEY AUTO_INCREMENT,
	last_name VARCHAR(255),
	gender CHAR(1),
	email VARCHAR(255)
	)
INSERT INTO tbl_employee(last_name,gender,email) 
	VALUES('tom','0','tom123@qq.com')	

CREATE TABLE tbl_dept(
	id INT(11) PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR(255)
	)
INSERT INTO tbl_dept(dept_name)
	VALUES('开发部')
	
INSERT INTO tbl_dept(dept_name)
	VALUES('测试部')
	
SELECT * FROM tbl_dept	

ALTER TABLE tbl_employee ADD COLUMN d_id INT(11);
ALTER TABLE tbl_employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(d_id) REFERENCES tbl_dept(id)

建javabean，根据返回数据结构不同，可能需要修改javabean，根据实际情况确定，此处只作为参考。

public class Employee {

    private Integer id;
    private String lastName;
    private String email;
    private String gender;

    public Integer getId() {
        return id;
    }

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

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }
}

public class Department {

    private Integer id;
    private String departmentName;

    public Department(Integer id, String departmentName) {
        this.id = id;
        this.departmentName = departmentName;
    }

    public Department() {
    }

    public Integer getId() {
        return id;
    }

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

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                '}';
    }
}

1. 返回List

EmployeeMapper.java

public interface EmployeeMapper {
    public List<Employee> getEmpsByLastNameLike(String LastName);
}

EmployeeMapper.xml

    <!--public List<Employee> getEmpsByLastNameLike(String LastName);-->
    <!--resultType:如果返回的是集合，要写集合中元素的类型-->
    <select id="getEmpsByLastNameLike" resultType="Employee">
        Select * from tbl_employee where last_name like #{lastName}
    </select>

2. 记录封装map

EmployeeMapper.java

public interface EmployeeMapper {

    //多条记录封装一个map,Map<Integer,Employee>:键是这条记录的主键，值是记录封装后的javabean
    //@MapKey("id"):告诉mybatis,封装map的时候使用哪个属性作为map的key
    @MapKey("id")
    public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName);

    //返回一条记录map,key就是列名，值就是对应的值
    public Map<String,Object> getEmpByIdReturnMap(Integer id);
}

EmployeeMapper.xml

<!--public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName);-->
    <select id="getEmpByLastNameLikeReturnMap" resultType="com.frx01.mybatis.bean.Employee">
        select * from tbl_employee where last_name like #{lastName}
    </select>

    <!--public Map<String,Object> getEmpByIdReturnMap(Integer id);-->
    <select id="getEmpByIdReturnMap" resultType="map">
        select * from tbl_employee where id=#{id}
    </select>

3. 自定义结果映射规则

resultMap

EmployeeMapperPlus.java

/**
 * @author frx
 * @version 1.0
 * @date 2022/2/11  15:25
 */
public interface EmployeeMapperPlus {

    public Employee getEmpById(Integer id);
}

EmployeeMapperPlus.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.frx01.mybatis.dao.EmployeeMapperPlus">

    <!--自定义某个javabean的封装规则
        type:自定义规则的java类型
        id:唯一id方便引用-->
    <resultMap type="com.frx01.mybatis.bean.Employee" id="MyEmp">
        <!--指定主键列的封装规则
        id定义主键会底层优化;
        column:指定哪一列
        property:指定对应的javabean属性-->
        <id column="id" property="id"></id>
        <!--定义普通列封装规则-->
        <result column="last_name" property="lastName"/>
        <!--其他不指定的列会自动封装;我们只要写resultMap就把全部的映射规则都写上-->
    </resultMap>

    <!--resultMap:自定义结果集映射规则-->
    <!--public Employee getEmpById(Integer id);-->
    <select id="getEmpById" resultMap="MyEmp">
        select * from tbl_employee where id=#{id}

    </select>
</mapper>

关联查询-级联属性封装结果

EmployeeMapperPlus.java

public interface EmployeeMapperPlus {

    public Employee getEmpAndDept(Integer id);
}

EmployeeMapperPlus.xml

    <!--
        场景一:
            查询Employee的同时查询员工对应的部门
            Employee===Department
            一个员工有之对应的部门信息:
            id last_name gender email d_id dept_name
            -->
    <!--
        联合查询:级联属性进行封装结果集-->
    <resultMap id="MyDifEmp" type="com.frx01.mybatis.bean.Employee">
        <id column="tbl_employee.id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="d_id" property="dept.id"/>
        <result column="dept_name" property="dept.departmentName"/>

    </resultMap>
    <!--public Employee getEmpAndDept(Integer id);-->
    <select id="getEmpAndDept" resultMap="MyDifEmp">
        SELECT tbl_employee.id,last_name,gender,tbl_employee.d_id,dept_name FROM tbl_employee LEFT JOIN tbl_dept ON tbl_employee.id=tbl_dept.id
            WHERE tbl_employee.id=tbl_dept.id AND tbl_employee.id=#{id}
    </select>

关联查询-association定义关联对象封装规则

EmployeeMapperPlus.java

public interface EmployeeMapperPlus {

    //联合查询：级联属性封装结果集
    public Employee getEmpAndDept2(Integer id);

EmployeeMapperPlus.xml

    <!--使用association定义单个对象的封装规则：-->
    </resultMap>
    <resultMap id="MyDifEmp2" type="com.frx01.mybatis.bean.Employee">
        <id column="tbl_employee.id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>

        <!--association可以指定联合的javabean对象
            property="dept" 指定哪个属性是联合的对象
            javaType:指定这个属性对象的类型[不能省略]-->
        <association property="dept" javaType="com.frx01.mybatis.bean.Department">
            <id column="d_id" property="id"/>
            <result column="dept_name" property="departmentName"></result>
        </association>
    <!--public Employee getEmpAndDept2(Integer id);-->
    <select id="getEmpAndDept2" resultMap="MyDifEmp2">
        SELECT tbl_employee.id,last_name,gender,tbl_employee.d_id,dept_name FROM tbl_employee LEFT JOIN tbl_dept ON tbl_employee.id=tbl_dept.id
            WHERE tbl_employee.id=tbl_dept.id AND tbl_employee.id=#{id}
    </select>

关联查询-association分步查询

DepartmentMapper.java

public interface DepartmentMapper {

    public Department getDeptById(Integer id);
}

EmployeeMapperPlus.java

public interface EmployeeMapperPlus {

    public Employee getEmpByIdStep(Integer id);
}

DepartmentMapper.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.frx01.mybatis.dao.DepartmentMapper">

    <!--public Department getDeptById(Integer id);-->
    <select id="getDeptById" resultType="com.frx01.mybatis.bean.Department">
        select id,dept_name departmentName from tbl_dept where id=#{id}
    </select>
</mapper>

EmployeeMapperPlus.xml

   <!--使用association进行分布查询:
        1.先按照员工ID查询员工信息
        2.根据查询员工信息中的d_id值去部门表查出部门信息
        3.部门设置到员工；-->
    <!--    id  last_name  gender  email    d_id  -->
    <resultMap id="MyEmpByStep" type="com.frx01.mybatis.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"></result>
        <result column="email" property="email"></result>
        <result column="gender" property="gender"></result>
        <!--association定义关联的对象的封装规则
            select:表明当前属性调用select指定的方法查出的结果
            columnL:指定将哪一列的值传给这个方法
            流程:使用select指定的方法(传入column指定的这列参数的值)查出对象，并封装给property属性
            -->
        <association property="dept" select="com.frx01.mybatis.dao.DepartmentMapper.getDeptById"
                column="d_id">
        </association>
    </resultMap>
    <!--public Employee getEmpByIdStep(Integer id);-->
    <select id="getEmpByIdStep" resultMap="MyEmpByStep">
        select * from tbl_employee where id=#{id}
    </select>

关联查询-分步查询&延迟加载

我们每次查询Employee对象的时候，都将一起查询出来。部门信息在我们使用的时候再去查询；分段查询的基础之上加上两个配置：

在全局配置文件中配置，实现懒加载

mybatis-config.xml

<configuration>
	...
	<settings>
		...
		<!--显示的指定每个我们需要更改的配置的值，即使他是默认的。防止版本更新带来的问题  -->
		<setting name="lazyLoadingEnabled" value="true"/>
		<setting name="aggressiveLazyLoading" value="false"/>
	</settings>

设置名	描述	有效值	默认值
lazyLoadingEnabled	延迟加载的全局开关。当开启时，所有关联对象都会延迟加载。特定关联关系中可通过设置 `fetchType` 属性来覆盖该项的开关状态	true\|false	false
aggressiveLazyLoading	开启时，任一方法的调用都会加载该对象的所有延迟加载属性。否则，每个延迟加载属性会按需加载（参考 `lazyLoadTriggerMethods`)	true\|false	false在 3.4.1 及之前的版本中默认为 true）

关联查询-collection定义关联集合封装规则

DepartmentMapper.java

public interface DepartmentMapper {

    public Department getDeptByIdPlus(Integer id);
}

DepartmentMapper.xml

<mapper namespace="com.frx01.mybatis.dao.DepartmentMapper">
    <!--
        collection嵌套结果集的方式，定义关联的集合类型元素的封装规则-->
    <!--
<!--
    public class Department {
        private Integer id;
        private String departmentName;
        private List<Employee> emps;
        }
    did  dept_name ||  eid  last_name  email   gender -->
    <resultMap id="MyDept" type="com.frx01.mybatis.bean.Department">
        <id column="did" property="id"/>
        <result column="dept_name" property="departmentName"/>
        <!--
            collection定义集合类型的属性的封装规则
            ofType:指定集合元素的类型
            -->
        <collection property="emps" ofType="com.frx01.mybatis.bean.Employee">
            <!--定义集合中元素的封装规则-->
            <id column="eid" property="id"/>
            <result column="last_name" property="lastName"/>
            <result column="email" property="email"/>
            <result column="gender" property="gender"/>
        </collection>
    </resultMap>
    <!--public Department getDeptByIdPlus(Integer id);-->
    <select id="getDeptByIdPlus" resultMap="MyDept">
        SELECT d.id AS did ,d.dept_name AS dept_name,
               e.id AS eid,e.last_name AS last_name,
               e.email AS email,e.gender AS gender
        FROM tbl_dept AS d
                 LEFT JOIN tbl_employee AS e
                           ON d.id=e.d_id
        WHERE d.id=#{id}
    </select>

关联查询-collection分步查询&延迟加载

DepartmentMapper.java

public interface DepartmentMapper {

    public List<Employee> getEmpsByDeptId(Integer deptId);
    public Department getDeptByIdStep(Integer id);
}

EmployeeMapper.xml

   <!--public List<Employee> getEmpsByDeptId(Integer deptId);-->
    <select id="getEmpsByDeptId" resultType="com.frx01.mybatis.bean.Employee">
        select * from tbl_employee where d_id=#{deptId}
    </select>

DepartmentMapper.xml

	<!--分段查询-->
    <resultMap id="MyDeptStep" type="com.frx01.mybatis.bean.Department">
        <id column="id" property="id"/>
        <id column="dept_name" property="departmentName"/>
        <collection property="emps"
                    select="com.frx01.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
                    column="id"></collection>
    </resultMap>
    <!--public Department getDeptByIdStep(Integer id);-->
    <select id="getDeptByIdStep" resultMap="MyDeptStep">
        select id,dept_name departmentName from tbl_dept where id=#{id}
    </select>

分步查询传递多列值&fetchType

扩展：

多列的值传递过去：
- 将多列的值封装map传递；column="{key1=column1,key2=column2}"
fetchType=“lazy”：表示使用延迟加载；
- lazy：延迟
- eager：立即

DepartmentMapper.xml

  <!--collection分段查询-->
    <resultMap id="MyDeptStep" type="com.frx01.mybatis.bean.Department">
        <id column="id" property="id"/>
        <id column="dept_name" property="departmentName"/>
        <collection property="emps"
                    select="com.frx01.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
                    column="{deptId=id}" fetchType="lazy"></collection>
    </resultMap>
    <!--public Department getDeptByIdStep(Integer id);-->
    <select id="getDeptByIdStep" resultMap="MyDeptStep">
        select id,dept_name departmentName from tbl_dept where id=#{id}
    </select>

discriminator鉴别器

EmployeeMapperPlus.java

public interface EmployeeMapperPlus {

    //带有鉴别器的
    public List<Employee> getEmpsWithDiscriminator();
}

EmployeeMapperPlus.xml

    <!--<discriminator javaType=''></discriminator>
        鉴别器:mybatis可以使用discriminator判断某列的值，然后改变某列的值改变封装行为
        封装Employee:
            如果查出的是女生:就把部门信息查询出来，否则不查询;
            如果是男生，把last_name这一列的值赋值给email;
      -->
    <resultMap id="MyEmpDis" type="com.frx01.mybatis.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"></result>
        <result column="email" property="email"></result>
        <result column="gender" property="gender"></result>
        <!--column:指定要判断的列
            javaType:列值对应的java类型-->
        <discriminator javaType="string" column="gender">
            <!--女生 resultType:指定封装的结果类型 不能缺少。/resultMap -->
            <case value="0" resultType="com.frx01.mybatis.bean.Employee">
            <association property="dept"
                         select="com.frx01.mybatis.dao.DepartmentMapper.getDeptById"
                         column="d_id" fetchType="eager">
            </association>
            </case>
            <!--男生 ;把last_name这一列的值赋值给email;-->
            <case value="1" resultType="com.frx01.mybatis.bean.Employee">
                <id column="id" property="id"/>
                <result column="last_name" property="lastName"></result>
                <result column="last_name" property="email"></result>
                <result column="gender" property="gender"></result>
            </case>
        </discriminator>
        </resultMap>

    <!--public List<Employee> getEmpsWithDiscriminator();-->
    <select id="getEmpsWithDiscriminator" resultMap="MyEmpDis">
        select * from tbl_employee limit 10
    </select>

原文连接：XML 映射文件 | xustudyxu's Blog (frxcat.fun)