Querying JPA

Querydsl 定义了一个通用静态类型的语法用于查询持久化的领域模型数据。JDO 和 JPA 是 Querydsl 主要的集成技术。这篇手册介绍了如何让Querydsl与JPA整合使用。

Querydsl JPA 是JPQL和标准条件查询（Criteria queries）的新的使用方式。它结合了条件查询的动态性和JPQL的表达能力，并且使用了完全的类型安全方式。

  • JDO：Java数据对象（Java Data Object，JDO）是一个用于存取某种数据仓库中的对象的标准化API，它使开发人员能够间接的访问数据库。
    JDO是JDBC的一个补充，它提供了透明的对象存储
  • JPA：JPA是Java Persistence API的简称，中文名Java持久层API
  • JPQL：JPQL 语言，即 Java Persistence Query Language 的简称。JPQL 和 HQL 是非常类似的，支持以面向对象的方式来写 SQL 语句，当然也支持本地的 SQL 语句。JPQL 最终会被编译成针对不同底层数据库的 SQL 查询从而屏蔽掉不同数据库的差异。

Maven 集成QueryDSL

在你的maven项目中添加下面的依赖：

  1. <dependency>
  2.   <groupId>com.querydsl</groupId>
  3.   <artifactId>querydsl-apt</artifactId>
  4.   <version>${querydsl.version}</version>
  5.   <scope>provided</scope>
  6. </dependency>
  8. <dependency>
  9.   <groupId>com.querydsl</groupId>
  10.   <artifactId>querydsl-jpa</artifactId>
  11.   <version>${querydsl.version}</version>
  12. </dependency>
  14. <dependency>
  15.   <groupId>org.slf4j</groupId>
  16.   <artifactId>slf4j-log4j12</artifactId>
  17.   <version>1.6.1</version>
  18. </dependency>

现在，来配置 maven APT 插件：

  1. <project>
  2.   <build>
  3.   <plugins>
  4.     ...
  5.     <plugin>
  6.       <groupId>com.mysema.maven</groupId>
  7.       <artifactId>apt-maven-plugin</artifactId>
  8.       <version>1.1.3</version>
  9.       <executions>
  10.         <execution>
  11.           <goals>
  12.             <goal>process</goal>
  13.           </goals>
  14.           <configuration>
  15.             <outputDirectory>target/generated-sources/java</outputDirectory>
  16.             <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
  17.           </configuration>
  18.         </execution>
  19.       </executions>
  20.     </plugin>
  21.     ...
  22.   </plugins>
  23.   </build>
  24. </project>

JPAAnnotationProcessor 查找带有 @javax.persistence.Entity 注解的领域类型并为其生成查询类型。

如果你在领域类型中使用的是Hibernate的注解，那应该使用 APT 处理器 com.querydsl.apt.hibernate.HibernateAnnotationProcessor 来代替 JPAAnnotationProcessor。

运行 clean install 命令后，在 target/generated-sources/java 目录下将生成相应的查询类型。
如果你使用Eclipse，运行 mvn eclipse:eclipse 更新你的Eclipse项目，将 target/generated-sources/java 作为源目录。
现在，你就可以构建 JPA 查询实例和查询领域模型的实例了

要使用Querydsl创建查询，您需要实例化变量和查询实现。我们从变量开始。

构建数据库信息

配置文件

  1. spring.datasource.driver-class-name=org.postgresql.Driver
  2. spring.datasource.url=jdbc:postgresql://127.0.0.1:5432/demo_querydsl?currentSchema=public
  3. spring.datasource.username=postgres
  4. spring.datasource.password=aaaaaa
  6. spring.jpa.show-sql=true
  7. spring.jpa.hibernate.ddl-auto=update

创建实体

（1）员工实体类Employee

  1. @Entity
  2. @Table(name = "employee")
  3. @Data
  4. public class Employee {  
  5.     @Id
  6.     @GenericGenerator(name = "idGeneratorUUID",strategy = "uuid")
  7.     @GeneratedValue(generator = "idGeneratorUUID")
  8.     private String id;
  9.     /**员工名称*/
  10.     private String name;
  11.     /**员工性别*/
  12.     private int gender;
  13.     /**员工电话*/
  14.     private String phone;
  15.     /**所在公司ID*/
  16.     private String companyId;
  17.     /**员工年龄*/
  18.     private Integer age;
  19. }

（2）公司实体类 Company

  1. @Entity
  2. @Table(name = "company")
  3. @Data
  4. public class Company{
  5.     @Id
  6.     @GenericGenerator(name = "idGeneratorUUID",strategy = "uuid")
  7.     @GeneratedValue(generator = "idGeneratorUUID")
  8.     private String id;
  9.     /**公司名称*/
  10.     private String name;
  11.     /**公司所在城市*/
  12.     private String city;
  13. }

生成Q类

之前有提到：JPAAnnotationProcessor 会查找带有 @javax.persistence.Entity 注解的领域类型并为其生成查询类型，即Q类。

执行maven complile 将会在 target/generated-sources/java 目录下将生成相应的Q类，如下：
1589423271377.png

单表简单查询

  1.     @Autowired
  2.     private JPAQueryFactory jpaQueryFactory;
  4.     private QCompany qCompany = QCompany.company;
  5.     private QEmployee qEmployee = QEmployee.employee;

获取全部员工信息

  1.     @Test
  2.     public void findAll(){
  3.         JPAQuery<Employee> jpaQuery = jpaQueryFactory.selectFrom(qEmployee);
  4.         List<Employee> employees = jpaQuery.fetch();
  5.         System.out.println("员工信息如下：");
  6.         for (Employee employee : employees) {
  7.             System.out.println("------分割线------");
  8.             System.out.println(employee.toString());
  9.         }
  10.     }

执行查询：

  1. Hibernate: 
  2. select 
  3.     employee0_.id as id1_1_, 
  4.     employee0_.age as age2_1_, 
  5.     employee0_.company_id as company_3_1_, 
  6.     employee0_.gender as gender4_1_, 
  7.     employee0_.name as name5_1_, 
  8.     employee0_.phone as phone6_1_ 
  9. from employee employee0_

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. Employee(id=1, name=李白, gender=1, phone=10086, companyId=1, age=22)
  4. ------分割线------
  5. Employee(id=3, name=孙尚香, gender=0, phone=10011, companyId=1, age=23)
  6. ------分割线------
  7. Employee(id=2, name=张飞, gender=1, phone=10010, companyId=1, age=20)
  8. ------分割线------
  9. Employee(id=4, name=孙悟空, gender=1, phone=00001, companyId=2, age=89)
  10. ------分割线------
  11. Employee(id=5, name=刘备, gender=1, phone=11111, companyId=1, age=25)
  12. ------分割线------
  13. Employee(id=6, name=复苏刘, gender=0, phone=12123, companyId=2, age=10)

根据员工ID

  1.     @Test
  2.     public void findById() {
  3.         String id = "1";
  4.         JPAQuery<Employee> jpaQuery = jpaQueryFactory.selectFrom(qEmployee).where(qEmployee.id.eq(id));
  5.         //.selectFrom(entity) == .select(entity).from (entity)
  6.         Employee employee = jpaQuery.fetchOne();
  7.         if (employee ==null) {
  8.             System.out.println("ID为" + id + "的员工不存在！");
  9.         } else {
  10.             System.out.println("员工信息："+employee.toString());
  11.         }
  12.     }

执行查询：

  1. Hibernate: 
  2. select 
  3.     employee0_.id as id1_1_, 
  4.     employee0_.age as age2_1_, 
  5.     employee0_.company_id as company_3_1_, 
  6.     employee0_.gender as gender4_1_, 
  7.     employee0_.name as name5_1_, 
  8.     employee0_.phone as phone6_1_ 
  9. from employee employee0_ 
  10. where employee0_.id=?

输出：

  1. 员工信息：Employee(id=1, name=李白, gender=1, phone=10086, companyId=1, age=22)

根据员工性别进行查询

  1.     @Test
  2.     public void findByGender() {
  3.         int gender = 1;//男
  4.         JPAQuery<Employee> jpaQuery = jpaQueryFactory.selectFrom(qEmployee).where(qEmployee.gender.eq(gender)).distinct();
  5.         List<Employee> employees = jpaQuery.fetch();
  7.         System.out.println("员工信息如下：");
  8.         for (Employee employee : employees) {
  9.             System.out.println("------分割线------");
  10.             System.out.println(employee.toString());
  11.         }
  12.     }

执行查询：

  1. Hibernate: select distinct employee0_.id as id1_1_, employee0_.age as age2_1_, employee0_.company_id as company_3_1_, employee0_.gender as gender4_1_, employee0_.name as name5_1_, employee0_.phone as phone6_1_ from employee employee0_ where employee0_.gender=?

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. Employee(id=1, name=李白, gender=1, phone=10086, companyId=1, age=22)
  4. ------分割线------
  5. Employee(id=2, name=张飞, gender=1, phone=10010, companyId=1, age=20)
  6. ------分割线------
  7. Employee(id=4, name=孙悟空, gender=1, phone=00001, companyId=2, age=89)
  8. ------分割线------
  9. Employee(id=5, name=刘备, gender=1, phone=11111, companyId=1, age=25)

根据员工名称模糊查询

  1.     @Test
  2.     public void findByLikeName() {
  3.         String name = "%刘%";//男
  4.         JPAQuery<Employee> jpaQuery = jpaQueryFactory.selectFrom(qEmployee).where(qEmployee.name.like(name)).distinct();
  5.         List<Employee> employees = jpaQuery.fetch();
  7.         System.out.println("员工信息如下：");
  8.         for (Employee employee : employees) {
  9.             System.out.println("------分割线------");
  10.             System.out.println(employee.toString());
  11.         }
  12.     }

执行查询：

  1. Hibernate: 
  2. select distinct 
  3.     employee0_.id as id1_1_, 
  4.     employee0_.age as age2_1_, 
  5.     employee0_.company_id as company_3_1_, 
  6.     employee0_.gender as gender4_1_, 
  7.     employee0_.name as name5_1_, 
  8.     employee0_.phone as phone6_1_ 
  9. from employee employee0_ 
  10. where employee0_.name like ? escape '!'

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. Employee(id=5, name=刘备, gender=1, phone=11111, companyId=1, age=25)
  4. ------分割线------
  5. Employee(id=6, name=复苏刘, gender=0, phone=12123, companyId=2, age=10)

多条件查询AND

  1.     @Test
  2.     public void findByGenderAndAgeGt() {
  3.         int gender = 1;
  4.         int age = 21;
  5.         JPAQuery<Employee> jpaQuery = jpaQueryFactory.selectFrom(qEmployee).where(qEmployee.gender.eq(gender), qEmployee.age.gt(age));
  6.         //.where(qEmployee.gender.eq(gender), qEmployee.age.gt(age)) = .where(qEmployee.gender.eq(gender).and(qEmployee.age.gt(age))
  7.         List<Employee> employees = jpaQuery.fetch();
  8.         System.out.println("员工信息如下：");
  9.         for (Employee employee : employees) {
  10.             System.out.println("------分割线------");
  11.             System.out.println(employee.toString());
  12.         }
  13.     }

执行查询：

  1. Hibernate: 
  2. select 
  3.     employee0_.id as id1_1_, 
  4.     employee0_.age as age2_1_, 
  5.     employee0_.company_id as company_3_1_, 
  6.     employee0_.gender as gender4_1_, 
  7.     employee0_.name as name5_1_, 
  8.     employee0_.phone as phone6_1_ 
  9. from employee employee0_ 
  10. where 
  11.     employee0_.gender=? 
  12.     and 
  13.     employee0_.age>?

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. Employee(id=1, name=李白, gender=1, phone=10086, companyId=1, age=22)
  4. ------分割线------
  5. Employee(id=4, name=孙悟空, gender=1, phone=00001, companyId=2, age=89)
  6. ------分割线------
  7. Employee(id=5, name=刘备, gender=1, phone=11111, companyId=1, age=25)

多条件查询OR

  1.     @Test
  2.     public void findByNameOrAgeGt() {
  3.         String name="李白";
  4.         int age = 25;
  5.         JPAQuery<Employee> jpaQuery = jpaQueryFactory.selectFrom(qEmployee).where(qEmployee.name.eq(name).or(qEmployee.age.gt(age)));
  6.         List<Employee> employees = jpaQuery.fetch();
  8.         for (Employee employee : employees) {
  9.             System.out.println("------分割线------");
  10.             System.out.println(employee.toString());
  11.         }
  12.     }

执行查询：

  1. Hibernate: select employee0_.id as id1_1_, employee0_.age as age2_1_, employee0_.company_id as company_3_1_, employee0_.gender as gender4_1_, employee0_.name as name5_1_, employee0_.phone as phone6_1_ from employee employee0_ where employee0_.name=? or employee0_.age>?

输出：

  1. ------分割线------
  2. Employee(id=1, name=李白, gender=1, phone=10086, companyId=1, age=22)
  3. ------分割线------
  4. Employee(id=4, name=孙悟空, gender=1, phone=00001, companyId=2, age=89)

查询分页

  1.     @Test
  2.     public void findAllPaged(){
  3.         //第2页，每页2条数据
  4.         int page = 2;
  5.         int size = 2;
  6.         JPAQuery<Employee> jpaQuery = jpaQueryFactory.selectFrom(qEmployee).offset((page-1)*size).limit(size);
  7.         List<Employee> employees = jpaQuery.fetch();
  8.         System.out.println("员工信息如下：");
  9.         for (Employee employee : employees) {
  10.             System.out.println("------分割线------");
  11.             System.out.println(employee.toString());
  12.         }
  13.     }

执行查询：

  1. Hibernate: 
  2. select 
  3.     employee0_.id as id1_1_, 
  4.     employee0_.age as age2_1_, 
  5.     employee0_.company_id as company_3_1_, 
  6.     employee0_.gender as gender4_1_, 
  7.     employee0_.name as name5_1_, 
  8.     employee0_.phone as phone6_1_ 
  9. from employee employee0_ 
  10. limit ? offset ?

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. Employee(id=2, name=张飞, gender=1, phone=10010, companyId=1, age=20)
  4. ------分割线------
  5. Employee(id=4, name=孙悟空, gender=1, phone=00001, companyId=2, age=89)

指定查询返回

指定查询列（Tuple）

有时候在查询时，我们只希望返回指定的列数据，示例：查询全部源信息，只获取name和age信息：

  1.     @Test
  2.     void findNameAndAge(){
  3.         List<Tuple> tuples = jpaQueryFactory.select(qEmployee.name, qEmployee.age).from(qEmployee).fetch();
  4.         System.out.println("员工信息如下：");
  5.         for (Tuple tuple:tuples){
  6.             System.out.println("name="+tuple.get(qEmployee.name)+",age="+tuple.get(qEmployee.age));
  7.         }
  8.     }

执行SQl:

  1. Hibernate: select employee0_.name as col_0_0_, employee0_.age as col_1_0_ from employee employee0_

输出：

  1. 员工信息如下：
  2. name=李白,age=22
  3. name=孙尚香,age=23
  4. name=张飞,age=20
  5. name=孙悟空,age=89
  6. name=刘备,age=25
  7. name=复苏刘,age=10

映射DTO（Projections）

有时候，我们想直接返回一个对象类型，这个对象的值可以由查询类型对象的部分字段组成。

Projections，这个类型是QueryDSL内置针对处理自定义返回结果集的解决方案，里面包含了构造函数、实体、字段等处理方法。

Bean

JPAQueryFactory工厂select方法可以将Projections方法返回的QBean作为参数，我们通过Projections的bean方法来构建返回的结果集映射到实体内。

bean:

  1. @Data
  2. public class EmployeeDyo1 {
  3.     private String id;
  4.     private String name;
  5.     private Integer age;
  6. }

查询：

  1.     /**
  2.      * bean 映射
  3.      */
  4.     @Test
  5.     void findEmployeeDyo1(){
  6.         List<EmployeeDyo1> employeeDyo1s = jpaQueryFactory
  7.             .select(
  8.             Projections.bean(EmployeeDyo1.class, 
  9.                              qEmployee.id, 
  10.                              qEmployee.name, 
  11.                              qEmployee.age))
  12.             .from(qEmployee).fetch();
  13.         System.out.println("员工信息如下：");
  14.         for (EmployeeDyo1 dyo1:employeeDyo1s){
  15.             System.out.println("------分割线------");
  16.             System.out.println(dyo1.toString());
  17.         }
  18.     }

执行SQl:

  1. Hibernate: select employee0_.id as col_0_0_, employee0_.name as col_1_0_, employee0_.age as col_2_0_ from employee employee0_

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. EmployeeDyo1(id=1, name=李白, age=22)
  4. ------分割线------
  5. EmployeeDyo1(id=3, name=孙尚香, age=23)
  6. ------分割线------
  7. EmployeeDyo1(id=2, name=张飞, age=20)
  8. ------分割线------
  9. EmployeeDyo1(id=4, name=孙悟空, age=89)
  10. ------分割线------
  11. EmployeeDyo1(id=5, name=刘备, age=25)
  12. ------分割线------
  13. EmployeeDyo1(id=6, name=复苏刘, age=10)

Bean as

如果QueryDSL查询实体内的字段与DTO实体的字段名字不一样时，我们就可以采用as方法来处理，为查询的结果集指定的字段添加别名，这样就会自动映射到DTO实体内。

  1. @Data
  2. @AllArgsConstructor
  3. @NoArgsConstructor
  4. public class EmployeeDto2 {
  5.     private String code;
  6.     private String userName;
  7.     private Integer age;
  8. }

首先我们试一下不使用as会查询出什么东西来：

测试查询：

  1.     @Test
  2.     void findEmployeeDyo2(){
  3.         List<EmployeeDto2> EmployeeDto2s = jpaQueryFactory
  4.                 .select(
  5.                 Projections.bean(EmployeeDto2.class,
  6.                         qEmployee.id,
  7.                         qEmployee.name,
  8.                         qEmployee.age))
  9.                 .from(qEmployee).fetch();
  10.         System.out.println("员工信息如下：");
  11.         for (EmployeeDto2 dto:EmployeeDto2s){
  12.             System.out.println("------分割线------");
  13.             System.out.println(dto.toString());
  14.         }
  15.     }

执行SQl:

  1. Hibernate: 
  2. select 
  3.     employee0_.id as col_0_0_, 
  4.     employee0_.name as col_1_0_, 
  5.     employee0_.age as col_2_0_ 
  6. from employee employee0_

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. EmployeeDto2(code=null, userName=null, age=22)
  4. ------分割线------
  5. EmployeeDto2(code=null, userName=null, age=23)
  6. ------分割线------
  7. EmployeeDto2(code=null, userName=null, age=20)
  8. ------分割线------
  9. EmployeeDto2(code=null, userName=null, age=89)
  10. ------分割线------
  11. EmployeeDto2(code=null, userName=null, age=25)
  12. ------分割线------
  13. EmployeeDto2(code=null, userName=null, age=10)

我们可以发现，code和userName两个字段都为null,因为与执行使用的默认as字段不相匹配，现在我们试着指定as:

测试查询：

  1.     @Test
  2.     void findEmployeeDto3(){
  3.         List<EmployeeDto2> EmployeeDto2s = jpaQueryFactory
  4.                 .select(
  5.                         Projections.bean(EmployeeDto2.class,
  6.                                 qEmployee.id.as("code"),//指定as为我们需要的字段名称code
  7.                                 qEmployee.name.as("userName"), //指定as为我们需要的字段名称userName
  8.                                 qEmployee.age))
  9.                 .from(qEmployee).fetch();
  10.         System.out.println("员工信息如下：");
  11.         for (EmployeeDto2 dto:EmployeeDto2s){
  12.             System.out.println("------分割线------");
  13.             System.out.println(dto.toString());
  14.         }
  15.     }

执行SQl:

  1. Hibernate: 
  2. select 
  3.     employee0_.id as col_0_0_, 
  4.     employee0_.name as col_1_0_, 
  5.     employee0_.age as col_2_0_ 
  6. from employee employee0_

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. EmployeeDto2(code=1, userName=李白, age=22)
  4. ------分割线------
  5. EmployeeDto2(code=3, userName=孙尚香, age=23)
  6. ------分割线------
  7. EmployeeDto2(code=2, userName=张飞, age=20)
  8. ------分割线------
  9. EmployeeDto2(code=4, userName=孙悟空, age=89)
  10. ------分割线------
  11. EmployeeDto2(code=5, userName=刘备, age=25)
  12. ------分割线------
  13. EmployeeDto2(code=6, userName=复苏刘, age=10)

Construct

同样也可以考虑以构造函数的形式来构造查询返回对象：

测试查询：

  1.     //EmployeeDto2(String code,String userName,int age)
  2.     @Test
  3.     void findEmployeeDto4(){
  4.         List<EmployeeDto2> EmployeeDto2s = jpaQueryFactory
  5.                 .select(
  6.                         Projections.constructor(EmployeeDto2.class,
  7.                                 qEmployee.id,
  8.                                 qEmployee.name,
  9.                                 qEmployee.age))
  10.                 .from(qEmployee).fetch();
  11.         System.out.println("员工信息如下：");
  12.         for (EmployeeDto2 dto:EmployeeDto2s){
  13.             System.out.println("------分割线------");
  14.             System.out.println(dto.toString());
  15.         }
  16.     }

执行SQl:

  1. Hibernate: 
  2. select 
  3.     employee0_.id as col_0_0_, 
  4.     employee0_.name as col_1_0_, 
  5.     employee0_.age as col_2_0_ 
  6. from employee employee0_

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. EmployeeDto2(code=1, userName=李白, age=22)
  4. ------分割线------
  5. EmployeeDto2(code=3, userName=孙尚香, age=23)
  6. ------分割线------
  7. EmployeeDto2(code=2, userName=张飞, age=20)
  8. ------分割线------
  9. EmployeeDto2(code=4, userName=孙悟空, age=89)
  10. ------分割线------
  11. EmployeeDto2(code=5, userName=刘备, age=25)
  12. ------分割线------
  13. EmployeeDto2(code=6, userName=复苏刘, age=10)

连表查询

Querydsl 在JPQL中支持的联合查询：inner join, join, left join, right join。参看以下示例：

查询某个公司的员工

测试查询

  1.     public void findEmployeesByCompanyName() {
  2.         String name = "三国";
  3.         // JPAQuery<Customer> jpaQuery = jpaQueryFactory.select(qEmployee).from(qCompany, qEmployee).where(qCompany.id.eq(qEmployee.companyId).and(qCompany.name.eq(name)));
  4.         JPAQuery<Employee> jpaQuery = jpaQueryFactory
  5.                 .select(qEmployee)
  6.                 .from(qEmployee)
  7.                 .rightJoin(qCompany)    //右连接
  8.                 .on(qCompany.id.eq(qEmployee.companyId))
  9.                 .where(qCompany.name.eq(name));
  11.         List<Employee> employees = jpaQuery.fetch();
  12.         System.out.println("员工信息如下：");
  13.         for (Employee employee : employees) {
  14.             System.out.println("------分割线------");
  15.             System.out.println(employee.toString());
  16.         }
  17.     }

执行SQL:

  1. Hibernate: 
  2. select 
  3.     employee0_.id as id1_1_, 
  4.     employee0_.age as age2_1_, 
  5.     employee0_.company_id as company_3_1_, 
  6.     employee0_.gender as gender4_1_, 
  7.     employee0_.name as name5_1_, 
  8.     employee0_.phone as phone6_1_ 
  9. from employee employee0_ 
  10.     right outer join company company1_ on (company1_.id=employee0_.company_id) 
  11. where company1_.name=?

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. Employee(id=1, name=李白, gender=1, phone=10086, companyId=1, age=22)
  4. ------分割线------
  5. Employee(id=3, name=孙尚香, gender=0, phone=10011, companyId=1, age=23)
  6. ------分割线------
  7. Employee(id=2, name=张飞, gender=1, phone=10010, companyId=1, age=20)
  8. ------分割线------
  9. Employee(id=5, name=刘备, gender=1, phone=11111, companyId=1, age=25)

子查询

使用 JPAExpressions 的静态工厂方法创建一个子查询，并且通过调用 from，where 等定义查询参数。

示例：查询 在上海的公司的所有员工

执行查询：

  1.     @Test
  2.     public void findEmployeesInCompanyInShangHai() {
  3.         String name = "三国";
  5.         JPQLQuery<String> jpqlQuery = JPAExpressions
  6.                 .select(qCompany.id)
  7.                 .from(qCompany)
  8.                 .where(qCompany.city.eq("上海"));
  10.         List<Employee> employees = jpaQueryFactory.selectFrom(qEmployee)
  11.                 .where(qEmployee.companyId.in(jpqlQuery)
  12.                 ).fetch();
  14.         System.out.println("员工信息如下：");
  15.         for (Employee employee : employees) {
  16.             System.out.println("------分割线------");
  17.             System.out.println(employee.toString());
  18.         }
  19.     }

执行查询：

  1. Hibernate: 
  2. select 
  3.     employee0_.id as id1_1_, 
  4.     employee0_.age as age2_1_, 
  5.     employee0_.company_id as company_3_1_, 
  6.     employee0_.gender as gender4_1_, 
  7.     employee0_.name as name5_1_, 
  8.     employee0_.phone as phone6_1_ 
  9. from employee employee0_ 
  10. where employee0_.company_id in (
  11.     select company1_.id 
  12.     from company company1_ 
  13.     where company1_.city=?
  14. )

输出：

  1. 员工信息如下：
  2. ------分割线------
  3. Employee(id=4, name=孙悟空, gender=1, phone=00001, companyId=2, age=89)
  4. ------分割线------
  5. Employee(id=6, name=复苏刘, gender=0, phone=12123, companyId=2, age=10)

聚合函数

QueryDSL内置了SQL所有的聚合函数,下面介绍我们常用的几个聚合函数。

sum 、avg 、count、max、min

  1.     @Test
  2.     void analyzeEmployee(){
  3.         long count = jpaQueryFactory
  4.                 .selectFrom(qEmployee)
  5.                 .fetchCount();
  6.         //Long count = jpaQueryFactory.select(qEmployee.count()).from(qEmployee).fetchOne();
  7.         System.out.println("用户表中共有"+count+"条数据！");
  9.         Integer sum = jpaQueryFactory
  10.                 .select(qEmployee.age.sum())
  11.                 .from(qEmployee).fetchOne();
  12.         System.out.println("员工总年龄是"+sum);
  14.         Integer max = jpaQueryFactory.select(qEmployee.age.max()).from(qEmployee).fetchOne();
  15.         System.out.println("员工最大年龄是"+max);
  17.         Integer min = jpaQueryFactory.select(qEmployee.age.min()).from(qEmployee).fetchOne();
  18.         System.out.println("员工最大年龄是"+min);
  20.         Double avg = jpaQueryFactory.select(qEmployee.age.avg()).from(qEmployee).fetchOne();
  21.         System.out.println("员工平均年龄是"+avg);
  22.     }

执行SQL:

  1. Hibernate: select count(employee0_.id) as col_0_0_ from employee employee0_
  2. Hibernate: select sum(employee0_.age) as col_0_0_ from employee employee0_
  3. Hibernate: select max(employee0_.age) as col_0_0_ from employee employee0_
  4. Hibernate: select min(employee0_.age) as col_0_0_ from employee employee0_
  5. Hibernate: select avg(employee0_.age) as col_0_0_ from employee employee0_

输出：

  1. 用户表中共有6条数据！
  2. 员工总年龄是189
  3. 员工最大年龄是89
  4. 员工最大年龄是10
  5. 员工平均年龄是31.5

group by

查询北京上海两座城市员工的的平均年龄

  1. @Test
  2.     void testGroupBy(){
  3.         List<Tuple> tuples = jpaQueryFactory.select(qCompany.city, qEmployee.age.avg())
  4.                 .from(qCompany, qEmployee)
  5.                 .where(qEmployee.companyId.eq(qCompany.id))
  6.                 .groupBy(qCompany.city).fetch();
  8.         System.out.println("查询结果：");
  9.         for (Tuple tuple:tuples){
  10.             String city = tuple.get(qCompany.city);
  11.             Double avg = tuple.get(qEmployee.age.avg());
  12.             System.out.println(city+"城市的平均员工年龄是："+avg);
  13.         }
  14.     }

执行SQl:

  1. Hibernate: 
  2. select 
  3.     company0_.city as col_0_0_, 
  4.     avg(employee1_.age) as col_1_0_ 
  5. from company company0_ cross join employee employee1_ 
  6. where employee1_.company_id=company0_.id 
  7. group by company0_.city

输出：

  1. 查询结果：
  2. 北京城市的平均员工年龄是：22.5
  3. 上海城市的平均员工年龄是：49.5

更新删除

更新员工信息

在Querydsl JPA中，更新语句是简单的 update-set/where-execute 形式。下面是一个例子：

  1. queryFactory.update(customer).where(customer.name.eq("Bob"))
  2.         .set(customer.name, "Bobby")
  3.         .execute();

调用 set 方法以 SQL-Update-style 方式定义要更新的属性，execute 调用指行更新操作并返回被更新的实体的数量。

JPA中的DML语句并不考虑JPA级联规则，也不提供细粒度二级缓存的交互。

测试代码：

  1.     @Test
  2.     @Transactional() //更新需加入事务，不然抛出异常 Executing an update/delete query
  3.     public void updateEmployee(){
  4.         long execute = jpaQueryFactory.update(qEmployee)
  5.                 .set(qEmployee.age, 100)
  6.                 .set(qEmployee.phone, "10009")
  7.                 .where(qEmployee.name.eq("李白"))
  8.                 .execute();
  9.         System.out.println("更新成功，影响了"+execute+"条数据！");
  10.     }

执行SQL：

  1. Hibernate: update employee set age=?, phone=? where name=?

输出：

  1. 更新成功，影响了1条数据！

删除员工信息

在Querydsl JPA中，删除语句是简单的 delete-where-execute 形式。下面是一个例子：

  1. QCustomer customer = QCustomer.customer;
  2. // delete all customers
  3. queryFactory.delete(customer).execute();
  4. // delete all customers with a level less than 3
  5. queryFactory.delete(customer).where(customer.level.lt(3)).execute();

调用 where 方法是可选的，调用 execute 方法是执行删除操作并返回被删除实体的数量。

JPA中的DML语句并不考虑JPA级联规则，也不提供细粒度二级缓存的交互。

测试代码：

  1.     @Test
  2.     @Transactional() //删除需加入事务，不然抛出异常 Executing an update/delete query
  3.     void deleteEmployee(){
  4.         long result = jpaQueryFactory.delete(qEmployee).where(qEmployee.age.gt(30)).execute();
  5.         System.out.println("删除更成功，影响了"+result+"条数据！");
  6.     }

执行SQL：

  1. Hibernate: delete from employee where age>?

输出：

  1. 删除更成功，影响了1条数据！

项目地址：