Mybatis系列八：动态SQL

旧城等待， 2022-02-16 01:15 310阅读 0赞

创建EmployeeMapperDynamicSQL接口

public interface EmployeeMapperDynamicSQL {
    		
    		public List<Employee> getEmpsTestInnerParameter(Employee employee);
    		
    		//携带了哪个字段查询条件就带上这个字段的值
    		public List<Employee> getEmpsByConditionIf(Employee employee);
    		
    		public List<Employee> getEmpsByConditionTrim(Employee employee);
    		
    		public List<Employee> getEmpsByConditionChoose(Employee employee);
    		
    		public void updateEmp(Employee employee);
    		
    		//查询员工id'在给定集合中的
    		public List<Employee> getEmpsByConditionForeach(@Param("ids") List<Integer> ids);
    		
    		public void addEmps(@Param("emps") List<Employee> emps);
    	
    	}

一、if和where标签  
![在这里插入图片描述][20190421115641499.png]

<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
    	 	select * from tbl_employee
    	 	<where>
    		 	
    		 	<if test="id!=null">
    		 		id=#{id}
    		 	</if>
    		 	<if test="lastName!=null && lastName!=""">
    		 		and last_name like #{lastName}
    		 	</if>
    		 	<if test="email!=null and email.trim()!=''">
    		 		and email=#{email}
    		 	</if> 
    		 	
    		 	<if test="gender==0 or gender==1">
    		 	 	and gender=#{gender}
    		 	</if>
    	 	</where>
    	 </select>

注意：查询的时候如果某些条件没带可能sql拼装会有问题：  
 1、给where后面加上1=1，以后的条件都and xxx.  
 2、mybatis使用where标签来将所有的查询条件包括在内。mybatis就会将where标签中拼装的sql，多出来的and或者or去掉。但是where只会去掉第一个多出来的and或者or。  
二、trim标签

<select id="getEmpsByConditionTrim" resultType="com.atguigu.mybatis.bean.Employee">
    	 	select * from tbl_employee
    	 	
    	 	
    	 	<trim prefix="where" suffixOverrides="and">
    	 		<if test="id!=null">
    		 		id=#{id} and
    		 	</if>
    		 	<if test="lastName!=null && lastName!=""">
    		 		last_name like #{lastName} and
    		 	</if>
    		 	<if test="email!=null and email.trim()!=""">
    		 		email=#{email} and
    		 	</if> 
    		 	
    		 	<if test="gender==0 or gender==1">
    		 	 	gender=#{gender}
    		 	</if>
    		 </trim>
    	 </select>

trim：更新拼串
    	update tbl_employee 
    	<trim prefix="set" suffixOverrides=",">
    		<if test="lastName!=null">
    			last_name=#{lastName},
    		</if>
    		<if test="email!=null">
    			email=#{email},
    		</if>
    		<if test="gender!=null">
    			gender=#{gender}
    		</if>
    	</trim>
    	where id=#{id}

三、choose标签

<select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">
    	 	select * from tbl_employee 
    	 	<where>
    	 		
    	 		<choose>
    	 			<when test="id!=null">
    	 				id=#{id}
    	 			</when>
    	 			<when test="lastName!=null">
    	 				last_name like #{lastName}
    	 			</when>
    	 			<when test="email!=null">
    	 				email = #{email}
    	 			</when>
    	 			<otherwise>
    	 				gender = 0
    	 			</otherwise>
    	 		</choose>
    	 	</where>
    	 </select>

四、set标签

<update id="updateEmp">
    	 	
    	 	update tbl_employee 
    		<set>
    			<if test="lastName!=null">
    				last_name=#{lastName},
    			</if>
    			<if test="email!=null">
    				email=#{email},
    			</if>
    			<if test="gender!=null">
    				gender=#{gender}
    			</if>
    		</set>
    		where id=#{id}

五、foreach标签

<select id="getEmpsByConditionForeach" resultType="com.atguigu.mybatis.bean.Employee">
    	 	select * from tbl_employee
    	 	
    	 	<foreach collection="ids" item="item_id" separator=","
    	 		open="where id in(" close=")">
    	 		#{item_id}
    	 	</foreach>
    	 </select>

测试：

@Test
    	public void test() throws IOException {
    		String resource = "mybatis-config.xml";
    		InputStream inputStream = Resources.getResourceAsStream(resource);
    		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    
    		SqlSession openSession = sqlSessionFactory.openSession();
    		try {
    			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
    			//测试if
    			Employee employee = new Employee(1, "jerry1", null, null);
    			List<Employee> emps = mapper.getEmpsByConditionIf(employee );
    			for (Employee emp : emps) {
    				System.out.println(emp);
    			}
    
    			/**查询的时候如果某些条件没带可能sql拼装会有问题:
    			  *1、给where后面加上1=1，以后的条件都and xxx.
    			  *2、mybatis使用where标签来将所有的查询条件包括在内。mybatis就会将where标签中拼装的sql，多出来的and或者or去掉。where只会去掉第一个多出来的and或者or。
    			  */
    
    			//测试trim
    			List<Employee> emps2 = mapper.getEmpsByConditionTrim(employee);
    			for (Employee emp : emps2) {
    				System.out.println(emp);
    			}
    
    
    			//测试choose
    			Employee employee1 = new Employee(1, "jerry1", null, null);
    			List<Employee> list = mapper.getEmpsByConditionChoose(employee1);
    			for (Employee emp : list) {
    				System.out.println(emp);
    			}
    
    			//测试set标签
    			Employee employee2 = new Employee(1, "admin", null, null);
    			mapper.updateEmp(employee2);
    			openSession.commit();
    
    			//测试foreach
    			List<Employee> list1 = mapper.getEmpsByConditionForeach(Arrays.asList(1,2));
    			for (Employee emp : list1) {
    				System.out.println(emp);
    			}
    
    		} finally {
    			openSession.close();
    		}
    
    	}

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhpcWlhbmcxMjE3_size_16_color_FFFFFF_t_70]  
foreach标签批量保存：  
1、方式一：

<insert id="addEmps">
    	 	insert into tbl_employee(
    	 		<include refid="insertColumn"></include>
    	 	) 
    		values
    		<foreach collection="emps" item="emp" separator=",">
    			(#{emp.lastName},#{emp.email},#{emp.gender})
    		</foreach>
    	</insert>
    
    	
    	<sql id="insertColumn">
    	  	last_name,email,gender
    	</sql>

测试：

@Test
    	public void testBatchSave() throws IOException{
    		String resource = "mybatis-config.xml";
    		InputStream inputStream = Resources.getResourceAsStream(resource);
    		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    
    		SqlSession openSession = sqlSessionFactory.openSession();
    		try{
    			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
    			List<Employee> emps = new ArrayList<Employee>();
    			emps.add(new Employee(null, "smith0x1", "smith0x1@atguigu.com", "1"));
    			emps.add(new Employee(null, "allen0x1", "allen0x1@atguigu.com", "0"));
    			mapper.addEmps(emps);
    			openSession.commit();
    		}finally{
    			openSession.close();
    		}
    	}

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhpcWlhbmcxMjE3_size_16_color_FFFFFF_t_70 1]  
2、方式二：需要配置数据库连接属性allowMultiQueries=true  
![在这里插入图片描述][20190421124231118.png]

<insert id="addEmps">
     	<foreach collection="emps" item="emp" separator=";">
     		insert into tbl_employee(last_name,email,gender)
     		values(#{emp.lastName},#{emp.email},#{emp.gender})
     	</foreach>
      </insert>

测试：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhpcWlhbmcxMjE3_size_16_color_FFFFFF_t_70 2]  
六、两个内置参数

<select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
    		
    		<bind name="_lastName" value="'%'+lastName+'%'"/>
    		select * from tbl_employee
    		<if test="_parameter!=null">
    			where last_name like #{_lastName}
    		</if>
    	</select>

测试：

@Test
    	public void testInnerParam() throws IOException{
    		String resource = "mybatis-config.xml";
    		InputStream inputStream = Resources.getResourceAsStream(resource);
    		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    
    		SqlSession openSession = sqlSessionFactory.openSession();
    		try{
    			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
    			Employee employee2 = new Employee();
    			employee2.setLastName("e");
    			List<Employee> list = mapper.getEmpsTestInnerParameter(employee2);
    			for (Employee employee : list) {
    				System.out.println(employee);
    			}
    		}finally{
    			openSession.close();
    		}
    	}

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhpcWlhbmcxMjE3_size_16_color_FFFFFF_t_70 3]

[20190421115641499.png]: /images/20220216/58c20806275e49e3b0129fc816dd8407.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhpcWlhbmcxMjE3_size_16_color_FFFFFF_t_70]: /images/20220216/364f05a704344987b869697eb09f019f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhpcWlhbmcxMjE3_size_16_color_FFFFFF_t_70 1]: /images/20220216/52154d53f65b4ba99e0a2b04bf4559aa.png
[20190421124231118.png]: /images/20220216/ad1ef437db4044af9187fa75706a5a33.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhpcWlhbmcxMjE3_size_16_color_FFFFFF_t_70 2]: /images/20220216/a8be465c81a44b6ca030e03f1ab3b6f4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhpcWlhbmcxMjE3_size_16_color_FFFFFF_t_70 3]: /images/20220216/7b017f99827149ca8a48158191ef6ee0.png