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

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

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

StudentMapper.java:

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：

测试：

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的用法一样。