Advanced dynamic SQL of SSM mybatis

Time:2022-6-19

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:
  2. List getStudents(@Param("sex") String sex, @Param("pageIndex") int pageIndex, @Param("pageSize") int pageSize);

  3. Write the mapper mapping file:

  4. Advanced dynamic SQL of SSM mybatis
    Syntax:Splicing SQL statements

  5. Write test method validation:
  6. @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:
    Advanced dynamic SQL of SSM mybatis


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

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

    Advanced dynamic SQL of SSM mybatis

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:
  2. List getStudents2(@Param("sex") String sex, @Param("name") String name);

  3. Write the mapper mapping file:

  4. Advanced dynamic SQL of SSM mybatis
    Key points: pay attention tolikeSplicing method of statements
    Syntax:

    Splice sql1
        
        
            Splice SQL2
        
        ...
        
            Splice default SQL

  5. Write test method validation:
  6. @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();
    }

    Advanced dynamic SQL of SSM mybatis

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:
  2. List getStudents3(@Param("sex") String sex, @Param("name") String name);

  3. Write the mapper mapping file:

  4. Advanced dynamic SQL of SSM mybatis

  5. Write test method validation:
  6. @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();
    }

    Advanced dynamic SQL of SSM mybatis

foreach

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

  1. Write dao:
  2. List getStudentsForeach(@Param("sex") String sex, @Param("ids") int[] ids);

  3. Write the mapper mapping file:

  4. Advanced dynamic SQL of SSM mybatis
    Collection can be list, set, array or map. When map is selected, index is the key and item is the value.

  5. Write test method validation:
  6. @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();
    }

    Advanced dynamic SQL of SSM mybatis