SpringJPA does paging condition query
Preface:
I believe that many of my friends’ projects use the SpringJPA framework. It is very convenient to use jpa for the addition, deletion, modification and query of a single table, but for conditional query and paging, many friends do not often write about it. Today I sorted it out Share it here.
Without further ado, let’s get straight to the code:
Controller:
@RestController
public class ProductInstanceController {
@Autowired
private ProductInstanceService productInstanceService;
@PostMapping("page-find-instance")
public PageInfoVO<ProductInst> pageFindInstance(@RequestBody ProductInstParams productInstParams) {
return productInstanceService.pageFindInstance(productInstParams);
}
}
Service:
@Service
public class ProductInstanceService {
@Autowired
private ProductInstRepository productInstRepository;
public PageInfoVO<ProductInst> pageFindInstance(ProductInstParams productInstParams) {
Sort.Direction direction = Sort.Direction.DESC;
//CreatePageableobject,which contains the requested page number(productInstParams.getPageNo()),page size(productInstParams.getPageSize()),Sorting rules and sorting field names。
Pageable pageable = PageRequest.of(productInstParams.getPageNo(), productInstParams.getPageSize(), direction, "createdTime");
//The start date that will be passed in as a string(startDate)and end date(endDate)converted toDatetype
Date start = DateUtil.parseUTC(productInstParams.getStartDate());
Date end = DateUtil.parseUTC(productInstParams.getEndDate());
//implementJPAPaging query:
Page<ProductInst> productInstPage = productInstRepository.findAll((root, query, criteriaBuilder) -> {
//initialize aArrayList<Predicate>,Store multiple predicate conditions,These conditions are ultimately combined into a logical AND(AND)expression
List<Predicate> predicatesAndList = new ArrayList<>();
// deleted = 'true'
if (null != productInstParams.getIsDeleted()) {
predicatesAndList.add(criteriaBuilder.equal(root.get("deleted").as(Boolean.class), productInstParams.getIsDeleted()));
}
// name like %name%
if (StringUtils.isNotBlank(productInstParams.getName())) {
predicatesAndList.add(criteriaBuilder.like(root.get("name").as(String.class), productInstParams.getName()));
}
// runStatus = "Running"
if (StringUtils.isNotBlank(productInstParams.getRunStatus())) {
predicatesAndList.add(criteriaBuilder.equal(root.get("runStatus").as(String.class), productInstParams.getRunStatus()));
}
// createdTime >= start
if (!Objects.isNull(start)) {
predicatesAndList.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createdTime").as(Date.class), start));
}
// createdTime <= end
if (!Objects.isNull(end)) {
predicatesAndList.add(criteriaBuilder.lessThanOrEqualTo(root.get("createdTime").as(Date.class), end));
}
// id in ('1','2')
if (!CollectionUtils.isEmpty(productInstParams.getIds())) {
CriteriaBuilder.In<String> in = criteriaBuilder.in(root.get("id").as(String.class));
productInstParams.getIds().forEach(in::value);
predicatesAndList.add(in);
}
Predicate andPredicate = criteriaBuilder.and(predicatesAndList.toArray(new Predicate[predicatesAndList.size()]));
query.where(andPredicate);
return query.getRestriction();
}, pageable);
return PageUtil.generatePageInfoVO(productInstPage);
}
}
explain:
The method of jpa paging query adjustment is:
Page findAll(@Nullable Specification spec, Pageable pageable);
This method accepts two parameters:
One is the Specification object, used to build complex query conditions;
The other is the pageable object created previously, which is used to specify paging and sorting information.
Click in to see directly
Let me explain this line of code again
criteriaBuilder.equal(root.get("runStatus").as(String.class), productInstParams.getRunStatus());
1. criteriaBuilder is an instance of javax.persistence.criteria.CriteriaBuilder, which is an object used to build JPQL query conditions.
2. root is the Root object representing the query main table, which points to the database table corresponding to the ProductInst entity class.
3. root.get(“runStatus”) means to get the runStatus attribute in the ProductInst entity class (Note: What is written here is not the field of the database, the field of my database is: run_status). This method returns a Path
4. .as(String.class) is a type conversion, ensuring that runStatus is treated as String type, because it may be mapped to VARCHAR or other text type fields in the database.
5. productInstParams.getRunStatus() gets the runStatus attribute value in the incoming parameter object productInstParams, which is an actual value to be matched.
To sum up: the sql corresponding to this code
WHERE run_status = 'XXXX'
Repository:
@Repository
public interface ProductInstRepository extends JpaRepository<ProductInst, String>, JpaSpecificationExecutor<ProductInst> {
}
PageInfoVO:
package com.king.alice.common.base;
import lombok.Getter;
import lombok.Setter;
import java.util.List;
/**
* @Author wlt
* @Description elements in paginated data
* @Date 2022/8/26
**/
@Getter
@Setter
public class PageInfoVO<T> extends BaseVO{
private static final long serialVersionUID = -3542944936096780651L;
/**
* total
*/
private long total;
/**
* current page
*/
private int pageNum;
/**
* quantity per page
*/
private int pageSize;
/**
* result set
*/
private List<T> list;
}
PageUtil:
package com.king.alice.common.util;
import com.king.alice.common.base.PageInfoVO;
import org.springframework.data.domain.Page;
/**
* @author demon king
* @description:
* @date 2024/3/22 11:02
*/
public class PageUtil {
/**
* according toPageobject generationPageInfoVO
*
* @param page Pagewrapper object
* @return PageDto object
*/
@SuppressWarnings({"unchecked"})
public static <T> PageInfoVO<T> generatePageInfoVO(Page page) {
PageInfoVO result = new PageInfoVO();
result.setPageNum(page.getNumber() + 1);
result.setPageSize(page.getPageable().getPageSize());
result.setTotal(page.getTotalElements());
result.setList(page.getContent());
return result;
}
}
ProductInstParams:
package com.king.alice.manage.instance.params;
import lombok.Data;
import java.util.List;
/**
* @author demon king
* @description: TODO
* @date 2024/3/21 16:56
*/
@Data
public class ProductInstParams {
/**
* current page
*/
private int pageNo;
/**
* quantity per page
*/
private int pageSize;
/**
* name
*/
private List<String> ids;
/**
* name
*/
private String name;
/**
* Operating status
*/
private String runStatus;
private String startDate;
private String endDate;
private Boolean isDeleted;
}
test:
Response result:
Console print sql:
Perfectly realized!