querydsl动态 sql_jpa、querydsl常用查询以及对照sql

一、动态条件查询

平时使用最多的场景，根据传入的条件动态拼接查询，类似mybatis中的if标签。根据条件判断是否添加条件查询，添加exist子句查询，根据关键字模糊查询id和关键字等。如下所示：

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 list(WxUserVO vo) {

QWxUser qWxUser = QWxUser.wxUser;

PageRequest pageRequest = vo.toPageRequest();

List fetch = jpaQueryFactory.selectFrom(qWxUser).where(vo.buildPredicate())

.offset(pageRequest.getOffset())

.limit(pageRequest.getPageSize())

.orderBy(qWxUser.createTime.asc()).fetch();

return fetch;

}

对应的原生SQL如下：

SELECT *

FROM wx_user u

WHERE u.app_id = '1111111'

AND (cast(u.wx_user_id AS CHAR) LIKE '张三' OR u.nickname 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, 20

二、多表关联查询

多表关联查询也是比较常用的查询，如下所示：

public List listByWxUserId(Long wxUserId) {

QTag qTag = QTag.tag;

QUserTag qUserTag = QUserTag.userTag;

List list = jpaQueryFactory.selectFrom(qTag)

.innerJoin(qUserTag).on(qTag.tagId.eq(qUserTag.tagId))

.where(qUserTag.wxUserId.eq(wxUserId)).fetch();

return list;

}

对应的原生SQL如下：

select *

from wx_tag t

inner join wx_user_tag ut on (t.tag_id=ut.tag_id)

where ut.wx_user_id= 1

本文由博客群发一文多发等运营工具平台 OpenWrite 发布