Mybatis的动态sql----where,trim,set,foreach

jdbc.properties

url=jdbc\:oracle\:thin\:@localhost\:1521\:orcl
driverClass=oracle.jdbc.OracleDriver
account=scott
password=tiger

mybatis.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">
<configuration>
	
	<properties resource="cn/et/mybatis/lesson04/jdbc.properties">
		
	</properties>

  <!-- 
  	配置连接数据库的环境   development开发环境
  -->
  <environments default="development">
    <environment id="development">
      <!-- 事务交给JDBC来管理   也就是通过 Connection的 commit和rollback管事事务 -->
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driverClass}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${account}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  
  <mappers>
  	<mapper class="cn.et.mybatis.lesson04.dynamicSql.EmpMapper"/>
  </mappers>
</configuration>

Emp实体类：

package cn.et.mybatis.lesson04.dynamicSql;

public class Emp {

	private String empNo;
	
	private String ename;

	private String sal;

	public String getEmpNo() {
		return empNo;
	}

	public void setEmpNo(String empNo) {
		this.empNo = empNo;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getSal() {
		return sal;
	}

	public void setSal(String sal) {
		this.sal = sal;
	}

	@Override
	public String toString() {
		return "Emp [empNo=" + empNo + ", ename=" + ename + ", sal=" + sal
				+ "]";
	}

	
	
}

EmpMapper：

package cn.et.mybatis.lesson04.dynamicSql;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;


public interface EmpMapper {

	/**
	 * where
	 * 如果其中有和任意一条if语句成功，则自动拼接WHERE字符串
	 * 并覆盖首个and|or
	 * 
	 * 没有一个if语句条件成立
	 * select * from emp 
	 * 
	 * 有if语句条件成立
	 * select * from emp WHERE ename=? and sal=? 
	 * @param emp
	 * @return
	 */
	@Select(
			{"<script>" +
			"select * from emp" +
			"<where>" +
			"<if test='ename!=null'> and ename=#{ename}</if>" +
			"<if test='sal!=null'> and sal=#{sal}</if>" +
			"</where>" +
			"</script>"
			}
	)
	public List<Emp> queryEmpWhere(Emp emp);
	
	
	/**
	 * trim可以替换后面拼接字符串的前缀和后缀
	 * 当拼接 and ename=#{ename}和 and sal=#{sal}时
	 * 这里只需要首个and替换成 where就可以了
	 * 
	 * select * from emp where ename=? and sal=? 
	 * 这里首个拼接的字符串的and就被替换成了where
	 * 
	 * @param emp
	 * @return
	 */
	@Select(
			{"<script>" +
			"select * from emp" +
			"<trim prefix='where' prefixOverrides='and' >" +
			"<if test='ename!=null'> and ename=#{ename}</if>" +
			"<if test='sal!=null'> and sal=#{sal}</if>" +
			"</trim>" +
			"</script>"
			}
	)
	public List<Emp> queryEmpTrim(Emp emp);
	
	/**
	 * trim
	 * 条件符合增加前缀 set
	 * 并把最后的,替换成空字符串
	 * 
	 * @param emp
	 */
	@Update(
			{"<script>" +
			"update emp" +
			"<trim prefix='set' prefixOverrides=''  suffix='' suffixOverrides=',' >" +
			"<if test='ename!=null'> ename=#{ename},</if>" +
			"<if test='sal!=null'> sal=#{sal},</if>" +
			"</trim>" +
			"where empno=#{empno}" +
			"</script>"
			}
	)
	public void updateEmpTrimSuffix(Emp emp);
	
	
	/**
	 * set
	 * 条件符合增加前缀 set
	 * 并把最后的,替换成空字符串
	 * @param emp
	 */
	@Update(
			{"<script>" +
			"update emp" +
			"<set>" +
			"<if test='ename!=null'> ename=#{ename},</if>" +
			"<if test='sal!=null'> sal=#{sal},</if>" +
			"</set>" +
			"where empno=#{empno}" +
			"</script>"
			}
	)
	public void updateEmpSet(Emp emp);
	
	/**
	 * set形状添加set 结尾去掉逗号
	 * open就是字符串拼接的开头，那这里便是(
	 * close就是字符串拼接的结尾，那这里便是)
	 * collection要遍历的集合
	 * item迭代变量
	 * separator分隔符
	 * 
	 * @param emp
	 */
	@Select(
			{"<script>" +
			"select * from emp where empno in" +
			"<foreach collection='list' open='('  close=')' separator=',' item='myVar'>" +
			"#{myVar}" +
			"</foreach>" +
			"</script>"
			}
	)
	public List<Emp> queryEmpByForeach(List list);
	
	
	
}

测试类：

package cn.et.mybatis.lesson04.dynamicSql;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

public class TestMybatis {
	
	public static SqlSession getSession(){
		String resource = "/cn/et/mybatis/lesson04/mybatis.xml";
		InputStream inputStream = TestMybatis.class.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		//打开会话
		SqlSession session = sqlSessionFactory.openSession();
		return session;
	}
	
	/**
	 * 测试动态Where
	 */
//	@Test
	public void testWhere(){
		SqlSession session = getSession();
		EmpMapper emp = session.getMapper(EmpMapper.class);
		Emp oEmp = new Emp();

		oEmp.setEname("SMITH");
		oEmp.setSal("800");
		List result = emp.queryEmpWhere(oEmp);
		for (Object object : result) {
			System.out.println(object);
		}
	}
	
	/**
	 * 测试动态trim
	 */
//	@Test
	public void testTrim(){
		SqlSession session = getSession();
		EmpMapper emp = session.getMapper(EmpMapper.class);
		Emp oEmp = new Emp();
/*
		oEmp.setEname("SMITH");
		oEmp.setSal("800");*/
		List result = emp.queryEmpTrim(oEmp);
		for (Object object : result) {
			System.out.println(object);
		}
	}
	
	/**
	 * 测试动态trim的suffix
	 */
//	@Test
	public void testTrimSuffix(){
		SqlSession session = getSession();
		EmpMapper emp = session.getMapper(EmpMapper.class);
		Emp oEmp = new Emp();

		//oEmp.setEname("SMITH");
		oEmp.setSal("20");
		oEmp.setEmpno("7839");
		emp.updateEmpTrimSuffix(oEmp);
		
		session.commit();
	}
	
	/**
	 * 测试动态set
	 */
//	@Test
	public void testSet(){
		SqlSession session = getSession();
		EmpMapper emp = session.getMapper(EmpMapper.class);
		Emp oEmp = new Emp();

		//oEmp.setEname("SMITH");
		oEmp.setSal("100");
		oEmp.setEmpno("7839");
		emp.updateEmpSet(oEmp);
		session.commit();
	}
	
	/**
	 * 测试动态foreach
	 */
	@Test
	public void testForeach(){
		SqlSession session = getSession();
		EmpMapper emp = session.getMapper(EmpMapper.class);
		
		List list = new ArrayList();
		list.add("8000");
		list.add("8001");
		list.add("8002");
		list.add("8003");
		
		List listEmp = emp.queryEmpByForeach(list);
		
		for (Object object : listEmp) {
			System.out.println(object);
		}
		
	}
}