Spring Jpa 拼sql查询舱单需求案例

    private static final String EXP_EMAIL_SQL = "" +
            "select m.LOAD_PORT_CODE, m.DISCHARGE_PORT_CODE,m.BL_NO,m.ID " +
            "from EXP_MANIFEST m " +
            "where m.IS_DEL = 0 and m.SAILING_SCHEDULE_ID = :sailingId and m.CONFIRM_STATUS=1 order by m.CREATED_DATE asc";

    private static final String EXP_CARGO_EMAIL_SQL = "" +
            "select c.CARGO_SEQUENCE_NO, c.CARGO_CODE, m.LOAD_PORT_CODE, m.DISCHARGE_PORT_CODE, " +
            "c.PACKAGE_KIND_CODE, m.TOTAL_PACKAGE_NUMBER, m.TOTAL_GROSS_WEIGHT,c.CARGO_DESC,m.BL_NO " +
            "from EXP_MANIFEST m left join EXP_MANIFEST_CARGO c on m.id = c.MANIFEST_ID " +
            "where m.IS_DEL = 0 and m.ID = :id order by c.CREATED_DATE asc";

public ShManifestCargo findExpCargoAndBlNo(String sailingId) {

        Pageable pageable = new PageRequest(0, 1);
        Map<String, Object> params = new HashMap<>();
        params.put("sailingId", sailingId);
        Page<Object> result = repository.findByNativeQuery(EXP_EMAIL_SQL, params, pageable);
        if (result.getContent().size() > 0) {
            ShManifestCargo shManifestCargo = this.getCargoData(result);
            if (shManifestCargo != null) {
                Map<String, Object> paramst = new HashMap<>();
                paramst.put("id", shManifestCargo.getId());
                Page<Object> resultObject = repository.findByNativeQuery(EXP_CARGO_EMAIL_SQL, paramst, pageable);
                if (resultObject.getContent().size() > 0) {
                    return this.getCargoData(resultObject);
                }
            }
        }
        return null;
    }



  @SuppressWarnings("unchecked")
    private ShManifestCargo getCargoData(Page<Object> result) {
        ShManifestCargo shManifestCargo = new ShManifestCargo();
        Map<String, Object> resultMap = (Map<String, Object>) result.getContent().get(0);
        if (resultMap.get("CARGO_SEQUENCE_NO") != null) {
            shManifestCargo.setCargoSequenceNo((BigDecimal) resultMap.get("CARGO_SEQUENCE_NO"));
        }
        if (resultMap.get("CARGO_CODE") != null) {
            shManifestCargo.setCargoCode((String) resultMap.get("CARGO_CODE"));
        }
        if (resultMap.get("LOAD_PORT_CODE") != null) {
            shManifestCargo.setLoadPortCode((String) resultMap.get("LOAD_PORT_CODE"));
        }
        if (resultMap.get("DISCHARGE_PORT_CODE") != null) {
            shManifestCargo.setDischargePortCode((String) resultMap.get("DISCHARGE_PORT_CODE"));
        }
        if (resultMap.get("PACKAGE_KIND_CODE") != null) {
            shManifestCargo.setPackageKindCode((String) resultMap.get("PACKAGE_KIND_CODE"));
        }
        if (resultMap.get("TOTAL_PACKAGE_NUMBER") != null) {
            shManifestCargo.setPackageNumber((BigDecimal) resultMap.get("TOTAL_PACKAGE_NUMBER"));
        }
        if (resultMap.get("TOTAL_GROSS_WEIGHT") != null) {
            shManifestCargo.setGrossWeight((BigDecimal) resultMap.get("TOTAL_GROSS_WEIGHT"));
        }
        if (resultMap.get("CARGO_DESC") != null) {
            shManifestCargo.setCargoDesc((String) resultMap.get("CARGO_DESC"));
        }
        if (resultMap.get("BL_NO") != null) {
            shManifestCargo.setBlNo((String) resultMap.get("BL_NO"));
        }
        if (resultMap.get("ID") != null) {
            shManifestCargo.setId((String) resultMap.get("ID"));
        }
        return shManifestCargo;
    }

拼sql时注意逗号是全角还是半角，如果弄错了会报无效的字符错误，但是将sql拿出来在数据库执行不报错误，这是一个坑。