MybatisPlus基于Lambda 分组统计如何使用Function统计

转载

mob64ca14010a69 2024-09-25 14:31:24

文章标签 Mybatis分步查询 Mybatis分步级联查询 bc sql ci 文章分类 运维

一、分步查询

在使用到多表关联查询时，经常会碰到多对多，一对多和一对一的级联操作，在Mybatis中通过resultMap标签下的association和collection解决级联操作的问题。

级联操作分为：关联查询和分步关联查询，本文介绍分步查询，如需学习关联查询，请看上一篇博客。

二、表关系说明

user表结构和数据：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(60) DEFAULT NULL COMMENT '用户名称',
  `real_name` varchar(60) DEFAULT NULL COMMENT '真实名称',
  `sex` char(1) NOT NULL DEFAULT '1' COMMENT '性别',
  `mobile` varchar(20) DEFAULT NULL COMMENT '电话',
  `email` varchar(60) DEFAULT NULL COMMENT '邮箱',
  `note` varchar(200) DEFAULT NULL COMMENT '备注',
  `position_id` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_4` (`position_id`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('1', 'lison', '李小宇', '1', '18232344223', 'lison@qq.com', 'lison的备注', '1');
INSERT INTO `user` VALUES ('2', 'james', '陈大雷', '1', '18454656125', 'james@qq.com', 'james的备注', '2');
INSERT INTO `user` VALUES ('3', 'cindy', '王美丽', '0', '14556656512', 'xxoo@163.com', 'cindy\'s note', '1');
INSERT INTO `user` VALUES ('126', 'mark', '毛毛', '0', '18635457815', 'xxoo@163.com', 'mark\'s note', '1');

position表结构和数据：

DROP TABLE IF EXISTS `position`;
CREATE TABLE `position` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `posname` varchar(20) DEFAULT NULL,
  `note` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `position` VALUES ('1', '总经理', '负责公司日常事务');
INSERT INTO `position` VALUES ('2', '零时工', '背锅的');

job_history表结构和数据：

DROP TABLE IF EXISTS `job_history`;
CREATE TABLE `job_history` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(20) DEFAULT NULL,
  `comp_name` varchar(50) DEFAULT NULL,
  `years` int(3) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_5` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `job_history` VALUES ('1', '1', '阿里', '2', '程序员');
INSERT INTO `job_history` VALUES ('2', '2', '百度', '4', '项目经理');
INSERT INTO `job_history` VALUES ('3', '2', '腾讯', '1', '程序员');
INSERT INTO `job_history` VALUES ('4', '3', '京东', '1', '测试');
INSERT INTO `job_history` VALUES ('5', '3', '网易', '2', '测试主管');
INSERT INTO `job_history` VALUES ('6', '3', '享学', '1', '讲师');

user_role表结构和数据：

DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
  `user_id` int(20) NOT NULL DEFAULT '0',
  `role_id` int(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`role_id`,`user_id`),
  KEY `fk_1` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user_role` VALUES ('1', '1');
INSERT INTO `user_role` VALUES ('1', '2');
INSERT INTO `user_role` VALUES ('2', '1');
INSERT INTO `user_role` VALUES ('3', '2');

role表结构和数据：

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(60) DEFAULT NULL COMMENT '角色名称',
  `note` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `role` VALUES ('1', '业务人员', '办理日常业务');
INSERT INTO `role` VALUES ('2', '管理员', '超级管理员');

三、association和collection标签

association和collection标签都是属于resultMap下的子标签，两者的属性相同如下表格：

序号	属性名	说明
1	`property`	`对应的属性名`
2	`column`	`传递到分步查询的列名结果，如果是多列则按格式{列名1:分步查询的形参名1,列名2:分步查询的形参名2},该属性用于分步查询。`
3	`select`	`需要进行分步查询的方法，如果是当前命名空间则直接写方法名即可，如果是其它命名空间，则写命名空间+方法名，用于分步查询。`
4	`fetchType`	`加载方式lazy\|eager，如果使用懒加载lazy则还需要去全局配置文件中修改aggressiveLazyLoading为false,lazyLoadTriggerMethods为空字符，用于分步查询。`
5	`javaType`	`该association绑定的java类型，用于联表查询`
6	`ofType`	`该collection绑定的泛型中的java类型，用于关联查询`
7	`resultMap`	`resultMap：绑定的java类型，同样可以引用其他命名空间的resultMap，和javaType使用其一即可，如果使用了resultMap则不需要在该标签下写result,id等子标签，用于关联查询。`
8	`columnPrefix`	`为每一行添加前缀，当联表查询的结果出现列名相同时需要用别名或者添加前缀，如果添加了前缀，则可以使用columnPrefix统一处理。`

association和collection标签的子标签说明：

序号	标签名	说明
1	`constructor`	`使用特定构造器（入参构造器）创建对象，一般用于处理没有无参构造器的类，该标签没有属性，但包含有2个子标签，idArg：类似下面的id标签，arg：类似下面的result标签`
2	`id`	`把主键和属性绑定，还能标识唯一的一条数据（关联查询时合并数据用到），最好有。`
3	`result`	`把其它列和属性绑定`
4	`association`	`一对一映射`
5	`collection`	`一对多映射`
6	`discriminator`	`鉴别器`

三、延迟加载设置

对于分步查询，Mybatis提供了延迟加载的功能，在使用延迟加载之前还需要去全局配置文件进行一些设置，设置如下：

<!--   开启全局延迟加载    -->
<setting name="lazyLoadingEnabled" value="false"/>
<!--   延迟加载的对象是否立马加载，需要使用延迟加载设置为false    -->
<setting name="aggressiveLazyLoading" value="false"/>
<!--   避免除了get方法以外，其它方法触发懒加载     -->
<setting name="lazyLoadTriggerMethods" value=""/>

注意：如果开启了全局延迟加载和使用延迟加载策略，则全部分步查询都会变成延迟加载，一般我们不设置全局延迟加载，而是通过局部设置，即在collection或association标签属性上设置 fetchType="lazy" 开启延迟加载。

四、一对一分步查询

mapper映射文件：

UserMapper.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.my.mapper.UserMapper">

  <resultMap id="BaseResultMap" type="com.my.entity.User">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="real_name" jdbcType="VARCHAR" property="realName" />
    <result column="sex" jdbcType="CHAR" property="sex" />
    <result column="mobile" jdbcType="VARCHAR" property="mobile" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="note" jdbcType="VARCHAR" property="note" />
    <result column="position_id" jdbcType="INTEGER" property="positionId" />
  </resultMap>

  <sql id="Base_Column_List">
    id, user_name, real_name, sex, mobile, email, note, position_id
  </sql>

  <resultMap id="UserAndPositionResultMap" type="com.my.entity.User" extends="BaseResultMap">
  <!-- 一对一级联操作标签   -->
    <association property="position" column="position_id" fetchType="lazy" select="com.my.mapper.PositionMapper.selectById">
    </association>
  </resultMap>

<!-- 一对一分步查询 -->
  <select id="selectUserAndPositionById" parameterType="java.lang.Integer" resultMap="UserAndPositionResultMap">
    select 
    <include refid="Base_Column_List" />
    from user
    where id = #{id}
  </select>

</mapper>

PositionMapper.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.my.mapper.PositionMapper">

  <resultMap id="BaseResultMap" type="com.my.entity.Position">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="posname" jdbcType="VARCHAR" property="posname" />
    <result column="note" jdbcType="VARCHAR" property="note" />
  </resultMap>

  <sql id="Base_Column_List">
    id, posname, note
  </sql>

  <select id="selectById" resultMap="BaseResultMap" parameterType="Integer">
    select
    <include refid="Base_Column_List" />
    from position
    where id = #{id,jdbcType=INTEGER}
  </select>

</mapper>

测试代码：

public class MybatisTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        String resource = "mybatis-config.xml";
        //1.使用mybatis的工具读取配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //2.创建sqlSessionFactory
        sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        inputStream.close();
    }

    /**
     * 测试一对一
     */
    @Test
    public void testOneToOne() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectUserAndPositionById(1);
        System.out.println(user);
        System.out.println(user.getPosition());
    }


}

五、一对多分步查询

mapper映射文件：

UserMapper.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.my.mapper.UserMapper">

  <resultMap id="BaseResultMap" type="com.my.entity.User">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="real_name" jdbcType="VARCHAR" property="realName" />
    <result column="sex" jdbcType="CHAR" property="sex" />
    <result column="mobile" jdbcType="VARCHAR" property="mobile" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="note" jdbcType="VARCHAR" property="note" />
    <result column="position_id" jdbcType="INTEGER" property="positionId" />
  </resultMap>

  <sql id="Base_Column_List">
    id, user_name, real_name, sex, mobile, email, note, position_id
  </sql>


  <resultMap id="UserAndJobHistorysResultMap" type="com.my.entity.User" extends="BaseResultMap">
    <!-- 一对多级联操作标签   -->
    <collection property="jobHistoryList" column="id" fetchType="lazy" select="com.my.mapper.JobHistoryMapper.selectByUserId">
    </collection>
  </resultMap>

<!-- 一对多分步查询 -->
  <select id="selectUserAndJobHistorysById" parameterType="java.lang.Integer" resultMap="UserAndJobHistorysResultMap">
    select
    <include refid="Base_Column_List" />
    from user
    where id = #{id}
  </select>

</mapper>

JobHistoryMapper.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.my.mapper.JobHistoryMapper">

  <resultMap id="BaseResultMap" type="com.my.entity.JobHistory">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="user_id" jdbcType="INTEGER" property="userId" />
    <result column="comp_name" jdbcType="VARCHAR" property="compName" />
    <result column="years" jdbcType="INTEGER" property="years" />
    <result column="title" jdbcType="VARCHAR" property="title" />
  </resultMap>
  
  <sql id="Base_Column_List">
    id, user_id, comp_name, years, title
  </sql>

  <select id="selectByUserId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from job_history
    where user_id = #{userId,jdbcType=INTEGER}
  </select>
</mapper>

测试代码：

public class MybatisTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        String resource = "mybatis-config.xml";
        //1.使用mybatis的工具读取配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //2.创建sqlSessionFactory
        sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        inputStream.close();
    }


    /**
     * 测试一对多
     */
    @Test
    public void testOneToMany() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectUserAndJobHistorysById(2);
        System.out.println(user);
        System.out.println(user.getJobHistoryList());
    }


}

六、多对多分步查询

分步查询的多对多和关联查询多对多类似，都是属于两个一对多的操作。

mapper映射文件：

UserMapper.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.my.mapper.UserMapper">

  <resultMap id="BaseResultMap" type="com.my.entity.User">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="real_name" jdbcType="VARCHAR" property="realName" />
    <result column="sex" jdbcType="CHAR" property="sex" />
    <result column="mobile" jdbcType="VARCHAR" property="mobile" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="note" jdbcType="VARCHAR" property="note" />
    <result column="position_id" jdbcType="INTEGER" property="positionId" />
  </resultMap>

  <sql id="Base_Column_List">
    id, user_name, real_name, sex, mobile, email, note, position_id
  </sql>

  <resultMap id="UserAndRoleResultMap" type="com.my.entity.User" extends="BaseResultMap">
    <!-- 多对多级联操作标签   -->
    <collection property="roleList" fetchType="lazy" column="id" select="com.my.mapper.RoleMapper.selectRolesByUserId">
    </collection>
  </resultMap>

<!--  多对多分步查询 -->
  <select id="selectUserAndRoleAll" resultMap="UserAndRoleResultMap">
    select
    <include refid="Base_Column_List" />
    from user
  </select>

  <!-- 根据角色ID查询用户 -->
  <select id="selectUsersByRoleId" parameterType="Integer" resultMap="BaseResultMap">
    select
    u.id,u.user_name,u.real_name,u.sex,u.mobile,u.email,u.note,u.position_id,
    ur.role_id
    from user u left join user_role ur
    on ur.user_id = u.id
    where ur.role_id = #{roleId}
  </select>
</mapper>

RoleMapper.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.my.mapper.RoleMapper">

  <resultMap id="BaseResultMap" type="com.my.entity.Role">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="role_name" jdbcType="VARCHAR" property="roleName" />
    <result column="note" jdbcType="VARCHAR" property="note" />
  </resultMap>

  <resultMap id="RoleAndUserResultMap" type="com.my.entity.Role" extends="BaseResultMap">
    <!-- 多对多级联操作标签   -->
    <collection property="userList" fetchType="lazy" column="id" select="com.my.mapper.UserMapper.selectUsersByRoleId">
    </collection>
  </resultMap>

<!--  多对多分步查询 -->
  <select id="selectRoleAndUserAll" resultMap="RoleAndUserResultMap">
    select
    id,role_name,note
    from role
  </select>

<!--  根据权限查询角色 -->
  <select id="selectRolesByUserId" parameterType="Integer" resultMap="BaseResultMap">
    select
    r.id,r.role_name,r.note,
    ur.user_id
    from role r left join user_role ur
    on r.id = ur.role_id
    where ur.user_id = #{userId}
  </select>
</mapper>

测试代码：

public class MybatisTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        String resource = "mybatis-config.xml";
        //1.使用mybatis的工具读取配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //2.创建sqlSessionFactory
        sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        inputStream.close();
    }


    /**
     * 测试多对多
     */
    @Test
    public void testManyToMany() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        //所有用户下的权限信息
        List<User> userList = userMapper.selectUserAndRoleAll();
        System.out.println(userList);
        System.out.println(userList.get(0).getRoleList());
        RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
        //所有权限信息下的用户
        List<Role> roleList = roleMapper.selectRoleAndUserAll();
        System.out.println(roleList);
        System.out.println(roleList.get(0).getUserList());
    }

}