spring data JPA 中带查询条件的in的用法

时间:2022-09-11 13:03:13
public Page<Material> findByMaterialList(Long subjectId,Long subjectGroupId, Material material, Pageable pageable) {
		Subject subject = subjectRelationRepository.findOne(subjectId);
		long[] ids = subjectLearingMapper.findMaterialId(subjectId, subjectGroupId);
		Specification<Material> specification = new Specification<Material>() {

			@Override
			public Predicate toPredicate(Root<Material> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				List<Predicate> list = new ArrayList<Predicate>();
				Predicate isDeleted = cb.equal(root.get("isDeleted"), false);
				list.add(isDeleted);
				if(StringUtils.isNotEmpty(material.getName())) {
					Predicate name = cb.and(cb.like(cb.lower(root.get("name")),
									"%" + StringUtils.trim(material.getName()).toLowerCase() + "%"));	
					list.add(name);
				}
				Predicate materialGroup = cb.and(
						cb.equal(root.get("materialGroup").get("id"), subject.getMaterialGroup().getId()));
				list.add(materialGroup);
				In<Long> in = cb.in(root.get("id"));
			    for (Long id : ids) {
			        in.value(id);
			    }
			    list.add(in);
			    Predicate[] p = new Predicate[list.size()];
				return cb.and(list.toArray(p));
			} 
		};

		return this.materialService.findAll(specification, pageable);
	}

sql语句

SELECT
	material0_.id AS id1_139_,
	material0_.created_by AS created_7_139_,
	material0_.created_date AS created_2_139_,
	material0_.last_modified_by AS last_mod8_139_,
	material0_.last_modified_date AS last_mod3_139_,
	material0_.is_deleted AS is_delet4_139_,
	material0_.description AS descript5_139_,
	material0_.document_info_id AS document9_139_,
	material0_.group_id AS group_i10_139_,
	material0_. NAME AS name6_139_
FROM
	lrn_material material0_
WHERE
	material0_.is_deleted = 0
AND material0_.group_id = 447
AND (material0_.id IN(26, 30))
LIMIT ?