JPA构建多条件查询

方式一

new Specification匿名内部类，通过实现该匿名内部类的toPredicate方法构建查询sql

Specification<T> specification = new Specification<T>() {
	@Override
	public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
			List<Predicate> predicateList = new ArrayList<Predicate>();
			predicateList.add(cb.equal(root.get("col1").as(String.class), "0"));
			predicateList.add(cb.equal(root.get("col2").as(String.class), "5"));
			predicateList.add(cb.equal(root.get("col3").as(String.class), t));
			// 构建(col4 != '24' or col3 is null)条件
			predicateList.add(cb.or(cb.notEqual(root.get("col4").as(String.class), "24"),
                        cb.isNull(root.get("col4").as(String.class))));
			if (map.get("col4") != null){
				predicateList.add(cb.equal(root.get("col4").as(String.class), map.get("col4")));
			}

			if (map.get("col5") != null){
				predicateList.add(cb.equal(root.get("col5").as(String.class), map.get("col5")));
			}

			if (map.get("col6") != null){
				predicateList.add(cb.equal(root.get("col6").as(String.class), map.get("col6")));
			}
			if (map.get("col7") != null){
				predicateList.add(cb.equal(root.get("col7").as(String.class), map.get("col7")));
			}
			if (map.get("col8") != null){
				predicateList.add(cb.like(root.get("col8").as(String.class), "%" + map.get("col8").toString().trim() + "%"));
			}
			if (map.get("col9") != null){
				Object obj = map.get("col9");
				if (obj instanceof List){
					List list = (List) obj;
					List<Integer> orgList = new ArrayList<>();
					list.forEach(o -> {
						if (o != null){
							try{
								orgList.add(Integer.valueOf(o.toString()));
							}catch (NumberFormatException e){
								log.error("传参col9格式错误");
							}
						}
					});
					if (!orgList.isEmpty()){
						predicateList.add(root.get("col").as(Integer.class).in(list));
					}
				}
			}
			Predicate[] p = new Predicate[predicateList.size()];
			query.where(predicateList.toArray(p));
			query.orderBy(cb.desc(root.get("id").as(Integer.class)));
			return query.getRestriction();
	}
	};

Pageable pageable = PageRequest.of(page, size);
// 得到查询结果
Page<T> pageResult = tDao.findAll(specification, pageable);

方式二

根据参数组装sql条件，设置对应位置参数。缺点：in查询支持比较麻烦

@PersistenceContext
private EntityManager entityManager;

// 构建in查询sql,使用list接收参数,为后续设置对应位置参数
Object obj = map.get("list");
if (obj instanceof List){
	sql.append(" and col in (");
	List orgList = (List) obj;
	orgList.forEach(o -> {
		queryCondition.add(o);
	});
	sql.append(orgList.stream().map(o -> "?").collect(Collectors.joining(","))).append(")");
}

// 普通查询条件构建
if (!isNullOrBlank(String.valueOf(map.get("col2")))) {
	sql.append(" and col2= ?");
	queryCondition.add(map.get("col2"));
}

// 根据sql List<Object> queryCondition list,
Query query = entityManager.createNativeQuery(sql,clazz);
query.setFirstResult((int) pageable.getOffset());
query.setMaxResults(pageable.getPageSize());

for (int batch = 0; batch < queryCondition.size(); batch++) {
	query.setParameter(batch + 1,queryCondition.get(batch));
}