SpringDataJPA 整合 QueryDSL

QueryDSLQueryDSL 简介

QueryDSL 目前是一个非常活跃的开源项目 Github 上的发布的 Release 多达版本 251 目前最新版本是个版本 4.2.1 ，并且由 Querydsl Google组 和 StackOverflow 两个团队提供支持。

QueryDSL 它是一个框架，可以用来构建类似SQL的静态类型。可以通过QueryDSL等进行查询 API 构造查询，而不是将查询编写成内联字符串或将其外部化为XML文件。

例如，与简单字符串相比，使用 API 的好处是

• 完成IDE中的代码
• 几乎没有语法无效的查询
• 域类型和属性可以安全引用
• 更好地重构域类型的变化

QueryDSL使用

Repository层

继承QuerydslPredicateexecutor接口，实现分页查询

导入依赖

<!--依赖QueryDSL--><dependency>    <groupId>com.querydsl</groupId>    <artifactId>querydsl-jpa</artifactId>    <version>4.4.0</version></dependency><!--自动生成查询实体依赖于查询实体--><dependency>    <groupId>com.querydsl</groupId>    <artifactId>querydsl-apt</artifactId>    <version>4.4.0</version></dependency>

插件配置

添加此插件是为了使程序自动生成 query type (查询实体，命名方式为:"Q“+对应实体名)。 依赖上述介绍 querydsl-apt 也就是说，为此服务的插件。

注：如果在使用过程中遇到问题 query type 不能自动生成的情况可以通过maven更新项目来解决(右键项目 -> Maven -> Update Folders)。

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

Qxxx类根据实体类自动生成

简单的增删改查

package com.du.pojo.service;import com.du.pojo.entity.QUser;import com.du.pojo.entity.User;import com.du.pojo.repository.UserRepository;import com.querydsl.core.BooleanBuilder;import com.querydsl.jpa.impl.JPAQueryFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional;import org.thymeleaf.util.StringUtils;import javax.annotation.PostConstruct;import javax.persistence.EntityManager;import java.util.List;@Servicepublic class UserServiceImpl implements UserService {    //JPA    @Autowired    private UserRepository userRepository;    ///查询工厂实体    @Autowired    private EntityManager entityManager;    private JPAQueryFactory queryFactory;    ///实例化控制器完成后，执行该方法的实例化JPAQueryFactory    @PostConstruct    public void initFactory() {        queryFactory = new JPAQueryFactory(entityManager);    }    @Override    public List<User> findAll(User user) {        // 使用QUSer进行查询        QUser qUser = QUser.user;        // 定于获取条件        BooleanBuilder booleanBuilder = new BooleanBuilder();        // 要查询的条件        if (!StringUtils.isEmpty(user.getName())) {            // 设置要查询的条件            booleanBuilder.and(qUser.name.contains(user.getName()));        }        // queryFactory 上面定义的工厂实体        // select(生成的实体字段).from(生成实体名称).where(上述查询条件).fetch()查询        return queryFactory.select(qUser)                .from(qUser)                .where(booleanBuilder)                .fetch();    }    @Override    public User findUserById(Integer id) {        // 使用QUser查询        QUser Quser = QUser.user;        // 条件        BooleanBuilder booleanBuilder = new BooleanBuilder();        booleanBuilder.and(Quser.id.eq(id));        //使用queryFactory查询        return queryFactory.select(Quser)                .from(Quser)                .where(booleanBuilder)                .fetchOne();    }    @Override    public long dropUserById(Integer id) {        QUser qUser = QUser.user;        return queryFactory.delete(qUser)                .where(new BooleanBuilder().and(qUser.id.eq(id)))                .execute();    }    @Override    @Transactional    public Long saveUser(User user) {        userRepository.save(user);        return 1L;    }    @Override    public Long updateUser(User user) {        QUser qUser = QUser.user;        return queryFactory.update(qUser)                .set(qUser.name, user.getName())                .set(qUser.password, user.getPassword())                .where(qUser.id.eq(user.getId()))                .execute();    }        @Override    public List<EmployeeEntity> page(Long page, Long size) {        QEmployeeDo qEmployeeDo = QEmployeeDo.employeeDo;        return jpaQueryFactory.selectFrom(qEmployeeDo)                .where(qEmployeeDo.deleted.isFalse())                .offset(page * size)                .limit(size)                .fetch()                .stream()                .map(EmployeeDo::toEntity)                .collect(Collectors.toList());    }}

连接查询

@Test    void findI() {        QDepartmentDo qDepartmentDo = QDepartmentDo.departmentDo;        QEmployeeDo qEmployeeDo = QEmployeeDo.employeeDo;        List<Tuple> list = jpaQueryFactory.select(                qEmployeeDo.id.as(员工id)，                qEmployeeDo.employee_name.as(员工姓名)，                qEmployeeDo.dept_name.as(员工部门)，                qDepartmentDo.id.as(部门id)，                qDepartmentDo.dept_name.as(部门名称)                .from(qDepartmentDo)                .innerJoin(qEmployeeDo)                .on(qDepartmentDo.dept_name.eq(qEmployeeDo.dept_name))                .fetch();        list.forEach(System.out::println);    }    @Test    void find() {        QDepartmentDo qDepartmentDo = QDepartmentDo.departmentDo;        QEmployeeDo qEmployeeDo = QEmployeeDo.employeeDo;        List<Tuple> list = jpaQueryFactory.select(                qEmployeeDo.id.as(员工id)，                qEmployeeDo.employee_name.as(员工姓名)，                qEmployeeDo.dept_name.as(员工部门)，                qDepartmentDo.id.as(部门id)，                qDepartmentDo.dept_name.as(部门名称)                .from(qDepartmentDo)                .rightJoin(qEmployeeDo)                .on(qDepartmentDo.dept_name.eq(qEmployeeDo.dept_name))                .fetch();        list.forEach(System.out::println);    }    @Test    void findL() {        QDepartmentDo qDepartmentDo = QDepartmentDo.departmentDo;        QEmployeeDo qEmployeeDo = QEmployeeDo.employeeDo;        List<Tuple> list = jpaQueryFactory.select(                qEmployeeDo.id.as(员工id)，                qEmployeeDo.employee_name.as(员工姓名)，                qEmployeeDo.dept_name.as(员工部门)，                qDepartmentDo.id.as(部门id)，                qDepartmentDo.dept_name.as(部门名称)                .from(qDepartmentDo)                .leftJoin(qEmployeeDo)                .on(qDepartmentDo.dept_name.eq(qEmployeeDo.dept_name))                .fetch();        list.forEach(System.out::println);    }

定制返回对象

当我们集成queryDsl时，它通常是这样使用的

@Overridepublic List<CityHotelVo> findcityHotel() {    JPAQuery<CityHotelVo> query = new JPAQuery<>(em);    QTCity c = QTCity.tCity;    QTHotel h = QTHotel.tHotel;    JPAQuery<Tuple> on = query.select(        c.id,        c.name,        h.name,        h.address).from(c).leftJoin(h).on(c.id.eq(h.city));    QueryResults<Tuple> rts = on.fetchResults();    List<Tuple> results = rts.getResults();    return results.stream().map(CityHotelVo::new).collect(Collectors.toList());}

转Vo实现

public CityHotelVo(Tuple t) {    this.id = t.get(QTCity.tCity.id);    this.cityName = t.get(QTCity.tCity.name);    this.hotelName = t.get(QTHotel.tHotel.name);    this.address = t.get(QTHotel.tHotel.address);}

回到List，我们还需要手动将Tuple转换为我们自定义的VO对象，以下总结了几种可以自动将Tuple转换为VO的实现。

方式一

/** * 方法1：使用Bean投影 * @return */@Overridepublic List<CityHotelVo> findcityHotel_2() {    JPAQuery<CityHotelVo> query = new JPAQuery<>(em);    QTCity c = QTCity.tCity;    QTHotel h = QTHotel.tHotel;    List<CityHotelVo> results1 = query.select(Projections.bean(CityHotelVo.class,                                                               c.id.as("id"),                                                               c.name.as("cityName"),                                                               h.name.as("hotelName"),                                                               h.address.as("address"))).from(c).leftJoin(h).on(c.id.eq(h.city)).fetchResults().getResults();    return results1;}

方式二

/** * 方式二 fields 投影 * @return */@Overridepublic List<CityHotelVo2> projectionsFields() {    JPAQuery<CityHotelVo> query = new JPAQuery<>(em);    QTCity c = QTCity.tCity;    QTHotel h = QTHotel.tHotel;    JPAQuery<CityHotelVo2> on = query.select(        Projections.fields(CityHotelVo2.class,                           c.id,                           c.name,                           h.address))        .from(c).leftJoin(h).on(c.id.eq(h.city));    List<CityHotelVo2> resultList = on.createQuery().getResultList();    return resultList;}

方式三

/** * @return */@Overridepublic List<CityHotelVo2> findcityHotel_31() {    QTCity c = QTCity.tCity;    QTHotel h = QTHotel.tHotel;    JPAQueryFactory queryFactory = new JPAQueryFactory(em);    JPAQuery<CityHotelVo2> on = queryFactory.select(        Projections.constructor(CityHotelVo2.class,                                c.id,                                c.name,                                h.address))        .from(c).leftJoin(h).on(c.id.eq(h.city));    List<CityHotelVo2> results = on.fetchResults().getResults();    return results;}

注意这种构造方式，只支持数值和String类型的映射处理。当您定义了Date等类型时，需要在构造函数中构建如下

@Data@Accessors(chain = true)public class CityHotelvo4 implements Serializable {    private static final long serialVersionUID = 2546523L;    private Integer id;    private String cityName;    private String hotelName;    private String address;    private LocalDateTime formatTime;    public CityHotelvo4(Integer id, String cityName, String hotelName, String address, String formatTime) throws ParseException {        this.id = id;        this.cityName = cityName;        this.hotelName = hotelName;        this.address = address;        this.formatTime = DateUtils.parseLocalDateTime(formatTime);    }}

API操作字符串

//字符串拼接//**this + str     * Create a {@code concat(this, str)} expression     *     * <p>Get the concatenation of this and str</p>     *     * @param str string to append     * @return this + str     */public StringExpression append(Expression<String> str) {    return Expressions.stringOperation(Ops.CONCAT, mixin, str);}/**this + str     * Create a {@code concat(this, str)} expression     *     * <p>Get the concatenation of this and str</p>     *     * @param str string to append     * @return this + str     */public StringExpression concat(Expression<String> str) {    return append(str);}/**str + this     * Create a {@code concat(str, this)} expression     *     * <p>Prepend the given String and return the result</p>     *     * @param str string     * @return str + this     */public StringExpression prepend(Expression<String> str) {    return Expressions.stringOperation(Ops.CONCAT, str, mixin);}//是否包含某个字符串//**     * Create a {@code this.contains(str)} expression     *     * <p>Returns true if the given String is contained</p>     *     * @param str string     * @return this.contains(str)     * @see java.lang.String#contains(CharSequence)     */public BooleanExpression contains(Expression<String> str) {    return Expressions.booleanOperation(Ops.STRING_CONTAINS, mixin, str);}/**是否包含字符串，忽略大小写     * Create a {@code this.containsIgnoreCase(str)} expression     *     * <p>Returns true if the given String is contained, compare case insensitively</p>     *     * @param str string     * @return this.containsIgnoreCase(str) expression     */public BooleanExpression containsIgnoreCase(Expression<String> str) {    return Expressions.booleanOperation(Ops.STRING_CONTAINS_IC, mixin, str);}//是否以某个字符串结尾//**     * Create a {@code this.endsWith(str)} expression     *     * <p>Returns true if this ends with str</p>     *     * @param str string     * @return this.endsWith(str)     * @see java.lang.String#endsWith(String)     */public BooleanExpression endsWith(Expression<String> str) {    return Expressions.booleanOperation(Ops.ENDS_WITH, mixin, str);}//是否以某个字符串结束，忽略大小写publicliclic BooleanExpression endsWithIgnoreCase(Expression<String> str) {    return Expressions.booleanOperation(Ops.ENDS_WITH_IC, mixin, str);}

API操作Number

//比较max(最大值)，min(最小值)goe(this >= num),goeAll(this >= nums),goeAny(this >= any num)gt(this > num),gtAll(this > nums),gtAny(this > any num)loe(this <= num),loeAll(this <= nums),loeany(this <= any num)lt(this < num),ltAll(this < nums),ltAny(this < any num)between(from <= this <= to),notBetween(this < from || this > to this < from || this > to)//操作add(求和)，avg(平均值)，pide(除法,this / num)    ///字节转换byteValue(num转换byte)    //Expresion表达式mod(return this, num),multiply(this * num),negate(this * -1 取反),subtract(this - right)