在使用Mybatis进行Null判定时，Null应该使用小写字母，而不是大写字母

2 年 ago
清, 扬
7 minutes

首先

在使用Mybatis时，在对SQL的WEREE条件进行Null检查时，遇到了以下类似的错误。

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'NULL' not found. Available parameters are [id, param1]
 at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)
 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
 at com.sun.proxy.$Proxy72.selectOne(Unknown Source)
 at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
 at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
 at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
 at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
 at com.sun.proxy.$Proxy86.selectNullError(Unknown Source)
 at jp.com.app.mapper.NullErrorMapperTest.Error_Test(NullErrorMapperTest.java:24)
 at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
 at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
Caused by: org.apache.ibatis.binding.BindingException: Parameter 'NULL' not found. Available parameters are [id, param1]
 at org.apache.ibatis.binding.MapperMethod$ParamMap.get(MapperMethod.java:212)
 at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:120)
 at org.apache.ibatis.ognl.OgnlRuntime.getProperty(OgnlRuntime.java:3356)
 at org.apache.ibatis.ognl.ASTProperty.getValueBody(ASTProperty.java:121)
 at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
 at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:258)
 at org.apache.ibatis.ognl.ASTNotEq.getValueBody(ASTNotEq.java:51)
 at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
 at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:258)
 at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:586)
 at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:550)
 at org.apache.ibatis.scripting.xmltags.OgnlCache.getValue(OgnlCache.java:46)
 at org.apache.ibatis.scripting.xmltags.ExpressionEvaluator.evaluateBoolean(ExpressionEvaluator.java:32)
 at org.apache.ibatis.scripting.xmltags.IfSqlNode.apply(IfSqlNode.java:34)
 at org.apache.ibatis.scripting.xmltags.MixedSqlNode.lambda$apply$0(MixedSqlNode.java:32)
 at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
 at org.apache.ibatis.scripting.xmltags.MixedSqlNode.apply(MixedSqlNode.java:32)
 at org.apache.ibatis.scripting.xmltags.TrimSqlNode.apply(TrimSqlNode.java:55)
 at org.apache.ibatis.scripting.xmltags.MixedSqlNode.lambda$apply$0(MixedSqlNode.java:32)
 at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
 at org.apache.ibatis.scripting.xmltags.MixedSqlNode.apply(MixedSqlNode.java:32)
 at org.apache.ibatis.scripting.xmltags.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:39)
 at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:305)
 at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:87)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
 ... 72 more

結果

和 等条件语句应该使用「小写」字母来书写！

<if test="id != null ">
    AND id = #{id}
</if>

<if test="id != NULL ">
    AND id = #{id}
</if>

实验（仅供感兴趣的人参考）

假设执行了从用户信息表中获取id为1的用户的SQL查询。
假设表结构如下。

CREATE TABLE users_infomaion (
  id integer,
  name VARCHAR(255)
);

我将添加以下数据。

INSERT INTO users_infomaion VALUES(1,'Tarou');
INSERT INTO users_infomaion VALUES(2,'Jirou');
INSERT INTO users_infomaion VALUES(3,'Saburou');

以下是用于获取SQL结果的Bean（实体）。

package jp.com.app.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class SampleEntity1Response {
    private Integer id;
    private String name;
}

Mapper.java 中包含以下内容。分别创建了用于正常结束的 selectRunning 方法和用于异常结束的 selectNullError 方法。

package jp.com.app.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import jp.com.app.entity.SampleEntity1Response;

@Mapper
public interface NullErrorMapper {
    SampleEntity1Response selectRunning(@Param("id")Integer id);

    SampleEntity1Response selectNullError(@Param("id")Integer id);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="jp.com.app.mapper.NullErrorMapper">
    <!-- 正常終了版 -->
    <select id="selectRunning" resultType="jp.com.app.entity.SampleEntity1Response">
        SELECT
            id,
            name
        FROM
            users_infomaion
        <where> 
            <!-- ※ nullが小文字 -->
            <if test="id != null ">
                AND id = #{id}
            </if>
        </where>
    </select>
    <!-- 異常終了版 -->
    <select id="selectNullError" resultType="jp.com.app.entity.SampleEntity1Response">
        SELECT
            id,
            name
        FROM
            users_infomaion
        <where> 
            <!-- ※ NULLが大文字 -->
            <if test="id != NULL ">
                AND id = #{id}
            </if>
        </where>
    </select>
</mapper>

我们将使用Junit来直接调用Mapper并执行。

package jp.com.app.mapper;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;

import jp.com.app.entity.SampleEntity1Response;

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
public class NullErrorMapperTest {
    @Autowired
    NullErrorMapper mapper;
    @Test
    public void Runnning_Test()throws Exception{
        Integer id = 1;
        SampleEntity1Response res = mapper.selectRunning(id);
        System.out.println("**************************************************************");
        System.out.println(res);
        System.out.println("**************************************************************");
    }

    @Test
    public void Error_Test()throws Exception{
        Integer id = 1;
        SampleEntity1Response res = mapper.selectNullError(id);
        System.out.println("**************************************************************");
        System.out.println(res);
        System.out.println("**************************************************************");
    }
}


以下のようにResponseの値がコンソールに表示されたら成功です。
...省略
2022-08-30 15:06:29.502  INFO 25171 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
**************************************************************
SampleEntity1Response(id=1, name=Tarou)
**************************************************************
2022-08-30 15:06:29.714  INFO 25171 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
```...省略

如果出现以下错误就表示失败。
如果发生以下这种错误就是失败。

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'NULL' not found. Available parameters are [id, param1]
 at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)
 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
 at com.sun.proxy.$Proxy72.selectOne(Unknown Source)
 at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
 at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
 at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
 at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
 at com.sun.proxy.$Proxy86.selectNullError(Unknown Source)
 at jp.com.app.mapper.NullErrorMapperTest.Error_Test(NullErrorMapperTest.java:26)
 at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
 at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
Caused by: org.apache.ibatis.binding.BindingException: Parameter 'NULL' not found. Available parameters are [id, param1]
 at org.apache.ibatis.binding.MapperMethod$ParamMap.get(MapperMethod.java:212)
 at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:120)
 at org.apache.ibatis.ognl.OgnlRuntime.getProperty(OgnlRuntime.java:3356)
 at org.apache.ibatis.ognl.ASTProperty.getValueBody(ASTProperty.java:121)
 at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
 at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:258)
 at org.apache.ibatis.ognl.ASTNotEq.getValueBody(ASTNotEq.java:51)
 at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
 at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:258)
 at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:586)
 at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:550)
 at org.apache.ibatis.scripting.xmltags.OgnlCache.getValue(OgnlCache.java:46)
 at org.apache.ibatis.scripting.xmltags.ExpressionEvaluator.evaluateBoolean(ExpressionEvaluator.java:32)
 at org.apache.ibatis.scripting.xmltags.IfSqlNode.apply(IfSqlNode.java:34)
 at org.apache.ibatis.scripting.xmltags.MixedSqlNode.lambda$apply$0(MixedSqlNode.java:32)
 at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
 at org.apache.ibatis.scripting.xmltags.MixedSqlNode.apply(MixedSqlNode.java:32)
 at org.apache.ibatis.scripting.xmltags.TrimSqlNode.apply(TrimSqlNode.java:55)
 at org.apache.ibatis.scripting.xmltags.MixedSqlNode.lambda$apply$0(MixedSqlNode.java:32)
 at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
 at org.apache.ibatis.scripting.xmltags.MixedSqlNode.apply(MixedSqlNode.java:32)
 at org.apache.ibatis.scripting.xmltags.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:39)
 at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:305)
 at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:87)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
 ... 72 more

最后 (zuì

Mybatis的Bean(Entity)的变量名类型和列名不一致时，可能无法获取值等情况发生，因此在命名时需要注意。顺便提一句，我曾经因为混淆了”resultMap”和”resultType”而感到困扰。请确保仔细检查！

✖
广告
将在 10 秒后关闭
bannerAds