MyBatis-03 动态sql

一、实现动态sql的元素

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

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

注: 以下代码中主要显示sql映射文件中语句,其他环境配置等代码见初始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属性中的条件 符合条件进行追加-->
		<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做修改，其他不做更改。

	<!--根据角色id和用户姓名模糊查询用户列表 -->
	<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());
		}
	}

执行结果：
MyBatis-03 动态sql

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">
		<!--此处#{gender}与map集合中的key对象，value为值；#{roleMap同理}-->
		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());
		}
	}

运行结果：
MyBatis-03 动态sql

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

	<!-- 根据用户名称、角色id、用户编码、创建日期查询用户列表 -->
	<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());
		}
	}

运行结果：
MyBatis-03 动态sql

五、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

	<typeAliases>
		<!--第一种方式：逐个配置->
		<!-- <typeAlias type="cn.smbms.pojo.User" alias="user"/> -->
		<!--将pojo包下所有javaBean统一配置->
		<package name="cn.smbms.pojo"/>
	</typeAliases>

Demo结构如图
MyBatis-03 动态sql

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

	<mappers>
		<!--  第一种方式：使用类资源路径获取资源-->
		<mapper resource="cn/smbms/dao/user/UserMapper.xml"></mapper>
		<mapper resource="cn/smbms/dao/provider/ProviderMapper.xml"></mapper>
		
		<!-- 第二种方式：使用url获取资源 -->
		<!-- <mapper url="file:///D:/UserMapper.xml"></mapper> -->
		
		<!--第三方式  映射接口  -->
		<!-- <mapper class="cn.smbms.dao.user.UserMapper"></mapper> -->
		 
		<!-- 第4种方式 配置指定包下的文件 --> 
		 <package name="cn.smbms.dao"/>
	</mappers>

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

	<settings>
		<setting name="logImpl" value="LOG4J"/>
	</settings>

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

<configuration>
    <!--设置启用数据库字段下划线映射到java对象的驼峰式命名属性，默认为false-->  
    <settings>
      <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

</configuration>

一、实现动态sql的元素

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

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

四、使用foreach完成复杂查询

五、MyBatis入参小结

相关推荐