Mybatis总结-动态sql

mybatis的优势之一就是动态sql.

-.动态sql的主要元素有if,where,trim,set,choose(when,otherwise),foreach下面一一介绍：
数据库的脚本：

CREATE TABLE `smbms_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `userCode` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户编码',
  `userName` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户名称',
  `userPassword` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户密码',
  `gender` int(10) DEFAULT NULL COMMENT '性别（1:女、 2:男）',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `phone` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机',
  `address` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '地址',
  `userRole` int(10) DEFAULT NULL COMMENT '用户角色（取自角色表-角色id）',
  `createdBy` bigint(20) DEFAULT NULL COMMENT '创建者（userId）',
  `creationDate` datetime DEFAULT NULL COMMENT '创建时间',
  `modifyBy` bigint(20) DEFAULT NULL COMMENT '更新者（userId）',
  `modifyDate` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `smbms_user` */

insert  into `smbms_user`(`id`,`userCode`,`userName`,`userPassword`,`gender`,`birthday`,`phone`,`address`,`userRole`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`) 
values (1,'admin','系统管理员','1234567',1,'1983-10-10','13688889999','北京市海淀区成府路207号',1,1,'2013-03-21 16:52:07',NULL,NULL),
(2,'liming','李明','0000000',2,'1983-12-10','13688884457','北京市东城区前门东大街9号',2,1,'0000-00-00 00:00:00',NULL,NULL),
(5,'hanlubiao','韩路彪','0000000',2,'1984-06-05','18567542321','北京市朝阳区北辰中心12号',2,1,'2014-12-31 19:52:09',NULL,NULL),
(6,'zhanghua','张华','0000000',1,'1983-06-15','13544561111','北京市海淀区学院路61号',3,1,'2013-02-11 10:51:17',NULL,NULL)

实体类：

package cn.smbms.pojo;

import java.util.Date;
import java.util.List;

public class User {
	private Integer id; //id 
	private String userCode; //用户编码
	private String userName; //用户名称
	private String userPassword; //用户密码
	private Integer gender;  //性别
	private Date birthday;  //出生日期
	private String phone;   //电话
	private String address; //地址
	private Integer userRole;    //用户角色ID
	private Integer createdBy;   //创建者
	private Date creationDate; //创建时间
	private Integer modifyBy;     //更新者
	private Date modifyDate;   //更新时间
	
	private Integer age;//年龄
	private String userRoleName; //用户角色名称
	
	public Integer getAge() {
		/*long time = System.currentTimeMillis()-birthday.getTime();
		Integer age = Long.valueOf(time/365/24/60/60/1000).IntegerValue();*/
		Date date = new Date();
		Integer age = date.getYear()-birthday.getYear();
		return age;
	}
	
	public String getUserRoleName() {
		return userRoleName;
	}
	public void setUserRoleName(String userRoleName) {
		this.userRoleName = userRoleName;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUserCode() {
		return userCode;
	}
	public void setUserCode(String userCode) {
		this.userCode = userCode;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getUserPassword() {
		return userPassword;
	}
	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}
	public Integer getGender() {
		return gender;
	}
	public void setGender(Integer gender) {
		this.gender = gender;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public Integer getUserRole() {
		return userRole;
	}
	public void setUserRole(Integer userRole) {
		this.userRole = userRole;
	}
	public Integer getCreatedBy() {
		return createdBy;
	}
	public void setCreatedBy(Integer createdBy) {
		this.createdBy = createdBy;
	}
	public Date getCreationDate() {
		return creationDate;
	}
	public void setCreationDate(Date creationDate) {
		this.creationDate = creationDate;
	}
	public Integer getModifyBy() {
		return modifyBy;
	}
	public void setModifyBy(Integer modifyBy) {
		this.modifyBy = modifyBy;
	}
	public Date getModifyDate() {
		return modifyDate;
	}
	public void setModifyDate(Date modifyDate) {
		this.modifyDate = modifyDate;
	}
}

mybatis-config.xml配置文件如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">

<!-- 通过这个配置文件完成mybatis与数据库的连接 -->
<configuration>

	<!-- 引入 database.properties 文件-->
	<properties resource="database.properties"/>
	
	<settings>
		<!-- 配置mybatis的log实现为LOG4J -->
		<setting name="logImpl" value="LOG4J" />
		<!-- 
			设置resultMap的自动映射级别为:
			NONE
			PARTIAL(默认)
			FULL
		-->
		 <!-- <setting name="autoMappingBehavior" value="FULL" />  -->
		<!-- <setting name="autoMappingBehavior" value="NONE" />  -->
	</settings>
	
	<!--类型别名-->
	<typeAliases>
	 	<!-- <typeAlias alias="User" type="cn.smbms.pojo.User"/> -->
	 	<package name="cn.smbms.pojo"/>
	</typeAliases>
	
	<environments default="development">
		<environment id="development">
			<!--配置事务管理，采用JDBC的事务管理  -->
			<transactionManager type="JDBC"></transactionManager>
			<!-- POOLED:mybatis自带的数据源，JNDI:基于tomcat的数据源 -->
			<dataSource type="POOLED">
				<property name="driver" value="${driver}"/>
				<property name="url" value="${url}"/>
				<property name="username" value="${user}"/>
				<property name="password" value="${password}"/>
			</dataSource>
		</environment>
	</environments>
	
	<!-- 将mapper文件加入到配置文件中 -->
	<mappers>
		<mapper resource="cn/smbms/dao/user/UserMapper.xml"/>
	</mappers>
</configuration>

UserMapper.java接口如下：

package cn.smbms.dao.user;

import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import cn.smbms.pojo.User;

public interface UserMapper {
	/**
	 * 查询用户表记录数
	 * @return
	 */
	public int count();
	
	/**
	 * 查询用户列表
	 * @param userName
	 * @param roleId
	 * @return
	 */
	public List<User> getUserList(@Param("userName")String userName,@Param("userRole")Integer roleId);
	
	/**
	 * 增加用户
	 * @param user
	 * @return
	 */
	public int add(User user);
	/**
	 * 修改用户信息
	 * @param user
	 * @return
	 */
	public int modify(User user);
	
	/**
	 * 修改当前用户密码
	 * @param id
	 * @param pwd
	 * @return
	 */
	public int updatePwd(@Param("id")Integer id, @Param("userPassword")String pwd);
	
	/**
	 * 根据userId删除用户信息
	 * @param delId
	 * @return
	 */
	public int deleteUserById(@Param("id")Integer delId);
	
	/**
	 * 根据用户角色列表，获取该角色列表下用户列表信息-foreach_array
	 * @param roleIds
	 * @return
	 */
	public List<User> getUserByRoleId_foreach_array(Integer[] roleIds);
	
	/**
	 * 根据用户角色列表，获取该角色列表下用户列表信息-foreach_list
	 * @param roleList
	 * @return
	 */
	public List<User> getUserByRoleId_foreach_list(List<Integer> roleList);
	
	/**
	 * 根据用户角色列表和性别(多参数)，获取该角色列表下指定性别的用户列表信息-foreach_map
	 * @param conditionMap
	 * @return
	 */
	public List<User> getUserByConditionMap_foreach_map(Map<String,Object> conditionMap);
	
	/**
	 * 根据用户角色列表，获取该角色列表下用户列表信息-foreach_map(单参数封装成map)
	 * @param roleMap
	 * @return
	 */
	public List<User> getUserByRoleId_foreach_map(Map<String,Object> roleMap);
	
	
	/**
	 * 查询用户列表(choose)
	 * @param userName
	 * @param roleId
	 * @param userCode
	 * @param creationDate
	 * @return
	 */
	public List<User> getUserList_choose(@Param("userName")String userName,
										 @Param("userRole")Integer roleId,
										 @Param("userCode")String userCode,
										 @Param("creationDate")Date creationDate);
}

UserMapper.xml的配置如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.smbms.dao.user.UserMapper">

	<!-- 查询用户表记录数 -->
	<select id="count" resultType="int">
		select count(1) as count from smbms_user
	</select>
	<!-- 当数据库中的字段信息与对象的属性不一致时需要通过resultMap来映射 -->
	<resultMap type="User" id="userList">
		<result property="id" column="id"/>
		<result property="userCode" column="userCode"/>
		<result property="userName" column="userName"/>
		<result property="phone" column="phone"/>
		<result property="birthday" column="birthday"/>
		<result property="gender" column="gender"/>
		<result property="userRole" column="userRole"/>
		<result property="userRoleName" column="roleName"/>
	</resultMap>
	
	<!-- 查询用户列表 -->
	<!-- <select id="getUserList" resultMap="userList">
		select u.*,r.roleName from smbms_user u,smbms_role r where u.userRole = r.id
			<if test="userRole != null">
				and u.userRole = #{userRole}
			</if>
			<if test="userName != null and userName != ''">
				and u.userName like CONCAT ('%',#{userName},'%') 
			</if>
	</select> --> 
	
	<!-- 
		  简单改造getUserList：
		  若 只传入一个参数：userRole，而不传入参数：userName的时候，控制台报sql异常错误
		  查看日志中sql语句：select * from smbms_user where and userRole = ? 
		  若 不传入任何参数：控制台报sql异常错误
		  查看日志中sql语句：select * from smbms_user where
		 
		<select id="getUserList" resultType="User">
			select * from smbms_user where
				<if test="userName != null and userName != ''">
					userName like CONCAT ('%',#{userName},'%') 
				</if>
				<if test="userRole != null">
					and userRole = #{userRole}
				</if>
		</select>
		  
		  演示如何灵活处理where and|or :
	-->
	<select id="getUserList" resultType="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>
	
	
	<!-- 增加用户 -->
	<insert id="add" parameterType="User">
		insert into smbms_user (userCode,userName,userPassword,gender,birthday,phone,
								address,userRole,createdBy,creationDate) 
				values (#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},
				#{address},#{userRole},#{createdBy},#{creationDate})
	</insert>
	
	<!-- 修改用户信息 -->
	<update id="modify" parameterType="User">
		 update smbms_user 
			 <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
		 		<if test="userCode != null">userCode=#{userCode},</if>
				<if test="userName != null">userName=#{userName},</if>
				<if test="userPassword != null">userPassword=#{userPassword},</if>
				<if test="gender != null">gender=#{gender},</if>
				<if test="birthday != null">birthday=#{birthday},</if>
				<if test="phone != null">phone=#{phone},</if>
				<if test="address != null">address=#{address},</if>
				<if test="userRole != null">userRole=#{userRole},</if>
				<if test="modifyBy != null">modifyBy=#{modifyBy},</if>
				<if test="modifyDate != null">modifyDate=#{modifyDate},</if>
			 </trim>
	</update>
	
	<!-- 修改当前用户密码 -->
	<update id="updatePwd" parameterType="Integer">
		update smbms_user set userPassword=#{userPassword} where id=#{id}
	</update>
	
	<!-- 根据userId删除用户信息 -->
	<delete id="deleteUserById" parameterType="Integer">
		delete from smbms_user where id=#{id}
	</delete>
	
	<!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_array -->
	<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>
	
	
	<!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_list -->
	<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>
	
	<!-- 根据用户角色列表和性别(多参数)，获取该角色列表下并指定性别的用户列表信息-foreach_map -->
	<select id="getUserByConditionMap_foreach_map" resultMap="userMapByRole">
		select * from smbms_user where gender = #{gender} and userRole in 
			<foreach collection="roleIds" item="roleMap" open="(" separator="," close=")">
				#{roleMap}
			</foreach>
	</select>
	
	<!-- 根据用户角色列表(单参数)，获取该角色列表下用户列表信息-foreach_map -->
	<select id="getUserByRoleId_foreach_map" resultMap="userMapByRole">
		select * from smbms_user where userRole in 
			<foreach collection="rKey" item="roleMap" open="(" separator="," close=")">
				#{roleMap}
			</foreach>
	</select>
	
	<!-- 查询用户列表(choose) -->
	<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(NOW()) -->
					and YEAR(creationDate) = YEAR(#{creationDate})
				</otherwise>
			</choose>
	</select>
</mapper>

测试类：

package cn.smbms.dao.user;

import static org.junit.Assert.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.Before;
import org.junit.Test;
import cn.smbms.pojo.Address;
import cn.smbms.pojo.User;
import cn.smbms.utils.MyBatisUtil;

public class UserMapperTest {
	
	private Logger logger = Logger.getLogger(UserMapperTest.class);
	
	@Before
	public void setUp() throws Exception {
	}

	@Test
	public void testCount() {
		SqlSession sqlSession = null;
		int count = 0;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			
			//第一种方式:调用selectOne方法执行查询操作
			//count = sqlSession.selectOne("cn.smbms.dao.user.UserMapper.count");
			
			//第二种方式:调用getMapper(Mapper.class)执行dao接口方法来实现对数据库的查询操作
			count = sqlSession.getMapper(UserMapper.class).count();
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		
		logger.debug("UserDaoTest count---> " + count);
	}
	
	@Test
	public void testGetUserList(){
		SqlSession sqlSession = null;
		List<User> userList = new ArrayList<User>();
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			String userName = "";
			Integer roleId = 3;
			userList = sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userlist.size ----> " + userList.size());
		for(User user: userList){
			logger.debug("testGetUserList=======> id: " + user.getId() +
						" and userCode: " + user.getUserCode() + 
						" and userName: " + user.getUserName() + 
						" and userRole: " + user.getUserRole() + 
						" and userRoleName: " + user.getUserRoleName() +
						" and age: " + user.getAge() +
						" and phone: " + user.getPhone() +
						" and gender: " + user.getGender());
		}
	}
	
	
	@Test
	public void testAdd(){
		logger.debug("testAdd !===================");
		SqlSession sqlSession = null;
		int count = 0;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			User user = new User();
			user.setUserCode("test001");
			user.setUserName("测试用户001");
			user.setUserPassword("1234567");
			Date birthday = new SimpleDateFormat("yyyy-MM-dd").parse("1984-12-12");
			user.setBirthday(birthday);
			user.setCreationDate(new Date());
			user.setAddress("地址测试");
			user.setGender(1);
			user.setPhone("13688783697");
			user.setUserRole(1);
			user.setCreatedBy(1);
			user.setCreationDate(new Date());
			count = sqlSession.getMapper(UserMapper.class).add(user);
			//模拟异常，进行回滚
			//int i = 2/0;
			sqlSession.commit();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			sqlSession.rollback();
			count = 0;
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("testAdd count: " + count);
	}
	
	@Test
	public void testModify(){
		logger.debug("testModify !===================");
		SqlSession sqlSession = null;
		int count = 0;
		try {
			User user = new User();
			user.setId(16);
			//user.setUserCode("testmodify");
			//user.setUserName("测试用户修改");
			//user.setUserPassword("0000000");
			//Date birthday = new SimpleDateFormat("yyyy-MM-dd").parse("1980-10-10");
			//user.setBirthday(birthday);
			//user.setAddress("地址测试修改");
			//user.setGender(2);
			//user.setPhone("13600002222");
			//user.setUserRole(2);
			user.setModifyBy(1);
			user.setModifyDate(new Date());
			sqlSession = MyBatisUtil.createSqlSession();
			count = sqlSession.getMapper(UserMapper.class).modify(user);
			//模拟异常，进行回滚
			//int i = 2/0;
			sqlSession.commit();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			sqlSession.rollback();
			count = 0;
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("testModify count: " + count);
	}
	
	@Test
	public void testUpdatePwd() {
		logger.debug("testUpdatePwd !===================");
		SqlSession sqlSession = null;
		String pwd = "8888888";
		Integer id = 1;
		int count = 0;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			count = sqlSession.getMapper(UserMapper.class).updatePwd(id, pwd);
			sqlSession.commit();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			sqlSession.rollback();
			count = 0;
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("testUpdatePwd count： " + count);
	}
	
	@Test
	public void testDeleteUserById() {
		logger.debug("testDeleteUserById !===================");
		SqlSession sqlSession = null;
		Integer delId = 25;
		int count = 0;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			count = sqlSession.getMapper(UserMapper.class).deleteUserById(delId);
			sqlSession.commit();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			sqlSession.rollback();
			count = 0;
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("testDeleteUserById count: " + count);
	}
	
	@Test
	public void testGetUserByRoleId_foreach_array(){
		SqlSession sqlSession = null;
		List<User> userList = new ArrayList<User>();
		Integer[] roleIds = {2,3};
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleIds);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userList.size ----> " + userList.size());
		for(User user : userList){
			logger.debug("user ===========> id: " + user.getId()+
						", userCode: " + user.getUserCode() + 
						", userName: " + user.getUserName() +
						", userRole: " + user.getUserRole());
		}
	}
	@Test
	public void testGetUserByRoleId_foreach_list(){
		SqlSession sqlSession = null;
		List<User> userList = new ArrayList<User>();
		List<Integer> roleList = new ArrayList<Integer>();
		roleList.add(2);
		roleList.add(3);
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_list(roleList);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userList.size ----> " + userList.size());
		for(User user : userList){
			logger.debug("user ===========> id: " + user.getId()+
						", userCode: " + user.getUserCode() + 
						", userName: " + user.getUserName() +
						", userRole: " + user.getUserRole());
		}
	}
	
	@Test
	public void testGetUserByConditionMap_foreach_map(){
		SqlSession sqlSession = null;
		List<User> userList = 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("gender", 1);
		conditionMap.put("roleIds",roleList);
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserByConditionMap_foreach_map(conditionMap);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userList.size ----> " + userList.size());
		for(User user : userList){
			logger.debug("user ===========> id: " + user.getId()+
						", userCode: " + user.getUserCode() + 
						", userName: " + user.getUserName() +
						", gender: " + user.getGender() +
						", userRole: " + user.getUserRole());
		}
	}
	
	@Test
	public void testGetUserByRoleId_foreach_map(){
		SqlSession sqlSession = null;
		List<User> userList = new ArrayList<User>();
		List<Integer> roleList = new ArrayList<Integer>();
		roleList.add(2);
		roleList.add(3);
		Map<String, Object> roleMap = new HashMap<String,Object>();
		roleMap.put("rKey", roleList);
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_map(roleMap);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userList.size ----> " + userList.size());
		for(User user : userList){
			logger.debug("user ===========> id: " + user.getId()+
						", userCode: " + user.getUserCode() + 
						", userName: " + user.getUserName() +
						", userRole: " + user.getUserRole());
		}
	}
	
	@Test
	public void testGetUserList_choose(){
		SqlSession sqlSession = null;
		List<User> userList = new ArrayList<User>();
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			String userName = "";
			Integer roleId = null;
			String userCode = "";
			Date creationDate = new SimpleDateFormat("yyyy-MM-dd").parse("2017-01-01");
			userList = sqlSession.getMapper(UserMapper.class).getUserList_choose(userName,roleId,userCode,creationDate);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userlist.size ----> " + userList.size());
		for(User user: userList){
			logger.debug("testGetUserList_choose=======> id: " + user.getId() +
						" and userCode: " + user.getUserCode() + 
						" and userName: " + user.getUserName() + 
						" and userRole: " + user.getUserRole() + 
						" and creationDate: " + new SimpleDateFormat("yyyy-MM-dd").format(user.getCreationDate()));
		}
	}
	
}

以上就是mybatis的动态sql