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

2023-09-14 12:41:07 65

前言

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

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

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

1、pom文件中，加入依赖



com.querydsl
querydsl-jpa


com.querydsl
querydsl-apt
provided

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


com.mysema.maven
apt-maven-plugin
1.1.3



process


target/generated-sources/java
com.querydsl.apt.jpa.JPAAnnotationProcessor

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

packagecom.chhliu.springboot.jpa.entity;
importstaticcom.querydsl.core.types.PathMetadataFactory.*;
importcom.querydsl.core.types.dsl.*;
importcom.querydsl.core.types.PathMetadata;
importjavax.annotation.Generated;
importcom.querydsl.core.types.Path;
/**
*QUserisaQuerydslquerytypeforUser
*/
@Generated("com.querydsl.codegen.EntitySerializer")
publicclassQUserextendsEntityPathBase{
privatestaticfinallongserialVersionUID=1153899872L;
publicstaticfinalQUseruser=newQUser("user");
publicfinalStringPathaddress=createString("address");
publicfinalNumberPathage=createNumber("age",Integer.class);
publicfinalNumberPathid=createNumber("id",Integer.class);
publicfinalStringPathname=createString("name");
publicQUser(Stringvariable){
super(User.class,forVariable(variable));
}

publicQUser(Pathpath){
super(path.getType(),path.getMetadata());
}

publicQUser(PathMetadatametadata){
super(User.class,metadata);
}
}

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

target/generated-sources/java

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

本文涉及到的Entity如下：

packagecom.chhliu.springboot.jpa.entity;
importjava.io.Serializable;
importjavax.persistence.Entity;
importjavax.persistence.GeneratedValue;
importjavax.persistence.GenerationType;
importjavax.persistence.Id;
importjavax.persistence.Table;
@Entity
@Table(name="t_user")
publicclassUserimplementsSerializable{
/**
*
*/
privatestaticfinallongserialVersionUID=1L;
@Id()
@GeneratedValue(strategy=GenerationType.AUTO)
privateintid;
privateStringname;
privateStringaddress;
privateintage;
…………省略getter，setter方法…………
/**
*attention:
*Details：方便查看测试结果
*@authorchhliu
*/
@Override
publicStringtoString(){
return"User[id="+id+",name="+name+",address="+address+",age="+age+"]";
}
}

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

packagecom.chhliu.springboot.jpa.entity;
importjavax.persistence.CascadeType;
importjavax.persistence.Entity;
importjavax.persistence.GeneratedValue;
importjavax.persistence.Id;
importjavax.persistence.OneToOne;
importjavax.persistence.Table;
/**
*描述：TODO
*@authorchhliu
*/
@Entity
@Table(name="PERSON")
publicclassPerson{
@Id
@GeneratedValue
privateIntegerid;
privateStringname;
privateStringaddress;

@OneToOne(mappedBy="person",cascade={CascadeType.PERSIST,CascadeType.REMOVE,CascadeType.MERGE})
privateIDCardidCard;
…………省略getter，setter方法…………
@Override
publicStringtoString(){
return"Person[id="+id+",name="+name+",address="+address+",idCard="+idCard+"]";
}
}

packagecom.chhliu.springboot.jpa.entity;
importjavax.persistence.CascadeType;
importjavax.persistence.Entity;
importjavax.persistence.FetchType;
importjavax.persistence.GeneratedValue;
importjavax.persistence.Id;
importjavax.persistence.OneToOne;
importjavax.persistence.Table;
/**
*描述：
*@authorchhliu
*/
@Entity
@Table(name="IDCARD")
publicclassIDCard{
@Id
@GeneratedValue
privateIntegerid;
privateStringidNo;
@OneToOne(cascade={CascadeType.MERGE,CascadeType.REMOVE,CascadeType.PERSIST},fetch=FetchType.EAGER)
privatePersonperson;
…………省略getter，setter方法…………
@Override
publicStringtoString(){
return"IDCard[id="+id+",idNo="+idNo+",person="+person+"]";
}
}

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

packagecom.chhliu.springboot.jpa.entity;
importjava.util.List;
importjavax.persistence.CascadeType;
importjavax.persistence.Column;
importjavax.persistence.Entity;
importjavax.persistence.FetchType;
importjavax.persistence.GeneratedValue;
importjavax.persistence.Id;
importjavax.persistence.OneToMany;
importjavax.persistence.Table;
/**
*描述：Order实体类
*@authorchhliu
*/
@Entity
@Table(name="ORDER_C")
publicclassOrder{
@Id
@GeneratedValue
@Column(name="ID")
privateIntegerid;

@Column(length=20,name="ORDER_NAME")
privateStringorderName;

@Column(name="COUNT")
privateIntegercount;

@OneToMany(mappedBy="order",cascade={CascadeType.PERSIST,CascadeType.REMOVE},fetch=FetchType.EAGER)
privateListorderItems;

…………省略getter，setter方法…………
}

packagecom.chhliu.springboot.jpa.entity;
importjavax.persistence.CascadeType;
importjavax.persistence.Column;
importjavax.persistence.Entity;
importjavax.persistence.FetchType;
importjavax.persistence.GeneratedValue;
importjavax.persistence.Id;
importjavax.persistence.JoinColumn;
importjavax.persistence.ManyToOne;
importjavax.persistence.Table;

/**
*描述：OrderItem实体类
*@authorchhliu
*/
@Entity
@Table(name="ORDER_ITEM")
publicclassOrderItem{
@Id
@GeneratedValue
@Column(name="ID",nullable=false)
privateIntegerid;

@Column(name="ITEM_NAME",length=20)
privateStringitemName;

@Column(name="PRICE")
privateIntegerprice;

@ManyToOne(cascade={CascadeType.PERSIST,CascadeType.REMOVE,CascadeType.MERGE},fetch=FetchType.EAGER)
@JoinColumn(name="ORDER_ID")
privateOrderorder;

…………省略getter，setter方法…………
}

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

首先，我们来看单表操作

1、使用springdatajpa

要想使用springdatajpa提供的QueryDSL功能，很简单，直接继承接口即可。SpringDataJPA中提供了QueryDslPredicateExecutor接口,用于支持QueryDSL的查询操作接口，如下：

packagecom.chhliu.springboot.jpa.repository;
importorg.springframework.data.jpa.repository.JpaRepository;
importorg.springframework.data.querydsl.QueryDslPredicateExecutor;
importcom.chhliu.springboot.jpa.entity.User;
publicinterfaceUserRepositoryDlsextendsJpaRepository,QueryDslPredicateExecutor{
//继承接口
}

QueryDslPredicateExecutor接口提供了如下方法：

publicinterfaceQueryDslPredicateExecutor{
TfindOne(Predicatepredicate);
IterablefindAll(Predicatepredicate);
IterablefindAll(Predicatepredicate,Sortsort);
IterablefindAll(Predicatepredicate,OrderSpecifier...orders);
IterablefindAll(OrderSpecifier...orders);
PagefindAll(Predicatepredicate,Pageablepageable);
longcount(Predicatepredicate);
booleanexists(Predicatepredicate);
}

以上方法的使用和springdatajpa中的其他接口使用方法类似，详情请参考：https://www.nhooo.com/article/137757.htm

测试如下：

publicUserfindUserByUserName(finalStringuserName){
/**
*该例是使用springdataQueryDSL实现
*/
QUserquser=QUser.user;
Predicatepredicate=quser.name.eq(userName);//根据用户名，查询user表
returnrepository.findOne(predicate);
}

对应的sql如下：

selectuser0_.idasid1_5_,user0_.addressasaddress2_5_,user0_.ageasage3_5_,user0_.nameasname4_5_fromt_useruser0_where user0_.name=?

单表操作示例代码如下：

packagecom.chhliu.springboot.jpa.repository;
importjava.util.List;
importjavax.persistence.EntityManager;
importjavax.persistence.PersistenceContext;
importjavax.persistence.Query;
importjavax.transaction.Transactional;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.data.domain.Page;
importorg.springframework.data.domain.PageRequest;
importorg.springframework.data.domain.Sort;
importorg.springframework.stereotype.Component;
importcom.chhliu.springboot.jpa.entity.QUser;
importcom.chhliu.springboot.jpa.entity.User;
importcom.querydsl.core.types.Predicate;
importcom.querydsl.jpa.impl.JPAQueryFactory;

/**
*描述：QueryDSLJPA
*@authorchhliu
*/
@Component
@Transactional
publicclassUserRepositoryManagerDsl{
@Autowired
privateUserRepositoryDlsrepository;

@Autowired
@PersistenceContext
privateEntityManagerentityManager;

privateJPAQueryFactoryqueryFactory;

@PostConstruct
publicvoidinit(){
queryFactory=newJPAQueryFactory(entityManager);
}

publicUserfindUserByUserName(finalStringuserName){
/**
*该例是使用springdataQueryDSL实现
*/
QUserquser=QUser.user;
Predicatepredicate=quser.name.eq(userName);
returnrepository.findOne(predicate);
}

/**
*attention:
*Details：查询user表中的所有记录
*/
publicListfindAll(){
QUserquser=QUser.user;
returnqueryFactory.selectFrom(quser)
.fetch();
}

/**
*Details：单条件查询
*/
publicUserfindOneByUserName(finalStringuserName){
QUserquser=QUser.user;
returnqueryFactory.selectFrom(quser)
.where(quser.name.eq(userName))
.fetchOne();
}

/**
*Details：单表多条件查询
*/
publicUserfindOneByUserNameAndAddress(finalStringuserName,finalStringaddress){
QUserquser=QUser.user;
returnqueryFactory.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查询
*/
publicListfindUsersByJoin(){
QUserquser=QUser.user;
QUseruserName=newQUser("name");
returnqueryFactory.selectFrom(quser)
.innerJoin(quser)
.on(quser.id.intValue().eq(userName.id.intValue()))
.fetch();
}

/**
*Details：将查询结果排序
*/
publicListfindUserAndOrder(){
QUserquser=QUser.user;
returnqueryFactory.selectFrom(quser)
.orderBy(quser.id.desc())
.fetch();
}

/**
*Details：GroupBy使用
*/
publicListfindUserByGroup(){
QUserquser=QUser.user;
returnqueryFactory.select(quser.name)
.from(quser)
.groupBy(quser.name)
.fetch();
}

/**
*Details：删除用户
*/
publiclongdeleteUser(StringuserName){
QUserquser=QUser.user;
returnqueryFactory.delete(quser).where(quser.name.eq(userName)).execute();
}

/**
*Details：更新记录
*/
publiclongupdateUser(finalUseru,finalStringuserName){
QUserquser=QUser.user;
returnqueryFactory.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
*/
publicUserfindOneUserByOriginalSql(finalStringuserName){
QUserquser=QUser.user;
Queryquery=queryFactory.selectFrom(quser)
.where(quser.name.eq(userName)).createQuery();
return(User)query.getSingleResult();
}

/**
*Details：分页查询单表
*/
publicPagefindAllAndPager(finalintoffset,finalintpageSize){
Predicatepredicate=QUser.user.id.lt(10);
Sortsort=newSort(newSort.Order(Sort.Direction.DESC,"id"));
PageRequestpr=newPageRequest(offset,pageSize,sort);
returnrepository.findAll(predicate,pr);
}
}

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

packagecom.chhliu.springboot.jpa.repository;
importjava.util.ArrayList;
importjava.util.List;
importjavax.annotation.PostConstruct;
importjavax.persistence.EntityManager;
importjavax.persistence.PersistenceContext;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.stereotype.Component;
importcom.chhliu.springboot.jpa.dto.PersonIDCardDto;
importcom.chhliu.springboot.jpa.entity.QIDCard;
importcom.chhliu.springboot.jpa.entity.QPerson;
importcom.querydsl.core.QueryResults;
importcom.querydsl.core.Tuple;
importcom.querydsl.core.types.Predicate;
importcom.querydsl.jpa.impl.JPAQuery;
importcom.querydsl.jpa.impl.JPAQueryFactory;

@Component
publicclassPersonAndIDCardManager{
@Autowired
@PersistenceContext
privateEntityManagerentityManager;

privateJPAQueryFactoryqueryFactory;

@PostConstruct
publicvoidinit(){
queryFactory=newJPAQueryFactory(entityManager);
}

/**
*Details：多表动态查询
*/
publicListfindAllPersonAndIdCard(){
Predicatepredicate=(QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
JPAQueryjpaQuery=queryFactory.select(QIDCard.iDCard.idNo,QPerson.person.address,QPerson.person.name)
.from(QIDCard.iDCard,QPerson.person)
.where(predicate);
returnjpaQuery.fetch();
}

/**
*Details：将查询结果以DTO的方式输出
*/
publicListfindByDTO(){
Predicatepredicate=(QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
JPAQueryjpaQuery=queryFactory.select(QIDCard.iDCard.idNo,QPerson.person.address,QPerson.person.name)
.from(QIDCard.iDCard,QPerson.person)
.where(predicate);
Listtuples=jpaQuery.fetch();
Listdtos=newArrayList();
if(null!=tuples&&!tuples.isEmpty()){
for(Tupletuple:tuples){
Stringaddress=tuple.get(QPerson.person.address);
Stringname=tuple.get(QPerson.person.name);
StringidCard=tuple.get(QIDCard.iDCard.idNo);
PersonIDCardDtodto=newPersonIDCardDto();
dto.setAddress(address);
dto.setIdNo(idCard);
dto.setName(name);
dtos.add(dto);
}
}
returndtos;
}

/**
*Details：多表动态查询，并分页
*/
publicQueryResultsfindByDtoAndPager(intoffset,intpageSize){
Predicatepredicate=(QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
returnqueryFactory.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投影
*/
publicListfindByDTOUseBean(){
Predicatepredicate=(QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
returnqueryFactory.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
*/
publicListfindByDTOUseFields(){
Predicatepredicate=(QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
returnqueryFactory.select(
Projections.fields(PersonIDCardDto.class,QIDCard.iDCard.idNo,QPerson.person.address,QPerson.person.name))
.from(QIDCard.iDCard,QPerson.person)
.where(predicate)
.fetch();
}

/**
*Details：方式三：使用构造方法，注意构造方法中属性的顺序必须和构造器中的顺序一致
*/
publicListfindByDTOUseConstructor(){
Predicatepredicate=(QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
returnqueryFactory.select(
Projections.constructor(PersonIDCardDto.class,QPerson.person.name,QPerson.person.address,QIDCard.iDCard.idNo))
.from(QIDCard.iDCard,QPerson.person)
.where(predicate)
.fetch();
}

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

一对多示例：

packagecom.chhliu.springboot.jpa.repository;
importjava.util.List;
importjavax.annotation.PostConstruct;
importjavax.persistence.EntityManager;
importjavax.persistence.PersistenceContext;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.stereotype.Component;
importcom.chhliu.springboot.jpa.entity.QOrder;
importcom.chhliu.springboot.jpa.entity.QOrderItem;
importcom.querydsl.core.Tuple;
importcom.querydsl.core.types.Predicate;
importcom.querydsl.jpa.impl.JPAQuery;
importcom.querydsl.jpa.impl.JPAQueryFactory;
@Component
publicclassOrderAndOrderItemManager{

@Autowired
@PersistenceContext
privateEntityManagerentityManager;

privateJPAQueryFactoryqueryFactory;

@PostConstruct
publicvoidinit(){
queryFactory=newJPAQueryFactory(entityManager);
}

/**
*Details：一对多，条件查询
*/
publicListfindOrderAndOrderItemByOrderName(StringorderName){
//添加查询条件
Predicatepredicate=QOrder.order.orderName.eq(orderName);
JPAQueryjpaQuery=queryFactory.select(QOrder.order,QOrderItem.orderItem)
.from(QOrder.order,QOrderItem.orderItem)
.where(QOrderItem.orderItem.order.id.intValue().eq(QOrder.order.id.intValue()),predicate);

//拿到结果
returnjpaQuery.fetch();
}

/**
*Details：多表连接查询
*/
publicListfindAllByOrderName(StringorderName){
//添加查询条件
Predicatepredicate=QOrder.order.orderName.eq(orderName);
JPAQueryjpaQuery=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);
//拿到结果
returnjpaQuery.fetch();
}
}

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

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

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

热门推荐

随机推荐