好东西都是要拿出来分享的,最近在做的项目,有这样的需求
1.处理大量数据的插入(Replace into)
分析:A: 数据量大 ——》尽量减少连接mysql的次数(非常耗时),一次性插入。
B:数据可能有重复 ——》如果你采用先查出数据库所有的数据,再重写hashcode和equal用set集合进行重复过滤,难免也有点太过复杂与耗时。
<insert id="insertBatchByReplace" parameterType="java.util.List" >
Replace into book (
name,
price ,
pic,
description
)VALUES
<foreach collection="list" item="book" separator="," >
(#{book.name},
#{book.price},
#{book.pic},
#{book.description}
)
</foreach>
</insert>
@Mapper
public interface BookMapper {
List<Book> findAll();
int insertBatchByReplace(List<Book> list);
}
@RunWith(SpringRunner.class)
@SpringBootTest
public class BookMapperTest {
@Autowired
private BookMapper bookMapper;
@Test
public void testInsertBatchByReplace() {
DynamicDataSource.setDatabaseType(DataSourceType.lucene);
ArrayList<Book> list = Lists.newArrayList();
int count = 1 ;
while (count <= 10) {
Book book = new Book();
book.setName("testName " + count );
book.setPrice(0.0 + count );
book.setPic("testPic " + count );
book.setDescription("testDescription " + count );
count ++;
list.add(book);
if (count % 2 == 0) {
list.add(book);
}
}
int i=bookMapper.insertBatchByReplace(list);
Assert.assertTrue( i > 0 );
}
}
2.插入定时处理后发生溢流路段的speed结果(ON DUPLICATE KEY UPDATE ) 分析:发生溢流的路段数据量可能小,但是可能是insert可能是update, 你需要的只是该对象中的speed 字段与updateTime字段的变化 ——》如果采用根据主键 和索引 采用update语句,难免也要使用for循环
<insert id="insertWithUpdate" parameterType="com.mybatis.datasources.dataobject.Book">
insert into book (
name,
price ,
pic,
description
) VALUES (
#{name},
#{price},
#{pic},
#{description}
)
ON DUPLICATE KEY UPDATE
pic= #{pic},
description = #{description}
</insert>
int insertWithUpdate(Book book);
@Test
public void testInsertWithUpdate() {
DynamicDataSource.setDatabaseType(DataSourceType.lucene);
ArrayList<Book> list = Lists.newArrayList();
int count = 1 ;
while (count <= 10) {
Book book = new Book();
book.setName("testName " + count );
book.setPrice(0.0 + count );
book.setPic("testPic " + count + count);
book.setDescription("testDescription " + count + count );
count ++;
int i=bookMapper.insertWithUpdate(book);
Assert.assertTrue( i > 0 );
}
}