Mybatis第六讲——动态SQL(1)

迈不过友情╰ 2022-03-16 01:36 223阅读 0赞

[Mybatis第五讲——关联一对多][Mybatis]

本讲介绍动态SQL之if和when两个语句。

目录结构：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lvbmdidXRpbmd4aWRl_size_16_color_FFFFFF_t_70][]

t\_student表：

![20190225213326336.png][]

Student.java:

package com.java1234.model;
    
    public class Student {
    
    	private Integer id;
    	private String name;
    	private Integer age;
    	
    	
    	public Student() {
    		super();
    	}
    	public Student(String name, Integer age) {
    		super();
    		this.name = name;
    		this.age = age;
    	}
    	
    	public Student(Integer id, String name, Integer age) {
    		super();
    		this.id = id;
    		this.name = name;
    		this.age = age;
    	}
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public Integer getAge() {
    		return age;
    	}
    	public void setAge(Integer age) {
    		this.age = age;
    	}
    	@Override
    	public String toString() {
    		return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    	}
    
    	
    }

StudentMapper.java:

package com.java1234.mappers;
    
    import java.util.List;
    import java.util.Map;
    
    import com.java1234.model.Student;
    
    public interface StudentMapper {
    
    	public List<Student> searchStudents(Map<String,Object> map);
    	public List<Student> searchStudents2(Map<String,Object> map);
    	
    }

StudentMapper.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.java1234.mappers.StudentMapper">
    
      <resultMap type="Student" id="StudentResult">
       <id property="id" column="id"></id>
       <result property="name" column="name"></result>
       <result property="age" column="age"/>
      </resultMap>
    
    	<select id="searchStudents" parameterType="Map" resultMap="StudentResult">
    	select * from t_student
    	where gradeId=#{gradeId}
    	<if test="name!=null">
    	and name like #{name}
    	</if>
    	<if test="age!=null">
    	and age = #{age}
    	</if>
    	</select>
    
    	<select id="searchStudents2" parameterType="Map" resultMap="StudentResult">
    	select * from t_student
    	<choose>
    	 <when test="searchby=='gradeId'">
    	  where gradeId=#{gradeId}
    	 </when>
    	 
    	 <when test="searchby=='name'">
    	  where name like #{name}
    	 </when>
    	 
    	<otherwise>
    	  where age=#{age}
    	</otherwise>
    	</choose>
    	
    	</select>
    </mapper>

测试：

package com.java1234.service;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.session.SqlSession;
    import org.junit.jupiter.api.AfterEach;
    import org.junit.jupiter.api.BeforeEach;
    import org.junit.jupiter.api.Test;
    
    import com.java1234.mappers.StudentMapper;
    import com.java1234.model.Student;
    import com.java1234.util.SqlSessionFactoryUtil;
    
    class MybatisTest {
    
    	private SqlSession sqlSession=null;
    	private StudentMapper studentMapper=null;
    	@BeforeEach
    	void setUp() throws Exception {
    		sqlSession=SqlSessionFactoryUtil.openSession();
    	    studentMapper=sqlSession.getMapper(StudentMapper.class);
    	}
    
    	@AfterEach
    	void tearDown() throws Exception {
    		sqlSession.close();
    	}
    
    	/**
    	 * if查找学生信息
    	 */
    	@Test
    	void searchStudent() {
    		Map<String,Object> map=new HashMap<String,Object>();
    		map.put("gradeId",2);
    		map.put("name", "%王%");
    		map.put("age",33);
    		List<Student> studentList=studentMapper.searchStudents(map);
    		for(Student s:studentList) {
    			System.out.println(s);
    		}
    	}
    
    	/**
    	 * when-otherwise查找学生信息
    	 */
    	@Test
    	void searchStudent2() {
    		Map<String,Object> map=new HashMap<String,Object>();
    		map.put("searchby", "name");
    		map.put("gradeId",1);
    		map.put("name", "%王%");
    		map.put("age",33);
    		List<Student> studentList=studentMapper.searchStudents2(map);
    		for(Student s:studentList) {
    			System.out.println(s);
    		}
    	}
    }

结果是符合预期的。

[Mybatis]: https://blog.csdn.net/yongbutingxide/article/details/87923085
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lvbmdidXRpbmd4aWRl_size_16_color_FFFFFF_t_70]: /images/20220316/a10aec7d265248d8a6c4fff67d9788dc.png
[20190225213326336.png]: /images/20220316/34ad62525feb45fbb974bb58eb3bbcbb.png