insert multiple rows and write-back id error

[elysia-mhy]

MyBatis version

3.5.3

Database vendor and version

Mysql8.0

Test case or example project

table def:

create table `ass_vehicle_brand` (
  `id`   int not null auto_increment,
  `name` varchar(255),
  primary key (`id`)
) engine = InnoDB charset = utf8mb4;

code: demo.zip

Steps to reproduce

modify the database configuration and run the main method of the DemoApplication

Expected result

print the inserted record ID in the console

Actual result

org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: java.lang.UnsupportedOperationException

[elysia-mhy]

after trying to modify the assignKeys method in the Jdbc3KeyGenerator class, the re-run result is normal

@SuppressWarnings("unchecked")
    private void assignKeys(Configuration configuration, ResultSet rs, ResultSetMetaData rsmd, String[] keyProperties,
                            Object parameter) throws SQLException {
        if (parameter instanceof ParamMap || parameter instanceof StrictMap) {
            // Multi-param or single param with @Param
            assignKeysToParamMap(configuration, rs, rsmd, keyProperties, (Map<String, ?>) parameter);
        } else if (parameter instanceof ArrayList && !((ArrayList<?>) parameter).isEmpty()
                && ((ArrayList<?>) parameter).get(0) instanceof ParamMap) {
            // modify code start
            ArrayList<ParamMap<?>> paramMaps = (ArrayList<ParamMap<?>>) parameter;
            if (paramMaps.size() == 1) {
                assignKeysToParamMap(configuration, rs, rsmd, keyProperties, paramMaps.get(0));
                return;
            }
            // modify code end
            // Multi-param or single param with @Param in batch operation
            assignKeysToParamMapList(configuration, rs, rsmd, keyProperties, ((ArrayList<ParamMap<?>>) parameter));
        } else {
            // Single param without @Param
            assignKeysToParam(configuration, rs, rsmd, keyProperties, parameter);
        }
    }

[harawata]

Hello @elysia-mhy ,

You are mixing multirow insert with batch operation which makes no sense.

If you use multirow insert, you don't have to use BATCH executor. See this test case.

If you use BATCH executor (which is recommended when the number of rows to insert is large), the mapper method parameter should be AssVehicleBrandEO, not List<AssVehicleBrandEO>. See this test case.

[elysia-mhy]

Hello@harawata In the case of a large amount of data inserted in batches by mysql, it will affect the insertion efficiency, I think to improve the efficiency of insertion through this secondary batching method, when I used this method in another project before, I found that I have achieved a significant performance improvement, and now it seems that this is not the correct usage, right?

[elysia-mhy]

Insert 1200 records in batches, the total data size is about 300MB, the database field is about 20, and there are 3 large fields of JSON type, it is relatively slow to insert in batches at once and insert one by one in a loop, so I tried this insertion method

[harawata]

@elysia-mhy ,

Batch size is important, but yours is not the right way. See this answer : https://stackoverflow.com/a/55518327/1261766

When using BATCH executor, executing insert statement does not send the data immediately (MyBatis internally calls java.sql.Statement#addBatch()). The driver sends the data when flushStatement is called ([java.sql.Statement#executeBatch()](https://docs.oracle.com/en/java/javase/20/docs/api/java.sql/java/sql/Statement.html#executeBatch()) is called internally).

Also, in case of mysql-connector-j, enabling rewriteBatchedStatements improves performance, usually.

[elysia-mhy]

@harawata Thank you for your help, I referred to the answer to stackoverflow mentioned above and modified it, and solved the problem