jpa、querydsl常用查询以及对照sql

作者:jcmp      发布时间:2021-05-10      浏览量:0
项目源码一、动态条件查询public P

项目源码

一、动态条件查询

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 uWHERE 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 = 0AND u.subscribe_time > 1586069773AND u.subscribe_time < 1586269773AND u.subscribe = 1AND ( 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 ASCLIMIT 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。