MyBatis注解动态SQL //TODO

MyBatis注解动态SQL

属性	说明
T SELECT(String… columns)	开始或追加select子句
T FROM(String… tables)	启动或追加from子句
T JOIN(String… joins)
T INNER_JOIN(String… joins)
T LEFT_OUTER_JOIN(String… joins)
T RIGHT_OUTER_JOIN(String… joins)
T WHERE(String… conditions)
T OR()
T AND()
T GROUP_BY(String… columns)
T HAVING(String… conditions)
T ORDER_BY(String… columns)
T INSERT_INTO(String tableName)
T VALUES(String columns, String values)
T DELETE_FROM(String table)
T UPDATE(String table)
T SET(String… sets)

##数据表 amd bean

tb_employee

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','杰克','男',26,'13902019999',9800,'ACTIVE');
INSERT INTO tb_employee(loginname,PASSWORD,NAME,sex,age,phone,sal,state) VALUES('rose','123456','露丝','女',21,'13902018888',6800,'ACTIVE');

Employee

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;	 	 // 状态
}

mapper

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

DynamicSQL

public class EmployeeDynaSqlProvider {
	
	// 动态查询
	// @SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhitParam")
	// List<Employee> selectWhitParam(Map<String, Object> param);

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

	// 动态插入
	// @InsertProvider(type=EmployeeDynaSqlProvider.class,method="insertEmployee")
	// @Options(useGeneratedKeys = true, keyProperty = "id")  
	// int insertEmployee(Employee employee);

	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();
	}
	
	// 动态更新
	// @UpdateProvider(type=EmployeeDynaSqlProvider.class,method="updateEmployee")
	// void updateEmployee(Employee employee);

	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();
	}
	
	// 动态删除
	// @DeleteProvider(type=EmployeeDynaSqlProvider.class,method="deleteEmployee")
	// void deleteEmployee(Map<String, Object> param);
	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();
	}
}