MyBatis动态查询(where trim set)

前言：

where标签：当无条件满足时 不添加where关键字

存在条件满足时 添加where关键字

where标签会自动去掉查询语句中第一个条件的and | or

where标签

where-EmpMapper

package cn.et.lesson04;import java.util.List;import org.apache.ibatis.annotations.Select;public interface EmpMapper {@Select("<script>" +"select * from emp" +"<where>"+"<if test='ename != null'> ename=#{ename} </if>" +"<if test='empno != null'> and empno=#{empno} </if>" +"</where>"+"</script>")public List<Emp> selectEmpByEmpNo(Emp emp);}

where-Test

package cn.et.lesson04;import java.io.InputStream;import java.util.List;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class TestMybatis {private static SqlSession getSession() {//因为需要的mybatis.xml文件 不在同一层目录  所以这里才使用 cn.et.lesson01.TestMybatisInputStream is = TestMybatis.class.getResourceAsStream("mybatis.xml");SqlSessionFactory session = new SqlSessionFactoryBuilder().build(is);//openSession()获取操作数据库的类 SqlSessionSqlSession sqlSession = session.openSession();return sqlSession;}public static void main(String[] args) {SqlSession sqlSession = getSession();//通过动态代理创建一个实体类    通过接口会自动调用配置文件EmpMapper dm = sqlSession.getMapper(EmpMapper.class);Emp myEmp = new Emp();//myEmp.setEname("SMITH");List<Emp> emp = dm.selectEmpByEmpNo(myEmp);System.out.println(emp);}}

where-Run :从 ==>  Preparing: select * from emp 看出当无条件满足时 不添加where关键字 

2017-06-15 19:47:17 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@565c7f6]2017-06-15 19:47:17 DEBUG [cn.et.lesson04.EmpMapper.selectEmpByEmpNo] ==>  Preparing: select * from emp 2017-06-15 19:47:17 DEBUG [cn.et.lesson04.EmpMapper.selectEmpByEmpNo] ==> Parameters: 2017-06-15 19:47:17 DEBUG [cn.et.lesson04.EmpMapper.selectEmpByEmpNo] <==      Total: 14

where-Test1 

public static void main(String[] args) {SqlSession sqlSession = getSession();//通过动态代理创建一个实体类    通过接口会自动调用配置文件EmpMapper dm = sqlSession.getMapper(EmpMapper.class);Emp myEmp = new Emp();myEmp.setEname("SMITH");List<Emp> emp = dm.selectEmpByEmpNo(myEmp);System.out.println(emp);}

where-Run1:从 ==>  Preparing: select * from emp WHERE ename=? 

存在条件满足时 添加where关键字

where标签会自动去掉查询语句中第一个条件的and | or

2017-06-15 19:51:38 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@477a1767]2017-06-15 19:51:38 DEBUG [cn.et.lesson04.EmpMapper.selectEmpByEmpNo] ==>  Preparing: select * from emp WHERE ename=? 2017-06-15 19:51:38 DEBUG [cn.et.lesson04.EmpMapper.selectEmpByEmpNo] ==> Parameters: SMITH(String)2017-06-15 19:51:38 DEBUG [cn.et.lesson04.EmpMapper.selectEmpByEmpNo] <==      Total: 1

set标签

开头添加 set  结尾去掉逗号  用于update

EmpMapper

@Select("<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);

Test

public static void main(String[] args) {SqlSession sqlSession = getSession();//通过动态代理创建一个实体类    通过接口会自动调用配置文件EmpMapper dm = sqlSession.getMapper(EmpMapper.class);Emp myEmp = new Emp();myEmp.setSal("5000");myEmp.setEmpno("7903");dm.updateEmpSet(myEmp);}

Run

2017-06-15 20:29:11 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@7eb05acd]2017-06-15 20:29:11 DEBUG [cn.et.lesson04.EmpMapper.updateEmpSet] ==>  Preparing: update emp SET sal=? where empno=? 2017-06-15 20:29:11 DEBUG [cn.et.lesson04.EmpMapper.updateEmpSet] ==> Parameters: 5000(String), 7903(String)2017-06-15 20:29:11 DEBUG [cn.et.lesson04.EmpMapper.updateEmpSet] <==    Updates: 1

trim标签：

一个相对智能的标签  可以根据自己的需求 替换掉 前缀 或者 后缀

其本身也能 实现 where 和 set 的功能

EmpMapper   trim实现 where 功能

@Select("<script>" +"select * from emp" +"<trim prefix='where' prefixOverrides='and'>"+"<if test='ename != null'> and ename=#{ename} </if>" +"<if test='empno != null'> and empno=#{empno} </if>" +"</trim>"+"</script>")public List<Emp> selectEmpByTrim(Emp emp);

Test

public static void main(String[] args) {SqlSession sqlSession = getSession();//通过动态代理创建一个实体类    通过接口会自动调用配置文件EmpMapper dm = sqlSession.getMapper(EmpMapper.class);Emp myEmp = new Emp();myEmp.setEmpno("7369");myEmp.setEname("SMITH");List<Emp> emp = dm.selectEmpByTrim(myEmp);System.out.println(emp)}

Run

2017-06-15 20:16:21 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@2a8ddc4c]2017-06-15 20:16:21 DEBUG [cn.et.lesson04.EmpMapper.selectEmpByTrim] ==>  Preparing: select * from emp where ename=? and empno=? 2017-06-15 20:16:21 DEBUG [cn.et.lesson04.EmpMapper.selectEmpByTrim] ==> Parameters: SMITH(String), 7369(String)2017-06-15 20:16:21 DEBUG [cn.et.lesson04.EmpMapper.selectEmpByTrim] <==      Total: 1

EmpMapper   trim实现 set功能

@Select("<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 updateEmpTrim(Emp emp);

Test

public static void main(String[] args) {SqlSession sqlSession = getSession();//通过动态代理创建一个实体类    通过接口会自动调用配置文件EmpMapper dm = sqlSession.getMapper(EmpMapper.class);Emp myEmp = new Emp();myEmp.setSal("4000");myEmp.setEmpno("7782");dm.updateEmpTrim(myEmp);}

Run

2017-06-15 20:34:44 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@6cd24e3f]2017-06-15 20:34:44 DEBUG [cn.et.lesson04.EmpMapper.updateEmpTrim] ==>  Preparing: update emp set sal=? where empno=? 2017-06-15 20:34:45 DEBUG [cn.et.lesson04.EmpMapper.updateEmpTrim] ==> Parameters: 4000(String), 7782(String)2017-06-15 20:34:45 DEBUG [cn.et.lesson04.EmpMapper.updateEmpTrim] <==    Updates: 1