2039859.jpg

前提是已經(jīng)搭好了 JPA 的框架。。。。。

1.使用 JPAQueryFactory 進(jìn)行查詢操作

1.1簡單查詢

分頁構(gòu)造信息

package cn.superfw.genesis.common.core;

public class ApiPagination {

    /** 當(dāng)前頁碼 */
    private Long page = 1L;
    /** 一頁表示件數(shù) */
    private Long limit = 10L;
    /** 總件數(shù) */
    private Long count = 0L;

    public ApiPagination() {
    }

    public ApiPagination(Long page, Long limit, Long count) {
        if (page != null && page > 1L) {
            this.page = page;
        }
        if (limit != null && limit > 0L) {
            this.limit = limit;
        }
        if (count != null && count > 0L) {
            this.count = count;
        }
    }

    public ApiPagination(Long page, Long limit) {
        this.page = page;
        this.limit = limit;
    }

    public Long getOffset() {
        return (this.page - 1) * this.limit;
    }

    public Long getOffsetLimit() {
        if (getOffset() + getLimit() > getCount()) {
            return getCount();
        } else {
            return getOffset() + getLimit();
        }
    }

    /**
     * 獲取 當(dāng)前頁碼
     *
     * @return page 當(dāng)前頁碼
     */
    public Long getPage() {
        return this.page;
    }

    /**
     * 設(shè)置 當(dāng)前頁碼
     *
     * @param page 當(dāng)前頁碼
     */
    public void setPage(Long page) {
        this.page = page;
    }

    /**
     * 獲取 一頁表示件數(shù)
     *
     * @return limit 一頁表示件數(shù)
     */
    public Long getLimit() {
        return this.limit;
    }

    /**
     * 設(shè)置 一頁表示件數(shù)
     *
     * @param limit 一頁表示件數(shù)
     */
    public void setLimit(Long limit) {
        this.limit = limit;
    }

    /**
     * 獲取 總件數(shù)
     *
     * @return count 總件數(shù)
     */
    public Long getCount() {
        return this.count;
    }

    /**
     * 設(shè)置 總件數(shù)
     *
     * @param count 總件數(shù)
     */
    public void setCount(Long count) {
        this.count = count;
    }
}

自定義返回類

package cn.superfw.genesis.common.core;

public class PlatformServiceResult<T> {

    /** 數(shù)據(jù) */
    private T data;

    /** 分頁構(gòu)造器 */
    private ApiPagination pagination;

    /** 構(gòu)造函數(shù) */
    public PlatformServiceResult(T data, ApiPagination pagination) {
        super();
        this.data = data;
        this.pagination = pagination;
    }

    /**
     * @return the data
     */
    public T getData() {
        return data;
    }

    /**
     * @param data the data to set
     */
    public void setData(T data) {
        this.data = data;
    }

    /**
     * @return the pagination
     */
    public ApiPagination getPagination() {
        return pagination;
    }

    /**
     * @param pagination the pagination to set
     */
    public void setPagination(ApiPagination pagination) {
        this.pagination = pagination;
    }

}

UserRepositoryDsl

package cn.superfw.genesis.zs.repository;


import cn.superfw.genesis.zs.domain.UserEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface UserRepositoryDsl extends JpaRepository<UserEntity, Long>, QuerydslPredicateExecutor<UserEntity> {

    @Query(value="select * from user where (:name is null or name like %:name%) and (coalesce (:ageList,:isnull) is null or age in (:ageList))",
            countQuery="select * from user where (:name is null or name like %:name%) and (coalesce (:ageList,:isnull) is null or age in (:ageList))",nativeQuery=true)
    Page<UserEntity> getUserByDsl(@Param(value="name") String name,
                                  @Param(value = "ageList") List<Integer> ageList,
                                  @Param(value="isnull") Long isnull,
                                  Pageable pageable);
}

邏輯實現(xiàn)類

//需要用的 jar 包
import com.querydsl.jpa.impl.JPAQueryFactory;
////////////////////////////////////////////////////////////////////////////////
package cn.superfw.genesis.zs.service.Impl;

import cn.superfw.genesis.common.core.ApiPagination;
import cn.superfw.genesis.common.core.PlatformServiceResult;
import cn.superfw.genesis.zs.domain.QUserEntity;
import cn.superfw.genesis.zs.domain.UserEntity;
import cn.superfw.genesis.zs.repository.UserRepositoryDsl;
import cn.superfw.genesis.zs.service.UserService;
import cn.superfw.genesis.zs.vo.RunScheduleVo;
import cn.superfw.genesis.zs.vo.UserVo;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import java.sql.Timestamp;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    JPAQueryFactory queryFactory;
    @Autowired
    UserRepositoryDsl userRepositoryDsl;


    /**
     * 動態(tài)條件查詢 hql
     * @param id
     * @param name
     * @param age
     * @param money
     * @param createdTime
     * @return
     */
    @Override
    public PlatformServiceResult<List<UserEntity>> findUsers(Long id, String name, Integer age, Double money, Timestamp createdTime,Long page, Long limit) {
        QUserEntity qUserEntity = QUserEntity.userEntity;
        // 拼造查詢條件
        BooleanExpression whereExp = qUserEntity.deleteStatus.eq(1);
        if (id != null){
            whereExp = whereExp.and(qUserEntity.id.eq(id));
        }
        //姓名模糊查詢
        if(StringUtils.isNotEmpty(name)){
            whereExp = whereExp.and(qUserEntity.name.contains(name));
        }
        if (age != null){
            whereExp = whereExp.and(qUserEntity.age.eq(age));
        }
        if (money != null){
            whereExp = whereExp.and(qUserEntity.money.eq(money));
        }
        if (createdTime != null){
            whereExp = whereExp.and(qUserEntity.createdTime.eq(createdTime));
        }
        //查詢所有(使用了上面自己拼接的查詢條件)
        List<UserEntity> userEntities = queryFactory.selectFrom(qUserEntity).where(whereExp).fetch();

        //簡單查詢（查詢 name 叫 周杰倫 的人）單個人 注：.fetch()結(jié)尾查詢的是列表，.fetchFirst() 或 .fetchOne() 查詢的是單個。
        UserEntity userEntity = queryFactory.selectFrom(qUserEntity).where(qUserEntity.name.eq("周杰倫")).fetchOne();

        //查詢某一個字段（查詢 id 為 1 的人的名字）
        String nName = queryFactory.select(qUserEntity.name).from(qUserEntity).where(qUserEntity.id.eq(id)).fetchFirst();

        //查詢返回自定義實體類(select中的字段順序需要和自定義實體類中我的構(gòu)造器順序一致)
        List<UserVo> userVos = queryFactory.select(Projections.constructor(UserVo.class,qUserEntity.id,qUserEntity.name,qUserEntity.age,qUserEntity.gender,qUserEntity.money))
                .from(qUserEntity).fetch();

        //分頁查詢
        //查詢到的數(shù)據(jù)數(shù)量
        Long count = queryFactory.select(qUserEntity.id).from(qUserEntity).where(whereExp).fetchCount();
        // 分頁控制信息構(gòu)造
        ApiPagination pagination =  new ApiPagination(page == null ? 1 : page, limit == null ? 20 : limit,count);
        //查詢數(shù)據(jù)
        List<UserEntity> data = queryFactory.selectFrom(qUserEntity).where(whereExp).offset(pagination.getOffset()).limit(pagination.getLimit()).fetch();

        return new PlatformServiceResult<List<UserEntity>>(data,pagination);
    }


    /**
     * 條件查詢?nèi)祟?sql
     * @param name
     * @param page
     * @param limit
     * @return
     */
    @Override
    public PlatformServiceResult<List<UserEntity>> findUsersForDSL(String name,List<Integer> ageList, Long page, Long limit) {
        //DSl中ageList判空時出錯，所以自己定義了一個isnull
        Long isnull = null;

        if (ageList!=null){
            if (ageList.size()==0){
                ageList=null;
            }
        }

        if (page==null){
            page = Long.valueOf(0);
        }else {
            page = page - 1;
        }
        //構(gòu)造分頁查詢
        Sort sort = new Sort(Sort.Direction.DESC, "id");
        Pageable pageable = PageRequest.of(page.intValue(),limit == null ? 20 : limit.intValue(),sort );
        Page<UserEntity> data = userRepositoryDsl.getUserByDsl(name,ageList,isnull,pageable);
        Long count = data.getTotalElements();
        ApiPagination pagination = new ApiPagination(page == null ? 1 : page, limit == null ? 20 : limit,count);
        return new PlatformServiceResult<List<UserEntity>>(data.getContent(),pagination);
    }


}