jpa原生拼接示例

 private final static Logger LOG = LoggerFactory.getLogger(ContractorNonMotorApplyDao.class);
    private static final String SQL = "SELECT t.ID," +
            "       t.CREATE_TIME," +
            "       t.ORDER_NUMBER," +
            "       t.REMARK," +
            "       t.STAFF_ID," +
            "       b.CNAME STAFFNAME," +
            "       t.STATE_ID," +
            "       t.SUBMIT_TIME," +
            "       b.PHONE STAFFPHONE" +
            "       FROM xxx t  " +
            "       LEFT JOIN xxxx b ON t.STAFF_ID = b.ID";

    public ContractorNonMotorApplyDao(DataSource dataSource) {
        super(dataSource);
    }

    public PageImpl<ContractorNonMotorApplyDto> findPage(ContractorNonMotorApplyFindDto findDto) {
        return new PageImplBuilder<>(ContractorNonMotorApplyDto::ofSqlResult, findDto, SQL, this::fullWhere)
                .baseWhere(" WHERE (t.DELETE_MARK = 0) ")
                .order(" ORDER BY t.CREATE_TIME DESC ")
                .buildPage();

    }

    private void fullWhere(ContractorNonMotorApplyFindDto findDto, StringBuilder stringBuilder, Map<String, Object> stringObjectMap) {
        new ContractorNonMotorApplyFindSqlCommandPackage(findDto, stringBuilder, stringObjectMap).commandPackage();
    }

    public ContractorNonMotorApplyDto findById(String id, Boolean deleteMark) {
        String sql = SQL + " WHERE(t.ID = :id) ";
        if (!deleteMark) {
            sql = sql + " AND (t.DELETE_MARK = '0') ";
        }
        Map<String, Object> parameterMap = new HashMap<>(1);
        parameterMap.put("id", id);
        return super.getFirstResult(ContractorNonMotorApplyDto::ofSqlResult, sql, parameterMap).orElseThrow(() -> new LhyException("未找到该数据"));
    }

    static class ContractorNonMotorApplyFindSqlCommandPackage extends SqlCommandPackage<ContractorNonMotorApplyFindDto> {

        /**
         * SQL原生封装 构建函数
         *
         * @param findDto      查询条件
         * @param sqlBuilder   sqlBuilder
         * @param parameterMap 参数
         * @author LiuHuiYu
         * Created DateTime 2022-11-20 8:27
         */
        public ContractorNonMotorApplyFindSqlCommandPackage(ContractorNonMotorApplyFindDto findDto, StringBuilder sqlBuilder, Map<String, Object> parameterMap) {
            super(findDto, sqlBuilder, parameterMap);
        }

        @Override
        public void commandPackage() {
            IfRun.create()
                    .elseIf(this.findDto.getOperating().isModel(ContractorNonMotorApplyFindDto.FIND_MODEL_01), this::none)
                    .elseIf(this.findDto.getOperating().isModel(ContractorNonMotorApplyFindDto.FIND_MODEL_02), this::all)
                    .elseIf(this.findDto.getOperating().isModel(ContractorNonMotorApplyFindDto.FIND_MODEL_03), this::model3)
                    .orElseThrow(() -> new LhyException("模式未设定。"));
        }

        private void model3() {
            if (StringUtils.hasText(this.findDto.getStaffName())) {
                super.likeValue("b.CNAME", "staffName", this.findDto.getStaffName());
            }
            if (StringUtils.hasText(this.findDto.getApplicationNumber())) {
                super.likeValue("t.ORDER_NUMBER", "orderNumber", this.findDto.getApplicationNumber());
            }
            if (StringUtils.hasText(this.findDto.getContractorName())) {
                this.sqlBuilder.append("and(t.ID in(select t_list.APPLY_ORDER_NUMBER from ZNAF_CONTR_NONMOTOR_LIST t_list where (DELETE_MARK=0)and(t_list.CONTRACTOR_PERSONNEL_NAME like :personnelName)))");
                this.parameterMap.put("personnelName", this.findDto.getContractorName());
            }
            super.inPackage( "STATE", "t.STATE_ID",this.findDto.getStateIdList().toArray(new Integer[0]));
        }

        private void none() {
            if (StringUtils.hasText(findDto.getStaffName())) {
                this.sqlBuilder.append(" AND b.CNAME LIKE :staffName ");
                this.parameterMap.put("staffName", "%" + findDto.getStaffName() + "%");
            }
            if (StringUtils.hasText(findDto.getStaffId())) {
                this.sqlBuilder.append(" AND t.CREATOR_ID = :operatorId");
                this.parameterMap.put("operatorId", findDto.getStaffId());
            }
        }

        public void all() {

        }
    }