jpa原生sql动态分页查询

使用拼接jsql查询，自定义返回结果集。这里可以进行联表查询等复杂操作。

不多说，上代码

package com.example.demo.service;


import com.example.demo.dto.CustomerReq;
import com.example.demo.dto.CustomerVO;
import com.example.demo.dto.PageBean;
import org.apache.maven.surefire.shade.org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.HashMap;
import java.util.List;

@Service
public class CustomerServiceImpl implements CustomerService {

    @Autowired
    private EntityManager entityManager;


    @Override
    public PageBean<CustomerVO> query(CustomerReq parameter) {
        StringBuilder whereSql = new StringBuilder("from  Customer a  WHERE 1 = 1");
       
        HashMap<String, Object> paramMap = new HashMap<>();
        if (StringUtils.isNotEmpty(parameter.getFirstName())) {
            whereSql.append(" AND a.firstName = :firstName");
            paramMap.put("firstName", parameter.getFirstName());
        }
        if (!CollectionUtils.isEmpty(parameter.getType())) {
            whereSql.append(" AND a.type in(:type)");
            paramMap.put("type", parameter.getType());
        }
        //这里的CustomerVO()里的字段值要对应其构造函数，没有对应的构造函数会报错
        StringBuilder dataSql = new StringBuilder("select new com.example.demo.dto.CustomerVO(a.firstName,a.lastName) ");
        StringBuilder countSql = new StringBuilder("SELECT count(1)  ");
        dataSql.append(whereSql).append(" order by a.id desc");
        countSql.append(whereSql);
        Query countQuery = entityManager.createQuery(String.valueOf(countSql));
        Query dataQuery = entityManager.createQuery(String.valueOf(dataSql), CustomerVO.class);

        paramMap.forEach((k, v) -> {
            countQuery.setParameter(k, v);
            dataQuery.setParameter(k, v);
        });
        dataQuery.setFirstResult(parameter.getPageNumber());
        dataQuery.setMaxResults(parameter.getPageSize());
        Long total = (Long) countQuery.getSingleResult();
        List<CustomerVO> resultList = dataQuery.getResultList();
        return new PageBean<>(resultList, total);
    }
}

CustomerVO

package com.example.demo.dto;

import lombok.Data;

@Data
public class CustomerVO {
    private String firstName;
    private String code;
    private String  type;
    public CustomerVO(String firstName, String code) {
        this.firstName = firstName;
        this.code = code;
    }
}

CustomerReq

package com.example.demo.dto;

import lombok.Data;

import java.util.Date;
import java.util.List;

@Data
public class CustomerReq {

    private String firstName;

    private List<String> type;

    private int pageNumber;

    private int pageSize;
}