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

详解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文件中，加入依赖

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实体类来说明，生成的查询类型如下：

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 {

private static final long serialVersionUID = 1153899872L;

public static final QUser user = new QUser("user");

public final StringPath address = createString("address");

public final NumberPath age = createNumber("age", Integer.class);

public final NumberPath 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命令，就会在

target/generated-sources/java

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

本文涉及到的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 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, QueryDslPredicateExecutor{

// 继承接口

}

QueryDslPredicateExecutor接口提供了如下方法：

public interface QueryDslPredicateExecutor {

T findOne(Predicate predicate);

Iterable findAll(Predicate predicate);

Iterable findAll(Predicate predicate, Sort sort);

Iterable findAll(Predicate predicate, OrderSpecifier>... orders);

Iterable findAll(OrderSpecifier>... orders);

Page findAll(Predicate predicate, Pageable pageable);

long count(Predicate predicate);

boolean exists(Predicate predicate);

}

以上方法的使用和spring data jpa中的其他接口使用方法类似，详情请参考：//jb51.net/article/137757.htm

测试如下：

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 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 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 findUserAndOrder(){

QUser quser = QUser.user;

return queryFactory.selectFrom(quser)

.orderBy(quser.id.desc())

.fetch();

}

/**

* Details：Group By使用

*/

public List 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 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 findAllPersonAndIdCard(){

Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());

JPAQuery 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 findByDTO(){

Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());

JPAQuery jpaQuery = queryFactory.select(QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name)

.from(QIDCard.iDCard, QPerson.person)

.where(predicate);

List tuples = jpaQuery.fetch();

List dtos = new ArrayList();

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 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 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 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 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 zadxYIrxclass OrderAndOrderItemManager {

@Autowired

@PersistenceContext

private EntityManager entityManager;

private JPAQueryFactory queryFactory;

@PostConstruct

public void init() {

queryFactory = new JPAQueryFactory(entityManager);

}

/**

* Details：一对多，条件查询

*/

public List findOrderAndOrderItemByOrderName(String orderName){

//添加查询条件

Predicate predicate = QOrder.order.orderName.eq(orderName);

JPAQuery 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 findAllByOrderName(String orderName){

//添加查询条件

Predicate predicate = QOrder.order.orderName.eq(orderName);

JPAQuery 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大大的简化了我们的操作

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。