MyBatis查询结果处理

一直使用mybatis-plus和jpa，好久没有使用只集成mybatis的项目，这里记录一下，方便以后快速使用

使用上次创建的项目，地址：SpringBoot集成MyBatis

在mybatis中经常使用到1对多，多对1的查询，往往会把查询结果进行聚合，例如查询雇员和所在的公司，或者查询公司和公司雇员信息，会用到mybatis的xml关键字association和collection，我们来看看他们的使用。

1、初始化

创建雇员表和公司表

DROP TABLE IF EXISTS `corporation`;
CREATE TABLE `corporation`  (
  `id` int(10) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of corporation
-- ----------------------------
INSERT INTO `corporation` VALUES (1, '阿里巴巴');
INSERT INTO `corporation` VALUES (2, '美团');

-- ----------------------------
-- Table structure for employ
-- ----------------------------
DROP TABLE IF EXISTS `employ`;
CREATE TABLE `employ`  (
  `id` int(10) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `c_id` int(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employ
-- ----------------------------
INSERT INTO `employ` VALUES (1, '张三', 1);
INSERT INTO `employ` VALUES (2, '李四', 2);
INSERT INTO `employ` VALUES (3, '王五', 2);
INSERT INTO `employ` VALUES (4, '赵六', 1);
INSERT INTO `employ` VALUES (5, '孙七', 1);
INSERT INTO `employ` VALUES (6, '周八', 1);

创建corporation和employ实体类xml，代码如下

Corporation实体类

@Data
public class Corporation implements Serializable {
    /**
     * 
     */
    private Integer id;

    /**
     * 
     */
    private String name;

    private static final long serialVersionUID = 1L;
}

Employ实体类

@Data
public class Employ implements Serializable {
    /**
     * 
     */
    private Integer id;

    /**
     * 
     */
    private String name;

    /**
     * 
     */
    private Integer cId;

    private static final long serialVersionUID = 1L;
}

EmployMapper

public interface EmployMapper {

}

CorporationMapper

public interface CorporationMapper {

}

CorporationMapper.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="cn.amoqi.cloud.mapper.CorporationMapper">

    <resultMap id="BaseResultMap" type="cn.amoqi.cloud.entity.Corporation">
            <id property="id" column="id" jdbcType="INTEGER"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
    </resultMap>

</mapper>

EmployMapper.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="cn.amoqi.cloud.mapper.EmployMapper">

    <resultMap id="BaseResultMap" type="cn.amoqi.cloud.entity.Employ">
            <id property="id" column="id" jdbcType="INTEGER"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
            <result property="cId" column="c_id" jdbcType="INTEGER"/>
    </resultMap>
</mapper>

2、一对一查询、多对一查询

查询雇员列表、里面包含公司的信息

创建结果类EmployCorporation.java，内容如下

@Data
public class EmployCorporation {
    /**
     *
     */
    private Integer id;

    /**
     *
     */
    private String name;

    /**
     *
     */
    private Integer cId;

    private Corporation cName;
}

2.1 按照查询嵌套处理

非关联查询处理，EmployMapper.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="cn.amoqi.cloud.mapper.EmployMapper">

    <resultMap id="BaseResultMap" type="cn.amoqi.cloud.entity.Employ">
            <id property="id" column="id" jdbcType="INTEGER"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
            <result property="cId" column="c_id" jdbcType="INTEGER"/>
    </resultMap>

    <select id="selectEmployCorporation" resultMap="EmployCorporationResultMap">
        select * from employ
    </select>
    
    
    <resultMap id="EmployCorporationResultMap" type="cn.amoqi.cloud.pojo.EmployCorporation">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <result property="cId" column="c_id"></result>
        <association property="corporation" column="c_id" javaType="Corporation" select="selectCorporationById" />
    </resultMap>

    <select id="selectCorporationById" resultType="Corporation">
        select * from corporation where id = #{cId}
    </select>
</mapper>

EmployMapper文件

public interface EmployMapper {
    List<EmployCorporation> selectEmployCorporation();
}

测试类，执行后可以正常打印信息

@SpringBootTest
class MybatisCreateApplicationTests {

    @Autowired
    private EmployMapper employMapper;

    @Test
    public void test1(){
        List<EmployCorporation> employCorporations = employMapper.selectEmployCorporation();
        employCorporations.forEach(System.out::println);
    }
}

2.2 按照结果嵌套处理

2.1 为非关联查询，也可以使用join等关联进行查询

在EmployMapper.xml加入以下关联查询

    <select id="selectEmployCorporation2" resultMap="EmployCorporationResultMap2">
        select e.id id,e.name name,e.c_id cId,c.name cName from employ e,corporation c where e.c_id = c.id
    </select>
    <resultMap id="EmployCorporationResultMap2" type="cn.amoqi.cloud.pojo.EmployCorporation">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <result property="cId" column="cId"></result>
        <association property="corporation" javaType="Corporation">
            <result property="id" column="cId"></result>
            <result property="name" column="cName"></result>
        </association>
    </resultMap>

在EmployMapper.java 中加入接口方法

List<EmployCorporation> selectEmployCorporation2();

查询测试

    @Test
    public void test2(){
        List<EmployCorporation> employCorporations = employMapper.selectEmployCorporation2();
        employCorporations.forEach(System.out::println);
    }

3、一对多查询

创建CorporationEmploy类，如下

@Data
public class CorporationEmploy {
    /**
     *
     */
    private Integer id;

    /**
     *
     */
    private String name;

    private List<Employ> employs;
}

3.1 按照查询嵌套处理

CorporationMapper.xml

    <select id="selectCorporationEmploy" resultMap="CorporationEmployResultMap">
        select * from corporation
    </select>

    <resultMap id="CorporationEmployResultMap" type="cn.amoqi.cloud.pojo.CorporationEmploy">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <collection property="employs" column="id" javaType="ArrayList" ofType="cn.amoqi.cloud.entity.Employ" select="selectEmployListOfCorporation"/>
    </resultMap>

    <select id="selectEmployListOfCorporation" resultType="cn.amoqi.cloud.entity.Employ">
        select id,name,c_id cId from employ where c_id = #{id}
    </select>

CorporationMapper.java

public interface CorporationMapper {
    List<CorporationEmploy> selectCorporationEmploy();
}

测试

    @Test
    public void test3(){
        List<CorporationEmploy> corporationEmploys = corporationMapper.selectCorporationEmploy();
        System.out.println(JSON.toJSONString(corporationEmploys));
    }

3.2 按照结果嵌套处理

CorporationMapper.xml

<select id="selectCorporationEmploy2" resultMap="CorporationEmployResultMap2">
        select c.id id,c.name name,e.id eId,e.name eName from corporation c left join employ e on c.id = e.c_id
    </select>
    <resultMap id="CorporationEmployResultMap2" type="cn.amoqi.cloud.pojo.CorporationEmploy">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <collection property="employs" ofType="cn.amoqi.cloud.entity.Employ">
            <result property="id" column="eId"></result>
            <result property="name" column="eName"></result>
        </collection>
    </resultMap>

CorporationMapper.java

List<CorporationEmploy> selectCorporationEmploy2();

测试

    @Test
    public void test4(){
        List<CorporationEmploy> corporationEmploys = corporationMapper.selectCorporationEmploy2();
        System.out.println(JSON.toJSONString(corporationEmploys));
    }