MyBatis的动态SQL是基于OGNL表达式的,它可以帮助我们方便的在SQL语句中实现某些逻辑。
MyBatis中用于实现动态SQL的元素主要有:
- if
- choose(when,otherwise)
- trim
- where
- set
- foreach
- <select id="dynamicIfTest" parameterType="Blog" resultType="Blog">
- select * from t_blog where 11 = 1
- <if test="title != null">
- and title = #{title}
- </if>
- <if test="content != null">
- and content = #{content}
- </if>
- <if test="owner != null">
- and owner = #{owner}
- </if>
- </select>
- <select id="dynamicChooseTest" parameterType="Blog" resultType="Blog">
- select * from t_blog where 11 = 1
- <choose>
- <when test="title != null">
- and title = #{title}
- </when>
- <when test="content != null">
- and content = #{content}
- </when>
- <otherwise>
- and owner = "owner1"
- </otherwise>
- </choose>
- </select>
- <select id="dynamicWhereTest" parameterType="Blog" resultType="Blog">
- select * from t_blog
- <where>
- <if test="title != null">
- title = #{title}
- </if>
- <if test="content != null">
- and content = #{content}
- </if>
- <if test="owner != null">
- and owner = #{owner}
- </if>
- </where>
- </select>
- <select id="dynamicTrimTest" parameterType="Blog" resultType="Blog">
- select * from t_blog
- <trim prefix="where" prefixOverrides="and |or">
- <if test="title != null">
- title = #{title}
- </if>
- <if test="content != null">
- and content = #{content}
- </if>
- <if test="owner != null">
- or owner = #{owner}
- </if>
- </trim>
- </select>
- <update id="dynamicSetTest" parameterType="Blog">
- update t_blog
- <set>
- <if test="title != null">
- title = #{title},
- </if>
- <if test="content != null">
- content = #{content},
- </if>
- <if test="owner != null">
- owner = #{owner}
- </if>
- </set>
- where id = #{id}
- </update>
- 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
- 如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
- 如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可以封装成map,实际上如果你在传入参数的时候,在MyBatis里面也是会把它封装成一个Map的,map的key就是参数名,所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key
- <select id="dynamicForeachTest" resultType="Blog">
- select * from t_blog where id in
- <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
- #{item}
- </foreach>
- </select>
- public List<Blog> dynamicForeachTest(List<Integer> ids);
- @Test
- public void dynamicForeachTest() {
- SqlSession session = Util.getSqlSessionFactory().openSession();
- BlogMapper blogMapper = session.getMapper(BlogMapper.class);
- List<Integer> ids = new ArrayList<Integer>();
- ids.add(1);
- ids.add(3);
- ids.add(6);
- List<Blog> blogs = blogMapper.dynamicForeachTest(ids);
- for (Blog blog : blogs)
- System.out.println(blog);
- session.close();
- }
- <select id="dynamicForeach2Test" resultType="Blog">
- select * from t_blog where id in
- <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
- #{item}
- </foreach>
- </select>
- public List<Blog> dynamicForeach2Test(int[] ids);
- @Test
- public void dynamicForeach2Test() {
- SqlSession session = Util.getSqlSessionFactory().openSession();
- BlogMapper blogMapper = session.getMapper(BlogMapper.class);
- int[] ids = new int[] {1,3,6,9};
- List<Blog> blogs = blogMapper.dynamicForeach2Test(ids);
- for (Blog blog : blogs)
- System.out.println(blog);
- session.close();
- }
- <select id="dynamicForeach3Test" resultType="Blog">
- select * from t_blog where title like "%"#{title}"%" and id in
- <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
- #{item}
- </foreach>
- </select>
- public List<Blog> dynamicForeach3Test(Map<String, Object> params);
- @Test
- public void dynamicForeach3Test() {
- SqlSession session = Util.getSqlSessionFactory().openSession();
- BlogMapper blogMapper = session.getMapper(BlogMapper.class);
- final List<Integer> ids = new ArrayList<Integer>();
- ids.add(1);
- ids.add(2);
- ids.add(3);
- ids.add(6);
- ids.add(7);
- ids.add(9);
- Map<String, Object> params = new HashMap<String, Object>();
- params.put("ids", ids);
- params.put("title", "中国");
- List<Blog> blogs = blogMapper.dynamicForeach3Test(params);
- for (Blog blog : blogs)
- System.out.println(blog);
- session.close();
- }
工作中用到,写三种用法吧,第四种为大小写匹配查询
1. sql中字符串拼接
2. 使用 ${...} 代替 #{...}
3. 程序中拼接
4. 大小写匹配查询