In actual development, batch insertion is a common method to improve data processing efficiency. MyBatis-Plus As an enhancement tool for MyBatis, it provides multiple ways to implement batch insertion. However, the default
saveBatchThe method is actually inserted one by one at the bottom, and its performance is not ideal. This article will introduce in detail how to achieve truly efficient batch insertion through MyBatis-Plus, including manual stitching of SQL, and useIServiceInterfaces and customizationinsertBatchSomeColumnand provide performance optimization suggestions.
1. Bulk insertion through XML manual stitching SQL
Advantages
- High efficiency: Perform batch insertion at one time to reduce the number of database interactions.
- flexible: Some fields can be optionally inserted as needed to reduce unnecessary data transmission.
shortcoming
- High maintenance cost: Each table needs to manually write the corresponding XML SQL.
- Automatic primary key generation is not supported: If there is a self-increment primary key in the table, additional processing is required.
Implementation steps
Writing Mapper XML files
byhistory_summaryAs an example, write batch insert XML:
<insert parameterType="">
INSERT INTO history_summary
(key_id, business_no, status, customer_id, instruction_id, customer_business_no, dept_id, doc_type_id, doc_page_no, document_version, result_mongo_doc_id, is_active, problem_status, tran_source, document_count_page, is_rush, is_deleted, document_tran_time, customer_tran_time, preprocess_recv_time, delete_time, create_time, complete_time, version, zip_name, document_no, new_task_type, handle_begin_time, handle_end_time, cost_seconds, char_num, ocr_result_mongo_id, reserve_text2, reserve_text3, reserve_text1, reserve_text4, reserve_text5, ocr_result_type, repeat_status, form_version, repetition_count)
VALUES
<foreach collection="list" item="item" separator=",">
(#{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{item.reserveText2}, #{item.reserveText3}, #{item.reserveText1}, #{item.reserveText4}, #{item.reserveText5}, #{}, #{}, #{}, #{})
</foreach>
</insert>Define batch insertion methods in the Mapper interface
public interface historySummaryMapper extends BaseMapper<historySummary> {
@Insert({
"<script>",
"INSERT INTO history_summary ",
"(key_id, business_no, status, customer_id, instruction_id, customer_business_no, dept_id, doc_type_id, doc_page_no, document_version, result_mongo_doc_id, is_active, problem_status, tran_source, document_count_page, is_rush, is_deleted, document_tran_time, customer_tran_time, preprocess_recv_time, delete_time, create_time, complete_time, version, zip_name, document_no, new_task_type, handle_begin_time, handle_end_time, cost_seconds, char_num, ocr_result_mongo_id, reserve_text2, reserve_text3, reserve_text1, reserve_text4, reserve_text5, ocr_result_type, repeat_status, form_version, repetition_count)",
"VALUES ",
"<foreach collection='list' item='item' index='index' separator=','>",
"(#{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{}, #{item.reserveText2}, #{item.reserveText3}, #{item.reserveText1}, #{item.reserveText4}, #{item.reserveText5}, #{}, #{}, #{}, #{})",
"</foreach>",
"</script>"
})
int insertBatch(@Param("list") List<historySummary> list);
}Calling batch insertion methods at the Service layer
@Service
public class historySummaryServiceImpl extends ServiceImpl<historySummaryMapper, historySummary> implements IhistorySummaryService {
@Autowired
private historySummaryMapper mapper;
@Transactional(rollbackFor = )
public boolean batchInsert(List<historySummary> list) {
int result = (list);
return result > 0;
}
}Example of usage
@RestController
@RequestMapping("/api/business")
public class BusinessController {
@Autowired
private IhistorySummaryService service;
@PostMapping("/batchInsert")
public ResponseEntity<String> batchInsert(@RequestBody List<historySummary> list) {
boolean success = (list);
if (success) {
return ("Batch insertion successful");
} else {
return (HttpStatus.INTERNAL_SERVER_ERROR).body("Batch insert failed");
}
}
}My scalp must be numb when I see this. This is also why I want to share this article! ! !
It involves multi-field batch insertion, orrecommendThe following method.
2. Use the saveBatch method of the MyBatis-Plus IService interface
Provided by MyBatis-PlussaveBatchThe method simplifies the operation of batch insertion, but its underlying layer is actually insertion by piece, so it does not perform well when processing large amounts of data.
Advantages
- Simple and easy to use: No need to manually write SQL, just call the interface method directly.
- Automatic transaction management: Built-in transaction support to ensure data consistency.
shortcoming
- Limited performance: The bottom layer is inserted one by one, which is less efficient when facing large data volumes.
- Limited batch size: The default batch size may not be applicable to all scenarios and needs to be adjusted manually.
Methods to improve performance
Configure database connection parameters
Add in the connection URL of the databaserewriteBatchedStatements=true, enable batch rewriting function to improve batch insertion performance.
=jdbc:mysql://localhost:3306/your_database?rewriteBatchedStatements=true
Adjust batch size
CallingsaveBatchWhen using the method, set the batch size reasonably (such as 1,000 batches) to balance performance and resource consumption.
@Transactional(rollbackFor = {})
public boolean saveBatch(Collection<T> entityList, int batchSize) {
String sqlStatement = (SqlMethod.INSERT_ONE);
return (entityList, batchSize, (sqlSession, entity) -> {
(sqlStatement, entity);
});
}3. InsertBatchSomeColumn method implements batch insertion
For truly efficient batch insertion, you can useinsertBatchSomeColumnMethod to realize one-time batch insertion.
Advantages
- High efficiency: Perform batch insertion at one time to reduce the number of database interactions.
- flexible: Selectively insert some fields to optimize data transmission. (via entity class attribute)
Implementation steps
1. Customize SQL injector to implement DefaultSqlInjector and add InsertBatchSomeColumn method
public class MySqlInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
List<AbstractMethod> methodList = (mapperClass, tableInfo);
(new InsertBatchSomeColumn(i -> () != ));
return methodList;
}
}2. Inject MySqlInjector into the bean
@Configuration
public class MyBatisConfig {
@Bean
public MySqlInjector sqlInjector() {
return new MySqlInjector();
}
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//Add a paging plugin (new PaginationInnerInterceptor());
//Add optimistic lock plugin (new OptimisticLockerInnerInterceptor());
return interceptor;
}
}3. Inherit the BaseMapper of Mybatis-plus and add the insertion method
public interface MyBaseMapper<T> extends BaseMapper<T> {
int insertBatchSomeColumn(Collection<T> entityList);
}@Mapper
public interface WorkingBusinessHistoryMapper extends MyBaseMapper<BusinessHistory> {
}Things to note
- Batch size: According to the performance of the database and application, set the size of batch inserts reasonably to avoid memory overflow or excessive database pressure by inserting too much data in a single time.
- Transaction Management: Ensure batch operations are executed in transactions to ensure data consistency and integrity.
- Error handling: In batch operations, if a record is inserted fails, a corresponding mechanism needs to roll back or record the failure information.
4. Performance optimization suggestions
In order to further improve the performance of batch insertion, the following optimization measures can be taken:
1. Turn on the batch rewrite function
Add in the connection URL of the databaserewriteBatchedStatements=true, to optimize the performance of batch inserts.
=jdbc:mysql://localhost:3306/your_database?rewriteBatchedStatements=true
2. Set the batch size reasonably
Adjust the batch size (such as 1,000 batches) according to the specific business scenario and database performance to avoid excessive data insertion in a single time.
int batchSize = 1000;
for (int i = 0; i < (); i += batchSize) {
List<historySummary> batchList = (i, (i + batchSize, ()));
(batchList);
}3. Use transaction management
Ensure batch operations are performed in transactions to avoid data inconsistencies caused by partial insertion.
@Transactional(rollbackFor = )
public boolean batchInsert(List<historySummary> list) {
int result = (list);
return result > 0;
}4. Index optimization
For tables with frequent insertion, design indexes reasonably to avoid excessive unnecessary indexes affecting insertion performance. Minimize the number of indexes during batch insertion, and create the necessary index after the insertion is completed.
5. Disable automatic submission
During batch insertion, automatic commit is disabled to reduce the number of transaction commits and improve performance.
((ConnectionCallback<Void>) connection -> {
(false);
// Perform batch insertion operation ();
return null;
});refer to
MybatisPlus custom insertBatchSomeColumn implements true batch insert - CSDN Blog
How to implement insertBatchSomeColumn for batch increase in MybatisPlus - Yisu Cloud
MyBatis-Plus official documentation
MySQL Connector/J Documentation
This is the article about MybatisPlus real batch insertion. For more related content on MybatisPlus, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!