【合集】Mybatis

MySQL存值逗号拼接的字符串，判断是否包含某个元素，比如a,b,c,d，怎么判断里面包含b,c？

先将前端传的b,c转Java List

1
2
3
4
5
6


<!-- https://mvnrepository.com/artifact/com.google.guava/guava -->
<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>33.2.1-jre</version>
</dependency>

1
2
3
4
5
6


import com.google.common.base.CaseFormat;
import com.google.common.base.CharMatcher;
import com.google.common.base.Splitter;

List<String> markIdList = Splitter.on(",").omitEmptyStrings().trimResults()
                .splitToList(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, "b,c"));

再通过mybaits查询

1
2
3
4
5
6
7


<!-- 在d.marks中是否能匹配到markList中一个元素。如markList为集合[b,c] d.star_mark_ids存值示例：a,b,c,d-->
<if test="markIdList != null and markIdList.size > 0">
    and
    <foreach item="markId" collection="markIdList" open="(" separator="or" close=")">
        (find_in_set(#{markId}, d.mark_id) > 0)
  </foreach>
</if>

Mybatis数据分批插入

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43


@Slf4j
public class 数据分批插入 {
    // 分批次批量插入
    public void testBatchInsertUser() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession();
        System.out.println("===== 开始插入数据 =====");
        long startTime = System.currentTimeMillis();
        int waitTime = 10;
        try {
            List<User> userList = new ArrayList<>();
            for (int i = 1; i <= 300000; i++) {
                User user = new User();
                user.setId((long) i);
                user.setName("共饮一杯无 " + i);
                user.setAge((int) (Math.random() * 100));
                userList.add(user);
                if (i % 1000 == 0) {
                    session.insert("batchInsertUser", userList);
                    // 每 1000 条数据提交一次事务
                    session.commit();
                    userList.clear();

                    // 等待一段时间
                    Thread.sleep(waitTime * 1000);
                }
            }
            // 最后插入剩余的数据
            if (!CollectionUtils.isEmpty(userList)) {
                session.insert("batchInsertUser", userList);
                session.commit();
            }

            long spendTime = System.currentTimeMillis() - startTime;
            System.out.println("成功插入 30 万条数据,耗时：" + spendTime + "毫秒");
        } catch (Exception e) {
            log.error("插入数据失败", e);
        } finally {
            session.close();
        }
    }
}

Mybatis批量查询

相关文章：list转map

获取list查询条件

1
2
3
4
5


List<String> keyList = Optional.ofNullable(list).orElse(new ArrayList<>())
        .stream().filter(Objects::nonNull)
        .map(s -> s.getValue()).filter(Objects::nonNull)
        .distinct()
        .collect(Collectors.toList());

查询调用

1
2
3
4
5
6


// list查询条件为空，返回空集合
List<Entity> entityList = ObjectUtils.isEmpty(keyList) ? new ArrayList<>() 
        : demoMapper.selectByKeyList(keyList);

// list查询条件为空，返回全部
List<Entity> entityList = demoMapper.selectByKeyList(keyList);

Mapper方法

1

List<Entity> selectByKeyList(@Nullable @Param("keyList") List<String> keyList);

xml方法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


<select id="selectByKeyList" resultType="com.zx.alice.entity.Demo">
    select 
        *
    from entity
    where deleted = 0
    <if test="keyList !=null and keyList.size > 0">
        and key in
        <foreach collection="keyList" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </if>
</select>

Mybatis批量更新

获取list更新条件

1
2
3
4
5


List<String> keyList = Optional.ofNullable(list).orElse(new ArrayList<>())
          .stream().filter(Objects::nonNull)
          .map(s -> s.getValue()).filter(Objects::nonNull)
          .distinct()
          .collect(Collectors.toList());

更新调用

1
2
3


if (!ObjectUtils.isEmpty(keyList)) {
    demoMapper.updateByKeyList(keyList);
}

Mapper方法

1

void updateByKeyList(@NotNull @Param("keyList") List<String> keyList);

xml方法

1
2
3
4
5
6
7
8


<update id="updateByKeyList">
    update entity
    set deleted = '1'
    where key in
    <foreach collection="keyList" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</update>