第七章：使用QueryDSL与SpringDataJPA实现子查询

恒宇少年

发布于 2018-06-27 09:32:29

5.2K00

代码可运行

文章被收录于专栏：SpringBoot 核心技术SpringBoot 核心技术

运行总次数：0

代码可运行

在上一章我们讲到了QueryDSL的聚合函数，让我们重新认识了QueryDSL的便利之处，它可以很好的使用原生SQL的思想来进行Java形式的描述，编写完成也不需要考虑更换数据库存在的不兼容问题。当然QueryDSL还有很多我们没有发掘出来的核心技术，我们今天来讲解下”子查询“，看看QueryDSL是怎么完美的诠释了使用Java写SQL。

本章目标

基于SpringBoot平台完成QueryDSL整合JPA实现多表、单表子查询。

构建项目

我们使用idea工具创建一个SpringBoot项目，然后添加部分依赖并配置QueryDSL自动生成QueryBean插件，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>chapter7</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>

    <name>chapter7</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>

我们从第一章到本章pom.xml内容几乎没有变动，所以有之前章节学习的小伙伴可以直接拿过来使用。下面我们需要创建两表，当然为了方便我们直接使用第四章内的表结构，

商品信息表

-- ----------------------------
-- Table structure for good_infos
-- ----------------------------
DROP TABLE IF EXISTS `good_infos`;
CREATE TABLE `good_infos` (
  `tg_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `tg_title` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '商品标题',
  `tg_price` decimal(8,2) DEFAULT NULL COMMENT '商品单价',
  `tg_unit` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '单位',
  `tg_order` varchar(255) DEFAULT NULL COMMENT '排序',
  `tg_type_id` int(11) DEFAULT NULL COMMENT '类型外键编号',
  PRIMARY KEY (`tg_id`),
  KEY `tg_type_id` (`tg_type_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

商品类型表

-- ----------------------------
-- Table structure for good_types
-- ----------------------------
DROP TABLE IF EXISTS `good_types`;
CREATE TABLE `good_types` (
  `tgt_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `tgt_name` varchar(30) CHARACTER SET utf8 DEFAULT NULL COMMENT '类型名称',
  `tgt_is_show` char(1) DEFAULT NULL COMMENT '是否显示',
  `tgt_order` int(2) DEFAULT NULL COMMENT '类型排序',
  PRIMARY KEY (`tgt_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

创建实体

我们对应上面两张表的结构创建两个实体并添加对应的SpringDataJPA注解配置，如下所示：

商品类型实体

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

import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;

/**
 * ========================
 * Created with IntelliJ IDEA.
 * User：恒宇少年
 * Date：2017/7/14
 * Time：10:04
 * 码云：http://git.oschina.net/jnyqy
 * ========================
 */
@Entity
@Table(name = "good_types")
@Data
public class GoodTypeBean
    implements Serializable
{
    //主键
    @Id
    @GeneratedValue
    @Column(name = "tgt_id")
    private Long id;
    //类型名称
    @Column(name = "tgt_name")
    private String name;
    //是否显示
    @Column(name = "tgt_is_show")
    private int isShow;
    //排序
    @Column(name = "tgt_order")
    private int order;
}

商品实体

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

import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;

/**
 * ========================
 * Created with IntelliJ IDEA.
 * User：恒宇少年
 * Date：2017/7/14
 * Time：10:08
 * 码云：http://git.oschina.net/jnyqy
 * ========================
 */
@Entity
@Table(name = "good_infos")
@Data
public class GoodInfoBean
    implements Serializable
{
    //主键
    @Id
    @GeneratedValue
    @Column(name = "tg_id")
    private Long id;
    //商品标题
    @Column(name = "tg_title")
    private String title;
    //商品价格
    @Column(name = "tg_price")
    private double price;
    //商品单位
    @Column(name = "tg_unit")
    private String unit;
    //商品排序
    @Column(name = "tg_order")
    private int order;
    //类型外键
    @Column(name = "tg_type_id")
    private Long typeId;
}

创建控制器

接下来我们创建一个商品控制器用来我们本章内容的讲解，在控制器初始化时我们需要实例化JPAQueryFactory对象，在实例化之前需要注入EntityManager对象，代码如下所示：

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

import com.querydsl.jpa.JPAExpressions;
import com.querydsl.jpa.impl.JPAQueryFactory;
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/14
 * Time：9:30
 * 码云：http://git.oschina.net/jnyqy
 * ========================
 */
@RestController
public class GoodController
{
    //实体管理对象
    @Autowired
    private EntityManager entityManager;

    //jpa查询工厂对象
    private JPAQueryFactory queryFactory;

    @PostConstruct
    public void init()
    {
        queryFactory = new JPAQueryFactory(entityManager);
    }
}

模糊查询

我们现在有个需求需要查询出商品类型名称包含蔬菜的商品列表，在原生SQL内也有多种方式可以实现如：子查询、关联查询等。我们在QueryDSL内也是一样的，我们就拿子查询来处理这个需求吧，方法代码如下所示：

    /**
     * 子查询 模糊查询
     * @return
     */
    @RequestMapping(value = "/childLikeSelect")
    public List<GoodInfoBean> childLikeSelect()
    {
        //商品基本信息查询实体
        QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;
        //商品类型查询实体
        QGoodTypeBean _Q_good_type = QGoodTypeBean.goodTypeBean;

        return queryFactory
                .selectFrom(_Q_good)//查询商品基本信息表
                .where(
                        //查询类型名称包含“蔬菜”
                        _Q_good.typeId.in(
                                JPAExpressions.select(
                                        _Q_good_type.id
                                )
                                .from(_Q_good_type)
                                .where(_Q_good_type.name.like("%蔬菜%"))
                        )
                ).fetch();
    }

我们上面的代码查询了商品表内的全部信息并且根据类型编号使用了"in"方法来实现子查询，子查询是查询的商品类型表内的信息并且类型的名称包含“蔬菜”，不过子查询仅仅返回了商品类型的编号。

我们来启动下项目测试我们这个方法是否是我们预期的效果查询出商品类型名称包含”蔬菜“两个字的列表。项目启动控制台输出日志出现Tomcat started on port(s): 8080 (http)，表示已经启动成功，日志如下所示：

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.5.4.RELEASE)

......
2017-07-14 10:15:22.255  INFO 11884 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2017-07-14 10:15:22.259  INFO 11884 --- [           main] c.y.q.s.chapter7.Chapter7Application     : Started Chapter7Application in 2.941 seconds (JVM running for 3.578)
2017-07-14 10:15:26.086  INFO 11884 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring FrameworkServlet 'dispatcherServlet'
2017-07-14 10:15:26.086  INFO 11884 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization started
2017-07-14 10:15:26.104  INFO 11884 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 18 ms
2017-07-14 10:15:26.215  INFO 11884 --- [nio-8080-exec-1] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory

访问我们配置的地址http://127.0.0.1:8080/childLikeSelect查看界面输出内容如下：

[
    {
        "id": 2,
        "title": "油菜",
        "price": 12.6,
        "unit": "斤",
        "order": 2,
        "typeId": 1
    }
]

我们看到数据返回”油菜“对应的商品类型编号是"1"，对应数据库的类型是”绿色蔬菜“，这证明了我们的编码跟返回的数据是一致的，那么接下来我们来看下QueryDSL为我们自动生成的SQL，如下所示：

Hibernate: 
    select
        goodinfobe0_.tg_id as tg_id1_0_,
        goodinfobe0_.tg_order as tg_order2_0_,
        goodinfobe0_.tg_price as tg_price3_0_,
        goodinfobe0_.tg_title as tg_title4_0_,
        goodinfobe0_.tg_type_id as tg_type_5_0_,
        goodinfobe0_.tg_unit as tg_unit6_0_ 
    from
        good_infos goodinfobe0_ 
    where
        goodinfobe0_.tg_type_id in (
            select
                goodtypebe1_.tgt_id 
            from
                good_types goodtypebe1_ 
            where
                goodtypebe1_.tgt_name like ? escape '!'
        )

价格最高的商品列表

我们又有了新的需求，需要查询出价格最高的商品列表，代码如下所示：

/**
     * 子查询 价格最高的商品列表
     * @return
     */
    @RequestMapping(value = "/childEqSelect")
    public List<GoodInfoBean> childEqSelect()
    {
        //商品基本信息查询实体
        QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;

        return queryFactory
                .selectFrom(_Q_good)
                //查询价格最大的商品列表
                .where(_Q_good.price.eq(
                        JPAExpressions.select(
                                _Q_good.price.max()
                        )
                        .from(_Q_good)
                ))
                .fetch();
    }

我们使用JPAExpressions创建一个子查询，查询出商品表内最大商品价格作为父查询的查询条件。

重启项目后访问地址http://127.0.0.1:8080/childEqSelect，接口返回内容如下所示：

[
    {
        "id": 4,
        "title": "秋葵",
        "price": 22.6,
        "unit": "斤",
        "order": 3,
        "typeId": 1
    }
]

我们数据库内有三条数据，价格最高的也就是名为“秋葵”的商品了。下面我们再来看下控制台输出的SQL如下所示：

Hibernate: 
    select
        goodinfobe0_.tg_id as tg_id1_0_,
        goodinfobe0_.tg_order as tg_order2_0_,
        goodinfobe0_.tg_price as tg_price3_0_,
        goodinfobe0_.tg_title as tg_title4_0_,
        goodinfobe0_.tg_type_id as tg_type_5_0_,
        goodinfobe0_.tg_unit as tg_unit6_0_ 
    from
        good_infos goodinfobe0_ 
    where
        goodinfobe0_.tg_price=(
            select
                max(goodinfobe1_.tg_price) 
            from
                good_infos goodinfobe1_
        )

价格高于平均价格的商品列表

现在我们需要查询高于平均价格的商品列表，那我们该怎么编写呢？代码如下所示：

 /**
     * 子查询 价格高于平均价格的商品列表
     * @return
     */
    @RequestMapping(value = "/childGtAvgSelect")
    public List<GoodInfoBean> childGtAvgSelect()
    {
        //商品基本信息查询实体
        QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;
        return queryFactory
                .selectFrom(_Q_good)
                //查询价格高于平均价的商品列表
                .where(
                        _Q_good.price.gt(
                                JPAExpressions.select(_Q_good.price.avg())
                                .from(_Q_good)
                        )
                ).fetch();
    }

我们使用JPAExpressions来创建一个子查询并且返回商品表内价格平均值，查询到的值作为父查询的查询条件。接下来我们重启项目后访问地址http://127.0.0.1:8080/childGtAvgSelect，接口返回的内容如下所示：

[
    {
        "id": 1,
        "title": "金针菇",
        "price": 5.5,
        "unit": "斤",
        "order": 1,
        "typeId": 3
    },
    {
        "id": 2,
        "title": "油菜",
        "price": 12.6,
        "unit": "斤",
        "order": 2,
        "typeId": 1
    }
]

我们再来看下控制台输出的生成SQL内容如下所示：

Hibernate: 
    select
        goodinfobe0_.tg_id as tg_id1_0_,
        goodinfobe0_.tg_order as tg_order2_0_,
        goodinfobe0_.tg_price as tg_price3_0_,
        goodinfobe0_.tg_title as tg_title4_0_,
        goodinfobe0_.tg_type_id as tg_type_5_0_,
        goodinfobe0_.tg_unit as tg_unit6_0_ 
    from
        good_infos goodinfobe0_ 
    where
        goodinfobe0_.tg_price<(
            select
                avg(goodinfobe1_.tg_price) 
            from
                good_infos goodinfobe1_
        )