动态 SQL 实战

1
<select id="search" resultMap="UserMap">
2
    SELECT id, user_name, email, status, created_at FROM user
3
    <where>
4
        <if test="userName != null and userName != ''">
5
            AND user_name LIKE CONCAT('%', #{userName}, '%')
6
        </if>
7
        <if test="status != null">
8
            AND status = #{status}
9
        </if>
10
    </where>
11
    ORDER BY id
12
</select>
13

14
<insert id="batchInsert">
15
    INSERT INTO user (user_name, email, status) VALUES
16
    <foreach collection="list" item="u" separator=",">
17
        (#{u.userName}, #{u.email}, #{u.status})
18
    </foreach>
19
</insert>

1
package demo.mybatis;
2

3
import org.apache.ibatis.io.Resources;
4
import org.apache.ibatis.session.SqlSession;
5
import org.apache.ibatis.session.SqlSessionFactory;
6
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
7

8
import java.io.InputStream;
9
import java.util.List;
10

11
public class DynamicSqlDemo {
12
    public static void main(String[] args) throws Exception {
13
        try (InputStream in = Resources.getResourceAsStream("mybatis-config.xml")) {
14
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
15
            try (SqlSession session = factory.openSession(true)) {
16
                UserMapper mapper = session.getMapper(UserMapper.class);
17

18
                System.out.println("search name contains 'li':");
19
                mapper.search("li", null).forEach(System.out::println);
20

21
                System.out.println("search status=1:");
22
                mapper.search(null, 1).forEach(System.out::println);
23

24
                List<User> batch = List.of(
25
                        new User("diana", "diana@example.com", 1),
26
                        new User("eric", "eric@example.com", 1)
27
                );
28
                mapper.batchInsert(batch);
29
                System.out.println("batchInsert done, count=" + mapper.selectByStatus(1).size());
30

31
                for (User u : batch) {
32
                    if (u.getId() != null) {
33
                        mapper.deleteById(u.getId());
34
                    }
35
                }
36
                for (User u : mapper.search("di", null)) {
37
                    mapper.deleteById(u.getId());
38
                }
39
            }
40
        }
41
    }
42
}