第六章:使用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 条评论
登录 后参与评论

相关文章

来自专栏idba

快速备份恢复工具mydumper/myloader

一 前言 大家对于MySQL的逻辑备份工具mysqldump应该都比较了解,本文介绍一款由MySQL ,Facebook 等公司的开发维护另外一套逻辑备份恢...

1183
来自专栏大眼瞪小眼

Mysql语句的执行过程

当你希望MySQL能够以更高的性能运行查询时,最好的办法是弄清楚MySQL是如何优化和执行查询。《高性能MySQL》

542
来自专栏蓝天

MySQL数据导入导出方法与工具mysqlimport

mysqlimport位于mysql/bin目录中,是mysql的一个载入(或者说导入)数据的一个非常有效的工具。这是一个命令行工具。有两个参数以及大量的选项可...

823
来自专栏码农阿宇

Centos运行Mysql因为内存不足进程被杀

今天刚刚申请了一个新的域名,在申请完域名刚准备绑定给小伙伴分享注册新域名的喜悦时,刚把网站发到我们小伙伴们的讨论群里,却发现访问不了了,提示,数据库连接失败! ...

4995
来自专栏安恒网络空间安全讲武堂

2017 LCTF WriteUp 4篇

周末刚刚结束的LCTF,我们队一共做出了4道web,一道misc还有一道问卷调查(好气啊没抢到一血换pwnhub邀请码),感谢吃饭去大佬带飞~ 前言 对本渣渣...

4178
来自专栏程序猿

SQL注入分析服务器类型

分析数据库服务器类型 一般来说,ACCESS与SQL-SERVER是最常用的数据库服务器,尽管它们都支持T-SQL标准,但还有不同之处,而且不同的数据库有不同的...

2836
来自专栏龙渊阁测试精英

SQL注入详解

SQL注入是一种将SQL代码插入或添加到应用(用户)的输入参数中的攻击,之后再将这些参数传递给后台的SQL服务器加以解析并执行。

963
来自专栏深度学习之tensorflow实战篇

hive基本操作整理

.hive模糊搜索表 show tables like '*name*'; 2.查看表结构信息 desc formatted table_name; desc...

2455
来自专栏乐沙弥的世界

RMAN 数据库克隆文件位置转换方法

      在使用RMAN克隆数据库时,如果辅助数据库(新数据库)使用了与目标数据库(原数据库)使用了不同的路径,那么就存在位置转换的问题。在Oracle中,控...

581
来自专栏开源优测

性能测试必备监控技能MySQL篇15

前言 性能测试过程中,数据库相关指标的监控是不可忽视的,在这里我们就MySQL的监控配置及重点涉及性能的一些参数进行说明。 在笔者的日常性能测试过程中,重点关注...

27712

扫码关注云+社区