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);
- }
- }
- }