queryDsl多表联合查询,任意查询联合字段-阿里云开发者社区

相关文章推荐

鼻子大的小狗 · OAuth2实现单点登录SSO完整教程，其实 ...· 1 月前 ·

阳刚的显示器 · 云数据库ClickHouse(ClickHo ...· 1 月前 ·

欢快的南瓜 · 阿里拆建中台启示：风口消失，问题到底出在哪里 ...· 2 周前 ·

暗恋学妹的小摩托 · 阿里云人工智能平台PAI是什么？_人工智能平 ...· 2 周前 ·

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。 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);