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

在上一章我们讲到了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_
        )

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

总结

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

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Dawnzhang的开发者手册

数据库截取字符串SUBSTR函数的使用

今天中午做需求的时候,有类似于根据银行卡卡号的前几位判断出是哪个银行的情况,每个银行需要截取的位数都不一样,这时我就想到了SUBSTR

552
来自专栏IT杂记

INET_ATON()函数在MySQL5.6版本和5.7版本的差异

问题 ### The error occurred while setting parameters ### SQL: insert into t_gatewa...

1819
来自专栏杨建荣的学习笔记

oracle中的数组(第一篇)(r4笔记第9天)

数组在各种编程语言中都是很重要的数据结构实现,在oracle中也有自己的一席之地。自己简单做了几个实验,发现很多东西还是眼高手低,真实去做的时候,里面还是有不少...

2776
来自专栏JetpropelledSnake

SQL学习之简单增删改查

1766
来自专栏学习力

《Java从入门到放弃》框架入门篇:hibernate查询——HQL

1727
来自专栏技术博文

Yii数据库操作方法指南

CDbConnection: 一个抽象数据库连接 CDbCommand: SQL statement CDbDataReader: 匹配结果集的一行记录 CDb...

2367
来自专栏更流畅、简洁的软件开发方式

我的数据访问函数库的源代码(二)—— SQL语句部分

/* 2008 4 25 更新 */ 我的数据访问函数库的源码。整个类有1400行,原先就是分开来写的,现在更新后还是分开来发一下吧。 第二部分:SQL语句...

1849
来自专栏张善友的专栏

Ibatisnet Quick Start

准备工作 1. 下载ibatis软件包(http://ibatis.apache.org/dotnetdownloads.html)。 2. 创建测试数据库,并...

1708
来自专栏.NET开发者社区

一步一步创建ASP.NET MVC5程序[Repository+Autofac+Automapper+SqlSugar](二)

在本系列第一篇《一步一步创建ASP.NET MVC5程序[Repository+Autofac+Automapper+SqlSugar](一)》中,我为大家介绍...

23010
来自专栏数说戏聊

07-08 创建计算字段使用函数处理数据第7章 创建计算字段第8章 使用函数处理数据

上述例子中,存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据,而不是检索出数据,然后再在客户端应用程序中重新格式化...

802

扫码关注云+社区