第六章:使用QueryDSL的聚合函数

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

本章目标

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

构建项目

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

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.yuqiyu.querydsl.sample</groupId>
    <artifactId>chapter6</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>

    <name>chapter6</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--阿里巴巴数据库连接池,专为监控而生 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.26</version>
        </dependency>
        <!-- 阿里巴巴fastjson,解析json视图 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.15</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <!--<scope>provided</scope>-->
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--queryDSL-->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>${querydsl.version}</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <version>${querydsl.version}</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.16</version>
        </dependency>
        <dependency>
            <groupId>javax.inject</groupId>
            <artifactId>javax.inject</artifactId>
            <version>1</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <!--添加QueryDSL插件支持-->
            <plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>
                <version>1.1.3</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>target/generated-sources/java</outputDirectory>
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

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

创建数据表

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

/*
Navicat MariaDB Data Transfer

Source Server         : local
Source Server Version : 100108
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MariaDB
Target Server Version : 100108
File Encoding         : 65001

Date: 2017-07-13 15:57:37
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `u_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键编号',
  `u_username` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '用户名',
  `u_age` int(10) DEFAULT NULL COMMENT '年龄',
  `u_score` double(8,2) DEFAULT NULL COMMENT '积分',
  PRIMARY KEY (`u_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('1', 'admin', '12', '45.70');
INSERT INTO `users` VALUES ('2', 'hengyu', '23', '56.40');
INSERT INTO `users` VALUES ('3', 'test', '22', '67.80');
INSERT INTO `users` VALUES ('4', 'jocker', '25', '99.00');

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

创建实体

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

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

import lombok.Data;

import javax.persistence.*;

/**
 * ========================
 * Created with IntelliJ IDEA.
 * User:恒宇少年
 * Date:2017/7/12
 * Time:10:58
 * 码云:http://git.oschina.net/jnyqy
 * ========================
 */
@Entity
@Table(name = "users")
@Data
public class UserBean
{
    @Id
    @GeneratedValue
    @Column(name = "u_id")
    private Long id;
    @Column(name = "u_username")
    private String name;
    @Column(name = "u_age")
    private int age;
    @Column(name = "u_score")
    private double socre;
}

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

图1

查看控制台输出Build Success表示项目构建完成,我们就可以在target/generated-sources/java目录下看到自动生成的查询实体源码。

创建控制器

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

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

import com.querydsl.jpa.impl.JPAQueryFactory;
import com.yuqiyu.querydsl.sample.chapter6.bean.UserBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;
import java.util.List;

/**
 * ========================
 * Created with IntelliJ IDEA.
 * User:恒宇少年
 * Date:2017/7/12
 * Time:10:59
 * 码云:http://git.oschina.net/jnyqy
 * ========================
 */
@RestController
public class UserController
{
    //实体管理对象
    @Autowired
    private EntityManager entityManager;
    //queryDSL,JPA查询工厂
    private JPAQueryFactory queryFactory;

    //实例化查询工厂
    @PostConstruct
    public void init()
    {
        queryFactory = new JPAQueryFactory(entityManager);
    }
}

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

Count函数

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

/**
     * count聚合函数
     * @return
     */
    @RequestMapping(value = "/countExample")
    public long countExample()
    {
        //用户查询实体
        QUserBean _Q_user = QUserBean.userBean;
        return queryFactory
                .select(_Q_user.id.count())//根据主键查询总条数
                .from(_Q_user)
                .fetchOne();//返回总条数
    }

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

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

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

图2

我们再来看下控制台输出的生成SQL是否为我们预期的效果,SQL如下所示:

Hibernate: 
    select
        count(userbean0_.u_id) as col_0_0_ 
    from
        users userbean0_

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

Sum函数

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

    /**
     * sum聚合函数
     * @return
     */
    @RequestMapping(value = "/sumExample")
    public double sumExample()
    {
        //用户查询实体
        QUserBean _Q_user = QUserBean.userBean;
        return queryFactory
                .select(_Q_user.socre.sum())//查询积分总数
                .from(_Q_user)
                .fetchOne();//返回积分总数
    }

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

图3

我们再来查看下控制台输出的生成SQL,如下所示:

Hibernate: 
    select
        sum(userbean0_.u_score) as col_0_0_ 
    from
        users userbean0_

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

Avg函数

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

    /**
     * avg聚合函数
     * @return
     */
    @RequestMapping(value = "/avgExample")
    public double avgExample()
    {
        //用户查询实体
        QUserBean _Q_user = QUserBean.userBean;
        return queryFactory
                .select(_Q_user.socre.avg())//查询积分平均值
                .from(_Q_user)
                .fetchOne();//返回平均值
    }

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

图4

我们再来看下控制台输出的SQL,如下所示:

Hibernate: 
    select
        avg(userbean0_.u_score) as col_0_0_ 
    from
        users userbean0_

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

Max函数

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

    /**
     * max聚合函数
     * @return
     */
    @RequestMapping(value = "/maxExample")
    public double maxExample()
    {
        //用户查询实体
        QUserBean _Q_user = QUserBean.userBean;
        return queryFactory
                .select(_Q_user.socre.max())//查询最大积分
                .from(_Q_user)
                .fetchOne();//返回最大积分
    }

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

图5

下面再来看下控制台输出的SQL,如下所示:

Hibernate: 
    select
        max(userbean0_.u_score) as col_0_0_ 
    from
        users userbean0_

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

Group By函数

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

    /**
     * group by & having聚合函数
     * @return
     */
    @RequestMapping(value = "/groupByExample")
    public List<UserBean> groupByExample()
    {
        //用户查询实体
        QUserBean _Q_user = QUserBean.userBean;
        return queryFactory
                .select(_Q_user)
                .from(_Q_user)
                .groupBy(_Q_user.socre)//根据积分分组
                .having(_Q_user.age.gt(22))//并且年龄大于22岁
                .fetch();//返回用户列表
    }

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

图6

可以看到我们读取到数据是正确的,仅仅查询出了大于22岁的数据。下面我们再来看下控制台输出的SQL如下所示:

Hibernate: 
    select
        userbean0_.u_id as u_id1_0_,
        userbean0_.u_age as u_age2_0_,
        userbean0_.u_username as u_userna3_0_,
        userbean0_.u_score as u_score4_0_ 
    from
        users userbean0_ 
    group by
        userbean0_.u_score 
    having
        userbean0_.u_age>?

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

总结

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

本章代码已经上传码云: SpringBoot配套源码地址:https://gitee.com/hengboy/spring-boot-chapter SpringCloud配套源码地址:https://gitee.com/hengboy/spring-cloud-chapter SpringBoot相关系列文章请访问:目录:SpringBoot学习目录 QueryDSL相关系列文章请访问:QueryDSL通用查询框架学习目录 SpringDataJPA相关系列文章请访问:目录:SpringDataJPA学习目录 感谢阅读!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏软件开发

一个软件项目的一生(认证考试)

一、概要 1.1、需求 某公司想开发一套学生信息管理系统,现在需要我们开发学生添加和查看功能模块,具体要求如下: 学生新增时需要提供的信息尽可能简单,以加快新增...

28310
来自专栏java思维导图

数据库常见的面试题大全

触发器是一中特殊的存储过程,主要是通过事件来触发而被执行的。它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可...

1474
来自专栏跟着阿笨一起玩NET

plsql developer的一些使用

1072
来自专栏Snova云数仓

Greenplum资源队列初识

在Greenplum的4.x版本之后,加入了资源队列的概念,其主要作用就是限制用户或者单个SQL对资源的消耗。避免出现消耗过多资源,影响其他用户或者SQL计算。...

79614
来自专栏c#开发者

General Database Adapter for Biztalk Server 2006 介绍

General Database Adapter for Biztalk Server 2006 介绍 目前该adapter分单向的Receive Adapte...

33611
来自专栏AILearning

Apache Zeppelin 中 Cassandra CQL 解释器

Name Class Description %cassandra CassandraInterpreter 为Apac...

3069
来自专栏Spark学习技巧

Java面试中常问的数据库方面问题

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

1292
来自专栏coder修行路

Go基础之--操作Mysql(三)

事务是数据库的一个非常重要的特性,尤其对于银行,支付系统,等等。 database/sql提供了事务处理的功能。通过Tx对象实现。db.Begin会创建tx对象...

3699
来自专栏逍遥剑客的游戏开发

关于Singleton

1432
来自专栏乐沙弥的世界

RMAN 提示符下执行SQL语句

       在手动恢复数据库时,有时候需要在SQL*Plus提示符以及操作系统提示符,RMAN提示符下来回切换显得有些繁琐。实际上RMAN为我们提供了命令行下...

1183

扫码关注云+社区

领取腾讯云代金券