06-动态SQL

动态SQL

xml方式

<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.Student">
  select * from students
  <where>	<!--使用where标签，MyBatis就会将该SQL动态拼接where子句了-->
    <if test="stuId != null">	<!--if标签的test属性做判断是否能获取到该属性，获取到就拼接包含的子句-->
      and stu_id = #{stuId}
    </if>
    <if test="stuName != null">
      and stu_name = #{stuName}
    </if>
  </where>
</select>

用于测试的关键代码片段，sqlSession为创建的SqlSession对象，其他代码省略

HashMap<String, Object> param = new HashMap<>();  //map的值也可以是其他数据类型
param.put("stuId", 1);
//param.put("stuName", "张三");
List<Student> students = sqlSession.selectList("student.dynamicSQL", param);
System.out.println(students);

注解方式

package com;

import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface StudentMapper {
  @Select(
      "<script>" +	//使用script标签即可像xml中一样书写
        "select * from students" +
        "<where>" +
          "<if test='stuId != null'> and stu_id = #{stuId}</if>" +  //and前面需要留有空格或换行，否则在拼接SQL时导致拼接无空格
          "<if test='stuName != null'> and stu_name = #{stuName}</if>" +
        "</where>" +
      "</script>"
  )
  List<Student> dynamicSQL(Map map);
}

用于测试的关键代码片段，sqlSession为创建的SqlSession对象，其他代码省略

StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
HashMap<String, Object> param = new HashMap<>();  //map的值也可以是其他数据类型
param.put("stuId", 1);
//param.put("stuName", "张三");
List<Student> students = studentMapper.dynamicSQL(param);
System.out.println(students);