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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
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;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
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()]);
    }



}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
=================================================自定义字段灵活条件分页查询方式==============================
@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);