Mybatis 的 动态sql

同样使用了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;}@Overridepublic 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 {// 测试ifList<Emp> selectEmpByEmpno(Map<String, Object> map);// 测试 chooseList<Emp> selectEmpByOrderParam(Object object);// 测试where、trim、setList<Emp> selectEmpWhere(Map<String, Object> map);// 测试foreachList<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 empwhere 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 empwhere 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 empwhere 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.OracleDriverurl=jdbc:oracle:thin:@127.0.0.1:1521:orclusername=scottpassword=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;@Beforepublic 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);}@Afterpublic void destory() {if (session != null)session.close();}@Testpublic 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);}@Testpublic 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());}}@Testpublic 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());}}@Testpublic 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());}}}