How to turn on rewriteBatchedStatements=true in MyBatisPlus

MyBatis Plus enables rewriteBatchedStatements=true

Advantages of turning on rewriteBatchedStatements=true

In the MyBatis Plus framework, batch insertion is an efficient way to operate the database.

OpenrewriteBatchedStatements=true, many advantages can be obtained, thereby improving database insertion performance, reducing load, and simplifying code.

1. Improve performance

The traditional method of inserting a record at one time will lead to frequent interactions with the database server, increasing network overhead and processing delays.

In contrast, combining multiple data records into a batch statement for insertion through batch insertion reduces the number of interactions with the database server and significantly improves performance.

2. Reduce load

Each SQL statement needs to be parsed, verified, and executed on the database server. These operations will occupy system resources and consume time.

Through batch insertion, multiple INSERT statements are merged into one statement to execute, reducing the load on the database and improving the overall system response speed.

3. Transaction support

Batch insertion in MyBatis Plus will enable transaction management by default.

During the batch insertion process, if an insertion error is encountered, the entire batch insertion operation will be rolled back to ensure the consistency of the data. It is very important to use the service support. It can effectively prevent unexpected situations during the data insertion process from inconsistent or loss of data.

4. Simplify the code

Using the traditional entry-by-it-by-item insertion method, you need to manually write loop statements to traverse the data set and perform insertion operations one by one. Through batch insertion, the entire data collection can be directly passed to the database driver for processing, reducing the coding complexity and workload. This simplified code logic makes development more efficient.

To sum up, by openingrewriteBatchedStatements=trueUsing the batch insert feature in MyBatis Plus brings many advantages. It not only improves database operation performance, reduces load, but also supports transaction management and simplifies code logic. This capability is very valuable and practical in scenarios where large amounts of data insertion are processed.

Implementation method

In a project using MyBatis Plus, we need to configure it in the configuration file to enable itrewriteBatchedStatements=true。

Here is an example configuration example:

# Or (based on your configuration file type)=jdbc:mysql://localhost:3306/mydatabase?rewriteBatchedStatements=true
=your_username
=your_password
-class-name=

In the above configuration,The option sets the URL of the database connection and passesrewriteBatchedStatements=trueThe batch processing function of the MySQL driver is enabled.

This example assumes that you are using Spring Boot and storing the relevant configuration information inorin the file. Depending on your specific project and method, you may need to add this configuration in different configuration files, or use other methods to set it up.

Please make sure toIn-housemydatabaseReplace with your actual database name andandThe correct database username and password are provided in it.

By setting it in the configuration filerewriteBatchedStatements=true, you can enable batch insertion in MyBatis Plus and get the advantages it brings. In this way, when performing batch insert operations, MyBatis Plus will automatically merge multiple INSERT statements into one statement to execute, improving the performance and efficiency of database insertion.

Please note that the specific configuration methods may vary depending on the project environment, please make the corresponding configuration according to your actual situation.

Code Demo

When using MyBatis Plus to implement batch insertion, you need to define the batch insertion method at the Mapper layer, call the method at the Service layer, and perform specific business logic processing at the ServiceImpl layer.

Here is a complete example code:

Mapper layer

import ;
import ;

import ;

public interface UserMapper extends BaseMapper<User> {
    
    void batchInsert(@Param("list") List<User> userList);
}

In the above code, we define a UserMapper interface, inherited from BaseMapper.

In this interface, abatchInsertMethod, parameterList<User>Type userList.

Service layer

import ;

import ;

public interface UserService extends IService<User> {

    void batchInsertUsers(List<User> userList);
}

In the above code, we define a UserService interface, inherited from IService.

In this interface, addedbatchInsertUsersMethod, parameterList<User>Type userList.

ServiceImpl layer

import ;
import ;
import ;

import ;

@Service
@Transactional(rollbackFor = )
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Override
    public void batchInsertUsers(List<User> userList) {
        (userList);
    }
}

In the above code, we create a UserServiceImpl class, which inherits ServiceImpl and implements the UserService interface. existbatchInsertUsersIn the method, we call baseMapperbatchInsertMethod to perform batch insert operations.

In this example,@TransactionalAnnotation to start the transaction and passrollbackFor = An exception rollback policy is set to ensure that the batch insert operation has transaction support. Please note that in the ServiceImpl layer, we inject the UserMapper interface, which allows us to access the corresponding database operations through baseMapper.

Test Method

When you need to test the performance of inserting large amounts of data, you can add a test method to insert hundreds of thousands of pieces of data and calculate the execution time.

Here is an example test method:

import ;
import ;
import ;

import ;
import ;

@SpringBootTest
public class UserBatchInsertTest {
    
    @Autowired
    private UserService userService;

    @Test
    public void testBatchInsertPerformance() {
        // Create ten thousand user data        List&lt;User&gt; userList = new ArrayList&lt;&gt;();
        for (int i = 0; i &lt; 100000; i++) {
            User user = new User();
            // Set user attributes            ("User" + i);
            // Add to user list            (user);
        }

        long startTime = ();

        // Perform batch insertion operation        (userList);

        long endTime = ();
        long executionTime = endTime - startTime;
        ("Inserting 100,000 pieces of data takes time:" + executionTime + "ms");
    }
}

In the above code, we create aUserBatchInsertTestTest the class and inject it intoUserServiceThe interface is used for batch insertion operations.

existtestBatchInsertPerformanceIn the test method, we first created 10,000 pieces of user data, set user attributes in the loop, and add them to the user list. Then record the start time.

Then callMethod, perform batch insertion operation.

Finally, record the end time, calculate the execution time, and print the result.

By running this test method, you can test the performance of inserting 100,000 pieces of data and get the time it takes to insert operations. This allows you to test the efficiency and performance of the batch insert function.

Please note that when running large amounts of data insertion tests, it may cause a certain load to the database and system. Please adjust and optimize according to the specific situation.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.