详解spring boot jpa整合QueryDSL来简化复杂操作

前言

使用过spring data jpa的同学，都很清楚，对于复杂的sql查询，处理起来还是比较复杂的，而本文中的querydsl就是用来简化jpa操作的。

querydsl定义了一种常用的静态类型语法，用于在持久域模型数据之上进行查询。jdo和jpa是querydsl的主要集成技术。本文旨在介绍如何使用querydsl与jpa组合使用。jpa的querydsl是jpql和criteria查询的替代方法。querydsl仅仅是一个通用的查询框架，专注于通过java api构建类型安全的sql查询。

要想使用querydsl，需要做两个前提操作：

1、pom文件中，加入依赖

<!--query dsl --> 
  <dependency> 
   <groupid>com.querydsl</groupid> 
   <artifactid>querydsl-jpa</artifactid> 
  </dependency> 
  <dependency> 
   <groupid>com.querydsl</groupid> 
   <artifactid>querydsl-apt</artifactid> 
   <scope>provided</scope> 
  </dependency> 

2、pom文件中，加入编译插件

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

该插件会查找使用javax.persistence.entity注解的域类型，并为它们生成对应的查询类型。下面以user实体类来说明，生成的查询类型如下：

package com.chhliu.springboot.jpa.entity; 
import static com.querydsl.core.types.pathmetadatafactory.*; 
import com.querydsl.core.types.dsl.*; 
import com.querydsl.core.types.pathmetadata; 
import javax.annotation.generated; 
import com.querydsl.core.types.path; 
/** 
 * quser is a querydsl query type for user 
 */ 
@generated("com.querydsl.codegen.entityserializer") 
public class quser extends entitypathbase<user> { 
 private static final long serialversionuid = 1153899872l; 
 public static final quser user = new quser("user"); 
 public final stringpath address = createstring("address"); 
 public final numberpath<integer> age = createnumber("age", integer.class); 
 public final numberpath<integer> id = createnumber("id", integer.class); 
 public final stringpath name = createstring("name"); 
 public quser(string variable) { 
  super(user.class, forvariable(variable)); 
 } 
 
 public quser(path<? extends user> path) { 
  super(path.gettype(), path.getmetadata()); 
 } 
 
 public quser(pathmetadata metadata) { 
  super(user.class, metadata); 
 } 
} 

我们建立好实体类之后，然后运行mvn clean complie命令，就会在

<outputdirectory>target/generated-sources/java</outputdirectory> 

目录下生成对应的查询类型。然后将生成的类都拷贝到项目中，即可。

本文涉及到的entity如下：

package com.chhliu.springboot.jpa.entity; 
import java.io.serializable; 
import javax.persistence.entity; 
import javax.persistence.generatedvalue; 
import javax.persistence.generationtype; 
import javax.persistence.id; 
import javax.persistence.table; 
@entity 
@table(name="t_user") 
public class user implements serializable{ 
  /** 
  * 
  */ 
 private static final long serialversionuid = 1l;  
 @id() 
 @generatedvalue(strategy = generationtype.auto) 
 private int id; 
 private string name; 
 private string address; 
 private int age; 
 …………省略getter，setter方法………… 
 /** 
  * attention: 
  * details：方便查看测试结果 
  * @author chhliu 
  */ 
 @override 
 public string tostring() { 
  return "user [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]"; 
 } 
} 

上面的这个实体类，主要用于单表操作。

package com.chhliu.springboot.jpa.entity; 
import javax.persistence.cascadetype; 
import javax.persistence.entity; 
import javax.persistence.generatedvalue; 
import javax.persistence.id; 
import javax.persistence.onetoone; 
import javax.persistence.table; 
/** 
 * 描述：todo 
 * @author chhliu 
 */ 
@entity 
@table(name="person") 
public class person { 
 @id 
 @generatedvalue 
 private integer id; 
 private string name; 
 private string address; 
  
 @onetoone(mappedby="person", cascade={cascadetype.persist, cascadetype.remove, cascadetype.merge}) 
 private idcard idcard;   
  …………省略getter，setter方法………… 
 @override 
 public string tostring() { 
  return "person [id=" + id + ", name=" + name + ", address=" + address + ", idcard=" + idcard + "]"; 
 } 
} 

package com.chhliu.springboot.jpa.entity; 
import javax.persistence.cascadetype; 
import javax.persistence.entity; 
import javax.persistence.fetchtype; 
import javax.persistence.generatedvalue; 
import javax.persistence.id; 
import javax.persistence.onetoone; 
import javax.persistence.table; 
/** 
 * 描述： 
 * @author chhliu 
 */ 
@entity 
@table(name="idcard") 
public class idcard { 
 @id 
 @generatedvalue 
 private integer id; 
 private string idno; 
 @onetoone(cascade={cascadetype.merge, cascadetype.remove, cascadetype.persist}, fetch=fetchtype.eager) 
 private person person;  
  …………省略getter，setter方法………… 
 @override 
 public string tostring() { 
  return "idcard [id=" + id + ", idno=" + idno + ", person=" + person + "]"; 
 } 
} 

上面两个entity主要用于一对一关系的示例操作

package com.chhliu.springboot.jpa.entity; 
import java.util.list; 
import javax.persistence.cascadetype; 
import javax.persistence.column; 
import javax.persistence.entity; 
import javax.persistence.fetchtype; 
import javax.persistence.generatedvalue; 
import javax.persistence.id; 
import javax.persistence.onetomany; 
import javax.persistence.table; 
/** 
 * 描述：order实体类 
 * @author chhliu 
 */ 
@entity 
@table(name="order_c") 
public class order { 
 @id 
 @generatedvalue 
 @column(name="id") 
 private integer id; 
  
 @column(length=20, name="order_name") 
 private string ordername; 
  
 @column(name="count") 
 private integer count; 
  
 @onetomany(mappedby = "order",cascade={cascadetype.persist,cascadetype.remove},fetch = fetchtype.eager) 
 private list<orderitem> orderitems; 
   
  …………省略getter，setter方法………… 
} 

package com.chhliu.springboot.jpa.entity; 
import javax.persistence.cascadetype; 
import javax.persistence.column; 
import javax.persistence.entity; 
import javax.persistence.fetchtype; 
import javax.persistence.generatedvalue; 
import javax.persistence.id; 
import javax.persistence.joincolumn; 
import javax.persistence.manytoone; 
import javax.persistence.table; 
 
/** 
 * 描述：orderitem实体类 
 * @author chhliu 
 */ 
@entity 
@table(name="order_item") 
public class orderitem { 
 @id 
 @generatedvalue 
 @column(name="id", nullable=false) 
 private integer id; 
  
 @column(name="item_name", length=20) 
 private string itemname; 
  
 @column(name="price") 
 private integer price; 
  
 @manytoone(cascade={cascadetype.persist,cascadetype.remove, cascadetype.merge}, fetch=fetchtype.eager) 
 @joincolumn(name = "order_id") 
 private order order; 
 
  …………省略getter，setter方法………… 
} 

上面两个entity用于展示一对多关系的示例操作。

首先，我们来看单表操作

1、使用spring data jpa

要想使用spring data jpa提供的querydsl功能，很简单，直接继承接口即可。spring data jpa中提供了querydslpredicateexecutor接口,用于支持querydsl的查询操作接口，如下：

package com.chhliu.springboot.jpa.repository; 
import org.springframework.data.jpa.repository.jparepository; 
import org.springframework.data.querydsl.querydslpredicateexecutor; 
import com.chhliu.springboot.jpa.entity.user; 
public interface userrepositorydls extends jparepository<user, integer>, querydslpredicateexecutor<user>{
// 继承接口 
} 

querydslpredicateexecutor接口提供了如下方法：

public interface querydslpredicateexecutor<t> { 
 t findone(predicate predicate); 
 iterable<t> findall(predicate predicate); 
 iterable<t> findall(predicate predicate, sort sort); 
 iterable<t> findall(predicate predicate, orderspecifier<?>... orders); 
 iterable<t> findall(orderspecifier<?>... orders); 
 page<t> findall(predicate predicate, pageable pageable); 
 long count(predicate predicate); 
 boolean exists(predicate predicate); 
} 

以上方法的使用和spring data jpa中的其他接口使用方法类似，详情请参考：

测试如下：

public user finduserbyusername(final string username){ 
  /** 
   * 该例是使用spring data querydsl实现 
   */ 
  quser quser = quser.user; 
  predicate predicate = quser.name.eq(username);// 根据用户名，查询user表 
  return repository.findone(predicate); 
 } 

对应的sql如下：

select user0_.id as id1_5_, user0_.address as address2_5_, user0_.age as age3_5_, user0_.name as name4_5_ from t_user user0_ where  user0_.name=?

单表操作示例代码如下：

package com.chhliu.springboot.jpa.repository; 
import java.util.list; 
import javax.persistence.entitymanager; 
import javax.persistence.persistencecontext; 
import javax.persistence.query; 
import javax.transaction.transactional; 
import org.springframework.beans.factory.annotation.autowired; 
import org.springframework.data.domain.page; 
import org.springframework.data.domain.pagerequest; 
import org.springframework.data.domain.sort; 
import org.springframework.stereotype.component; 
import com.chhliu.springboot.jpa.entity.quser; 
import com.chhliu.springboot.jpa.entity.user; 
import com.querydsl.core.types.predicate; 
import com.querydsl.jpa.impl.jpaqueryfactory; 
 
/** 
 * 描述：querydsl jpa 
 * @author chhliu 
 */ 
@component 
@transactional 
public class userrepositorymanagerdsl { 
 @autowired 
 private userrepositorydls repository; 
  
 @autowired 
 @persistencecontext 
 private entitymanager entitymanager; 
 
  private jpaqueryfactory queryfactory; 
  
  @postconstruct 
  public void init() { 
  queryfactory = new jpaqueryfactory(entitymanager); 
  } 
 
  public user finduserbyusername(final string username){ 
  /** 
   * 该例是使用spring data querydsl实现 
   */ 
  quser quser = quser.user; 
  predicate predicate = quser.name.eq(username); 
  return repository.findone(predicate); 
 } 
  
 /** 
  * attention: 
  * details：查询user表中的所有记录 
  */ 
 public list<user> findall(){ 
  quser quser = quser.user; 
  return queryfactory.selectfrom(quser) 
     .fetch(); 
 } 
  
 /** 
  * details：单条件查询 
  */ 
 public user findonebyusername(final string username){ 
  quser quser = quser.user; 
  return queryfactory.selectfrom(quser) 
   .where(quser.name.eq(username)) 
   .fetchone(); 
 } 
  
 /** 
  * details：单表多条件查询 
  */ 
 public user findonebyusernameandaddress(final string username, final string address){ 
  quser quser = quser.user; 
  return queryfactory.select(quser) 
   .from(quser) // 上面两句代码等价与selectfrom 
   .where(quser.name.eq(username).and(quser.address.eq(address)))// 这句代码等同于where(quser.name.eq(username), quser.address.eq(address)) 
   .fetchone(); 
 } 
  
 /** 
  * details：使用join查询 
  */ 
 public list<user> findusersbyjoin(){ 
  quser quser = quser.user; 
  quser username = new quser("name"); 
  return queryfactory.selectfrom(quser) 
   .innerjoin(quser) 
   .on(quser.id.intvalue().eq(username.id.intvalue())) 
   .fetch(); 
 } 
  
 /** 
  * details：将查询结果排序 
  */ 
 public list<user> finduserandorder(){ 
  quser quser = quser.user; 
  return queryfactory.selectfrom(quser) 
   .orderby(quser.id.desc()) 
   .fetch(); 
 } 
  
 /** 
  * details：group by使用 
  */ 
 public list<string> finduserbygroup(){ 
  quser quser = quser.user; 
  return queryfactory.select(quser.name) 
     .from(quser) 
     .groupby(quser.name) 
     .fetch(); 
 } 
  
 /** 
  * details：删除用户 
  */ 
 public long deleteuser(string username){ 
  quser quser = quser.user; 
  return queryfactory.delete(quser).where(quser.name.eq(username)).execute(); 
 } 
  
 /** 
  * details：更新记录 
  */ 
 public long updateuser(final user u, final string username){ 
  quser quser = quser.user; 
  return queryfactory.update(quser).where(quser.name.eq(username)) 
   .set(quser.name, u.getname()) 
   .set(quser.age, u.getage()) 
   .set(quser.address, u.getaddress()) 
   .execute(); 
 } 
  
 /** 
  * details：使用原生query 
  */ 
 public user findoneuserbyoriginalsql(final string username){ 
  quser quser = quser.user; 
  query query = queryfactory.selectfrom(quser) 
    .where(quser.name.eq(username)).createquery(); 
  return (user) query.getsingleresult(); 
 } 
  
 /** 
  * details：分页查询单表 
  */ 
 public page<user> findallandpager(final int offset, final int pagesize){ 
  predicate predicate = quser.user.id.lt(10); 
  sort sort = new sort(new sort.order(sort.direction.desc, "id")); 
  pagerequest pr = new pagerequest(offset, pagesize, sort); 
  return repository.findall(predicate, pr); 
 } 
} 

多表操作示例(一对一)如下：

package com.chhliu.springboot.jpa.repository; 
import java.util.arraylist; 
import java.util.list; 
import javax.annotation.postconstruct; 
import javax.persistence.entitymanager; 
import javax.persistence.persistencecontext; 
import org.springframework.beans.factory.annotation.autowired; 
import org.springframework.stereotype.component; 
import com.chhliu.springboot.jpa.dto.personidcarddto; 
import com.chhliu.springboot.jpa.entity.qidcard; 
import com.chhliu.springboot.jpa.entity.qperson; 
import com.querydsl.core.queryresults; 
import com.querydsl.core.tuple; 
import com.querydsl.core.types.predicate; 
import com.querydsl.jpa.impl.jpaquery; 
import com.querydsl.jpa.impl.jpaqueryfactory; 
 
@component 
public class personandidcardmanager { 
 @autowired 
 @persistencecontext 
 private entitymanager entitymanager; 
  
 private jpaqueryfactory queryfactory; 
  
 @postconstruct 
 public void init() { 
  queryfactory = new jpaqueryfactory(entitymanager); 
 } 
  
 /** 
  * details：多表动态查询 
  */ 
 public list<tuple> findallpersonandidcard(){ 
  predicate predicate = (qperson.person.id.intvalue()).eq(qidcard.idcard.person.id.intvalue()); 
  jpaquery<tuple> jpaquery = queryfactory.select(qidcard.idcard.idno, qperson.person.address, qperson.person.name) 
    .from(qidcard.idcard, qperson.person) 
    .where(predicate); 
  return jpaquery.fetch(); 
 } 
  
 /** 
  * details：将查询结果以dto的方式输出 
  */ 
 public list<personidcarddto> findbydto(){ 
  predicate predicate = (qperson.person.id.intvalue()).eq(qidcard.idcard.person.id.intvalue()); 
  jpaquery<tuple> jpaquery = queryfactory.select(qidcard.idcard.idno, qperson.person.address, qperson.person.name) 
    .from(qidcard.idcard, qperson.person) 
    .where(predicate); 
  list<tuple> tuples = jpaquery.fetch(); 
  list<personidcarddto> dtos = new arraylist<personidcarddto>(); 
  if(null != tuples && !tuples.isempty()){ 
   for(tuple tuple:tuples){ 
    string address = tuple.get(qperson.person.address); 
    string name = tuple.get(qperson.person.name); 
    string idcard = tuple.get(qidcard.idcard.idno); 
    personidcarddto dto = new personidcarddto(); 
    dto.setaddress(address); 
    dto.setidno(idcard); 
    dto.setname(name); 
    dtos.add(dto); 
   } 
  } 
  return dtos; 
 } 
  
 /** 
  * details：多表动态查询，并分页 
  */ 
 public queryresults<tuple> findbydtoandpager(int offset, int pagesize){ 
  predicate predicate = (qperson.person.id.intvalue()).eq(qidcard.idcard.person.id.intvalue()); 
  return queryfactory.select(qidcard.idcard.idno, qperson.person.address, qperson.person.name) 
    .from(qidcard.idcard, qperson.person) 
    .where(predicate) 
    .offset(offset) 
    .limit(pagesize) 
    .fetchresults(); 
 } 
} 

上面将查询结果以dto的方式输出的示例中，在查询结束后，将查询结果手动的转换成了dto对象，这种方式其实不太优雅，querydsl给我们提供了更好的方式，见下面的示例：

/** 
  * details：方式一：使用bean投影 
  */ 
 public list<personidcarddto> findbydtousebean(){ 
  predicate predicate = (qperson.person.id.intvalue()).eq(qidcard.idcard.person.id.intvalue()); 
  return queryfactory.select( 
    projections.bean(personidcarddto.class, qidcard.idcard.idno, qperson.person.address, qperson.person.name)) 
    .from(qidcard.idcard, qperson.person) 
    .where(predicate) 
    .fetch(); 
 } 
  
 /** 
  * details：方式二：使用fields来代替setter 
  */ 
 public list<personidcarddto> findbydtousefields(){ 
  predicate predicate = (qperson.person.id.intvalue()).eq(qidcard.idcard.person.id.intvalue()); 
  return queryfactory.select( 
    projections.fields(personidcarddto.class, qidcard.idcard.idno, qperson.person.address, qperson.person.name)) 
    .from(qidcard.idcard, qperson.person) 
    .where(predicate) 
    .fetch(); 
 } 
  
 /** 
  * details：方式三：使用构造方法，注意构造方法中属性的顺序必须和构造器中的顺序一致 
  */ 
 public list<personidcarddto> findbydtouseconstructor(){ 
  predicate predicate = (qperson.person.id.intvalue()).eq(qidcard.idcard.person.id.intvalue()); 
  return queryfactory.select( 
    projections.constructor(personidcarddto.class, qperson.person.name, qperson.person.address, qidcard.idcard.idno)) 
    .from(qidcard.idcard, qperson.person) 
    .where(predicate) 
    .fetch(); 
 } 

上面只是提供了几种思路，当然，还可以使用@queryprojection来实现，非常灵活。

一对多示例：

package com.chhliu.springboot.jpa.repository; 
import java.util.list; 
import javax.annotation.postconstruct; 
import javax.persistence.entitymanager; 
import javax.persistence.persistencecontext; 
import org.springframework.beans.factory.annotation.autowired; 
import org.springframework.stereotype.component; 
import com.chhliu.springboot.jpa.entity.qorder; 
import com.chhliu.springboot.jpa.entity.qorderitem; 
import com.querydsl.core.tuple; 
import com.querydsl.core.types.predicate; 
import com.querydsl.jpa.impl.jpaquery; 
import com.querydsl.jpa.impl.jpaqueryfactory; 
@component 
public class orderandorderitemmanager { 
  
 @autowired 
 @persistencecontext 
 private entitymanager entitymanager; 
  
 private jpaqueryfactory queryfactory; 
  
 @postconstruct 
 public void init() { 
  queryfactory = new jpaqueryfactory(entitymanager); 
 } 
  
 /** 
  * details：一对多，条件查询 
  */ 
 public list<tuple> findorderandorderitembyordername(string ordername){ 
  //添加查询条件 
  predicate predicate = qorder.order.ordername.eq(ordername); 
  jpaquery<tuple> jpaquery = queryfactory.select(qorder.order, qorderitem.orderitem) 
          .from(qorder.order, qorderitem.orderitem) 
          .where(qorderitem.orderitem.order.id.intvalue().eq(qorder.order.id.intvalue()), predicate); 
   
  //拿到结果 
  return jpaquery.fetch(); 
 } 
  
 /** 
  * details：多表连接查询 
  */ 
 public list<tuple> findallbyordername(string ordername){ 
  //添加查询条件 
  predicate predicate = qorder.order.ordername.eq(ordername); 
  jpaquery<tuple> jpaquery = queryfactory.select(qorder.order, qorderitem.orderitem) 
          .from(qorder.order, qorderitem.orderitem) 
          .rightjoin(qorder.order) 
          .on(qorderitem.orderitem.order.id.intvalue().eq(qorder.order.id.intvalue())); 
  jpaquery.where(predicate); 
  //拿到结果 
  return jpaquery.fetch(); 
 } 
} 

从上面的示例中，我们可以看出，querydsl大大的简化了我们的操作

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。