7 - 动态SQL--where

where if
dao
在含有int类型的查询参数，最好设置为Integer,以便接收null值

List<Student> selectStudents(@Param("name") String name,
                             @Param("age") Integer age,
                             @Param("gender") GenderEnum gender);

mapper

 <select id="selectStudents" resultMap="studentMapper">
       select * from `student`
       <where>
           <if test="name != null">
               `name` like concat('%',#{name},'%')
           </if>
           <if test="age != null">
               and `age` = #{age}
           </if>
           <if test="gender != null">
               and `gender` = #{gender,typeHandler=genderTypeHandler}
           </if>
       </where>
    </select>

test

@Test
    public void test() {
        SqlSession session = factory.openSession();
        try {
            StudentMapper studentMapper = session.getMapper(StudentMapper.class);
//            Student student = new Student("hu",19,GenderEnum.getGender(1));
//            int affectedRows = studentMapper.insertStudent(student);
            String name = "h";
            int age = 20;
            GenderEnum gender = GenderEnum.getGender(2);
            List<Student> students = studentMapper.selectStudents(name, age,gender);
//            List<Student> students = studentMapper.selectStudents(name, age,null);
//            List<Student> students = studentMapper.selectStudents(name, null,gender);
//            List<Student> students = studentMapper.selectStudents(name, null,null);
//            List<Student> students = studentMapper.selectStudents(null, null,null);
            if(!students.isEmpty()){
                Iterator<Student> it = students.iterator();
                while(it.hasNext()){
                    Student student = it.next();
                    System.out.println(student);
                }
            }else{
                System.out.println("没有查到结果");
            }
        } finally {
            session.commit();
            session.close();
        }
    }