mybatis报错：java.lang.NumberFormatException: For input string: \“qwe123\““,“rel“:false

清疚 2023-09-27 15:37 2阅读 0赞

**目录**

1.问题

2.说明

3.解决方案

4.精度丢失问题(额外补充)

--------------------

### 1.问题 ###

mybatis在执行映射文件中的sql时，报NumberFormatException。如下：

java.lang.NumberFormatException: For input string: \\"qwe123\\""。

问题的sql：

<select id="selectInventoryStorageRecord"  resultMap="inventoryStorageRecordMap">
            SELECT *
            FROM inventory_storage_record
            WHERE warehouse_code = #{record.warehouseCode}
            AND customer_code = #{record.customerCode}
            AND goods_no = #{record.goodsNo}
            AND storage_type in
                <foreach collection="typeList" open="(" close=")" item="type" separator=",">
                    #{type}
                </foreach>
            AND due_date > #{record.dueDate}
            AND quantity > frozen_quantity
            <if test="record.batchNo != null  and record.batchNo != '*'">
                AND batch_no = #{record.batchNo}
            </if>
            ORDER BY storage_type,due_date,quantity ASC
        </select>

### 2.说明 ###

问题主要出现在条件中对batchNo的判断上，在实体类中这个项目的类型是String，在数据库中这个项目是varchar类型的。在这个判断中有一个不等于“\*”的判断，mybatis是用OGNL表达式来解析的，在OGNL的表达式中，'\*'会被解析成字符，java是强类型的，char 和 string进行比较就会出现这个问题，至于为什么是NumberFormatException，我没有进行深入的研究。

### 3.解决方案 ###

(1)方案1

<select id="selectInventoryStorageRecord"  resultMap="inventoryStorageRecordMap">
            SELECT *
            FROM inventory_storage_record
            WHERE warehouse_code = #{record.warehouseCode}
            AND customer_code = #{record.customerCode}
            AND goods_no = #{record.goodsNo}
            AND storage_type in
                <foreach collection="typeList" open="(" close=")" item="type" separator=",">
                    #{type}
                </foreach>
            AND due_date > #{record.dueDate}
            AND quantity > frozen_quantity
            <if test='record.batchNo != null and record.batchNo != "" and record.batchNo != "*"'>
                AND batch_no = #{record.batchNo}
            </if>
            ORDER BY storage_type,due_date,quantity ASC
        </select>

判断中使用双引号，外层使用单引号。

(2)方案2

<select id="selectInventoryStorageRecord"  resultMap="inventoryStorageRecordMap">
            SELECT *
            FROM inventory_storage_record
            WHERE warehouse_code = #{record.warehouseCode}
            AND customer_code = #{record.customerCode}
            AND goods_no = #{record.goodsNo}
            AND storage_type in
                <foreach collection="typeList" open="(" close=")" item="type" separator=",">
                    #{type}
                </foreach>
            AND due_date > #{record.dueDate}
            AND quantity > frozen_quantity
            <if test="record.batchNo != null  and record.batchNo != '*'.toString()">
                AND batch_no = #{record.batchNo}
            </if>
            ORDER BY storage_type,due_date,quantity ASC
        </select>

使用toString()方法转换为字符串进行比较。

### 4.精度丢失问题(额外补充) ###

后端传到前端的数据精度丢失，因为long的位数是64位，而js只能承载17位，多余的位数会被舍弃掉。所以只要在后端将传递出的数据转成string就行。

@JsonFormat(shape = JsonFormat.Shape.STRING)
    private Long id;