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 */@Overridepublic 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@GeneratedValueprivate Integer id;private String name;private String address;@OneToOne(mappedBy="person", cascade={CascadeType.PERSIST, CascadeType.REMOVE, CascadeType.MERGE})private IDCard idCard;             …………省略getter，setter方法…………@Overridepublic 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@GeneratedValueprivate Integer id;private String idNo;@OneToOne(cascade={CascadeType.MERGE, CascadeType.REMOVE, CascadeType.PERSIST}, fetch=FetchType.EAGER)private Person person;       …………省略getter，setter方法…………@Overridepublic 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接口}

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中的其他接口使用方法类似，详情请参考：

http://blog.csdn.net/liuchuanhong1/article/details/52042477

测试如下：

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@Transactionalpublic class UserRepositoryManagerDsl {@Autowiredprivate UserRepositoryDls repository;@Autowired@PersistenceContextprivate 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;@Componentpublic 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;@Componentpublic 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大大的简化了我们的操作