数据库后台的SQL语句都是死板的,我们在项目中可以通过一些拼接语句实现一些动态的判断(如:A条件不为空就根据A条件查找,否则根据B条件查找)。
学习这个知识点最有意思的就是看它拼接的sql语句,很多问题都能在里面找到。
mybatis的基本动态sql:
①if
②choose
③where
④set
⑤trim
⑥foreach
实体类无参方法赋空值:
①动态sql-if(查询)
xml文件:
<select id="BlogTitle" parameterType="string" resultType="Blog">
SELECT * FROM blog
WHERE state='ACTIVE'
<if test="value!=null and value!=''">
AND title LIKE #{value}
</if>
</select>
接口:
List<Blog> BlogTitle(String title);
测试类:
@Test
public void BlogTitle() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
//两种情况,有值和空值
//List<Blog> bloglist = blogMapper.BlogTitle("%a%");
List<Blog> bloglist = blogMapper.BlogTitle(null);
System.out.println(bloglist);
session.close();
}
分析:如果title输出的不是空值,则拼接后面的语句,否则执行前面的语句。
两种情况的后台打印的sql语句:
②动态sql-choose(查询)
xml文件:
<select id="BlogStyle" parameterType="Blog" resultType="Blog">
SELECT * FROM blog
WHERE state='ACTIVE'
<choose>
<when test="title!=null and title!=''">
and lower(title) like lower(#{title})
</when>
<when test="style!=null and style!=''">
and style =#{style}
</when>
<otherwise>
and featured=true
</otherwise>
</choose>
</select>
接口:
List<Blog> BlogStyle(Blog blog);
测试类:
@Test
public void BlogStyle() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
//满足前面的条件就不会执行后面的when
Blog blog=new Blog();
blog.setTitle("%a%");
//blog.setTitle(null);
blog.setStyle("red");
//blog.setStyle(null);
List<Blog> bloglist = blogMapper.BlogStyle(blog);
session.close();
System.out.println(bloglist);
}
分析:when条件有且最多满足一个,otherwise里面的是必执行的拼接语句。
执行两个when条件都有值的情况下的后台打印的sql语句:
③动态sql-where(查询)
xml文件:
<select id="Blogwhere" parameterType="Blog" resultType="Blog">
select * from blog
<where>
<if test="state!=null and state!=''">
state=#{state}
</if>
<if test="title!=null and title!=''">
and lower(title) like lower(#{title})
</if>
<if test="featured!=null">
and featured=#{featured}
</if>
</where>
</select>
接口:
List<Blog> Blogwhere(Blog blog);
测试类:
@Test
public void Blogwhere() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Blog blog=new Blog();
blog.setState("ACTIVE");
//blog.setState(null);
blog.setTitle("%a%");
//blog.setTitle(null);
blog.setFeatured(true);
List<Blog> bloglist = blogMapper.Blogwhere(blog);
session.close();
System.out.println(bloglist);
}
分析:如果存在多个判断值,就要加个where,其他和if一样。
全部条件都满足的后台打印的sql语句:
④动态sql-set(修改更新)
xml文件:
<update id="Blogset" parameterType="Blog">
UPDATE `blog`
<set>
<if test="title!=null">`title` = #{title},</if>
<if test="authorId!=null">`authorId` = #{authorId},</if>
<if test="state!=null">`state` = #{state},</if>
<if test="featured!=null">`featured` = #{featured},</if>
<if test="style!=null">`style` = #{style}</if>
</set>
WHERE `id` = #{id}
</update>
接口:
//根据id修改
int Blogset(Blog blog);
测试类:
@Test
public void Blogset() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Blog blog=new Blog();
blog.setId(35);
blog.setTitle("台湾");
blog.setAuthorId(10);
// blog.setState("NO ACTIVE");
// blog.setFeatured(false);
// blog.setStyle("blue");
int upblog = blogMapper.Blogset(blog);
session.commit();
session.close();
System.out.println(blog);
System.out.println("修改了"+upblog+"条记录");
}
分析:更新下的set,相当于查询下的where,输入几个就修改几个。
样例后台打印的sql语句:
⑤动态sql-trim
分析:trim的功能就是标记where和set的功能,就是换个形式表现,prefixOverrides=“and | or"过滤掉多余的and或or,
suffixOverrides=”,"过滤掉多余的,号。
需要注意的是:and | or 的是 | 不是\。
xml文件:
<!--trim -->
<select id="Blogwheretrim" parameterType="Blog" resultType="Blog">
select * from blog
<trim prefix="where" prefixOverrides="and | or">
<if test="state!=null and state!=''">
state=#{state}
</if>
<if test="title!=null and title!=''">
and lower(title) like lower(#{title})
</if>
<if test="featured!=null">
and featured=#{featured}
</if>
</trim>
</select>
<update id="Blogsettrim" parameterType="Blog">
UPDATE `blog`
<trim prefix="set" suffixOverrides=",">
<if test="title!=null">`title` = #{title},</if>
<if test="authorId!=null">,`authorId` = #{authorId},</if>
<if test="state!=null">`state` = #{state},</if>
<if test="featured!=null">`featured` = #{featured},</if>
<if test="style!=null">`style` = #{style}</if>
</trim>
WHERE `id` = #{id}
</update>
接口:
List<Blog> Blogwheretrim(Blog blog);
int Blogsettrim(Blog blog);
测试类:
@Test
public void Blogwheretrim() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Blog blog=new Blog();
blog.setState("ACTIVE");
//blog.setState(null);
blog.setTitle("%a%");
//blog.setTitle(null);
blog.setFeatured(true);
List<Blog> bloglist = blogMapper.Blogwheretrim(blog);
session.close();
System.out.println(bloglist);
}
@Test
public void Blogsettrim() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Blog blog=new Blog();
blog.setId(35);
blog.setTitle("台湾");
blog.setAuthorId(10);
// blog.setState("NO ACTIVE");
// blog.setFeatured(false);
// blog.setStyle("blue");
int upblog = blogMapper.Blogsettrim(blog);
session.commit();
session.close();
System.out.println(blog);
System.out.println("修改了"+upblog+"条记录");
}
⑥动态sql-foreach(批量删除)
xml文件:
<delete id="delectBloglist" parameterType="list">
DELETE FROM `blog` where id in
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
接口:
//根据id删除
int delectBloglist(List<Integer> id);
测试类:
@Test
public void delectBloglist() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
//输入需要删除数据的id
List<Integer> id=Arrays.asList(35,37);
int deblog = blogMapper.delectBloglist(id);
session.commit();
session.close();
System.out.println("删除了"+deblog+"条记录");
}
分析:
foreach collection=“list” item=“item” open="(" close=")" separator=","
是固定写法,至于为什么要这样,要去问mybatis的开发者了。
后台打印的sql语句: