queryDsl多表联合查询,任意查询联合字段

简介: 这是我的通用方法package com.spark.bitrade.service.Base;import com.querydsl.core.Tuple;import com.

这是我的通用方法

package com.spark.bitrade.service.Base;

import com.querydsl.core.Tuple;
import com.querydsl.core.types.EntityPath;
import com.querydsl.core.types.Expression;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.EntityPathBase;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.spark.bitrade.pagination.PageListMapResult;
import com.spark.bitrade.pagination.PageResult;
import com.spark.bitrade.pagination.QueryDslContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author rongyu
 * @description
 * @date 2018/1/18 10:29
 */
@Component
public class BaseService<T> {

    //JPA查询工厂
    @Autowired
    protected JPAQueryFactory queryFactory;

    @Autowired
    protected JPAQueryFactory jpaQueryFactory;

    @Autowired
    @PersistenceContext
    protected EntityManager em;
    /**
     * 查询列表
     *
     * @param pageNo             分页参数
     * @param pageSize           分页大小
     * @param predicateList      查询条件
     * @param entityPathBase     查询表
     * @param orderSpecifierList 排序条件
     * @return
     */
    @Transactional(readOnly = true)
    public PageResult<T> queryDsl(Integer pageNo, Integer pageSize, List<Predicate> predicateList, EntityPathBase<T> entityPathBase, List<OrderSpecifier> orderSpecifierList) {
        List<T> list;
        //查询表
        JPAQuery<T> jpaQuery = jpaQueryFactory.selectFrom(entityPathBase);
        //查询条件
        if (predicateList != null && predicateList.size() > 0)
            jpaQuery.where(predicateList.toArray(new Predicate[predicateList.size()]));
        //排序方式
        if (orderSpecifierList != null && orderSpecifierList.size() > 0)
            jpaQuery.orderBy(orderSpecifierList.toArray(new OrderSpecifier[orderSpecifierList.size()]));
        //分页查询
        if (pageNo != null && pageSize != null) {
            list = jpaQuery.offset((pageNo - 1) * pageSize).limit(pageSize).fetch();
        } else {
            list = jpaQuery.fetch();
        }
        return new PageResult<>(list, pageNo, pageSize, jpaQuery.fetchCount());
    }

    /**
     * 查询单个
     *
     * @param predicate      查询条件
     * @param entityPathBase 查询表
     * @return
     */
    @Transactional(readOnly = true)
    public T queryOneDsl(Predicate predicate, EntityPathBase<T> entityPathBase) {
        return jpaQueryFactory.selectFrom(entityPathBase).where(predicate).fetchFirst();
    }

    //多表联合查询
    /**
     *
     * @param qdc queryDsl 查询对象
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Transactional(readOnly = true)
    public PageListMapResult queryDslForPageListResult(QueryDslContext qdc, Integer pageNo, Integer pageSize) {
        JPAQuery<Tuple> jpaQuery = queryFactory.select(qdc.expressionToArray())
                .from(qdc.entityPathToArray())
                .where(qdc.predicatesToArray());
        List<Tuple> tuples = jpaQuery.orderBy(qdc.orderSpecifiersToArray())
                .offset((pageNo - 1) * pageSize).limit(pageSize)
                .fetch();
        List<Map<String, Object>> list = new LinkedList<>();//返回结果
        //封装结果
        for (int i = 0; i < tuples.size(); i++) {
            //遍历tuples
            Map<String, Object> map = new LinkedHashMap<>();//一条信息
            for (Expression expression : qdc.getExpressions()) {
                map.put(expression.toString().split(" as ")[1],//别名作为Key
                        tuples.get(i).get(expression));//获取结果
            }
            list.add(map);
        }
        PageListMapResult pageListMapResult = new PageListMapResult(list, pageNo, pageSize, jpaQuery.fetchCount());//分页封装
        return pageListMapResult;
    }
}

支持的对象
queryDslContext

package com.spark.bitrade.pagination;

import com.querydsl.core.types.EntityPath;
import com.querydsl.core.types.Expression;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.Predicate;

import java.util.ArrayList;
import java.util.List;

/**
 * @author rongyu
 * @description
 * @date 2018/3/7 13:10
 */
public class QueryDslContext {

    private List<Expression> expressions;
    private List<EntityPath> entityPaths;
    private List<Predicate> predicates;
    private List<OrderSpecifier> orderSpecifiers;

    public QueryDslContext() {
        this.expressions = new ArrayList<>();
        this.entityPaths = new ArrayList<>();
        this.predicates = new ArrayList<>();
        this.orderSpecifiers = new ArrayList<>();
    }

    public List<Expression> getExpressions() {
        return expressions;
    }

    public void add(Expression expression) {
        expressions.add(expression);
    }

    public void add(EntityPath entityPath) {
        entityPaths.add(entityPath);
    }

    public void add(Predicate predicate) {
        predicates.add(predicate);
    }

    public void add(OrderSpecifier orderSpecifier) {
        orderSpecifiers.add(orderSpecifier);
    }

    public Expression[] expressionToArray() {
        return expressions.toArray(new Expression[expressions.size()]);
    }

    public EntityPath[] entityPathToArray() {
        return entityPaths.toArray(new EntityPath[entityPaths.size()]);
    }

    public Predicate[] predicatesToArray() {
        return predicates.toArray(new Predicate[predicates.size()]);
    }

    public OrderSpecifier[] orderSpecifiersToArray() {
        return orderSpecifiers.toArray(new OrderSpecifier[orderSpecifiers.size()]);
    }



}

结果 对象

package com.spark.bitrade.pagination;

import lombok.AllArgsConstructor;
import lombok.Data;

import java.io.Serializable;
import java.util.List;
import java.util.Map;

/**
 * @author rongyu
 * @description
 * @date 2018/2/28 15:48
 */
@AllArgsConstructor
@Data
public class PageListMapResult implements Serializable {
    private List<Map<String,Object>> list;
    private int pageNo;
    private int pageSize;
    private Long totalNumber;
}

这是调用

=================================================自定义字段灵活条件分页查询方式==============================
@GetMapping("withdraw-records/page-query/test")
    public MessageResult pageQueryTest(
            @RequestParam(value = "pageNo", defaultValue = "1") Integer pageNo,
            @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize,
            @RequestParam(value = "status", required = false) WithdrawStatus status) {
        QueryDslContext queryDslContext = new QueryDslContext();
        //查询字段
        queryDslContext.add(QWithdrawRecord.withdrawRecord.id.as("id"));
        queryDslContext.add(QWithdrawRecord.withdrawRecord.fee.as("fee"));
        queryDslContext.add(QWithdrawRecord.withdrawRecord.arrivedAmount.as("arrivedAmount"));
        queryDslContext.add(QWithdrawRecord.withdrawRecord.transactionNumber.as("transactionNumber"));
        queryDslContext.add(QWithdrawRecord.withdrawRecord.createTime.as("createTime"));
        queryDslContext.add(QWithdrawRecord.withdrawRecord.address.as("address"));
        queryDslContext.add(QWithdrawRecord.withdrawRecord.remark.as("remark"));
        queryDslContext.add(QWithdrawRecord.withdrawRecord.status.as("status"));
        //查询表
        queryDslContext.add(QWithdrawRecord.withdrawRecord);
        queryDslContext.add(QMember.member);
        //查询条件
        queryDslContext.add(QWithdrawRecord.withdrawRecord.memberId.eq(QMember.member.id));
        if (status != null)
            queryDslContext.add(withdrawRecord.status.eq(status));
        //排序
        queryDslContext.add(QWithdrawRecord.withdrawRecord.id.desc());

        PageListMapResult pageListMapResult = withdrawRecordService.queryDslForPageListResultTest(queryDslContext, pageNo, pageSize);
        return success(pageListMapResult);
    }
=================================================灵活条件分页查询方式==============================
        //条件
        List<Predicate> predicateList = new ArrayList<>();
        predicateList.add(QAdvertise.advertise.member.id.eq(member.getId()));
        if (status != null) predicateList.add(QAdvertise.advertise.status.eq(status));
        if (advertiseType != null) predicateList.add(QAdvertise.advertise.advertiseType.eq(advertiseType));
        //排序
        List<OrderSpecifier> orderSpecifierList = new ArrayList<>();
        orderSpecifierList.add(QAdvertise.advertise.id.desc());
        //查询
        PageResult<Advertise> advertisePageResult = advertiseService.queryDsl(pageNo, pageSize, predicateList, QAdvertise.advertise, orderSpecifierList);
相关文章
|
5月前
|
索引
Elasticsearch 精确查询:术语查询、多术语查询、主键查询、范围查询、存在查询、前缀查询、正则查询与通配符查询
Elasticsearch 精确查询:术语查询、多术语查询、主键查询、范围查询、存在查询、前缀查询、正则查询与通配符查询
|
6月前
T-sql 高级查询( 5*函数 联接 分组 子查询)
T-sql 高级查询( 5*函数 联接 分组 子查询)
|
SQL
一条集多表查询、字段与字段拼接、合并每张表共同字段、新增列并赋值的SQL
一条集多表查询、字段与字段拼接、合并每张表共同字段、新增列并赋值的SQL
67 0
|
SQL 安全 数据库
【T-SQL基础】02.联接查询
【T-SQL基础】02.联接查询
171 0
【T-SQL基础】02.联接查询
|
SQL 关系型数据库 MySQL
MySQL优化系列(二)--查找优化(2)(外连接、多表联合查询以及查询注意点)
MySQL优化系列(二)--查找优化(2)(外连接、多表联合查询以及查询注意点) 继续这一系列,上篇的简单查询优化并没讲完,第二点还有查询注意点以及多表查询优化呢!! 文章结构:(1)多表查询步步优化;(2)查询编写的注意点。
1843 0