动态SQL_02

1、choose (when, otherwise)

1. 编写Mapper接口和Mapper.xml文件；

   package com.beyond.dao;
   
   import com.beyond.pojo.Blog;
   
   import java.util.List;
   import java.util.Map;
   
   public interface BlogMapper {
       //查询博客
       List<Blog> queryBlogChoose(Map map);
   }
   

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.beyond.dao.BlogMapper">
       <select id="queryBlogChoose" parameterType="map" resultType="Blog">
           select * from blog
           <where>
               <choose>
                   <when test="title != null">
                       and title=#{title}
                   </when>
                   <when test="author != null">
                       and author=#{author}
                   </when>
                   <otherwise>
                       and views=#{views}
                   </otherwise>
               </choose>
           </where>
       </select>
   </mapper>
   

2. 测试

   import com.beyond.dao.BlogMapper;
   import com.beyond.pojo.Blog;
   import com.beyond.utils.IdUtil;
   import com.beyond.utils.MyBatisUtil;
   import org.apache.ibatis.session.SqlSession;
   import org.junit.Test;
   
   import java.util.Date;
   import java.util.HashMap;
   import java.util.List;
   
   public class MyTest {
       @Test
       public void queryBlogChoose(){
           SqlSession sqlSession = MyBatisUtil.getSqlSession();
           BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
   
           HashMap map = new HashMap();
           map.put("title","微服务");
           map.put("author","狂神说");
           map.put("views","9999");
   
           List<Blog> blogs = mapper.queryBlogChoose(map);
   
           for (Blog blog:blogs) {
               System.out.println(blog);
           }
   
           sqlSession.close();
       }
   }
   

2、trim (where, set)

1、where标签只会在至少有一个子元素的条件满足时返回SQL子句的情况下才插入“WHERE”子句；而当子句的开头为“AND”或“OR”时，where标签也会将他们去除。

2、set元素将动态地置于SET关键字的前面，并且还消除了在应用条件后可能拖延值分配的任何多余的逗号。

1. 编写Mapper接口和Mapper.xml文件；

   package com.beyond.dao;
   
   import com.beyond.pojo.Blog;
   
   import java.util.List;
   import java.util.Map;
   
   public interface BlogMapper {
       //更新博客
       int updateBlog(Map map);
   }
   

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.beyond.dao.BlogMapper">
       <update id="updateBlog" parameterType="map">
           update blog
           <set>
               <if test="title != null">
                   title=#{title},
               </if>
               <if test="author != null">
                   author=#{author}
               </if>
           </set>
           where id=#{id}
       </update>
   </mapper>
   

2. 测试

   import com.beyond.dao.BlogMapper;
   import com.beyond.pojo.Blog;
   import com.beyond.utils.IdUtil;
   import com.beyond.utils.MyBatisUtil;
   import org.apache.ibatis.session.SqlSession;
   import org.junit.Test;
   
   import java.util.Date;
   import java.util.HashMap;
   import java.util.List;
   
   public class MyTest {
       @Test
       public void updateBlog(){
           SqlSession sqlSession = MyBatisUtil.getSqlSession();
           BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
   
           HashMap map = new HashMap();
           //map.put("title","java");
           map.put("author","遇见狂神说");
           map.put("id","95ef66054aa249ca8e01bcf9344c9ccb");
   
           mapper.updateBlog(map);
   
           sqlSession.close();
       }
   }
   

3、SQL片段

有的时候，我们可能会讲一些功能的部分抽取出来，方便复用。

	<sql id="if-title-author">
        <if test="title != null">
            and title=#{title}
        </if>
        <if test="author != null">
            and author=#{author}
        </if>
    </sql>

    <select id="queryBlogIf" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <include refid="if-title-author"></include>
        </where>
    </select>

使用的注意事项：

• 最好基于但表定义SQL语句；
• 不要存在where标签。

4、Foreach

1. 编写Mapper接口和Mapper.xml文件；

   package com.beyond.dao;
   
   import com.beyond.pojo.Blog;
   
   import java.util.List;
   import java.util.Map;
   
   public interface BlogMapper {
       //查询id为第1、2、4号记录的博客
       List<Blog> queryBlogForeach(Map map);
   }
   

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.beyond.dao.BlogMapper">
       <select id="queryBlogForeach" parameterType="map" resultType="Blog">
           select * from blog
           <where>
               <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                   id=#{id}
               </foreach>
           </where>
       </select>
   </mapper>
   

2. 测试

   import com.beyond.dao.BlogMapper;
   import com.beyond.pojo.Blog;
   import com.beyond.utils.IdUtil;
   import com.beyond.utils.MyBatisUtil;
   import org.apache.ibatis.session.SqlSession;
   import org.junit.Test;
   
   import java.util.Date;
   import java.util.HashMap;
   import java.util.List;
   
   public class MyTest {
       @Test
       public void queryBlogForeach(){
           SqlSession sqlSession = MyBatisUtil.getSqlSession();
           BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
   
           HashMap map = new HashMap();
   
           ArrayList<Integer> ids=new ArrayList<Integer>();
           ids.add(1);
           ids.add(2);
           ids.add(4);
   
           map.put("ids",ids);
           List<Blog> blogList=mapper.queryBlogForeach(map);
           for (Blog blog:blogList) {
               System.out.println(blog);
           }
   
           sqlSession.close();
       }
   }