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
Insert image description here
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 object, indicating the position of the runStatus field on the query 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;
}