querydsl

使用queryDsl时需注意hibernate的版本问题,具体问题TODO…

// 自定义Q前缀
@EnableJpaRepositories(repositoryImplementationPostfix='xxx')

// 比如时间的比较
default void customize(final QuerydslBindings bindings, final QFoo foo) {
    bindings.bind(foo.age).all((path, ageValue) -> {
        Iterator<? extends Long> it = value.iterator();
        return path.between(it.next(), it.next());
    });
}

    @Override
    default void customize(QuerydslBindings bindings, QMemberAccountDetails root){
    	bindings.bind(root.indate).all(new MultiValueBinding<DateTimePath<Date>, Date>() {
			@Override
			public Predicate bind(DateTimePath<Date> path, Collection<? extends Date> values) {
				Date startTime = values.stream().min(Date::compareTo).get();
				Date endTime = values.stream().max(Date::compareTo).get();
				return path.between(startTime, endTime);
			}
		});
    }

// 分页查询
public Page<CameraInfoPO> findCameraInfoByPage(Pageable pageable, Integer isEnabled) { 
      
    //CriteriaBuilder,用来构建CritiaQuery的构建器对象 
    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); 
      
    //CriteriaQuery,它包含着查询语句的条件各个部分，比如：select 、from、where、group by、order by等 
    CriteriaQuery<CameraInfoPO> criteriaQuery = criteriaBuilder.createQuery(CameraInfoPO.class); 
      
    //查询根，用于获取查询实例的属性，通过CriteriaQuery的from方法获取 
    Root<CameraInfoPO> rootFrom = criteriaQuery.from(CameraInfoPO.class); 
      
    //查询条件 
    List<Predicate> predicates = new ArrayList<Predicate>(); 
      
    if (null != isEnabled) { 
      Predicate predicate = criteriaBuilder.equal(rootFrom.get("isEnabled").as(Integer.class), isEnabled); 
      predicates.add(predicate); 
        
    } 
      
    //格式化参数 
    criteriaQuery.where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]))); 
      
    //默认按照id排序（从小到大） 
    criteriaQuery.orderBy(criteriaBuilder.asc(rootFrom.get("id"))); 
      
    //SQL查询对象 
    TypedQuery<CameraInfoPO> createQuery = em.createQuery(criteriaQuery); 
      
    //分页参数 
    Integer pageSize = pageable.getPageSize(); 
    Integer pageNo = pageable.getPageNumber(); 
      
    //计数查询结果条数 
    TypedQuery<CameraInfoPO> createCountQuery = em.createQuery(criteriaQuery); 
      
    // 实际查询返回分页对象 
    int startIndex = pageSize * pageNo; 
    createQuery.setFirstResult(startIndex); 
    createQuery.setMaxResults(pageable.getPageSize()); 
    Page<CameraInfoPO> pageRst = 
      new PageImpl<CameraInfoPO>(createQuery.getResultList(), pageable, createCountQuery.getResultList().size()); 
    return pageRst;   
}

// 分页处理
public Page<DIssue> issues(Pageable pageable, String userid, Predicate... predicates) {
		QIssue qIssue = QIssue.issue;

		JPAQuery<Issue> jpaQuery = jpaQueryFactory.select(qIssue).from(qIssue);
		for (Predicate predicate : predicates) {
			jpaQuery.where(predicate);
		}

		// 查询当前会员的账户详情信息
		jpaQuery.where(qIssue.userid.eq(userid).and(qIssue.state.eq(1)));
		// 分页
		jpaQuery.offset(pageable.getOffset()).limit(pageable.getPageSize());

		List<Issue> fetch = jpaQuery.fetch();
		Page<Issue> page = new PageImpl<>(fetch, pageable, jpaQuery.fetchCount());
		return issueAssembler.assembler(page);
}

// querydsl 子查询
@Transactional(readOnly = true)
@Override
public Page<OfficialAccountTemplateDTO> listOfficialAccountMessageTemplatePage(OfficialAccountTemplateDTO dto,
			Pageable pageable, Predicate... predicates) {
		QWechatMessageTemplate qWechatMessageTemplate = QWechatMessageTemplate.wechatMessageTemplate;
		QOfficialAccountMessageTemplate qOfficialAccountMessageTemplate = QOfficialAccountMessageTemplate.officialAccountMessageTemplate;

		JPQLQuery<OfficialAccountMessageTemplate> subQuery1 = JPAExpressions.select(qOfficialAccountMessageTemplate)
				.from(qOfficialAccountMessageTemplate)
				.where(qOfficialAccountMessageTemplate.messageTemplateId.eq(qWechatMessageTemplate.id)
						.and(qOfficialAccountMessageTemplate.officialAccountId.eq(dto.getOfficialAccountId())));

		QList qList = Projections.list(qWechatMessageTemplate, subQuery1);
		JPAQuery<List<?>> jpaQuery = jpaQueryFactory.select(qList).from(qWechatMessageTemplate);

		for (Predicate predicate : predicates) {
			jpaQuery.where(predicate);
		}

		QueryResults<List<?>> queryResults = jpaQuery.fetchResults();
		List<List<?>> lists = queryResults.getResults();
		List<OfficialAccountTemplateDTO> content = new ArrayList<OfficialAccountTemplateDTO>();

		for (List<?> objects : lists) {
			OfficialAccountTemplateDTO mvo = new OfficialAccountTemplateDTO();
			for (Object object : objects) {
				if (!ObjectUtils.isEmpty(object)) {
					CglibAssembler.copy(object, mvo);
				}
			}
			content.add(mvo);
		}
		Page<OfficialAccountTemplateDTO> page = new PageImpl<>(content, pageable, queryResults.getTotal());
		return page;
}

public Page<DIssue> issues(String userid, Pageable pageable, Predicate... predicates) {
		QIssue qIssue = QIssue.issue;
		QImage qImage = QImage.image;
		QIssueTag qIssueTag = QIssueTag.issueTag;
		
		QCustCrop custcrop = QCustCrop.custCrop;
		
		JPAQuery<Tuple> jpaQuery = jpaQueryFactory.select(qIssue, qImage, custcrop).from(qIssue);
		for (Predicate predicate : predicates) {
			jpaQuery.where(predicate);
		}

		// 查询当前会员的账户详情信息
		jpaQuery.where(qIssue.userid.eq(userid).and(qIssue.state.eq(1)));
		jpaQuery.leftJoin(qImage).on(qImage.pid.eq(qIssue.id)).where(qImage.type.eq(BizEnum.ISSUE.key()));
		
		jpaQuery.leftJoin(qIssueTag).on(qIssueTag.issueId.eq(qIssue.id));
		jpaQuery.leftJoin(custcrop).on(custcrop.cropno.eq(qIssueTag.cropno));
		
		// 分页
		jpaQuery.offset(pageable.getOffset()).limit(pageable.getPageSize());
//		QueryResults<Tuple> fetchResults = jpaQuery.fetchResults();
//		for (Tuple row : fetchResults.getResults()) {
//			Issue issue = row.get(qIssue);
//			Image image = row.get(qImage);
//            System.out.println("--------------------");
//        }
		
		JPAQuery<Tuple> fetchAll = jpaQuery.fetchAll();
		

		List<Tuple> fetch = jpaQuery.fetch();
		System.out.println(fetch.size());
		System.out.println(fetch.get(0));
		System.out.println(fetch.get(1));
		for(Tuple t:fetch) {
			Issue issue = t.get(qIssue);
			Image image = t.get(qImage);
		}
		//Page<Issue> page = new PageImpl<>(fetch, pageable, jpaQuery.fetchCount());
		return null;
//		return issueAssembler.assembler(page);
	}

// querydsl 动态查询
// 1.使用EntityPath
public class PersonRepositoryImpl implements QuerydslBinderCustomizer<EntityPath<Person>> {
    @Override
    public void customize(QuerydslBindings bindings, EntityPath<Person> t) {
        bindings.bind(String.class).first(new SingleValueBinding<StringPath, String>() {
            @Override
            public Predicate bind(StringPath path, String s) {
                return path.equalsIgnoreCase(s);
            }
        });
    }
}

// 2.使用QArticle
@Repository
public interface ArticleRepository extends BaseRepository<Article>, QuerydslBinderCustomizer<QArticle> {
	@Override
	default void customize(QuerydslBindings bindings, QArticle root) {
		bindings.bind(root.title).first(new SingleValueBinding<StringPath, String>() {
			@Override
			public Predicate bind(StringPath path, String value) {
				return path.like(Expressions.asString("%").concat(value).concat("%"));
			}
		});
	}
}

domain->dto

还有个Transformers这个没有尝试过,com.querydsl.core.types.ConstructorExpression<T>源码很贴心最上面还给了栗子

@Autowired
private JPAQueryFactory jpaQueryFactory;

private static final QExpert qExpert =  QExpert.expert;
JPAQuery<DExpert> japQuery = jpaQueryFactory.selectFrom(qExpert).where(qExpert.status.eq(1))
		.select(Projections.constructor(DExpert.class, qExpert);
		List<DExpert> experts = japQuery.fetch();

参考文献

https://www.programcreek.com/java-api-examples/index.php?api=com.mysema.query.BooleanBuilder

赏

谢谢你请我吃糖果

支付宝

微信