Mybatis根据List批量查询List结果

r囧r小猫 2022-05-11 05:08 229阅读 0赞

转自：[https://blog.csdn.net/u014710520/article/details/72882947][https_blog.csdn.net_u014710520_article_details_72882947]

## 一、mapper接口 ##

/**
     * 根据剧典id list查询剧典
     */
    public List<Drama> selectByIds(@Param("dramaIds")List<Long> dramaIds);

## 二、mapper.xml文件 ##

<select id="selectByIds" resultMap="DramaImageResultMap">
        select * from drama where drama_id in 
        <foreach collection="dramaIds" item="dramaId" open="(" close=")" separator=",">
        #{dramaId}
       </foreach>
    </select>

# 推荐的MyBatis传参方式List、数组等 #

转自：[http://blog.51cto.com/13523664/2060417][http_blog.51cto.com_13523664_2060417]

# MyBatis 推荐的传参方式 #

如果要详细的学习 MyBatis，推荐看这个教程：[https://www.w3cschool.cn/mybatis/][https_www.w3cschool.cn_mybatis]

## 1. 单个参数 ##

//接口方法
    int getAgeById(Integer id);
    //xml映射文件
    <select id="getAgeById" resultType="Integer">
        select age from user where user_id = #{id}
    </select>

## 2. 多个参数 ##

//接口方法
     User login(@Param("username") String username, @Param("password") String password);
     //xml映射文件
     <select id="login" resultMap="BaseResultMap">
        select
        *
        from user
        where username = #{username} and password = #{password}
    </select>

## 3. 数组参数 ##

//接口方法
    ArrayList<User> selectByIds(Integer [] ids);
    //xml映射文件
    <select id="selectByIds" resultMap="BaseResultMap">
        select
        *
        from user where id in
        <foreach item="item" index="index" collection="array" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

## 4.List参数 ##

//接口方法
    ArrayList<User> selectByIds(List<Integer> ids);
    //xml映射文件
    <select id="selectByIds" resultMap="BaseResultMap">
        Select
        <include refid="Base_Column_List" />
        from jria where ID in
        <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
              #{item}
          </foreach>
      </select>

# Mybatis通过出入Map参数作为条件进行查询 #

转自：[https://blog.csdn.net/u014268482/article/details/80622216][https_blog.csdn.net_u014268482_article_details_80622216]

映射文件中查询语句部分:

<select id="selectByMap" resultType="com.heiketu.testpackage.pojo.Product">
        select * from Products where prod_price = #{prodPrice} and prod_desc = #{prodDesc}
    </select>

*  1
 *  2
 *  3
 *  4

接口文件中对应的查询方法:

//输入参数为Map的条件查询
    Product selectByMap(Map<String,Object> map);

*  1
 *  2

测试代码:

//...前面有创建sqlSessionFactory对象和SQLSession对象的代码
    
    Map<String,Object> map = new HashMap<>();
    map.put("prodPrice","11.99");
    map.put("prodDesc","18 inch teddy bear, comes with cap and jacket");
    
    ProductsMapper mapper = sqlSession.getMapper(ProductsMapper.class);
    Product product = mapper.selectByMap(map);
    System.out.println(product);

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9

**`map中的key值与映射文件中的select语句#{}占位符中的值需要一一对应`**

在mybatis中，任何传入的参数都会被对应封装成Map集合，然后会以map的key=value形式取值。   
对传入的参数是`List`集合，mybatis会对`list`集合进行特殊处理，其取值方式通过`list[下标]`或`collection[下标]`的方式入参取值。   
对于数组，Mybatis同样会做特殊处理，它会对数组采用`array[下标]`的方式入参取值。

<select id="selectByMap" resultType="com.heiketu.testpackage.pojo.Product">
        select * from Products where prod_price = #{list[0]} and prod_desc = #{collection[1]}
    </select>

<select id="selectByMap" resultType="com.heiketu.testpackage.pojo.Product">
        select * from Products where prod_price = #{array[0]} and prod_desc = #{array[1]}
    </select>

[https_blog.csdn.net_u014710520_article_details_72882947]: https://blog.csdn.net/u014710520/article/details/72882947
[http_blog.51cto.com_13523664_2060417]: http://blog.51cto.com/13523664/2060417
[https_www.w3cschool.cn_mybatis]: https://www.w3cschool.cn/mybatis/
[https_blog.csdn.net_u014268482_article_details_80622216]: https://blog.csdn.net/u014268482/article/details/80622216