jpa+oracle Query原生sql方式分页和动态排序

jpa+oracle Query原生sql方式分页和动态排序

注：如果你是jpa高手，请绕道

1、原生sql方式（Dto里的数据来自同一个数据库，缺点是Dto里要是有从其他模块call到的数据，若是用此字段排序，则不能实现排序和分页）

2、List工具类方式（缺点是必须先查出全部符合的数据，才能再进行分页和排序）

第一种，原生sql方式

说明：推荐用于多表连接查询，且业务比较复杂，单表不建议使用

1. A.xxx || B.yyy as xy —>为oracle拼接字段方式
2. where 1=1 and （‘ ’ is null or A.xxx=‘hello world’） and (’ ’ is null or B.yyy=’ hello boss’) —>为oracle动态拼接查询条件语句，（‘ ’ is null or A.xxx=‘hello world’）意思是，如果单引号内为空，什么都不做，否则让A.xxx=‘hello world’
3. pageable在原生sql不用去引用，只要返回的是Page
4. SortListUtil.propertyToField(sortBy)，下面的这个方法是个工具类来的，是把abcDecc小驼峰式属性名转成数据库abc_decc下划线式格式，用于排序
5. 为什么套那么多层（select *）呢，Query里的是由于有拼接字段，必须整合成同一个表的字段才能排序，countQuery里的，是由于oracle的rownum分页所需要的

serviceImpl

package hk.health.immu.service.impl;

import com.alibaba.fastjson.JSON;
import hk.health.immu.exception.ImmuException;
import hk.health.immu.pojo.dto.PatientHistoryDto;
import hk.health.immu.pojo.dto.VaccineManageDto;
import hk.health.immu.pojo.dto.inner.PatientUtilDto;
import hk.health.immu.pojo.dto.inner.SortParamDto;
import hk.health.immu.pojo.po.ImmuRecordPo;
import hk.health.immu.repository.ImmuRecordRepository;
import hk.health.immu.restservice.service.CommonRestService;
import hk.health.immu.service.ImmuRecordService;
import hk.health.immu.util.SortListUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

@Service("ImmuRecordService")
public class ImmuRecordServiceImpl implements ImmuRecordService {

    @Autowired
    private ImmuRecordRepository recordRepository;

    @Autowired
    private CommonRestService commonRestService;

    public static final String SORT_ASC = "ASC";

    @Override
    public PatientUtilDto listRecordsByPatientKey(SortParamDto paramDto) throws ImmuException {

        PatientUtilDto patientUtilDto = new PatientUtilDto();
        List<PatientHistoryDto> resultList = new ArrayList<>(0);

        Long pantientKey = paramDto.getPantientKey();
        List<String> recStsList = new ArrayList<>(0);
        recStsList.add("C");

        List<String> vacGroup = paramDto.getVacGroup();
        String sortBy = paramDto.getSortBy();

        String sortType = paramDto.getSortType();

        int pageIndex = paramDto.getPageIndex() - 1;
        int pageSize = paramDto.getPageSize();
        String flag = "";
        if (CollectionUtils.isNotEmpty(paramDto.getVacGroup())) {
            pageIndex = 0;
            pageSize = 9999999;
            flag = "1";
        } else {
            vacGroup.add("");
        }
        Sort sort = null;
        if (StringUtils.isBlank(sortType) || StringUtils.isBlank(sortBy)) {
            sortBy = "immunDate";
            sortType = "desc";
        }
        sortBy = SortListUtil.propertyToField(sortBy);
        if (sortType.equalsIgnoreCase(ImmuRecordServiceImpl.SORT_ASC)) {
            sort = new Sort(Sort.Direction.ASC, sortBy);
        } else {
            sort = new Sort(Sort.Direction.DESC, sortBy);
        }
        Pageable pageable = PageRequest.of(pageIndex, pageSize, sort);
        Page<Map> maps = recordRepository.listRecord(pantientKey, recStsList, flag, vacGroup, pageable);
        List<Map> content = maps.getContent();

        if (CollectionUtils.isEmpty(content)) {
            return null;
        }
        // find cims data
        List<PatientHistoryDto> cimsList = JSON.parseArray(JSON.toJSONString(content), PatientHistoryDto.class);

        for (PatientHistoryDto dto : cimsList) {
            dto.setDifferent(1);
            if (StringUtils.isNotBlank(dto.getAdminPlcCd())) {
                String clinic = commonRestService.listClinic(dto.getAdminPlcCd());
                dto.setClinicName(clinic);
            } else {
                String service = commonRestService.listService(dto.getServiceCd());
                dto.setServiceName(service);
            }
            resultList.add(dto);
        }

        Integer totalNum = resultList.size();
        patientUtilDto.setPatientHistoryDtos(resultList);
        patientUtilDto.setTotalNum(totalNum);
        return patientUtilDto;

    }

repository

package hk.health.immu.repository;

import hk.health.immu.pojo.po.ImmuRecordPo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.*;

public interface ImmuRecordRepository extends JpaRepository<ImmuRecordPo, Long> {

    @Query(value = "SELECT * FROM (select t.*,rt.ADMIN_RT_DESC,site.ADMIN_SITE_DESC,seq.VAC_DOS_SEQ_DESC\n" +
            " from (SELECT t.IMMU_RECORD_ID,t.IMMU_RECORD_ID_SRC,t.PATIENT_KEY,t.VERSION,t.ENCOUNTER_ID,t.LOT_NUM,\n" +
            " t.ADMIN_PLC_CD,t.IS_VLD_DOS,t.IMMUN_DATE,t.REC_STS,t.BARCODE,t.REMARK,t.INVLD_DOS_RSN_TXT,t.UPDATE_BY,t.UPDATE_DTM,\n" +
            " t.PCV13_HX_INDT,t.PCV23_HX_INDT,t.SERVICE_CD,t.IMMU_VACCINE_LIST_ID,t.ADMIN_RT_CD,t.ADMIN_SITE_CD,t.VAC_DOS_SEQ_CD,\n" +
            " P.PROD_NAME,P.HK_REG_NUM,P.VAC_CATGRY,t.INVLD_DOS_RSN_TXT||t.REMARK AS INVLD_DOS_RSN_TXT_AND_REMARK,\n" +
            " '(HK-'||p.HK_REG_NUM||')'||p.PROD_NAME AS HK_REG_NUM_AND_PROD_NAME,P.VAC_GROUP\n" +
            "FROM IMMU_RECORD T \n" +
            "INNER JOIN IMMU_VACCINE_LIST P ON T.IMMU_VACCINE_LIST_ID = P.IMMU_VACCINE_LIST_ID\n" +
            "WHERE  T.PATIENT_KEY=?1 AND T.REC_STS IN (?2) AND (?3 IS NULL OR P.VAC_GROUP IN (?4)) AND (?3 IS NULL OR T.IS_VERIF=1)) t\n" +
            "left JOIN CODE_ADMIN_RT rt ON t.ADMIN_RT_CD = rt.ADMIN_RT_CD\n" +
            "left JOIN CODE_ADMIN_SITE site ON t.ADMIN_SITE_CD = site.ADMIN_SITE_CD\n" +
            "left JOIN CODE_VAC_DOS_SEQ seq ON t.VAC_DOS_SEQ_CD = seq.VAC_DOS_SEQ_CD) t",
            countQuery = "select count(*) from (SELECT * FROM (select t.*,rt.ADMIN_RT_DESC,site.ADMIN_SITE_DESC,seq.VAC_DOS_SEQ_DESC\n" +
                    " from (SELECT t.IMMU_RECORD_ID,t.IMMU_RECORD_ID_SRC,t.PATIENT_KEY,t.VERSION,t.ENCOUNTER_ID,t.LOT_NUM,\n" +
                    " t.ADMIN_PLC_CD,t.IS_VLD_DOS,t.IMMUN_DATE,t.REC_STS,t.BARCODE,t.REMARK,t.INVLD_DOS_RSN_TXT,t.UPDATE_BY,t.UPDATE_DTM,\n" +
                    " t.PCV13_HX_INDT,t.PCV23_HX_INDT,t.SERVICE_CD,t.IMMU_VACCINE_LIST_ID,t.ADMIN_RT_CD,t.ADMIN_SITE_CD,t.VAC_DOS_SEQ_CD,\n" +
                    " P.PROD_NAME,P.HK_REG_NUM,P.VAC_CATGRY,t.INVLD_DOS_RSN_TXT||t.REMARK AS INVLD_DOS_RSN_TXT_AND_REMARK,\n" +
                    " '(HK-'||p.HK_REG_NUM||')'||p.PROD_NAME AS HK_REG_NUM_AND_PROD_NAME,P.VAC_GROUP\n" +
                    "FROM IMMU_RECORD T \n" +
                    "INNER JOIN IMMU_VACCINE_LIST P ON T.IMMU_VACCINE_LIST_ID = P.IMMU_VACCINE_LIST_ID\n" +
                    "WHERE  T.PATIENT_KEY=?1 AND T.REC_STS IN (?2) AND (?3 IS NULL OR P.VAC_GROUP IN (?4)) AND (?3 IS NULL OR T.IS_VERIF=1)) t\n" +
                    "left JOIN CODE_ADMIN_RT rt ON t.ADMIN_RT_CD = rt.ADMIN_RT_CD\n" +
                    "left JOIN CODE_ADMIN_SITE site ON t.ADMIN_SITE_CD = site.ADMIN_SITE_CD\n" +
                    "left JOIN CODE_VAC_DOS_SEQ seq ON t.VAC_DOS_SEQ_CD = seq.VAC_DOS_SEQ_CD) t) t",
            nativeQuery = true)
    Page<Map> listRecord(Long pantientKey, List<String> recStsList, String flag,List<String> vacGroup,Pageable pageable);   
}

第二种，List工具类排序方式

看这位大神的博客就行了

https://blog.csdn.net/baiyan3212/article/details/87279740

如果你需要分页的话，可以在工具类里加上下面这段代码

if (pageIndex > 0 && pageSize > 0) {
            List<?> list = new ArrayList<>();
            int beginIndex = (pageIndex - 1) * pageSize;
            int endIndex = beginIndex + pageSize;
            int maxIndex = data.size();

            if (beginIndex > maxIndex) {
                //抛出自定义参数异常
            }

            if (maxIndex >= endIndex) {
                list = data.subList(beginIndex, endIndex);
            } else {
                list = data.subList(beginIndex, maxIndex);
            }
            return list;
        }

这是我工作中的总结，如果你有更好的方法，请联系我，希望你也乐于分享，thanks