51工具盒子MySQL存值逗号拼接的字符串，判断是否包含某个元素，比如a,b,c,d，怎么判断里面包含b,c？ {#mysql存值逗号拼接的字符串判断是否包含某个元素比如abcd怎么判断里面包含bc}
先将前端传的b,c转Java List
|-------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 | xml <!-- 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 | java 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 | xml <!-- 在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数据分批插入 {#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 | java @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批量查询 {#mybatis批量查询}
相关文章：list转map
获取list查询条件
|-----------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 | java 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 | java // list查询条件为空，返回空集合 List<Entity> entityList = ObjectUtils.isEmpty(keyList) ? new ArrayList<>() : demoMapper.selectByKeyList(keyList); // list查询条件为空，返回全部 List<Entity> entityList = demoMapper.selectByKeyList(keyList); |
Mapper方法
|---|---------------------------------------------------------------------------------------------|
| 1 | java List<Entity> selectByKeyList(@Nullable @Param("keyList") List<String> keyList); |
xml方法
|----------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 7 8 9 10 11 12 | xml <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批量更新 {#mybatis批量更新}
获取list更新条件
|-----------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 | java 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 | java if (!ObjectUtils.isEmpty(keyList)) { demoMapper.updateByKeyList(keyList); } |
Mapper方法
|---|------------------------------------------------------------------------------------|
| 1 | java void updateByKeyList(@NotNull @Param("keyList") List<String> keyList); |
xml方法
|-----------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 2 3 4 5 6 7 8 | xml <update id="updateByKeyList"> update entity set deleted = '1' where key in <foreach collection="keyList" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </update> |
