Mybatis传多个参数的bug

青旅半醒 2022-06-07 12:39 222阅读 0赞

# Mybatis传多个参数的bug #

项目上采用了mybatis做ORM框架,今天遇到一个bug

dao层代码如下,这里我才用mybatis注解方式:

@Select("SELECT a.id AS "id", a. NAME AS "name", a.description AS "description", a.points AS "points", a.sales AS "sales", a.picture_url_default AS "pictureUrlDefault" FROM shop_product a WHERE a.category_id = #{id} ORDER BY sales DESC LIMIT #{startIndex},#{pageSize};")
    List<ShopProduct> getShopProductByCategoryId( String id,  int startIndex,int pageSize);

单元测试报如下错误:

Caused by: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [arg2, arg1, arg0, param3, param1, param2]

报参数找不到异常,  
后来改dao底层代码如下,采用索引传参方法:

@Select("SELECT a.id AS "id", a. NAME AS "name", a.description AS "description", a.points AS "points", a.sales AS "sales", a.picture_url_default AS "pictureUrlDefault" FROM shop_product a WHERE a.category_id = #{0} ORDER BY sales DESC LIMIT #{1},#{2};")
    List<ShopProduct> getShopProductByCategoryId( String id,  int startIndex,int pageSize);

再次单元测试,还是报错

Caused by: org.apache.ibatis.binding.BindingException: Parameter '0' not found. Available parameters are [arg2, arg1, arg0, param3, param1, param2]

再次gaidao层代码如下:  
**这次采用 @param 注解方法**

@Select("SELECT a.id AS "id", a. NAME AS "name", a.description AS "description", a.points AS "points", a.sales AS "sales", a.picture_url_default AS "pictureUrlDefault" FROM shop_product a WHERE a.category_id = #{id} ORDER BY sales DESC LIMIT #{startIndex},#{pageSize};")
     List<ShopProduct> getShopProductByCategoryId(@Param("id") String id, @Param("startIndex") int startIndex,@Param("pageSize") int pageSize);

再次单元测试,成功通过.

**为此困惑不已,特地记录此次bug,并总结下 mybatis多参数传递的几种方法:(注:我是用的是springboot的mybatis,不知道是否是这个问题导致)**

## 第一种:索引 ##

**DAO层的函数方法**

Public User selectUser(String name,String area);

**对应的Mapper.xml**

<select id="selectUser" resultMap="BaseResultMap">
        select  *  from user_user_t   where user_name = #{0} and user_area=#{1}
    </select>

其中，\#\{0\}代表接收的是dao层中的第一个参数，\#\{1\}代表dao层中第二参数，更多参数一致往后加即可。

## 第二种:采用Map传多参数 ##

**Dao层的函数方法**

Public User selectUser(Map paramMap);

**对应的Mapper.xml**

<select id=" selectUser" resultMap="BaseResultMap">
       select  *  from user_user_t   where user_name = #{userName，jdbcType=VARCHAR} and user_area=#{userArea,jdbcType=VARCHAR}
    </select>

**Service层调用**

Private User xxxSelectUser(){
    Map paramMap=new hashMap();
    paramMap.put(“userName”,”对应具体的参数值”);
    paramMap.put(“userArea”,”对应具体的参数值”);
    User user=xxx. selectUser(paramMap);}

## 第三种:我最常用的: 注解方式 @Param ##

**Dao层的函数方法**

Public User selectUser(@param(“userName”)Stringname,@param(“userArea”)String area);

**对应的Mapper.xml**

<select id=" selectUser" resultMap="BaseResultMap">
       select  *  from user_user_t   where user_name = #{userName，jdbcType=VARCHAR} and user_area=#{userArea,jdbcType=VARCHAR}
    </select>