MyBatis学习6之动态sql

参照官网：http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。

动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

1、数据准备(表结构以及示例数据)

 2、查询结构实体类PersonInfoVo.java

public class PersonInfoVo {
	
	private int id;
	private String userName;
	private String password;
	private int pid;
	private int state;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public int getPid() {
		return pid;
	}
	public void setPid(int pid) {
		this.pid = pid;
	}
	public int getState() {
		return state;
	}
	public void setState(int state) {
		this.state = state;
	}
}

 3.1查询用户名带有er的用户信息

3.1.1编写sql映射

<?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="com.zlt.mybatis.vo.PersonInfoVo">
	<select id="queryPerson" resultMap="personResultMap" parameterType="String">
		select id,user_name,password,pid,state  
		from t_user 
		where state = 1
		<if test="user_name != null">
			and user_name like #{userName} 
		</if> 	
       </select> 
       <resultMap type="com.zlt.mybatis.vo.PersonInfoVo" id="personResultMap">
		<id property="id" column="id"/>
		<result property="userName" column="user_name"/>
		<result property="password" column="password"/>
		<result property="pid" column="pid"/>
		<result property="state" column="state"/>
       </resultMap>
</mapper>

   第二种写法:bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文

<!-- 模糊查询 -->
	<select id="queryPerson" resultMap="personResultMap" parameterType="String">
		 <bind name="pattern" value="'%' + _parameter + '%'" />
		select id,user_name,password,pid,state  
		from t_user 
		where state = 1
		<if test="_parameter != null">
			and user_name like #{pattern} 
		</if> 
	</select>
 	<resultMap type="com.zlt.mybatis.vo.PersonInfoVo" id="personResultMap">
		<id property="id" column="id"/>
		<result property="userName" column="user_name"/>
		<result property="password" column="password"/>
		<result property="pid" column="pid"/>
		<result property="state" column="state"/>
	</resultMap>

3.1.2、加载sql映射文件(mybatis.xml)

<mappers>
       <mapper resource="com/zlt/mybatis/mapping/PersonMapper.xml"/>
</mappers>

 3.1.3、编写单元测试

public void testQuery(){
		SqlSession session = null;
		try{
				session = sqlSessionFactory.openSession();
				String statement = "com.zlt.mybatis.vo.PersonInfoVo.queryPerson";
				//List<PersonInfoVo> persons = session.selectList(statement);
				Map<String, String> map = new HashMap<String, String>();
				map.put("userName", "er");
				List<PersonInfoVo> persons = session.selectList(statement,map);
				for(PersonInfoVo person : persons){
					if(null != person) {
						System.out.println("id:" + person.getId() + "userName:" + person.getUserName() + "password:" + person.getPassword() + "pid:" + person.getPid() +"state:" + person.getState());
					}
				}
			}finally{
			if(null != session) session.close();
		}
	}

  第二种测试单元测试

public void testQuery(){
		SqlSession session = null;
		try{
				session = sqlSessionFactory.openSession();
				String statement = "com.zlt.mybatis.vo.PersonInfoVo.queryPerson";
				//List<PersonInfoVo> persons = session.selectList(statement);
				//Map<String, String> map = new HashMap<String, String>();
				//map.put("userName", "er");
				List<PersonInfoVo> persons = session.selectList(statement,"er");
				for(PersonInfoVo person : persons){
					if(null != person) {
						System.out.println("id:" + person.getId() + "userName:" + person.getUserName() + "password:" + person.getPassword() + "pid:" + person.getPid() +"state:" + person.getState());
					}
				}
			}finally{
			if(null != session) session.close();
		}
	}

3.1.4、运行结果：

id:2userName:lisierpassword:123456pid:2state:1
id:3userName:wangerpassword:123456pid:3state:1

 3.2查询用户含有"li"，并且pid为2的用户信息

3.2.1编写SQL映射

<select id="queryPerson2" resultMap="personResultMap2" parameterType="String">
		select id,user_name,password,pid,state  
		from t_user 
		where state = 1
		<if test="user_name != null">
			and user_name like #{userName} 
		</if>
		<if test="pid != null">
			and pid =#{pid} 
		</if> 
	</select>
	
	<resultMap type="com.zlt.mybatis.vo.PersonInfoVo" id="personResultMap2" extends="personResultMap">
	</resultMap>

 3.2.3加载SQL映射文件

<mappers>
       <mapper resource="com/zlt/mybatis/mapping/PersonMapper.xml"/>
</mappers>

3.2.4编写单元测试

public void testQuery2(){
		SqlSession session = null;
		try{
				session = sqlSessionFactory.openSession();
				String statement = "com.zlt.mybatis.vo.PersonInfoVo.queryPerson2";
				//List<PersonInfoVo> persons = session.selectList(statement);
				Map<String, String> map = new HashMap<String, String>();
				map.put("userName", "li");
				map.put("pid", "2");
				List<PersonInfoVo> persons = session.selectList(statement,map);
				for(PersonInfoVo person : persons){
					if(null != person) {
						System.out.println("id:" + person.getId() + "\tuserName:" + person.getUserName() + "\tpassword:" + person.getPassword() + "\tpid:" + person.getPid() +"\tstate:" + person.getState());
					}
				}
			}finally{
			if(null != session) session.close();
		}
	}

 3.2.5运行结果

id:2	userName:lisier	password:123456	pid:2	state:1