Mybatis [12] – how to query mybatis multi criteria?

Time:2021-2-27

Many times, we need to pass in multiple parameters to the SQL statement, but if these parameters are not an object as a whole, what should we do? Here are two solutions for reference only.

1. Encapsulate multiple parameters into a map

To test the interface, we pass in a map where value is an object. Then we can put strings, numbers and a student object.

  @Test
  public void testselectStudentByNameAndAge(){
      Student stu=new Student("lallal", 1212, 40);
      Map<String,Object> map=new HashMap<String, Object>();
      map.put("nameCon", "hello");
      map.put("ageCon", 14);
      map.put("stu", stu);
      List<Student>students=dao.selectStudentByNameAndAge(map);
      if(students.size()>0){
          for(Student student:students)
              System.out.println(student);
      }
  }

Our SQL interface passes in a map, with key as string and value as object.

public List<Student>selectStudentByNameAndAge(Map<String,Object> map);

The following is an SQL statement. If value is a basic type, we need to use # {}, which must write the corresponding key. If value is an object, we need to write the corresponding keyKey. AttributeFor example#{stu.score}

<select id="selectStudentByNameAndAge" resultType="Student">
    select id,name,age,score from student where name like '%' #{nameCon} '%' and age> #{ageCon} and score>#{stu.score}
</select>

2. Use index to receive multiple parameters

Our test class is as follows, passing in two parameters:

  @Test
  public void testselectStudentByNameAndAgeV2(){
        Student stu=new Student("lallal", 1212, 40);
        List<Student>students=dao.selectStudentByNameAndAgeV2("hello",14);
        if(students.size()>0){
            for(Student student:students)
                System.out.println(student);
        }
    }

Using two parameters in SQL interface

public List<Student>selectStudentByNameAndAgeV2(String name,int age);

Index numbers can be used in SQL statements, such as # {0}. Subscripts start from zero and correspond to parameters one by one

<! -- accepts multiple parameters -- >
    <select id="selectStudentByNameAndAgeV2" resultType="Student">
        select id,name,age,score from student where name like '%' #{0} '%' and age> #{1}
    </select>

Personal understanding: if it’s a simple multi parameter, for example, if it doesn’t involve an object, you can use the index number directly. This looks simpler. If it involves an object, you need to use the attribute of the object instead of the index number. You need to use map. If you have more parameters, it’s more convenient to use map and has more advantages when modifying.

[about the author]
Qin Huai, official account.Qinhuai grocery store】The author points out that the road of technology is not for a moment, even though it is slow and continuous. The world hopes that everything will be fast and faster, but I hope I can take every step and write every article well. I look forward to communicating with you.

This article is only on behalf of their own (this rookie) learning accumulation records, or learning notes, if there is infringement, please contact the author to verify the deletion. No one is perfect, so is the article. The style of writing is immature. If you don’t have talent, don’t spray. If you have any mistakes, I hope you can point them out. Thank you very much~