MyBatis框架的动态SQL

蔚落 2022-06-18 04:14 239阅读 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 + "]";
    	}
    	
    }

EmployeeMapper.java

package com.mapper;
    
    import java.util.HashMap;
    import java.util.List;
    
    import com.bean.Employee;
    
    public interface EmployeeMapper {
    	List<Employee> selectEmployeeByIdLike(HashMap<String, Object> params);
    	
    	List<Employee> selectEmployeeByLoginLike(HashMap<String, Object> params);
    	
    	List<Employee> selectEmployeeChoose(HashMap<String, Object> params);
    	
    	List<Employee> findEmployeeLike(HashMap<String, Object> params);
    	
    	List<Employee> selectEmployeeLike(HashMap<String, Object> params);
    	
        Employee selectEmployeeWithId(Integer id);//根据id查询员工
    	
    	void updateEmployeeIfNecessary(Employee employee);//动态更新员工
    
    	List<Employee> selectEmployeeIn(List<Integer> ids);//根据传入的id查询员工
    	
    	List<Employee> selectEmployeeLikeName(Employee employee);//根据传入的参数进行模糊查询
    
    }

EmployeeMapper.xml

<?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.mapper.EmployeeMapper">
       <select id="selectEmployeeWithId" parameterType="int" resultType="com.bean.Employee">
      	     SELECT * FROM tb_employee where id = #{id}
       </select>
       
        
        <select id="selectEmployeeByIdLike" resultType="com.bean.Employee">
             SELECT * FROM tb_employee WHERE state = 'ACTIVE'
      	
      	<if test="id != null ">
      		 and id = #{id}
      	</if>
        </select>
        
        
       <select id="selectEmployeeByLoginLike" resultType="com.bean.Employee">
      	      SELECT * FROM tb_employee WHERE state = 'ACTIVE'
      	
      	<if test="loginname != null and password != null">
      		 and loginname = #{loginname} and password = #{password}
      	</if>
      </select>
      
      
      <select id="selectEmployeeChoose" parameterType="hashmap" resultType="com.bean.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 sex = '男'
      		</otherwise>
      	</choose>
      </select>
      
      <select id="findEmployeeLike" resultType="com.bean.Employee">
      	     SELECT * FROM tb_employee WHERE 
      	<if test="state != null ">
      		state = #{state}
      	</if>
      	<if test="id != null ">
      		and id = #{id}
      	</if>
      	<if test="loginname != null and password != null">
      		and loginname = #{loginname} and password = #{password}
      	</if>
      </select>
      
      
      <select id="selectEmployeeLike" resultType="com.bean.Employee">
         SELECT * FROM tb_employee  
         <where>
      		<if test="state != null ">
      			state = #{state}
    	  	</if>
    	  	<if test="id != null ">
    	  		and id = #{id}
    	  	</if>
    	  	<if test="loginname != null and password != null">
    	  		and loginname = #{loginname} and password = #{password}
    	  	</if>
      	</where>
      </select>
      
      
      <update id="updateEmployeeIfNecessary" parameterType="com.bean.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>
    	      <if test="sal != null">sal=#{sal},</if>
    	      <if test="state != null">state=#{state}</if>
          </set>
      where id=#{id}
      </update>
      
      
      <select id="selectEmployeeIn" resultType="com.bean.Employee">
          SELECT * FROM tb_employee WHERE ID in
          <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
                #{item}
          </foreach>
      </select>
      
      
      <select id="selectEmployeeLikeName"  resultType="com.bean.Employee">
      <bind name="pattern" value="'%' + _parameter.getName() + '%'" />
    	  	SELECT * FROM tb_employee WHERE loginname LIKE #{pattern}
      </select>
    </mapper>

DynamicSQLTest.java

package com.test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    import com.bean.Employee;
    import com.mapper.EmployeeMapper;
    
    public class DynamicSQLTest {
    
    	public static void main(String[] args) throws Exception {
    		// TODO Auto-generated method stub
    		// 读取mybatis-config.xml文件
    		InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    		// 初始化mybatis，创建SqlSessionFactory类的实例
    		SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
    		// 创建Session实例
    		SqlSession session=sqlSessionFactory.openSession();
    		DynamicSQLTest t = new DynamicSQLTest();
    	  //t.testSelectEmployeeByIdLike(session);
    	  //t.testSelectEmployeeByLoginLike(session);
    	  //t.testSelectEmployeeChoose(session);
          //t.testFindEmployeeLike(session);
    	  //t.testSelectEmployeeLike(session);
    	  //t.testUpdateEmployeeIfNecessary(session);
    	  //t.testSelectEmployeeIn(session);
    		t.testSelectEmployeeLikeName(session);
    		session.commit();
    		session.close();
    	}
    	
    	// 测试<select id="selectEmployeeByIdLike" >
    	public void testSelectEmployeeByIdLike(SqlSession session){
    		// 获得EmployeeMapper接口的代理对象
    		EmployeeMapper em=session.getMapper(EmployeeMapper.class);
    		// 创建一个HashMap存储参数
    		HashMap<String, Object> params=new HashMap<String,Object>();
    		// 设置id属性
    		params.put("id", 1);
    		// 调用EmployeeMapper接口的selectEmployeeByIdLike方法
    		List<Employee> list=em.selectEmployeeByIdLike(params);
    		// 查看返回结果
    		list.forEach(employee -> System.out.println(employee));
    	}
    
    	public void testSelectEmployeeByLoginLike(SqlSession session){
    		EmployeeMapper em=session.getMapper(EmployeeMapper.class);
    		HashMap<String, Object> params = new HashMap<String, Object>();
    		// 设置loginname和password属性
    		params.put("loginname", "jack");
    		params.put("password", "123456");
    		List<Employee> list = em.selectEmployeeByLoginLike(params);
    		list.forEach(employee -> System.out.println(employee));
    	}
    	
    	public void testSelectEmployeeChoose(SqlSession session){
    		EmployeeMapper em = session.getMapper(EmployeeMapper.class);
    		HashMap<String, Object> params = new HashMap<String, Object>();
    		// 设置id属性
    //		params.put("id", 1);
    //		params.put("loginname", "jack");
    //		params.put("password", "123456");
    		List<Employee> list = em.selectEmployeeChoose(params);
    		list.forEach(employee -> System.out.println(employee));
    	}
    	
    	public void testFindEmployeeLike(SqlSession session){
    		EmployeeMapper em = session.getMapper(EmployeeMapper.class);
    		HashMap<String, Object> params = new HashMap<String, Object>();
    		params.put("id", 1);
    		List<Employee> list = em.findEmployeeLike(params);
    		list.forEach(employee -> System.out.println(employee));
    	}
    	
    	public void testSelectEmployeeLike(SqlSession session){
    		EmployeeMapper em = session.getMapper(EmployeeMapper.class);
    		HashMap<String, Object> params = new HashMap<String, Object>();
    		// 设置state属性和id属性
    		params.put("id", 3);
    		params.put("loginname", "jack");
    		params.put("password", "123456");
    		List<Employee> list = em.selectEmployeeLike(params);
    		list.forEach(employee -> System.out.println(employee));
    	}
    	
    	public void testUpdateEmployeeIfNecessary(SqlSession session){
    		EmployeeMapper em=session.getMapper(EmployeeMapper.class);
    		//查询id为3的员工
    		Employee employee=em.selectEmployeeWithId(3);
    		//设置需要修改的属性
    		employee.setLoginname("mary");
    		employee.setPassword("1234");
    		employee.setName("玛丽");
    		em.updateEmployeeIfNecessary(employee);
    	}
    	
    	public void testSelectEmployeeIn(SqlSession session){
    		EmployeeMapper em = session.getMapper(EmployeeMapper.class);
    		// 创建List集合
    		List<Integer> ids = new ArrayList<Integer>();
    		// 往List集合中添加两个测试数据
    		ids.add(1);
    		ids.add(2);
    		List<Employee> list = em.selectEmployeeIn(ids);
    		list.forEach(employee -> System.out.println(employee));
    	}
    	
    	public void testSelectEmployeeLikeName(SqlSession session){
    		EmployeeMapper em = session.getMapper(EmployeeMapper.class);
    		Employee employee = new Employee();
    		// 设置模糊查询的参数
    		employee.setName("o");
    		List<Employee> list = em.selectEmployeeLikeName(employee);
    		list.forEach(result -> System.out.println(result));
    	}
    }

截图（发最后一个：模糊查询）

![Center][]

[Center]: /images/20220618/1e7ac78ef8fc4fbf94ddd277a50e1d55.png