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

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

		}
	}

}