同样使用了Oracle 的 scott 模式下的emp表
1、创建Emp类
package com.briup.dynamicSql;
import java.sql.Date;
import org.apache.ibatis.type.Alias;
@Alias("emp")
public class Emp {
private int empno;
private String ename;
private String job;
private int mgr;
private Date hiredate;
private double sal;
private double comm;
public Emp() {
super();
}
public Emp(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm) {
super();
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
@Override
public String toString() {
return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
+ ", sal=" + sal + ", comm=" + comm + "]";
}
}
2、编写mapper接口
package com.briup.dynamicSql;
import java.util.List;
import java.util.Map;
public interface EmpMapper {
// 测试if
List<Emp> selectEmpByEmpno(Map<String, Object> map);
// 测试 choose
List<Emp> selectEmpByOrderParam(Object object);
// 测试where、trim、set
List<Emp> selectEmpWhere(Map<String, Object> map);
// 测试foreach
List<Emp> selectEmpForeach(List list);
}
3、编写映射文件
<?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.briup.dynamicSql.EmpMapper">
<!-- if -->
<select id="selectEmpByEmpno" parameterType="map"
resultType="com.briup.dynamicSql.Emp">
select * from emp
where 1=1
<if test="minNo!=null">
and empno>#{minNo}
</if>
<if test="minNo!=null">
and empno <![CDATA[<]]>
#{maxNo}
</if>
</select>
<!-- 测试where、trim、set -->
<!-- <select id="selectEmpWhere" resultType="com.briup.dynamicSql.Emp" parameterType="map">
select * from emp <where> <if test="minNo!=null"> empno>#{minNo} </if> <if
test="minNo!=null"> and empno <![CDATA[<]]> #{maxNo} </if> </where> </select> -->
<select id="selectEmpWhere" resultType="com.briup.dynamicSql.Emp"
parameterType="map">
select * from emp
<trim prefix="where" prefixOverrides="and|or">
<if test="minNo!=null">
and empno>#{minNo}
</if>
<if test="minNo!=null">
and empno <![CDATA[<]]>
#{maxNo}
</if>
</trim>
</select>
<!-- choose -->
<select id="selectEmpByOrderParam" parameterType="map"
resultType="com.briup.dynamicSql.Emp">
select * from emp
where 1=1
<!-- 类似java 的switch语句,只会选择一个 -->
<choose>
<when test="empno!=null">
and empno > #{empno}
</when>
<!-- <when test="order!=null"> order by #{order} </when> -->
<otherwise>
order by empno
</otherwise>
</choose>
</select>
<!-- 测试foreach -->
<select id="selectEmpForeach" resultType="com.briup.dynamicSql.Emp"
parameterType="map">
select * from emp
where empno in
<!-- 遍历集合时 以 open 开始 ,以close结束 每个元素用 separator分隔 ,item表示每个元素的变量名 -->
<foreach collection="list" open="(" close=")" separator=","
item="itme">
#{itme}
</foreach>
</select>
</mapper>
4.编写db.propertise
dirver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
username=scott
password=tiger
5、编写配置文件conf-DanymicSql.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="db.properties"></properties><!-- 导入db.properties资源文件 -->
<environments default="oracle">
<environment id="oracle">
<transactionManager type="JDBC">
</transactionManager><!-- JDBC/MANAGED -->
<dataSource type="POOLED"><!--unpooled不使用连接池/pooled使用连接池 -->
<property name="driver" value="${dirver}" /><!--数据库的dirver -->
<property name="url" value="${url}" /><!--连接数据库的url -->
<property name="username" value="${username}" /><!--数据库用户名 -->
<property name="password" value="${password}" /><!--对应用户名的密码 -->
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.briup.dynamicSql.EmpMapper" />
</mappers>
</configuration>
6、测试类
package com.briup.dynamicSql;
import java.io.FileReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
public class DynamicSqlTest {
private SqlSessionFactory factory;
private EmpMapper empMapper = null;
private SqlSession session = null;
@Before
public void init() throws Exception {
FileReader reader = new FileReader("src/conf-DanymicSql.xml");
factory = new SqlSessionFactoryBuilder().build(reader);
session = factory.openSession();
empMapper = session.getMapper(EmpMapper.class);
}
@After
public void destory() {
if (session != null)
session.close();
}
@Test
public void DynamicIf() {
Map<String, Object> map = new HashMap<>();
map.put("minNo", 7500);
map.put("maxNo", 7800);
List<Emp> list = empMapper.selectEmpByEmpno(map);
System.out.println(list);
}
@Test
public void DynamicChoose() {
try {
Map<String, Object> map = new HashMap<>();
map.put("empno", 7500);
map.put("order", "sal");
List<Emp> list = empMapper.selectEmpByOrderParam(map);
System.out.println(list);
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
@Test
public void testWhereOrTrim() {
try {
Map<String, Object> map = new HashMap<>();
map.put("minNo", 7500);
map.put("maxNo", 7800);
List<Emp> list = empMapper.selectEmpWhere(map);
System.out.println(list);
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
@Test
public void testforeach() {
try {
List<Integer> list = new ArrayList<>();
list.add(7369);
list.add(7499);
list.add(7782);
List<Emp> listemp = empMapper.selectEmpForeach(list);
System.out.println(listemp);
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}