【09】动态sql

通过mybatis提供的各种标签方法实现动态拼接sql。

1. if 、2. where

• Mapper.xml

    <!-- sql 片段 -->
    <sql id="usercolumn">
    		id ,username , sex , birthday , address
    </sql>
    
    <select id="findUserList" parameterType="user" resultType="user">
    	select <include refid="usercolumn" /> from `user`
    	<where>
    		<if test="username!=null and username != ''">
    			and username like '%${username}%'
    		</if>
    		<if test="sex != null and sex != ''">
    			and sex = #{sex}
    		</if>
    		<if test="address != null and address != ''">
    			and address like '%${address}%'
    		</if>
    	</where>
    </select>
  

where 可以自动处理第一个and。

• Mapper接口

     /**
  	 * 根据传入的user对象的属性，查询匹配的用户列表
  	 * @param user
  	 * @return
  	 */
  	public List<User> findUserList(User user);
  

• 测试：

  	@Test
  	public void method01() throws IOException {
  
  		SqlSession sqlSession = sqlSessionFactory.openSession();
  		User user = new User();
  		user.setUsername("张");
  		user.setBirthday(new Date());
  		user.setSex("男");
  		user.setAddress("文化东路");
  		
  		UserMapper userDao = sqlSession.getMapper(UserMapper.class);
  		List<User> userlist = userDao.findUserList(user);
  		System.out.println(userlist);
  		sqlSession.close();
  	}
  

3. set、if

• Mapper.xml

  	<update id="updateUser2" parameterType="user">
  		UPDATE `USER`
  		<set>
  			<if test="username != null">username = #{username} ,</if>
  			<if test="birthday != null">birthday = #{birthday} ,</if>
  			<if test="sex != null">sex = #{sex} ,</if>
  			<if test="address != null">address = #{address} ,</if>
  		</set>
  		WHERE ID = #{id}
  	</update>
  

• Mapper接口

    /**
    	 * 更新用户
    	 * @param user
    	 */
    	public int updateUser2(User user);
  

• 测试：

  	@Test
  	public void method02() throws IOException {
  		SqlSession sqlSession = sqlSessionFactory.openSession();
  		
  		User user = new User();
  		user.setId(14);
  		user.setUsername("张小三");
  		
  		user.setBirthday(java.sql.Date.valueOf("1995-6-9"));
  		user.setSex("男");
  		user.setAddress("文化东路55");
  		
  		UserMapper userDao = sqlSession.getMapper(UserMapper.class);
  		int i = userDao.updateUser2(user);
  		System.out.println(i);
  		
  		sqlSession.commit();//提交事务
  		sqlSession.close();
  	}
  

4. foreach

向sql传递数组或List，mybatis使用foreach解析，如下：

4.1 通过pojo传递list

• 需求
  传入多个id查询用户信息，用下边两个sql实现：

    SELECT * FROM USERS WHERE username LIKE '%张%' AND (id =10 OR id =89 OR id=16)
    SELECT * FROM USERS WHERE username LIKE '%张%'  id IN (10,89,16)
  

• 在pojo中定义list属性ids存储多个用户id，并添加getter/setter方法
  

• mapper.xml

      <if test="ids!=null and ids.size>0">
      	<foreach collection="ids" open=" and id in(" close=")" item="id" separator="," >
      	   		#{id}
      	</foreach>
      </if>
  

• 测试代码：

  	List<Integer> ids = new ArrayList<Integer>();
  	ids.add(1);//查询id为1的用户
  	ids.add(10); //查询id为10的用户
  	queryVo.setIds(ids);
  	List<User> list = userMapper.findUserList(queryVo);
  

4.2 传递单个List

传递List类型在编写mapper.xml没有区别，唯一不同的是只有一个List参数时它的参数名为list。

• Mapper.xml

      <select id="selectUserByList" parameterType="java.util.List" resultType="user">
    		select * from user 
    		<where>
    		<!-- 传递List，List中是pojo -->
    		<if test="list!=null and list.size() >0 ">
    		<foreach collection="list" item="item" open="and id in("separator=","close=")">
    		    #{item.id} 
    		</foreach>
    		</if>
    		</where>
    	</select>
  

• Mapper接口

      public List<User> selectUserByList(List userlist) throws Exception;
  

• 测试：

      public void testselectUserByList()throws Exception{
    		//获取session
    		SqlSession session = sqlSessionFactory.openSession();
    		//获限mapper接口实例
    		UserMapper userMapper = session.getMapper(UserMapper.class);
    		//构造查询条件List
    		List<User> userlist = new ArrayList<User>();
    		User user = new User();
    		user.setId(1);
    		userlist.add(user);
    		user = new User();
    		user.setId(2);
    		userlist.add(user); 
    		//传递userlist列表查询用户列表
    		List<User>list = userMapper.selectUserByList(userlist);
    		//关闭session
    		session.close();
    	}
  

4.3 传递单个数组（数组中是pojo）：

• Mapper.xml

    	<!-- 传递数组综合查询用户信息 -->
    	<select id="selectUserByArray" parameterType="Object[]" resultType="user">
    		select * from user 
    		<where>
    		<!-- 传递数组 -->
    		<if test="array!=null and array.length != 0">
    		<foreach collection="array" index="index" item="item" open="and id in("separator=","close=")">
    		    #{item.id} 
    		</foreach>
    		</if>
    		</where>
    	</select>
  

sql只接收一个数组参数，这时sql解析参数的名称mybatis固定为array，如果数组是通过一个pojo传递到sql则参数的名称为pojo中的属性名。
index：为数组的下标。
item：为数组每个元素的名称，名称随意定义
open：循环开始
close：循环结束
separator：中间分隔输出

• Mapper接口：

      public List<User> selectUserByArray(Object[] userlist) throws Exception;
  

• 测试：

     public void testselectUserByArray()throws Exception{
    	  //获取session
    	  SqlSession session = sqlSessionFactory.openSession();
    	  //获限mapper接口实例
    	  UserMapper userMapper = session.getMapper(UserMapper.class);
    	  //构造查询条件List
    	  Object[] userlist = new Object[2];
    	  User user = new User();
    	  user.setId(1);
    	  userlist[0]=user;
    	  user = new User();
    	  user.setId(2);
    	  userlist[1]=user;
    	  //传递user对象查询用户列表
    	  List<User>list = userMapper.selectUserByArray(userlist);
    	  //关闭session
    	  session.close();
    }
  

4.4 传递单个数组（数组中是字符串类型）：

• Mapper.xml

    <!-- 传递数组综合查询用户信息 -->
    <select id="selectUserByArray" parameterType="Object[]" resultType="user">
    	select * from user 
    	<where>
    		<!-- 传递数组 -->
    		<if test="array!=null">
    			<foreach collection="array"index="index"item="item"open="and id in("separator=","close=")">
    	    		#{item} 
    			</foreach>
    		</if>
    	</where>
    </select>
  

如果数组中是简单类型则写为#{item}，不用再通过ognl获取对象属性值了。

• Mapper接口：

    public List<User> selectUserByArray(Object[] userlist) throws Exception;
  

• 测试：

    public void testselectUserByArray()throws Exception{
    	//获取session
    	SqlSession session = sqlSessionFactory.openSession();
    	//获限mapper接口实例
    	UserMapper userMapper = session.getMapper(UserMapper.class);
    	//构造查询条件List
    	Object[] userlist = new Object[2];
    	userlist[0]=”1”;
    	userlist[1]=”2”;
    	//传递user对象查询用户列表
    	List<User>list = userMapper.selectUserByArray(userlist);
    	//关闭session
    	session.close();
    }
  

5. choose、when、otherwise

• 不想选择所有的查询条件，指向选择其中的一个，查询条件有一个满足即可，可以使用choose标签。
• Mapper.xml

	<select id="findUserList2" parameterType="user"
		resultType="user">
		select
		<include refid="usercolumn" />
		from `user`
		<where>
			<choose>
				<when test="username!=null and username != ''">
					and username like '%${username}%'
				</when>
				<when test="sex != null and sex != ''">
					and sex = #{sex}
				</when>
				<when test="address != null and address != ''">
					and address like '%${address}%'
				</when>
				<otherwise>
					and 1 = 2
				</otherwise>
			</choose>
		</where>
	</select>

6. trim语句

• trim标记是一个格式化标记，可以完成set或者where标记的功能

• trim改写if+where

    <select id="findUserList" parameterType="user" resultType="user">
    		select <include refid="usercolumn" /> from `user`
    		<trim prefix = "where" prefixOverrides= "and | or" >
    			<if test="username!=null and username != ''">
    				and username like '%${username}%'
    			</if>
    			<if test="sex != null and sex != ''">
    				and sex = #{sex}
    			</if>
    			<if test="address != null and address != ''">
    				and address like '%${address}%'
    			</if>
    		</trim>
    </select>
  

prifix：前缀
prefixOverrides ： 去掉第一个 and 或者是 or

• trim改写if+where

    <update id="updateUser2" parameterType="user">
    	UPDATE `USER`
    	<trim prefix = "set" prefixOverrides= "," >
    		<if test="username != null">username = #{username} ,</if>
    		<if test="birthday != null">birthday = #{birthday} ,</if>
    		<if test="sex != null">sex = #{sex} ,</if>
    		<if test="address != null">address = #{address} ,</if>
    	</trim >
    	WHERE ID = #{id}
    </update>
  

prifix：前缀
prefixOverrides ： 去掉第一个 and 或者是 or

7. Sql片段

Sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的，如下：

	<!-- 传递pojo综合查询用户信息 -->
	<select id="findUserList" parameterType="user" resultType="user">
		select * from user 
		<where>
			<if test="id!=null and id!=''">
				and id=#{id}
			</if>
			<if test="username!=null and username!=''">
				and username like '%${username}%'
			</if>
		</where>
	</select>

 将where条件抽取出来：

	<sql id="query_user_where">
		<if test="id!=null and id!=''">
			and id=#{id}
		</if>
		<if test="username!=null and username!=''">
			and username like '%${username}%'
		</if>
	</sql>

 使用include引用：

	<select id="findUserList" parameterType="user" resultType="user">
		select * from user 
		<where>
			<include refid="query_user_where"/>
		</where>
	</select>

注意：如果引用其它mapper.xml的sql片段，则在引用时需要加上namespace，如下：
<include refid="namespace.sql片段”/>