使用QueryDSL与SpringDataJPA实现子查询

恒宇少年2019-01-14 06:08:04

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

本章目标

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

构建项目

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

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

  8.    <packaging>war</packaging>

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

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

商品信息表

  1. -- ----------------------------

  2. -- Table structure for good_infos

  3. -- ----------------------------

  4. DROP TABLE IF EXISTS `good_infos`;

  5. CREATE TABLE `good_infos` (

  6.  `tg_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',

  7.  `tg_title` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '商品标题',

  8.  `tg_price` decimal(8,2) DEFAULT NULL COMMENT '商品单价',

  9.  `tg_unit` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '单位',

  10.  `tg_order` varchar(255) DEFAULT NULL COMMENT '排序',

  11.  `tg_type_id` int(11) DEFAULT NULL COMMENT '类型外键编号',

  12.  PRIMARY KEY (`tg_id`),

  13.  KEY `tg_type_id` (`tg_type_id`)

  14. ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

商品类型表

  1. -- ----------------------------

  2. -- Table structure for good_types

  3. -- ----------------------------

  4. DROP TABLE IF EXISTS `good_types`;

  5. CREATE TABLE `good_types` (

  6.  `tgt_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',

  7.  `tgt_name` varchar(30) CHARACTER SET utf8 DEFAULT NULL COMMENT '类型名称',

  8.  `tgt_is_show` char(1) DEFAULT NULL COMMENT '是否显示',

  9.  `tgt_order` int(2) DEFAULT NULL COMMENT '类型排序',

  10.  PRIMARY KEY (`tgt_id`)

  11. ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

创建实体

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

商品类型实体

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

  2. import lombok.Data;

  3. import javax.persistence.*;

  4. import java.io.Serializable;

  5. /**

  6. * ========================

  7. * Created with IntelliJ IDEA.

  8. * User:恒宇少年

  9. * Date:2017/7/14

  10. * Time:10:04

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

  12. * ========================

  13. */

  14. @Entity

  15. @Table(name = "good_types")

  16. @Data

  17. public class GoodTypeBean

  18.    implements Serializable

  19. {

  20.    //主键

  21.    @Id

  22.    @GeneratedValue

  23.    @Column(name = "tgt_id")

  24.    private Long id;

  25.    //类型名称

  26.    @Column(name = "tgt_name")

  27.    private String name;

  28.    //是否显示

  29.    @Column(name = "tgt_is_show")

  30.    private int isShow;

  31.    //排序

  32.    @Column(name = "tgt_order")

  33.    private int order;

  34. }

商品实体

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

  2. import lombok.Data;

  3. import javax.persistence.*;

  4. import java.io.Serializable;

  5. /**

  6. * ========================

  7. * Created with IntelliJ IDEA.

  8. * User:恒宇少年

  9. * Date:2017/7/14

  10. * Time:10:08

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

  12. * ========================

  13. */

  14. @Entity

  15. @Table(name = "good_infos")

  16. @Data

  17. public class GoodInfoBean

  18.    implements Serializable

  19. {

  20.    //主键

  21.    @Id

  22.    @GeneratedValue

  23.    @Column(name = "tg_id")

  24.    private Long id;

  25.    //商品标题

  26.    @Column(name = "tg_title")

  27.    private String title;

  28.    //商品价格

  29.    @Column(name = "tg_price")

  30.    private double price;

  31.    //商品单位

  32.    @Column(name = "tg_unit")

  33.    private String unit;

  34.    //商品排序

  35.    @Column(name = "tg_order")

  36.    private int order;

  37.    //类型外键

  38.    @Column(name = "tg_type_id")

  39.    private Long typeId;

  40. }

创建控制器

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

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

  2. import com.querydsl.jpa.JPAExpressions;

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

  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/14

  15. * Time:9:30

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

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

  18. */

  19. @RestController

  20. public class GoodController

  21. {

  22.    //实体管理对象

  23.    @Autowired

  24.    private EntityManager entityManager;

  25.    //jpa查询工厂对象

  26.    private JPAQueryFactory queryFactory;

  27.    @PostConstruct

  28.    public void init()

  29.    {

  30.        queryFactory = new JPAQueryFactory(entityManager);

  31.    }

  32. }

模糊查询

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

  1.    /**

  2.     * 子查询 模糊查询

  3.     * @return

  4.     */

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

  6.    public List<GoodInfoBean> childLikeSelect()

  7.    {

  8.        //商品基本信息查询实体

  9.        QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;

  10.        //商品类型查询实体

  11.        QGoodTypeBean _Q_good_type = QGoodTypeBean.goodTypeBean;

  12.        return queryFactory

  13.                .selectFrom(_Q_good)//查询商品基本信息表

  14.                .where(

  15.                        //查询类型名称包含“蔬菜”

  16.                        _Q_good.typeId.in(

  17.                                JPAExpressions.select(

  18.                                        _Q_good_type.id

  19.                                )

  20.                                .from(_Q_good_type)

  21.                                .where(_Q_good_type.name.like("%蔬菜%"))

  22.                        )

  23.                ).fetch();

  24.    }

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

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

  1.  .   ____          _            __ _ _

  2. /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \

  3. ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \

  4. \\/  ___)| |_)| | | | | || (_| |  ) ) ) )

  5.  '  |____| .__|_| |_|_| |_\__, | / / / /

  6. =========|_|==============|___/=/_/_/_/

  7. :: Spring Boot ::        (v1.5.4.RELEASE)

  8. ......

  9. 2017-07-14 10:15:22.255  INFO 11884 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)

  10. 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)

  11. 2017-07-14 10:15:26.086  INFO 11884 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring FrameworkServlet 'dispatcherServlet'

  12. 2017-07-14 10:15:26.086  INFO 11884 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization started

  13. 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

  14. 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查看界面输出内容如下:

  1. [

  2.    {

  3.        "id": 2,

  4.        "title": "油菜",

  5.        "price": 12.6,

  6.        "unit": "斤",

  7.        "order": 2,

  8.        "typeId": 1

  9.    }

  10. ]

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

  1. Hibernate:

  2.    select

  3.        goodinfobe0_.tg_id as tg_id1_0_,

  4.        goodinfobe0_.tg_order as tg_order2_0_,

  5.        goodinfobe0_.tg_price as tg_price3_0_,

  6.        goodinfobe0_.tg_title as tg_title4_0_,

  7.        goodinfobe0_.tg_type_id as tg_type_5_0_,

  8.        goodinfobe0_.tg_unit as tg_unit6_0_

  9.    from

  10.        good_infos goodinfobe0_

  11.    where

  12.        goodinfobe0_.tg_type_id in (

  13.            select

  14.                goodtypebe1_.tgt_id

  15.            from

  16.                good_types goodtypebe1_

  17.            where

  18.                goodtypebe1_.tgt_name like ? escape '!'

  19.        )

价格最高的商品列表

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

  1. /**

  2.     * 子查询 价格最高的商品列表

  3.     * @return

  4.     */

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

  6.    public List<GoodInfoBean> childEqSelect()

  7.    {

  8.        //商品基本信息查询实体

  9.        QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;

  10.        return queryFactory

  11.                .selectFrom(_Q_good)

  12.                //查询价格最大的商品列表

  13.                .where(_Q_good.price.eq(

  14.                        JPAExpressions.select(

  15.                                _Q_good.price.max()

  16.                        )

  17.                        .from(_Q_good)

  18.                ))

  19.                .fetch();

  20.    }

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

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

  1. [

  2.    {

  3.        "id": 4,

  4.        "title": "秋葵",

  5.        "price": 22.6,

  6.        "unit": "斤",

  7.        "order": 3,

  8.        "typeId": 1

  9.    }

  10. ]

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

  1. Hibernate:

  2.    select

  3.        goodinfobe0_.tg_id as tg_id1_0_,

  4.        goodinfobe0_.tg_order as tg_order2_0_,

  5.        goodinfobe0_.tg_price as tg_price3_0_,

  6.        goodinfobe0_.tg_title as tg_title4_0_,

  7.        goodinfobe0_.tg_type_id as tg_type_5_0_,

  8.        goodinfobe0_.tg_unit as tg_unit6_0_

  9.    from

  10.        good_infos goodinfobe0_

  11.    where

  12.        goodinfobe0_.tg_price=(

  13.            select

  14.                max(goodinfobe1_.tg_price)

  15.            from

  16.                good_infos goodinfobe1_

  17.        )

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

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

  1. /**

  2.     * 子查询 价格高于平均价格的商品列表

  3.     * @return

  4.     */

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

  6.    public List<GoodInfoBean> childGtAvgSelect()

  7.    {

  8.        //商品基本信息查询实体

  9.        QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;

  10.        return queryFactory

  11.                .selectFrom(_Q_good)

  12.                //查询价格高于平均价的商品列表

  13.                .where(

  14.                        _Q_good.price.gt(

  15.                                JPAExpressions.select(_Q_good.price.avg())

  16.                                .from(_Q_good)

  17.                        )

  18.                ).fetch();

  19.    }

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

  1. [

  2.    {

  3.        "id": 1,

  4.        "title": "金针菇",

  5.        "price": 5.5,

  6.        "unit": "斤",

  7.        "order": 1,

  8.        "typeId": 3

  9.    },

  10.    {

  11.        "id": 2,

  12.        "title": "油菜",

  13.        "price": 12.6,

  14.        "unit": "斤",

  15.        "order": 2,

  16.        "typeId": 1

  17.    }

  18. ]

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

  1. Hibernate:

  2.    select

  3.        goodinfobe0_.tg_id as tg_id1_0_,

  4.        goodinfobe0_.tg_order as tg_order2_0_,

  5.        goodinfobe0_.tg_price as tg_price3_0_,

  6.        goodinfobe0_.tg_title as tg_title4_0_,

  7.        goodinfobe0_.tg_type_id as tg_type_5_0_,

  8.        goodinfobe0_.tg_unit as tg_unit6_0_

  9.    from

  10.        good_infos goodinfobe0_

  11.    where

  12.        goodinfobe0_.tg_price<(

  13.            select

  14.                avg(goodinfobe1_.tg_price)

  15.            from

  16.                good_infos goodinfobe1_

  17.        )

我们可以看到生成的SQL完全是按照我们预期来创建的。

总结

以上内容就是本章的全部内容,我们使用三个简单的例子来讲述了QueryDSL子查询,QueryDSL完美的将原生的SQL编写方式转移到了Java程序内,内置了几乎所有的原生SQL的函数、关键字、语法等。

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实现查询返回自定义对象


使用QueryDSL的聚合函数

推荐阅读

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