JPA

jpa 动态添加条件查询 like equal in between

Specification querySpecifi = new Specification<Product>(){
	@Override
	public Predicate toPredicate(Root< Product> root, CriteriaQuery<?> criteriaQ, CriteriaBuilder criteriaB) {
		List<Predicate> predicates = new ArrayList<>();
        if(null != name){
          predicates.add(criteriaB.like(root.get("name"), StringUtil.formatLike(name)));
        }
        if (null != idArr) {//IN
            String[] splitId = idArr.split("\\+");
            Expression<String> exp = root.<String>get("id");
            predicates.add(exp.in(splitId));
        }
        if(null != productId && productId.length > 0){//NOTIN
            Expression<String> exp = root.<String>get("id");
            predicates.add(criteriaB.not(exp.in(productId)));
        }
        if(null != isdelete){
            predicates.add(criteriaB.equal(root.get("isdelete"), isdelete));
        } else {
            predicates.add(criteriaB.equal(root.get("isdelete"), 0));
        }
        if (StringUtil.isNotEmpty(allotId)) {//物资调拨
            List<MaterialsAllotRecord> materialsAllotRecords=materialsAllotRecordRepository.findByAllotIdAndIsdelete(allotId,0);
                if(materialsAllotRecords.size() > 0){
                    for(MaterialsAllotRecord ma: materialsAllotRecords){
                        predicates.add(criteriaB.or(criteriaB.and(criteriaB.notEqual(root.get("id"), ma.getProcurementRecordId()))));
                }
            }
        }
        return criteriaB.and(predicates.toArray(new Predicate[predicates.size()]));
	}
};

时间段查询：

    Date starttime = json.getDate("startTime");
    Date endtime = json.getDate("endTime");
    if (endtime != null && starttime != null) {
        predicates.add(criteriaB.between(root.get("opttime"), starttime, endtime));
    }

String字段存数字；按数字大小排序：

@Query(value = "SELECT * FROM(SELECT * FROM TIN_MATERIALS_INVENTORY a " +
			"WHERE a.MATERIAL_CODE is not NULL " +
			"ORDER BY to_number(regexp_substr(a.MATERIAL_CODE,'[0-9]*[0-9]',1)) DESC )WHERE  rownum = 1 ",nativeQuery = true)

排序；第一条数据；JPA

findFirstByOrderByCreatedTimeDesc()

jpa 排序条件相同+分页 （本页最后一条数据与下一页第一条数据相同）

• 原因：批量添加，所以添加时间一样。jpa查询，按添加时间排序，所以可能会有问题（分页时，所有数据添加时间一样）

• 解决方案：增加排序条件

// 先根据id倒序排列，再根据创建时间倒序排序，再分页。 
List<Sort.Order> orders=new ArrayList<Sort.Order>();
orders.add(new Sort.Order(Sort.Direction.DESC, "createDatetime"));
orders.add(new Sort.Order(Sort.Direction.DESC, "id"));
Sort sort = new Sort(orders);

注解方式

• add
• update
• delete

@Transactional
    @Modifying
    @Query("update Dict d set d.status = ?1  where d.id in ?2")