渐渐老去 2017-11-05
参照官网: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 的表达式来消除其他元素。
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