MyBatis-03 动态sql

布满荆棘的人生 2022-03-27 08:50 297阅读 0赞

## 一、实现动态sql的元素 ##

*  if 利用if实现简单的条件选择
 *  choose 相当于Java中的switch语句，通常与when、otherwise搭配
 *  where 简化sql语句中的where条件判断
 *  set 解决动态更新语句
 *  trim 可以灵活的去除多余的关键字
 *  foreach 迭代一个集合，通常用于in条件

## 二、使用动态sql完成多条件查询 ##

**注:** 以下代码中主要显示sql映射文件中语句,其他环境配置等代码见[初始MyBatis-1][MyBatis-1]

**1、使用if+where实现多条件查询**

**UserMapper.java**

/**
    	 * 查询用户列表(对象入参)
    	 * @param user
    	 * @return
    	 * @throws Exception
    	 */
    	public List<User> getUserList(User user)throws Exception;

**UserMapper.xml**

<select id="getUserList" resultMap="userList" parameterType="user">
    	select * from smbms_user
    	<where>
    		
    		<if test="userRole!=null">
    			and u.userRole=#{userRole}
    		</if>
    		<if test="userName!=null and userName!=''">
    			and u.userName like CONCAT('%',#{userName},'%') 
    		</if>
    	</where>
    </select

**UserMapperTest.java**

/**
    	 * 查询用户列表
    	 */
    	@Test
    	public void testGetUserList() {
    		SqlSession sqlSession = null;
    		List<User> ulist = null;
    		try {
    			// 3.创建sqlSession
    			sqlSession = MyBatisUtil.createSqlSession();
    			// 对象入参
    			User user = new User();
    			user.setUserName("赵");
    			user.setUserRole(3);
    			ulist = sqlSession.getMapper(UserMapper.class).getUserList(user);
    			for (User u : ulist) {
    				logger.info("用户编码:"+u.getUserCode()+",用户姓名:"+u.getUserName()+",	用户角色:"+u.getUserRoleName()+",用户地址:"+u.getAddress());
    			}
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			MyBatisUtil.closeSqlSession(sqlSession);
    		}
    
    	}

**2、使用if+trim实现多条件查询**  
**注：** 只对UserMapper.xml做修改，其他不做更改。

<select id="getUserList" resultType="user" parameterType="user">
    		select * from smbms_user
    		<trim prefix="where" prefixOverrides="and | or">
    			<if test="userName!=null and userName!=''">
    				and userName like CONCAT('%',#{userName},'%')
    			</if>
    			<if test="userRole!=null">
    				and userRole=#{userRole}
    			</if>
    		</trim>
    	</select>

**讲解：**

*  **trim 元素作用：** trim元素会自动识别其标签内是否有返回值，若有返回值，会在自己包含的内容前加上某些前缀，也可在其后加上某些后缀；也可把包含的内容的首部某些内容覆盖(即忽略)，或者把尾部内容忽略。
 *  **trim 元素属性：**
    
     *  prefix：前缀，通过自动识别是否有返回值后，在trim包含的内容前加上前缀。如where
     *  suffix：后缀，作用是在trim包含的内容上加上后缀
     *  prefixOverrides：对于trim包含内容的首部进行执行内容的忽略(如 and | or)
     *  stuffixOverrides：对于trim包含内容的尾部进行执行内容的忽略(如 and | or)

## 三、使用动态sql实现更新操作 ##

**1、使用if+set改造更新操作**

**UserMapper.java**

/**
    	 * 修改用户
    	 * @param user
    	 * @return
    	 */
    	public int modify(User user)throws Exception;

**UserMapper.xml**

<update id="modify" parameterType="user">
    	UPDATE smbms_user 
    	<set>
    		<if test="userCode!=null">userCode=#{userCode},</if>
    		<if test="userName!=null">userName=#{userName},</if>
    		<if test="userPassword!=null">userPassword=#{userPassword},</if>	
    		
    	</set>
    	where id=#{id}
    </update>

**2、使用if+trim改造修改操作**

**注：** 只对UserMapper.xml做修改,用法与上述demo相同，此处不做演示

## 四、使用foreach完成复杂查询 ##

**1、foreach基本属性**

*  item：表示集合中每一个元素进行迭代时的别名
 *  index：指定一个名称，用于表示在迭代过程中，每次迭代到的位置(可有可无)
 *  open：表示该语句是以什么开始
 *  separator：表示在每次进行迭代时以什么符号作为分隔符
 *  close：表示该语句以什么结束
 *  collection：该属性必须指定，
    
     *  若入参为单参数且参数类型是一个list时，collection=“list”
     *  若入参为单参数且参数类型是一个数组时，collection=“array”
     *  若入参为多参数，就需要封装为一个Map处理,collection就为一个map

**2、MyBatis入参为数组类型的foreach迭代**  
**Demo演示**

**需求：** 根据传入的角色列表获取角色列表  
**UserMapper.java**

/**
    	 * 根据用户角色列表，获取该角色列表下用户列表信息foreach_array
    	 * @param roleIds
    	 * @return
    	 */
    	public List<User> getUserByRoleId_foreach_array(List<Integer> roleIds)throws Exception;

**UserMapper.xml**

<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
    		select * from smbms_user where userRole in
    		<foreach collection="array" item="roleIds" open="(" separator="," close=")">
    			#{roleIds}
    		</foreach>
    	</select>
    	
    	<resultMap type="User" id="userMapByRole">
    		<id property="id" column="id"/>
    		<result property="userCode" column="userCode"/>
    		<result property="userName" column="userName"/>
    	</resultMap>

**UserMapperTest.java** 测试类

/**
    	 * 根据角色列表获取用户列表
    	 */
    	@Test
    	public void testGetUserByRoleId_foreach_array(){
    		SqlSession sqlSession=null;
    		List<User> list=new ArrayList<User>();
    		Integer[] roleIds={2,3};
    		try {
    			sqlSession=MyBatisUtil.createSqlSession();
    			//单参数 类型为数组
    			list=sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleIds);
    		} catch (Exception e) {
    			e.printStackTrace();
    		}finally
    		{
    			MyBatisUtil.closeSqlSession(sqlSession);
    		}
    		for (User u : list) {
    			logger.info("用户编码:"+u.getUserCode()+",用户姓名:"+u.getUserName()+",用户角色:"+u.getUserRole());
    		}
    	}

**执行结果：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70]

**3、MyBatis入参为List类型的foreach迭代**

**UserMapper.java**

/**
    	 * 根据用户角色列表，获取该角色列表下用户列表信息foreach_array
    	 * @param roleIds
    	 * @return
    	 */
    	public List<User> getUserByRoleId_foreach_list(List<Integer> roleIds)throws Exception;

**UserMapper.xml**

<select id="getUserByRoleId_foreach_list" resultMap="userMapByRole">
    		select * from smbms_user where userRole in
    		<foreach collection="list" item="roleList" open="(" separator="," close=")">
    			#{roleList}
    		</foreach>
    	</select>
    		<resultMap type="User" id="userMapByRole">
    		<id property="id" column="id"/>
    		<result property="userCode" column="userCode"/>
    		<result property="userName" column="userName"/>
    	</resultMap>

**UserMapperTest.java**

/**
    	 * 根据角色列表获取用户列表
    	 */
    	@Test
    	public void testGetUserByRoleId_foreach_array(){
    		SqlSession sqlSession=null;
    		List<User> list=new ArrayList<User>();
    		//将多个角色id封装为list集合
    		List<Integer> roleList=new ArrayList<Integer>();
    		roleList.add(2);
    		roleList.add(3);
    		try {
    			sqlSession=MyBatisUtil.createSqlSession();		
    			//单参数 list入参
    			list=sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_list(roleList);
    		
    		} catch (Exception e) {
    			e.printStackTrace();
    		}finally
    		{
    			MyBatisUtil.closeSqlSession(sqlSession);
    		}
    		for (User u : list) {
    			logger.info("用户编码:"+u.getUserCode()+",用户姓名:"+u.getUserName()+",用户角色:"+u.getUserRole());
    		}
    	}

**4、MyBatis入参为Map类型的foreach迭代**  
**Demo演示**  
**需求：** 获取该角色列表下指定性别的用户列表信息  
**分析：** 多条件查，条件为rolesId 角色列表与gender 性别  
**编码：**  
**UserMapper.java**

/**
    	 * 根据用户角色列表和性别(多参数),获取该角色列表下指定性别的用户列表信息
    	 * @param conditionMap
    	 * @return
    	 * @throws Exception
    	 */
    	public List<User> getUserByConditionMap_froeach_map(Map<String,Object> conditionMap)throws Exception;

**UserMapper.xml**

<select id="getUserByConditionMap_froeach_map" resultMap="userMapByRole">
    		
    		select * from smbms_user where gender=#{gender} and userRole in
    		<foreach collection="roleIds" item="roleMap"  open="(" separator="," close=")">
    			#{roleMap}
    		</foreach>
    	</select>
    	<resultMap type="User" id="userMapByRole">
    		<id property="id" column="id"/>
    		<result property="userCode" column="userCode"/>
    		<result property="userName" column="userName"/>
    	</resultMap>

**UserMapperTest.java**

/**
    	 *  根据用户角色列表和性别(多参数),获取该角色列表下指定性别的用户列表信息
    	 */
    	@Test
    	public void getUserByConditionMap_froeach_mapTest(){
    		SqlSession sqlSession=null;
    		List<User> ulist=new ArrayList<User>();		//存放查询出的用户列表	
    		Map<String,Object> conditionMap=new HashMap<String, Object>();	//存放角色列表和性别条件
    		List<Integer> roleList=new ArrayList<Integer>();	//存放查询条件角色列表
    		roleList.add(2);
    		roleList.add(3);
    		conditionMap.put("roleIds", roleList);
    		conditionMap.put("gender", 1);
    		try {
    			sqlSession=MyBatisUtil.createSqlSession();
    			ulist=sqlSession.getMapper(UserMapper.class).getUserByConditionMap_froeach_map(conditionMap);
    		} catch (Exception e) {
    			e.printStackTrace();
    		}finally
    		{
    			MyBatisUtil.closeSqlSession(sqlSession);
    		}
    		for (User u : ulist) {
    			logger.info("用户编码:"+u.getUserCode()+",用户姓名:"+u.getUserName());
    			logger.info("用户角色:"+u.getUserRole()+",用户性别:"+u.getGender());
    		}
    	}

**运行结果：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 1]

**5、choose(when、otherwise)**  
**基本元素：**

*  when元素：当其test属性条件满足的时候，就会输出when元素中的内容，当when中一旦有条件满足时，就会跳出choose，即所有的when和otherwise中，只有一个条件会输出。
 *  otherwise元素：当when中的所有条件都不满足时，就会自动输出otherwise元素中的内容

**需求：**  
根据用户名称、角色id、用户编码、创建日期查询用户列表，如果前三个条件都为空，则默认根据创建日期查询用户。

**UserMapper.java**

/**
    	 * 根据用户名称、角色id、用户编码、创建日期查询用户列表
    	 * @param userName	用户名称
    	 * @param roleId	角色id
    	 * @param userCode	用户编码
    	 * @param creationDate	创建日期
    	 * @return
    	 * @throws Exception
    	 */
    	public List<User> getUserList_choose(@Param("userName")String userName,
    					@Param("userRole")Integer roleId,@Param("userCode")String userCode,
    					@Param("creationDate")Date creationDate)throws Exception;

**UserMapper.xml**

<select id="getUserList_choose" resultType="User">
    		select * from smbms_user where 1=1
    		<choose>
    			
    			<when test="userName!=null and userName!=''">
    				and userName like CONCAT('%',#{userName},'%')
    			</when>
    			
    			<when test="userCode!=null and userCode!=''">
    				and userCode like CONCAT('%',#{userCode},'%')
    			</when>
    			
    			<when test="userRole!=null">
    				and userRole=#{userRole}
    			</when>
    			
    			<otherwise>
    				and YEAR(creationDate)=YEAR(#{creationDate})
    			</otherwise>
    			
    		</choose>
    	</select>

**UserMapperTest.java**

/**
    	 * 查询用户列表(choose)
    	 */
    	@Test
    	public void getUserList_chooseTest(){
    		SqlSession sqlSession=null;
    		List<User> ulist=new ArrayList<User>();		//存放查询出的用户列表	
    		try {
    			sqlSession=MyBatisUtil.createSqlSession();
    			Date date=new SimpleDateFormat("yyy-MM-dd").parse("2016-1-1");
    			ulist=sqlSession.getMapper(UserMapper.class).getUserList_choose("", null, "", date);
    		} catch (Exception e) {
    			e.printStackTrace();
    		}finally
    		{
    			MyBatisUtil.closeSqlSession(sqlSession);
    		}
    		for (User u : ulist) {
    			logger.info("-----------用户列表-----------");
    			logger.info("用户编码:"+u.getUserCode()+",用户姓名:"+u.getUserName());
    			logger.info("用户角色:"+u.getUserRole()+",用户性别:"+u.getGender());
    		}
    	}

**运行结果：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 2]

## 五、MyBatis入参小结 ##

1、MyBatis接收的参数类型：基本类型、对象、list、数组、map  
2、无论MyBatis的入参是哪种类型，MyBatis都会将参数放入一个Map中。

**对于单参入参的情况**

*  若入参为基本类型：变量名作为key，变量值为value，此时生成的map中只有一个元素
 *  若参数为对象：对象的属性名作为key，属性值为value
 *  若入参为list：默认"list"为key，该List即为value
 *  若入参为数组：默认"array"作为key，该数据即为value
 *  若入参为Map：键值不变

**六、MyBatis补充**

**1、为实体类起别名**  
**mybatis-config.xml**

**Demo结构如图**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 3]

**2、在核心配置文件为mapper做映射**

**3、log4j在config.xml文件中的配置方式**

**4、适用于项目与数据库中存在包含下划线的字段，故无法映射的情况(解决方式之一)**

[MyBatis-1]: https://blog.csdn.net/weixin_43094361/article/details/85799024
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70]: /images/20220327/233d8b577fb445b09457d3b6d38ccbca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 1]: /images/20220327/61fc431283414c2dbcf3bdc3a35dfbb2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 2]: /images/20220327/d19528b5b21f4675bc7082331a4c1cb9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 3]: /images/20220327/8887e341a88d430883305cea1ba1bee4.png