springboot 注解版案例

1、dao层

 /**
     * 查询列表
     * @param dataRequest
     * @return
     */
    @SelectProvider(type = CuiShouSqlProvider.class, method = "cuiShouList")
    @Options(useGeneratedKeys = true)
    public List<Map<String,String>> selectCuiShouOrderList(@Param("CuiKuanRequest") CuiKuanRequest dataRequest);
    //public List<Map<String,String>> selectCuiShouOrderList(@Param("CuiKuanRequest") CuiKuanRequest dataRequest);

    /**
     * 查询条数
     * @param dataRequest
     * @return
     */
    @SelectProvider(type = CuiShouSqlProvider.class, method = "cuiShouListCountNum")
    @Options(useGeneratedKeys = true)
    public int CuiShouOrderListCountNum(@Param("CuiKuanRequest") CuiKuanRequest dataRequest);

 

2、sql拼接类

/**
 * @author wuhongpu
 * 催收动态sql拼接
 */

public class  CuiShouSqlProvider {

    private Logger logger = LoggerFactory.getLogger(CuiShouSqlProvider.class);
    /**
     * 查询list列表方法
     * @param dataRequest
     * @return
     */
    public String cuiShouList(CuiKuanRequest dataRequest){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("select * from (SELECT w.`status` AS clearStatus,w.thirdType,w.createTime,o.uuid,o.`status` AS orderStatus,o.lendingTime,o.refundTime,o.amountApply,u.realName,u.mobileNumber,u.idCardNo,datediff(now(),DATE_FORMAT(o.refundTime, '%Y-%m-%d')) AS DAY ");
        selectSql.append("FROM wk_cuishou w LEFT JOIN orderorder o ON w.orderNo = o.uuid LEFT JOIN useruser u ON o.userUuid = u.uuid)t where 1=1 ");

        StringBuilder conditionSql = this.generateCondition(dataRequest);

        if (dataRequest.getPageNo() != null && dataRequest.getPageSize() != null) {
            Integer num = (dataRequest.getPageNo() - 1) * dataRequest.getPageSize();
            conditionSql.append(" limit ").append(num.toString()).append(",")
                    .append(dataRequest.getPageSize().toString());
        }

        selectSql.append(conditionSql);
        logger.info(selectSql.toString());
        return selectSql.toString();
    }


    /**
     * 统计条数方法
     * @param dataRequest
     * @return
     */
    public String cuiShouListCountNum(CuiKuanRequest dataRequest) {
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("select count(1) from (SELECT w.`status` AS clearStatus,w.thirdType,w.createTime,o.uuid,o.`status` AS orderStatus,o.lendingTime,o.refundTime,o.amountApply,u.realName,u.mobileNumber,u.idCardNo,datediff(now(),DATE_FORMAT(o.refundTime, '%Y-%m-%d')) AS DAY ");
        selectSql.append("FROM wk_cuishou w LEFT JOIN orderorder o ON w.orderNo = o.uuid LEFT JOIN useruser u ON o.userUuid = u.uuid)t where 1=1 ");

        StringBuilder conditionSql = this.generateCondition(dataRequest);
        selectSql.append(conditionSql);
        this.logger.info(selectSql.toString());
        return selectSql.toString();
    }

    /**
     * 条数分页方法
     * @param dataRequest
     * @return
     */
    public String cuiShouListListByPage(CuiKuanRequest dataRequest) {
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("select * from (SELECT w.`status` AS clearStatus,w.thirdType,w.createTime,o.uuid,o.`status` AS orderStatus,o.lendingTime,o.refundTime,o.amountApply,u.realName,u.mobileNumber,u.idCardNo,datediff(now(),DATE_FORMAT(o.refundTime, '%Y-%m-%d')) AS DAY ");
        selectSql.append("FROM wk_cuishou w LEFT JOIN orderorder o ON w.orderNo = o.uuid LEFT JOIN useruser u ON o.userUuid = u.uuid)t where 1=1 ");


        StringBuilder conditionSql = this.generateCondition(dataRequest);

        Integer num = (dataRequest.getPageNo() - 1) * dataRequest.getPageSize();
        conditionSql.append("limit ").append(num.toString()).append(",")
                .append(dataRequest.getPageSize().toString());

        selectSql.append(conditionSql);
        logger.info(selectSql.toString());
        return selectSql.toString();
    }

    /**
     * 参数动态拼接方法
     * @param dataRequest
     * @return
     */
    private StringBuilder generateCondition(CuiKuanRequest dataRequest) {
        StringBuilder conditionSql = new StringBuilder();

        if(dataRequest.getOrderStatus()!=null){
            conditionSql.append("t.orderStatus=#{CuiKuanRequest.orderStatus} and ");
        }
        if(dataRequest.getClearStatus()!=null){
            conditionSql.append("t.clearStatus=#{CuiKuanRequest.clearStatus} and ");
        }
        if(dataRequest.getDay()!=null  ){
            if(dataRequest.getDay()>=7){
                conditionSql.append("t.day>#{CuiKuanRequest.day} ");
            }else{
                conditionSql.append("t.day=#{CuiKuanRequest.day} ");
            }

        }
       conditionSql.append("order by t.day desc");

        return conditionSql;
    }
}