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

小咪咪 2022-03-16 02:30 219阅读 0赞

[Mybatis第六讲——动态SQL(1)][Mybatis_SQL_1]

本讲我们来学习动态SQL之where语句，还有trim语句

StudentMapper.java:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lvbmdidXRpbmd4aWRl_size_16_color_FFFFFF_t_70][]

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="searchStudents3" parameterType="Map" resultMap="StudentResult">
    	select * from t_student
    	<where> 
    		<if test="gradeId!=null">
    		gradeId=#{gradeId}
    		</if>
    		<if test="name!=null">
    		and name like #{name}
    		</if>
    		<if test="age!=null">
    		and age = #{age}
    		</if>	
    	</where>
    </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>

主要SQL，where可以自动去掉where后面紧跟着的第一个and：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lvbmdidXRpbmd4aWRl_size_16_color_FFFFFF_t_70 1][]

测试：

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);
    		}
    	}
    	/**
    	 * where查找学生信息
    	 */
    	@Test
    	void searchStudent3() {
    		Map<String,Object> map=new HashMap<String,Object>();
    		map.put("gradeId",2);
    		map.put("name", "%王%");
    		map.put("age",33);
    		List<Student> studentList=studentMapper.searchStudents3(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);
    		}
    	}
    }

结果与预期相符。

trim语句：

在StudentMapper.xml中将where语句改为如下trim语句，和where语句作用相同。prefixOverrides是前缀覆盖，覆盖第一个and或者or，与where的用法一样。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lvbmdidXRpbmd4aWRl_size_16_color_FFFFFF_t_70 2][]

[Mybatis_SQL_1]: https://blog.csdn.net/yongbutingxide/article/details/87925357
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lvbmdidXRpbmd4aWRl_size_16_color_FFFFFF_t_70]: /images/20220316/d747e7a24ec4476ab563700f28d63707.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lvbmdidXRpbmd4aWRl_size_16_color_FFFFFF_t_70 1]: /images/20220316/423ff617563845c3991b60a06e9e8cb0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lvbmdidXRpbmd4aWRl_size_16_color_FFFFFF_t_70 2]: /images/20220316/29b467277d1a416cb9aa033af157fc3b.png