mybatis动态sql的基本使用

缺乏、安全感 2021-11-23 09:50 316阅读 0赞

**数据库后台的SQL语句都是死板的，我们在项目中可以通过一些拼接语句实现一些动态的判断（如：A条件不为空就根据A条件查找，否则根据B条件查找）。**

**学习这个知识点最有意思的就是看它拼接的sql语句，很多问题都能在里面找到。**

**mybatis的基本动态sql：**  
**①if  
②choose  
③where  
④set  
⑤trim  
⑥foreach**

**实体类无参方法赋空值：**  
![在这里插入图片描述][20190726125925430.png]

--------------------

## ①动态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语句：**  
![在这里插入图片描述][20190726130556251.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgzMDA5_size_16_color_FFFFFF_t_70]

--------------------

## ②动态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语句：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgzMDA5_size_16_color_FFFFFF_t_70 1]

--------------------

## ③动态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语句：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgzMDA5_size_16_color_FFFFFF_t_70 2]

--------------------

## ④动态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语句:**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgzMDA5_size_16_color_FFFFFF_t_70 3]

--------------------

## ⑤动态sql-trim ##

**分析：trim的功能就是标记where和set的功能，就是换个形式表现，prefixOverrides=“and | or"过滤掉多余的and或or,  
suffixOverrides=”,"过滤掉多余的，号。  
需要注意的是：and | or 的是 | 不是\\。**  
**xml文件：**

<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语句：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgzMDA5_size_16_color_FFFFFF_t_70 4]

[20190726125925430.png]: /images/20211122/2beedf3082c74dc39254172ac8105ffa.png
[20190726130556251.png]: /images/20211122/aed645007dfa4bb9b167e10511e6c09c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgzMDA5_size_16_color_FFFFFF_t_70]: /images/20211122/3d6abc03bd27450c9cc68a623ff0bf8c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgzMDA5_size_16_color_FFFFFF_t_70 1]: /images/20211122/0fdf5d9e88f34efba6afa0bccbe660a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgzMDA5_size_16_color_FFFFFF_t_70 2]: /images/20211122/5234638d025c4ba08fc029c34d6b830e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgzMDA5_size_16_color_FFFFFF_t_70 3]: /images/20211122/4159502fe1854fa7954edebb85418aa4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgzMDA5_size_16_color_FFFFFF_t_70 4]: /images/20211122/926ab476b0e54779a7a3f2f64cfe30f0.png