Mybatis之动态SQL

样例包含了If,choose,where,set,foreach,bind.

基本覆盖了常见的动态SQL用法。

建表语句：

CREATE TABLE tb_employee (
  ID INT(11) PRIMARY KEY AUTO_INCREMENT,
  LOGINNAME VARCHAR(18),
  PASSWORD VARCHAR(18),
  NAME VARCHAR(18) DEFAULT NULL,
  SEX CHAR(2) DEFAULT NULL,
  AGE INT(11) DEFAULT NULL,
  PHONE VARCHAR(21),
  SAL DOUBLE,
  STATE VARCHAR(18)
);

INSERT INTO tb_employee(LOGINNAME, PASSWORD, NAME, SEX, AGE, PHONE, SAL, STATE)
VALUES('jack', '123456', 'zhang', 'M', 26, '13918232123', 9800, 'active');

INSERT INTO tb_employee(LOGINNAME, PASSWORD, NAME, SEX, AGE, PHONE, SAL, STATE)
VALUES('rose', '123456', 'wen', 'F', 22, '134213232123', 7600, 'disactive');

INSERT INTO tb_employee(LOGINNAME, PASSWORD, NAME, SEX, AGE, PHONE, SAL, STATE)
VALUES('sky', '123456', 'chang', 'M', 42, '13118232123', 12800, 'active');

<?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="org.fkit.mapper.EmployeeMapper">

	<select id="selectEmployeeByIdLike" resultType="org.fkit.domain.Employee">
		SELECT * FROM tb_employee WHERE state = "ACTIVE"
		<if test="id != null">
			and id = #{id} 
		</if>
		<if test="password != null">
			and password = #{password}
		</if>
	</select>
	
	<select id="selectEmployeeChoose" parameterType="hashmap" resultType="org.fkit.domain.Employee">
		SELECT * FROM tb_employee WHERE state = "ACTIVE"
		<choose>
			<when test="id != null">
				and id = #{id}
			</when>
			<when test="loginname != null and password != null">
				and loginname = #{loginname} and password = #{password}
			</when>
			<otherwise>
				and set = "M"
			</otherwise>
		</choose>
	</select>
	
	<select id="selectEmployeeLike"  resultType="org.fkit.domain.Employee">
		SELECT * FROM tb_employee
		<where>
			<if test="id != null">
				id = #{id}
			</if>
			<if test="state != null">
				state = #{state}
			</if>

		</where>
	</select>
	
	<select id="selectEmployeeIn" resultType="org.fkit.domain.Employee">
		SELECT * 
		FROM tb_employee
		WHERE ID IN
		<foreach collection="list" index="index" item="item"
			open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>
	
	<select id="selectEmployeeLikeName" resultType="org.fkit.domain.Employee">
		<bind name="pattern" value="'%' + _parameter.getName() + '%'" />
		SELECT * FROM tb_employee
		WHERE loginname LIKE #{pattern}
	</select>
	
	<select id="selectEmployeeById" parameterType="int" resultType="org.fkit.domain.Employee">
		SELECT * FROM tb_employee WHERE id = #{id}
	</select>
	
	<update id="updateEmployeeIfNecessary" parameterType="org.fkit.domain.Employee">
		UPDATE tb_employee
			<set>
				<if test="loginname != null">loginname=#{loginname},</if>
				<if test="password != null">password=#{password},</if>
				<if test="name != null">name=#{name},</if>
				<if test="sex != null">sex=#{sex},</if>
				<if test="age != null">age=#{age},</if>
				<if test="phone != null">phone=#{phone}</if>
			</set>
		WHERE id = #{id}
	</update>
	
</mapper>

package org.fkit.tst;


import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.fkit.domain.Employee;
import org.fkit.factory.FKSqlSessionFactory;
import org.fkit.mapper.EmployeeMapper;



public class DynamicSQLTest {

	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		SqlSession session = FKSqlSessionFactory.getSqlSession();
		
		DynamicSQLTest t = new DynamicSQLTest();
		t.testSelectEmployeeByIdLike(session);
		t.testSelectEmployeeChoose(session);
		t.testUpdateEmployeeIfNecessary(session);
		t.testSelectEmployeeIn(session);
		t.testSelectEmployeeLikeName(session);

		session.commit();
		session.close();

	}
	
	public void testSelectEmployeeByIdLike(SqlSession session) {
		EmployeeMapper em = session.getMapper(EmployeeMapper.class);
		HashMap<String, Object> params = new HashMap<String, Object>();
		params.put("id", 1);
		params.put("password", "123456");
		List<Employee> list = em.selectEmployeeByIdLike(params);
		list.forEach(employee -> System.out.println(employee.getLoginname()));
	}
	
	public void testSelectEmployeeChoose(SqlSession session) {
		EmployeeMapper em = session.getMapper(EmployeeMapper.class);
		HashMap<String, Object> params = new HashMap<String, Object>();
		params.put("id", 1);
		params.put("password", "123456");
		List<Employee> list = em.selectEmployeeChoose(params);
		list.forEach(employee -> System.out.println(employee.getLoginname()));
	}
	
	public void testUpdateEmployeeIfNecessary(SqlSession session) {
		EmployeeMapper em = session.getMapper(EmployeeMapper.class);
		Employee employee = em.selectEmployeeById(2);
		employee.setLoginname("march");
		employee.setPassword("password");
		em.updateEmployeeIfNecessary(employee);
	}
	
	public void testSelectEmployeeIn(SqlSession session) {
		EmployeeMapper em = session.getMapper(EmployeeMapper.class);
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(1);
		ids.add(2);
		List<Employee> list = em.selectEmployeeIn(ids);
		list.forEach(employee -> System.out.println(employee.getLoginname()));
		
	}
	
	public void testSelectEmployeeLikeName(SqlSession session) {
		EmployeeMapper em = session.getMapper(EmployeeMapper.class);
		Employee employee = new Employee();
		employee.setName("k");
		List<Employee> list = em.selectEmployeeLikeName(employee);
		System.out.println(list);
		list.forEach(epe -> System.out.println(epe.getLoginname()));
	}
	

}