MyBatis框架基于Annotation注解的动态SQL

 代码：

Employee.java

package com.bean;

import java.io.Serializable;

public class Employee implements Serializable {
	private Integer id;			 // 主键id
	private String loginname;	 // 登录名
	private String password;	 // 密码
	private String name;		 // 真实姓名
	private String sex;			 // 性别
	private Integer age;		 // 年龄
	private String phone;		 // 电话
	private Double sal;		     // 薪水
	private String state;	 	 // 状态
	public Employee() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getLoginname() {
		return loginname;
	}
	public void setLoginname(String loginname) {
		this.loginname = loginname;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public Double getSal() {
		return sal;
	}
	public void setSal(Double sal) {
		this.sal = sal;
	}
	public String getState() {
		return state;
	}
	public void setState(String state) {
		this.state = state;
	}
	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return "Employee [id=" + id + ", loginname=" + loginname + ", password=" + password + ", name=" + name + ", sex=" + sex
				+ ", age=" + age + ", phone=" + phone + ", sal=" + sal
				+ ", state=" + state + "]";
	}
	
}

FKSqlSessionFactory.java

package com.factory;

import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class FKSqlSessionFactory {
	
	private static SqlSessionFactory sqlSessionFactory = null;
	
	// 初始化创建SqlSessionFactory对象
	static{
		try {
			// 读取mybatis-config.xml文件
			InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
			sqlSessionFactory = new SqlSessionFactoryBuilder()
					.build(inputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// 获取SqlSession对象的静态方法
	public static SqlSession getSqlSession(){
		return sqlSessionFactory.openSession();
	}

	// 获取SqlSessionFactory的静态方法
	public static SqlSessionFactory getSqlSessionFactory() {
		return sqlSessionFactory;
	}

}

EmployeeMapper.java

package com.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;

import com.bean.Employee;
 
public interface EmployeeMapper {
	// 动态查询
	@SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhitParam")
	List<Employee> selectWhitParam(Map<String, Object> param);
	
	// 动态插入
	@InsertProvider(type=EmployeeDynaSqlProvider.class,method="insertEmployee")
	@Options(useGeneratedKeys = true,keyProperty="id")
	int insertEmployee(Employee employee);
	
	// 根据id查询
	@SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhitParam")
	Employee selectEmployeeWithId(Map<String, Object> param);
	
	// 动态更新
	@UpdateProvider(type=EmployeeDynaSqlProvider.class,method="updateEmployee")
	void updateEmployee(Employee employee);
	
	// 动态删除
	@DeleteProvider(type=EmployeeDynaSqlProvider.class,method="deleteEmployee")
	void deleteEmployee(Map<String, Object> param);
}

EmployeeDynaSqlProvider.java

package com.mapper;

import java.util.Map;

import org.apache.ibatis.jdbc.SQL;

import com.bean.Employee;

public class EmployeeDynaSqlProvider {
	public String selectWhitParam(Map<String, Object> param){
		return new SQL(){
			{
				SELECT("*");
				FROM("tb_employee");
				if(param.get("id") != null){
					WHERE(" id = #{id} ");
				}
				if(param.get("loginname") != null){
					WHERE(" loginname = #{loginname} ");
				}
				if(param.get("password") != null){
					WHERE("password = #{password}");
				}
				if(param.get("name")!= null){
					WHERE("name = #{name}");
				}
				if(param.get("sex")!= null){
					WHERE("sex = #{sex}");
				}
				if(param.get("age")!= null){
					WHERE("age = #{age}");
				}
				if(param.get("phone")!= null){
					WHERE("phone = #{phone}");
				}
				if(param.get("sal")!= null){
					WHERE("sal = #{sal}");
				}
				if(param.get("state")!= null){
					WHERE("state = #{state}");
				}
				
			}
		}.toString();
	}
	
	public String insertEmployee(Employee employee){
		return new SQL(){
			{
				INSERT_INTO("tb_employee");
				if(employee.getLoginname()!=null){
					VALUES("loginname", "#{loginname}");
				}
				if(employee.getPassword() != null){
					VALUES("password", "#{password}");
				}
				if(employee.getName()!= null){
					VALUES("name", "#{name}");
				}
				if(employee.getSex()!= null){
					VALUES("sex", "#{sex}");
				}
				if(employee.getAge()!= null){
					VALUES("age", "#{age}");
				}
				if(employee.getPhone()!= null){
					VALUES("phone", "#{phone}");
				}
				if(employee.getSal()!= null){
					VALUES("sal", "#{sal}");
				}
				if(employee.getState()!= null){
					VALUES("state", "#{state}");
				}
			}
	}.toString();
	
  }
	
   public String updateEmployee(Employee employee){
	   return new SQL(){
		   {
			   UPDATE("tb_employee");
			   if(employee.getLoginname() != null){
					SET("loginname = #{loginname}");
				}
				if(employee.getPassword() != null){
					SET("password = #{password}");
				}
				if(employee.getName()!= null){
					SET("name = #{name}");
				}
				if(employee.getSex()!= null){
					SET("sex = #{sex}");
				}
				if(employee.getAge()!= null){
					SET("age = #{age}");
				}
				if(employee.getPhone()!= null){
					SET("phone = #{phone}"); 
				}
				if(employee.getSal()!= null){
					SET("sal = #{sal}");
				}
				if(employee.getState()!= null){
					SET("state = #{state}");
				}
				WHERE(" id = #{id} "); 
		   }
	   }.toString();
   }
   
   public String deleteEmployee(Map<String, Object> param){
	   return new SQL(){
		   {
			   DELETE_FROM("tb_employee");
			   if(param.get("id") != null){
					WHERE(" id = #{id} ");
				}
				if(param.get("loginname") != null){
					WHERE(" loginname = #{loginname} ");
				}
				if(param.get("password") != null){
					WHERE("password = #{password}");
				}
				if(param.get("name")!= null){
					WHERE("name = #{name}");
				}
				if(param.get("sex")!= null){
					WHERE("sex = #{sex}");
				}
				if(param.get("age")!= null){ 
					WHERE("age = #{age}");
				}
				if(param.get("phone")!= null){
					WHERE("phone = #{phone}");
				}
				if(param.get("sal")!= null){
					WHERE("sal = #{sal}");
				}
				if(param.get("state")!= null){
					WHERE("state = #{state}");
				}
		   }
		   
	   }.toString();
   }
}

DynamicSQLTest.java

package com.test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.bean.Employee;
import com.factory.FKSqlSessionFactory;
import com.mapper.EmployeeMapper;

public class DynamicSQLTest {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		SqlSession session = FKSqlSessionFactory.getSqlSession();			
		DynamicSQLTest test = new DynamicSQLTest();
		EmployeeMapper em = session.getMapper(EmployeeMapper.class);			
      //test.testSelectWhitParam(em);
	  //test.testInsertEmployee(em);
	  //test.testUpdateEmployee(em);
		test.testDeleteEmployee(em);
		session.commit();
		session.close();
	}
	
	// 根据动态参数查询员工数据
	public void testSelectWhitParam(EmployeeMapper em){
		// 使用Map装载参数
		Map<String, Object> param = new HashMap<String,Object>();
		param.put("loginname", "rose");
		param.put("password", "123456");
		// 调用selectWhitParam方法
		List<Employee> list = em.selectWhitParam(param);
		// 查看返回结果
		System.out.println(list);
	}
	
	// 根据设置的属性动态插入数据
	public void testInsertEmployee(EmployeeMapper em){
		Employee e =new Employee();
		e.setLoginname("mary");
		e.setPassword("123456");
		e.setName("玛丽");
		e.setSex("女");
		e.setAge(20);
		e.setPhone("13902019999");
		e.setSal(9800.99);
		// 注意：没有设置state属性，则insert语句中不会包含state列
	    e.setState("ACTIVE");
	    em.insertEmployee(e);
	    System.out.println("插入成功，返回id：" + e.getId());
	} 

	// 根据设置的属性动态更新数据
	public void testUpdateEmployee(EmployeeMapper em){
		Map<String, Object> param = new HashMap<String,Object>();
		param.put("id", 6);
		Employee e = em.selectEmployeeWithId(param);
		// 修改员工对象的三个属性
		e.setLoginname("update");
		e.setPassword("1234");
		e.setName("test");
		// 动态更新
		em.updateEmployee(e);
	}
	
	// 根据设置的属性动态删除数据
	public void testDeleteEmployee(EmployeeMapper em){
		Map<String, Object> param = new HashMap<String,Object>();
		param.put("loginname", "mary");
		param.put("password", "1234");
		em.deleteEmployee(param);
	}
}

截图（查询）：