使用QueryDSL的聚合函数

恒宇少年2018-10-29 21:43:47

在企业级项目开发过程中,往往会经常用到数据库内的聚合函数,一般ORM框架应对这种逻辑问题时都会采用编写原生的SQL来处理,而QueryDSL完美的解决了这个问题,它内置了SQL所有的聚合函数下面我们简单介绍我们常用的几个聚合函数。

本章目标

基于SpringBoot平台整合QueryDSL完成常用聚合函数使用。

构建项目

我们使用idea来创建一个SpringBoot项目,pom.xml配置文件依赖如下所示:

  1. <?xml version="1.0" encoding="UTF-8"?>

  2. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

  3.    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  4.    <modelVersion>4.0.0</modelVersion>

  5.    <groupId>com.yuqiyu.querydsl.sample</groupId>

  6.    <artifactId>chapter6</artifactId>

  7.    <version>0.0.1-SNAPSHOT</version>

  8.    <packaging>war</packaging>

  9.    <name>chapter6</name>

  10.    <description>Demo project for Spring Boot</description>

  11.    <parent>

  12.        <groupId>org.springframework.boot</groupId>

  13.        <artifactId>spring-boot-starter-parent</artifactId>

  14.        <version>1.5.4.RELEASE</version>

  15.        <relativePath/> <!-- lookup parent from repository -->

  16.    </parent>

  17.    <properties>

  18.        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

  19.        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

  20.        <java.version>1.8</java.version>

  21.    </properties>

  22.    <dependencies>

  23.        <dependency>

  24.            <groupId>org.springframework.boot</groupId>

  25.            <artifactId>spring-boot-starter-data-jpa</artifactId>

  26.        </dependency>

  27.        <dependency>

  28.            <groupId>org.springframework.boot</groupId>

  29.            <artifactId>spring-boot-starter-web</artifactId>

  30.        </dependency>

  31.        <dependency>

  32.            <groupId>mysql</groupId>

  33.            <artifactId>mysql-connector-java</artifactId>

  34.            <scope>runtime</scope>

  35.        </dependency>

  36.        <!--阿里巴巴数据库连接池,专为监控而生 -->

  37.        <dependency>

  38.            <groupId>com.alibaba</groupId>

  39.            <artifactId>druid</artifactId>

  40.            <version>1.0.26</version>

  41.        </dependency>

  42.        <!-- 阿里巴巴fastjson,解析json视图 -->

  43.        <dependency>

  44.            <groupId>com.alibaba</groupId>

  45.            <artifactId>fastjson</artifactId>

  46.            <version>1.2.15</version>

  47.        </dependency>

  48.        <dependency>

  49.            <groupId>org.springframework.boot</groupId>

  50.            <artifactId>spring-boot-starter-tomcat</artifactId>

  51.            <!--<scope>provided</scope>-->

  52.        </dependency>

  53.        <dependency>

  54.            <groupId>org.springframework.boot</groupId>

  55.            <artifactId>spring-boot-starter-test</artifactId>

  56.            <scope>test</scope>

  57.        </dependency>

  58.        <!--queryDSL-->

  59.        <dependency>

  60.            <groupId>com.querydsl</groupId>

  61.            <artifactId>querydsl-jpa</artifactId>

  62.            <version>${querydsl.version}</version>

  63.        </dependency>

  64.        <dependency>

  65.            <groupId>com.querydsl</groupId>

  66.            <artifactId>querydsl-apt</artifactId>

  67.            <version>${querydsl.version}</version>

  68.            <scope>provided</scope>

  69.        </dependency>

  70.        <dependency>

  71.            <groupId>org.projectlombok</groupId>

  72.            <artifactId>lombok</artifactId>

  73.            <version>1.16.16</version>

  74.        </dependency>

  75.        <dependency>

  76.            <groupId>javax.inject</groupId>

  77.            <artifactId>javax.inject</artifactId>

  78.            <version>1</version>

  79.        </dependency>

  80.    </dependencies>

  81.    <build>

  82.        <plugins>

  83.            <plugin>

  84.                <groupId>org.springframework.boot</groupId>

  85.                <artifactId>spring-boot-maven-plugin</artifactId>

  86.            </plugin>

  87.            <!--添加QueryDSL插件支持-->

  88.            <plugin>

  89.                <groupId>com.mysema.maven</groupId>

  90.                <artifactId>apt-maven-plugin</artifactId>

  91.                <version>1.1.3</version>

  92.                <executions>

  93.                    <execution>

  94.                        <goals>

  95.                            <goal>process</goal>

  96.                        </goals>

  97.                        <configuration>

  98.                            <outputDirectory>target/generated-sources/java</outputDirectory>

  99.                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>

  100.                        </configuration>

  101.                    </execution>

  102.                </executions>

  103.            </plugin>

  104.        </plugins>

  105.    </build>

  106. </project>

上面内的QueryDSL这里就不多做讲解了,如有疑问请查看第一章:Maven环境下如何配置QueryDSL环境。

创建数据表

下面我们来创建一个张数据表来讲解本章的内容,表结构如下所示:

  1. /*

  2. Navicat MariaDB Data Transfer

  3. Source Server         : local

  4. Source Server Version : 100108

  5. Source Host           : localhost:3306

  6. Source Database       : test

  7. Target Server Type    : MariaDB

  8. Target Server Version : 100108

  9. File Encoding         : 65001

  10. Date: 2017-07-13 15:57:37

  11. */

  12. SET FOREIGN_KEY_CHECKS=0;

  13. -- ----------------------------

  14. -- Table structure for users

  15. -- ----------------------------

  16. DROP TABLE IF EXISTS `users`;

  17. CREATE TABLE `users` (

  18.  `u_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键编号',

  19.  `u_username` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '用户名',

  20.  `u_age` int(10) DEFAULT NULL COMMENT '年龄',

  21.  `u_score` double(8,2) DEFAULT NULL COMMENT '积分',

  22.  PRIMARY KEY (`u_id`)

  23. ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

  24. -- ----------------------------

  25. -- Records of users

  26. -- ----------------------------

  27. INSERT INTO `users` VALUES ('1', 'admin', '12', '45.70');

  28. INSERT INTO `users` VALUES ('2', 'hengyu', '23', '56.40');

  29. INSERT INTO `users` VALUES ('3', 'test', '22', '67.80');

  30. INSERT INTO `users` VALUES ('4', 'jocker', '25', '99.00');

我们简单创建了一张用户信息表,表内的年龄、积分是我们本章主要使用到的字段,下面我们就开始来讲解本章的内容。

创建实体

我们对应数据库内的表结构创建我们需要的实体并添加JPA的映射,实体代码如下所示:

  1. package com.yuqiyu.querydsl.sample.chapter6.bean;

  2. import lombok.Data;

  3. import javax.persistence.*;

  4. /**

  5. * ========================

  6. * Created with IntelliJ IDEA.

  7. * User:恒宇少年

  8. * Date:2017/7/12

  9. * Time:10:58

  10. * 码云:http://git.oschina.net/jnyqy

  11. * ========================

  12. */

  13. @Entity

  14. @Table(name = "users")

  15. @Data

  16. public class UserBean

  17. {

  18.    @Id

  19.    @GeneratedValue

  20.    @Column(name = "u_id")

  21.    private Long id;

  22.    @Column(name = "u_username")

  23.    private String name;

  24.    @Column(name = "u_age")

  25.    private int age;

  26.    @Column(name = "u_score")

  27.    private double socre;

  28. }

如果对@Data注解有疑问,大家可以去GitHub查一下lombok开源项目。 我们的实体已经创建完成,下面我们开始使用maven compile命令完成QueryDSL查询实体的创建,我们找到Maven Projects窗口,展开Lifecyle组,双击compile命令即可,如下图1所示:

创建控制器

本章创建控制器的方法与前几章一致,采用@PostConstruct来初始化JPAQueryFactory实体对象,控制器代码如下所示:

  1. package com.yuqiyu.querydsl.sample.chapter6.controller;

  2. import com.querydsl.jpa.impl.JPAQueryFactory;

  3. import com.yuqiyu.querydsl.sample.chapter6.bean.UserBean;

  4. import org.springframework.beans.factory.annotation.Autowired;

  5. import org.springframework.web.bind.annotation.RequestMapping;

  6. import org.springframework.web.bind.annotation.RestController;

  7. import javax.annotation.PostConstruct;

  8. import javax.persistence.EntityManager;

  9. import java.util.List;

  10. /**

  11. * ========================

  12. * Created with IntelliJ IDEA.

  13. * User:恒宇少年

  14. * Date:2017/7/12

  15. * Time:10:59

  16. * 码云:http://git.oschina.net/jnyqy

  17. * ========================

  18. */

  19. @RestController

  20. public class UserController

  21. {

  22.    //实体管理对象

  23.    @Autowired

  24.    private EntityManager entityManager;

  25.    //queryDSL,JPA查询工厂

  26.    private JPAQueryFactory queryFactory;

  27.    //实例化查询工厂

  28.    @PostConstruct

  29.    public void init()

  30.    {

  31.        queryFactory = new JPAQueryFactory(entityManager);

  32.    }

  33. }

下面我们开始编写聚合函数代码。

Count函数

我们现在的需求是查询用户表内的总条数,控制器方法代码如下所示:

  1. /**

  2.     * count聚合函数

  3.     * @return

  4.     */

  5.    @RequestMapping(value = "/countExample")

  6.    public long countExample()

  7.    {

  8.        //用户查询实体

  9.        QUserBean _Q_user = QUserBean.userBean;

  10.        return queryFactory

  11.                .select(_Q_user.id.count())//根据主键查询总条数

  12.                .from(_Q_user)

  13.                .fetchOne();//返回总条数

  14.    }

可以看到我们根据id这个字段进行了count聚合,当然我们也可以根据实体内任意字段进行count聚合,我们一般会根据主键来进行聚合,因为主键默认有索引,效率会更高。

这里要注意一点,我们使用的fetchOne方法返回的类型完全是根据select方法内单个参数的类型对应的。

下面我们来启动项目测试下我们这个count聚合是否有效,项目启动完成后我们访问地址http://127.0.0.1:8080/countExample,界面输入内容如下图2所示:

  1. Hibernate:

  2.    select

  3.        count(userbean0_.u_id) as col_0_0_

  4.    from

  5.        users userbean0_

可以看到QueryDSL自动生成的SQL跟我们预期的是一样的,我又被QueryDSL的方便深深的折服了。

Sum函数

接下来我们需要查询所有用户分数总和,代码如下所示:

  1.    /**

  2.     * sum聚合函数

  3.     * @return

  4.     */

  5.    @RequestMapping(value = "/sumExample")

  6.    public double sumExample()

  7.    {

  8.        //用户查询实体

  9.        QUserBean _Q_user = QUserBean.userBean;

  10.        return queryFactory

  11.                .select(_Q_user.socre.sum())//查询积分总数

  12.                .from(_Q_user)

  13.                .fetchOne();//返回积分总数

  14.    }

我们重启项目测试我们的sum聚合函数是否能够查询出总分数,访问地址http://127.0.0.1:8080/sumExample界面输出内容如下图3所示: 

  1. Hibernate:

  2.    select

  3.        sum(userbean0_.u_score) as col_0_0_

  4.    from

  5.        users userbean0_

也是没问题的,很智能,可谓是指哪打哪。

Avg函数

下面我们又有新的需求了,需要查询下积分的平均值,代码如下所示:

  1.    /**

  2.     * avg聚合函数

  3.     * @return

  4.     */

  5.    @RequestMapping(value = "/avgExample")

  6.    public double avgExample()

  7.    {

  8.        //用户查询实体

  9.        QUserBean _Q_user = QUserBean.userBean;

  10.        return queryFactory

  11.                .select(_Q_user.socre.avg())//查询积分平均值

  12.                .from(_Q_user)

  13.                .fetchOne();//返回平均值

  14.    }

访问映射地址界面输出内容如下图4所示: 

  1. Hibernate:

  2.    select

  3.        avg(userbean0_.u_score) as col_0_0_

  4.    from

  5.        users userbean0_

可以看到QueryDSL自动根据积分字段进行了avg聚合实现。

Max函数

接下来我们来查询用户最大积分值,代码如下所示:

  1.    /**

  2.     * max聚合函数

  3.     * @return

  4.     */

  5.    @RequestMapping(value = "/maxExample")

  6.    public double maxExample()

  7.    {

  8.        //用户查询实体

  9.        QUserBean _Q_user = QUserBean.userBean;

  10.        return queryFactory

  11.                .select(_Q_user.socre.max())//查询最大积分

  12.                .from(_Q_user)

  13.                .fetchOne();//返回最大积分

  14.    }

我们根据积分字段调用max方法即可获取最大积分,然后调用fetchOne方法就能够返回double类型的最大积分值。我们重启下项目访问路径http://127.0.0.1:8080/maxExample界面输出内容如下图5所示: 

  1. Hibernate:

  2.    select

  3.        max(userbean0_.u_score) as col_0_0_

  4.    from

  5.        users userbean0_

到现在为止我们得出来了一个结论,如果原生SQL内聚合函数是作用在字段上,在QueryDSL内使用方法则是查询属性.xxx函数,那么接下来的聚合函数作用域就不是字段了而变成了表。

Group By函数

我们的分组函数该如何使用呢?下面我们根据积分进行分组并且仅查询年龄大于22岁的数据,控制器代码如下所示:

  1.    /**

  2.     * group by & having聚合函数

  3.     * @return

  4.     */

  5.    @RequestMapping(value = "/groupByExample")

  6.    public List<UserBean> groupByExample()

  7.    {

  8.        //用户查询实体

  9.        QUserBean _Q_user = QUserBean.userBean;

  10.        return queryFactory

  11.                .select(_Q_user)

  12.                .from(_Q_user)

  13.                .groupBy(_Q_user.socre)//根据积分分组

  14.                .having(_Q_user.age.gt(22))//并且年龄大于22岁

  15.                .fetch();//返回用户列表

  16.    }

因为Group By函数作用域不是字段而是表,所以会与select、from方法同级,跟原生SQL一样使用Group By进行查询时查询条件不能使用where,而是having!在QueryDSL内也是一样,因为QueryDSL完全遵循了SQL标准。 下面我们重启下项目访问地址http://127.0.0.1:8080/groupByExample看下效果,如下图6所示: 

  1. Hibernate:

  2.    select

  3.        userbean0_.u_id as u_id1_0_,

  4.        userbean0_.u_age as u_age2_0_,

  5.        userbean0_.u_username as u_userna3_0_,

  6.        userbean0_.u_score as u_score4_0_

  7.    from

  8.        users userbean0_

  9.    group by

  10.        userbean0_.u_score

  11.    having

  12.        userbean0_.u_age>?

可以看到SQL是根据积分字段进行分组并且查询年龄大于22岁的列表。

总结

以上内容就是本章的全部讲解,我们不管是从上面的代码还是之前章节的代码可以得到一个QueryDSL的设计主导方向,QueryDSL完全遵循SQL标准进行设计,SQL内的作用域的关键字在QueryDSL内也是通过,不过展现形式不同罢了。 上面函数不是全部的聚合函数,项目中如果需要其他函数可按照本章的思路去写。


QueryDSL官方文档:http://www.querydsl.com/static/querydsl/latest/reference/html/ch02.html 

本章代码已经上传到码云:

SpringBoot配套源码地址:https://gitee.com/hengboy/spring-boot-chapter 

SpringCloud配套源码地址:https://gitee.com/hengboy/spring-cloud-chapter

同系列文章

SpringBoot与QueryDSL初整合


QueryDSL与SpringDataJPA实现单表普通条件查询


使用QueryDSL与SpringDataJPA完成Update&Delete


QueryDSL与SpringDataJPA实现多表关联查询


使用QueryDSL与SpringDataJPA实现查询返回自定义对象

推荐阅读

SpringBoot平台使用Lombok来优雅的编码


在SpringBoot内如何使用ApplicationEvent&Listener完成业务解耦?


SpringBoot自定义专属业务的Starter


SpringBoot使用MapStruct自动映射DTO


SpringBoot架构使用Profile完成打包环境分离


SpringBoot2.0新特性 - 你get到WebMvcConfigurer两种配置方式了吗?


SpringBoot2.0新特性 - 岂止至今最简单redis缓存集成


SpringBoot2.0新特性 - Quartz自动化配置集成


编码规范 - 养成良好的Java编码习惯


基于SpringBoot 设计业务逻辑异常统一处理


基于SpringBoot & Quartz分布式单节点持久化

加入知识星球,恒宇少年带你走以后的技术道路!!!

Copyright © 温县电话机虚拟社区@2017