mybatis-动态sql标签

最新推荐文章于 2025-09-22 10:06:17 发布

原创最新推荐文章于 2025-09-22 10:06:17 发布 · 197 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

mybatis 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍了MyBatis框架中动态SQL的三种标签：if、set和foreach的应用方法及案例，包括条件查询、更新字段及批量操作等功能实现。

1.<if>标签

  <select id="selectById" resultType="com.chen.pojo.User">
//2=2 进行等值判断 
        select * from emp where 2=2
        <if test="minSal!=null">
            and salary>=#{minSal}
        </if>
        <if test="maxSal!=null">
//这里<=会识别成标签开头的<   换成<![CDATA[<=]]>
            and salary <![CDATA[<=]]>#{maxSal}
        </if>
    </select>

  private static SqlSessionFactory ssf=null;
    static {
        try {
            InputStream  is = Resources.getResourceAsStream("mybatis-config.xml");
            ssf=new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
  @Test
    public void selectById(){
         Map map = new HashMap<>();
//这里的key值为mapper当中的#{}
         map.put("minSal",3000);
         map.put("maxSal",6000);
//自动提交
         SqlSession ss = ssf.openSession(true);
         List<User> user = ss.selectList("UserMapper.selectById", map);
         for(User u:user){
             System.out.println(u);
         }
     }

2.<set>标签

  <update id="updateById">
        update emp
        <set>
//进行不为空判断 防止数据丢失 通过参数传递进行修改
            <if test="name!=null">
                name=#{name},
            </if>
            <if test="job!=null">
                job=#{job},
            </if>
            <if test="salary!=null">
                salary=#{salary}
            </if>
        </set>
         where  id=#{id}
    </update>

  @Test
    public void updateById(){
        Map map=new HashMap();
        map.put("salary",8000);
        map.put("id",6);
         SqlSession ss = ssf.openSession(true);
         int rows = ss.update("UserMapper.updateById", map);
         System.out.println(rows);

     }

3.<foreach>标签

xxx:为数组/集合时用array/list

多个参数传递用Map集合中的key值

#{item} 这里为item中的值

</foreach>

3.1批量删除

  <delete id="deleteByIds">
        delete from emp where id in
        <foreach collection="array" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </delete>

  @Test
    public void deleteByIds(){
        Integer [] arr={6,7,8};
         SqlSession ss = ssf.openSession(true);
         int rows = ss.delete("UserMapper.deleteByIds", arr);
         System.out.println(rows);
     }

3.2批量查询

 <select id="selectByIds" resultType="com.chen.pojo.User">
        select * from emp where id in
        <foreach collection="array" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
</select>

  @Test
    public void selectByIds(){
        Integer [] arr={13,14};
         SqlSession ss = ssf.openSession(true);
         List<User> list = ss.selectList("UserMapper.selectByIds", arr);
        for(User u:list){
            System.out.println(u);
        }
     }

3.3批量修改

 <update id="updateByIds">
        update emp set salary=salary+#{sal} where id in
        <foreach collection="ids" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </update>

 @Test
    public void updateByIds(){
        Integer [] arr={13,14};
        Integer sal=600;
        Map map=new HashMap();
//connection  中 放的为map中的key值  这里为ids
        map.put("ids",arr);
        map.put("sal",sal);
        SqlSession ss = ssf.openSession(true);
         int rows = ss.update("UserMapper.updateByIds", map);
         System.out.println(rows);


     }