MyBatis Deep Dive - Dynamic SQL, Parameters, Foreach, and...

MyBatis Configuration Deep Dive

Core Configuration

Dynamic SQL

Dynamic SQL is an important feature of MyBatis, used to dynamically generate different SQL statements based on conditions. It allows developers to use logic structures similar to programming languages to construct SQL, solving the problem of SQL concatenation under complex query conditions and improving development efficiency and code readability.

Uses of Dynamic SQL

• Flexibility: Handle dynamically changing query conditions, such as form filter conditions from user interfaces.
• Avoid Redundancy: Can merge multiple similar SQL query logics through dynamic syntax, reducing code redundancy.
• Improve Efficiency: Generate corresponding SQL only when needed, avoiding loading unnecessary data.

Notes on Dynamic SQL

Handling null Values

MyBatis does not automatically filter null values; explicit judgment is required in SQL tags.

Readability of Complex Logic

Excessive dynamic SQL logic can make XML files overly complex; reasonable splitting is recommended.

Performance Issues

Dynamically generated SQL should minimize complexity to avoid impacting database query performance.

Debugging

You can enable MyBatis logging functionality to view generated SQL and ensure correctness.

---

Parameter Concatenation

We need to use different SQL statements for querying based on different entity values. For example, when ID is not empty, we can query by ID; when username is not empty, we also need to add username as a condition. This situation is common in our multi-condition combined queries.

<!-- Query all user info -->
<select id="selectList" resultType="icu.wzk.model.UserInfo">
    SELECT
        *
    FROM
        user_info
    <where>
        <if test="username != null and username != ''">
            and username=#{username}
        </if>
        <if test="password != null and password != ''">
            and password=#{password}
        </if>
        <if test="age != null and age != ''">
            and age=#{age}
        </if>
    </where>
</select>

Test Code

public class WzkIcu05 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);
        UserInfo userInfo = UserInfo
                .builder()
                .username("wzk")
                .build();
        List<UserInfo> dataList = userInfoMapper.selectList(userInfo);
        dataList.forEach(System.out::println);
        sqlSession.close();
    }
}

Execution Result Log

24/11/11 15:59:59 DEBUG UserInfoMapper.selectList: ==>  Preparing: SELECT * FROM user_info WHERE username=?
24/11/11 15:59:59 DEBUG UserInfoMapper.selectList: ==> Parameters: wzk(String)
24/11/11 15:59:59 DEBUG UserInfoMapper.selectList: <==      Total: 1
UserInfo(id=1, username=wzk, password=icu, age=18)

---

Loop Concatenation

We add a new method in UserMapper:

List<UserInfo> selectListByIdList(@Param("idList") List<Integer> idList);

Corresponding XML

<select id="selectListByIdList" parameterType="icu.wzk.model.UserInfo" resultType="icu.wzk.model.UserInfo">
    SELECT
        *
    FROM
        user_info
    <where>
        id IN
        <foreach collection="idList" open="(" close=")" separator="," index="index" item="item">
            #{item}
        </foreach>
    </where>
</select>

Test Code

public class WzkIcu06 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);
        List<Integer> idList = Arrays.asList(1, 2, 3);
        List<UserInfo> dataList = userInfoMapper.selectListByIdList(idList);
        dataList.forEach(System.out::println);
        sqlSession.close();
    }
}

Execution Result Log

24/11/11 16:15:16 DEBUG UserInfoMapper.selectListByIdList: ==>  Preparing: SELECT * FROM user_info WHERE id IN ( ? , ? , ? )
24/11/11 16:15:16 DEBUG UserInfoMapper.selectListByIdList: ==> Parameters: 1(Integer), 2(Integer), 3(Integer)
24/11/11 16:15:16 DEBUG UserInfoMapper.selectListByIdList: <==      Total: 3
UserInfo(id=1, username=wzk, password=icu, age=18)

foreach Tag Attribute Description

• collection: The collection element to iterate over; do not use #{} in the collection name
• open: The beginning part of the statement
• close: The ending part of the statement
• item: Each element during collection iteration, the generated iteration variable name
• separator: The separator character

---

Fragment Extraction

Extract repeated SQL and use include to reference it, ultimately achieving the purpose of SQL reuse.

We add a method to the UserInfoMapper interface:

UserInfo selectOneBySegment(UserInfo userInfo);

Corresponding XML

<sql id="SELECT_USER_INFO">
    SELECT * FROM user_info
</sql>

<select id="selectOneBySegment" parameterType="icu.wzk.model.UserInfo" resultType="icu.wzk.model.UserInfo">
    <include refid="SELECT_USER_INFO"></include>
    <where>
        id=#{id}
    </where>
</select>

Test Code

public class WzkIcu07 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);
        UserInfo userInfo = UserInfo
                .builder()
                .id(1L)
                .build();
        userInfo = userInfoMapper.selectOneBySegment(userInfo);
        System.out.println(userInfo);
        sqlSession.close();
    }
}

Execution Result Log

24/11/11 17:04:08 DEBUG UserInfoMapper.selectOneBySegment: ==>  Preparing: SELECT * FROM user_info WHERE id=?
24/11/11 17:04:08 DEBUG UserInfoMapper.selectOneBySegment: ==> Parameters: 1(Long)
24/11/11 17:04:08 DEBUG UserInfoMapper.selectOneBySegment: <==      Total: 1
UserInfo(id=1, username=wzk, password=icu, age=18)