Program source code

Dynamic conditional query

The most commonly used scenario, according to the incoming condition dynamic Mosaic query, similar to the if tag in Mybatis. Judge whether to add conditional query according to conditions, add exist clause query, fuzzy query ID and keyword according to keywords, etc. As follows:

public Predicate buildPredicate() {
    QWxUser qWxUser = QWxUser.wxUser;
    QUserTag qUserTag = QUserTag.userTag;
    QTag qTag = QTag.tag;
    BooleanBuilder predicate = new BooleanBuilder();
    if (StrUtil.isNotBlank(appId)) {
        predicate.and(qWxUser.appId.eq(appId));
    }
    if (StrUtil.isNotBlank(keyword)) {
        String fuzzKeyword = "%" + keyword + "%";
        predicate.and(qWxUser.wxUserId.stringValue().like(fuzzKeyword).or(qWxUser.nickname.like(fuzzKeyword)));
    }
    if (StrUtil.isNotBlank(openId)) {
        predicate.and(qWxUser.openId.eq(openId));
    }
    if (StrUtil.isNotBlank(unionId)) {
        predicate.and(qWxUser.unionId.eq(unionId));
    }
    Optional.ofNullable(status).ifPresent(status -> predicate.and(qWxUser.status.eq(status)));
    Optional.ofNullable(subscribeStartTime).ifPresent(subscribeStartTime ->
            predicate.and(qWxUser.subscribeTime.gt(subscribeStartTime.atStartOfDay(BEIJING_ZONE).toEpochSecond())));
    Optional.ofNullable(subscribeEndTime).ifPresent(subscribeEndTime ->
            predicate.and(qWxUser.subscribeTime.lt(subscribeEndTime.plusDays(1).atStartOfDay(BEIJING_ZONE).toEpochSecond())));
    Optional.ofNullable(subscribe).ifPresent(subscribe -> predicate.and(qWxUser.subscribe.eq(subscribe)));

    Optional.ofNullable(tagId).ifPresent(tagId ->
            predicate.and(JPAExpressions.selectFrom(qUserTag)
                    .where(qWxUser.wxUserId.eq(qUserTag.wxUserId).and(qUserTag.tagId.eq(tagId))).exists()));

    return predicate;
}

public List<WxUser> list(WxUserVO vo) {
    QWxUser qWxUser = QWxUser.wxUser;
    PageRequest pageRequest = vo.toPageRequest();
    List<WxUser> fetch = jpaQueryFactory.selectFrom(qWxUser).where(vo.buildPredicate())
            .offset(pageRequest.getOffset())
            .limit(pageRequest.getPageSize())
            .orderBy(qWxUser.createTime.asc()).fetch();
    return fetch;
}
Copy the code

The corresponding native SQL is as follows:

SELECT * FROM wx_user u WHERE u.app_id = '1111111' AND (cast(u.x_user_id AS CHAR) LIKE 'OR u.ickname LIKE ') AND u.open_id = 'openid11111' AND u. STATUS = 0 AND u.subscribe_time > 1586069773 AND u.subscribe_time < 1586269773 AND u.subscribe = 1 AND ( EXISTS ( SELECT 1 FROM wx_user_tag ut WHERE u.wx_user_id = ut.wx_user_id AND ut.tag_id = 1 ) ) ORDER BY u.create_time ASC LIMIT 0, 20Copy the code

2. Multiple table associated query

Multi-table associated query is also a common query, as shown below:

public List<Tag> listByWxUserId(Long wxUserId) {
    QTag qTag = QTag.tag;
    QUserTag qUserTag = QUserTag.userTag;
    List<Tag> list = jpaQueryFactory.selectFrom(qTag)
            .innerJoin(qUserTag).on(qTag.tagId.eq(qUserTag.tagId))
            .where(qUserTag.wxUserId.eq(wxUserId)).fetch();
    return list;
}Copy the code

The corresponding native SQL is as follows:

select * 
from wx_tag t 
inner join wx_user_tag ut on (t.tag_id=ut.tag_id)
where ut.wx_user_id= 1Copy the code

This article is published by OpenWrite, a blogging tool platform