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

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

目录结构：

t_student表：

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

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第六讲——动态SQL(1)

相关推荐