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

矫情吗；* 2022-06-18 08:47 327阅读 0赞

代码：

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

截图（查询）：

![Center][]

[Center]: /images/20220618/86e461f0372142edbbf03815f0a4e909.png