前言:
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.TestMybatis
InputStream is = TestMybatis.class.getResourceAsStream("mybatis.xml");
SqlSessionFactory session = new SqlSessionFactoryBuilder().build(is);
//openSession()获取操作数据库的类 SqlSession
SqlSession 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