问题描述

Mapper

/**
* 生成新增数据的申请单号(流程编号)
*
* @param flowType 流程类型 {@link com.tencent.finance.enums.FlowTypeEnum}
* @return 申请单号
*/
FlowNoVO generateFlowNo(@Param("flowType") String flowType);

Mapper.xml

<select id="generateFlowNo"
        parameterType="java.lang.String"
        resultType="com.tencent.finance.model.entity.FlowNoVO">
    select max(t.flow_no)                                                         flow_no
         , concat(date_format(now(), '%Y%m%d'), '-#{flowType}-') flow_pre
    from smallflow_basic_information t
    where t.flow_type = #{flowType}
      and t.flow_no like concat(date_format(now(), '%Y%m%d'), '-#{flowType}-%')
</select>

调用报错

{
  "code": 400,
  "message": "nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='flowType', mode=IN, javaType=class java.lang.Object, jdbcType=VARCHAR, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.lang.reflect.UndeclaredThrowableException"
}

问题分析

• 原因是使用 #{} 会把对应变量添加一个单引号 '

• 在下面两个 SQL 语句中，已经有单引号了，在其中使用 #{} 会再添加单引号导致解析错误

concat(date_format(now(), '%Y%m%d'), '-#{flowType}-')
concat(date_format(now(), '%Y%m%d'), '-#{flowType}-%')

问题解决

• 修改 Mapper.xml，在单引号中使用 ${} 替换 #{}

<!--生成新增数据的申请单号(流程编号)-->
<select id="generateFlowNo"
        parameterType="java.lang.String"
        resultType="com.tencent.finance.model.entity.FlowNoVO">
    select max(t.flow_no)                                                         flow_no
         , concat(date_format(now(), '%Y%m%d'), '-${flowType}-') flow_pre
    from smallflow_basic_information t
    where t.flow_type = #{flowType}
      and t.flow_no like concat(date_format(now(), '%Y%m%d'), '-${flowType}-%')
</select>

附：本案例中序列的生成方式

Impl

/**
 * 生成新增数据的申请单号(流程编号)
 *
 * @param flowType 流程类型 {@link com.tencent.finance.enums.FlowTypeEnum}
 * @return 申请单号
 */
@Override
public String generateFlowNo(String flowType) {
    String msg;
    String logInfo = "generateFlowNo >> {}";
    String key = FlowTypeEnum.getCodeByKey(flowType);
    if (CommonUtil.isNull(key)) {
        msg = String.format("流程类型“%s”不正确", flowType);
        log.info(logInfo, msg);
        return null;
    }
    FlowNoVO flowNoVO = baseMapper.generateFlowNo(flowType);
    String flowNo = flowNoVO.getFlowNo();
    String flowPre = flowNoVO.getFlowPre();
    if (CommonUtil.isNull(flowNo)) {
        flowNo = flowPre + "001";
    } else {
        String noStr = flowNo.split("-")[2];
        int no = Integer.parseInt(noStr) + 1;
        if (no >= 1000) {
            flowNo = flowPre + no;
        } else {
            flowNo = flowPre + CommonUtil.getSeqLengthStr(no, 3);
        }
    }
    return flowNo;
}

CommonUtil.getSeqLengthStr

/**
 * 获取指定长度的序列号
 *
 * @param seqNumber 序列数字，例如 1
 * @param lenght 指定长度，例如 3
 * @return 返回指定长度的序列号，比如序列数字 1，指定长度 3，返回 001
 */
public static String getSeqLengthStr(Integer seqNumber, Integer lenght) {
    //seqNumber = seqNumber + 1;
    String seqNumberStr = String.valueOf(seqNumber);
    if (seqNumberStr.length() >= lenght) {
        return seqNumberStr;
    }
    StringBuilder sb = new StringBuilder();
    int count = lenght - seqNumberStr.length();
    for (int i = 0; i < count; i++) {
        sb.append("0");
    }
    sb.append(seqNumberStr);
    return sb.toString();
}

Mapper

/**
* 生成新增数据的申请单号(流程编号)
*
* @param flowType 流程类型 {@link com.tencent.finance.enums.FlowTypeEnum}
* @return 申请单号
*/
FlowNoVO generateFlowNo(@Param("flowType") String flowType);

Mapper.xml

<!--生成新增数据的申请单号(流程编号)-->
<select id="generateFlowNo"
        parameterType="java.lang.String"
        resultType="com.tencent.finance.model.entity.FlowNoVO">
    select max(t.flow_no)                                                         flow_no
         , concat(date_format(now(), '%Y%m%d'), '-${flowType}-') flow_pre
    from smallflow_basic_information t
    where t.flow_type = #{flowType}
      and t.flow_no like concat(date_format(now(), '%Y%m%d'), '-${flowType}-%')
</select>

例如

-- 获取对应类型最大的流程编号
select max(t.flow_no) flow_no
       ,concat(date_format(now(),'%Y%m%d'),'-04-') flow_pre 
from smallflow_basic_information t 
where t.flow_type = '04' 
  and t.flow_no like concat(date_format(now(),'%Y%m%d'),'-04-%')

结果

调用结果

//20230823-04-001
String no = generateFlowNo('04');

附：其它序列生成方式

Oracle-seqence（序列 ），手动序列