Advanced dynamic SQL of SSM mybatis

Dynamic SQL is one of the powerful features of mybatis. If you have used JDBC or other similar frameworks, you should be able to understand how painful it is to splice SQL statements according to different conditions,

Dynamic SQL elements

After mybatis 3, the elements have been greatly simplified, mainly including the following:

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

if

The most common scenario for using dynamic SQL is to include a part of the where clause according to the condition. The following examples are given:

1. Write dao:

List getStudents(@Param("sex") String sex, @Param("pageIndex") int pageIndex, @Param("pageSize") int pageSize);

2. Write the mapper mapping file:



Syntax:Splicing SQL statements


3. Write test method validation:

@Test
public void getStudents() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    //Do not filter gender and do not page
    List students = mapper.getStudents("", 0, 0);
    Object jsonStr = JSON.toJSON(students);
    System.out.println(jsonStr);
    sqlSession.close();
}

Do not filter gender and do not page. The results are as follows:


---

Modify the test method, filter the performance ratio and verify as follows:

List students = mapper. Getstudents ("male", 0, 2);

choose (when, otherwise)

Sometimes, we don’t want to use all the conditions, but just want to use one of the conditions. Mybatis doesn’t provide else.

1. Write dao:

List getStudents2(@Param("sex") String sex, @Param("name") String name);

2. Write the mapper mapping file:



Key points: pay attention tolikeSplicing method of statements
Syntax:

Splice sql1
    
    
        Splice SQL2
    
    ...
    
        Splice default SQL

3. Write test method validation:

@Test
public void getStudents2() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    //Perform gender filtering only
    List students = mapper. Getstudents2 ("male", "");
    Object jsonStr = JSON.toJSON(students);
    System.out.println("[1]:" + jsonStr);
    //Perform name filtering only
    students = mapper. Getstudents2 ("", "Zhang");
    jsonStr = JSON.toJSON(students);
    System.out.println("[2]:" + jsonStr);
    //Parameters are passed, but only the first filter will be performed by search in sequence
    students = mapper. Getstudents2 ("female", "Zhang");
    jsonStr = JSON.toJSON(students);
    System.out.println("[3]:" + jsonStr);
    //No one will be transferred. The first one will be returned by default
    students = mapper.getStudents2("", "");
    jsonStr = JSON.toJSON(students);
    System.out.println("[4]:" + jsonStr);
    sqlSession.close();
}

trim (where, set)

Trim is mainly used to solve the problem of redundant keywords and symbols when splicing multiple statements. Mybatis has extracted common de redundant elements:,。The label mainly solves the problem of removing redundant and and or in the process of splicing and screening,Solve the problem of redundant commas in the assignment process.To illustrate（Similar)

1. Write dao:

List getStudents3(@Param("sex") String sex, @Param("name") String name);

2. Write the mapper mapping file:





3. Write test method validation:

@Test
public void getStudents3() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    //Perform gender filtering only
    List students = mapper. Getstudents3 ("male", "");
    Object jsonStr = JSON.toJSON(students);
    System.out.println("[1]:" + jsonStr);
    //Perform name filtering only
    students = mapper. Getstudents3 ("", "Zhang");
    jsonStr = JSON.toJSON(students);
    System.out.println("[2]:" + jsonStr);
    //Parameters are passed and filtered
    students = mapper. Getstudents3 ("female", "Li");
    jsonStr = JSON.toJSON(students);
    System.out.println("[3]:" + jsonStr);
    //No one, return all
    students = mapper.getStudents3("", "");
    jsonStr = JSON.toJSON(students);
    System.out.println("[4]:" + jsonStr);
    sqlSession.close();
}

foreach

Another common use case for dynamic SQL is to traverse a collection (especially when building in conditional statements).

1. Write dao:

List getStudentsForeach(@Param("sex") String sex, @Param("ids") int[] ids);

2. Write the mapper mapping file:



Collection can be list, set, array or map. When map is selected, index is the key and item is the value.

3. Write test method validation:

@Test
public void getStudentsForeach() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    int[] ids = new int[]{1, 2, 3};
    List students = mapper. Getstudentsforeach ("male", IDS);
    Object jsonStr = JSON.toJSON(students);
    System.out.println(jsonStr);
    sqlSession.close();
}