The Bug of Jdbc3KeyGenerator when use DB2 database and insertBatch Method

[hepengju]

Environment:

• Database: DB2 V11
• Table id: auto-increment (GENERATED ALWAYS AS IDENTITY)
• JDBC: db2jcc4-4.21.29.jar
• Mybatis:
  • BatchExecutor.doFlushStatements()
  • jdbc3KeyGenerator.processBatch

public void processBatch(MappedStatement ms, Statement stmt, Collection<Object> parameters) {
    ResultSet rs = null;
    try {
      rs = stmt.getGeneratedKeys();
      final Configuration configuration = ms.getConfiguration();
      final TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
      final String[] keyProperties = ms.getKeyProperties();
      final ResultSetMetaData rsmd = rs.getMetaData();
      
      //**** Here rsmd is Null When use DB2 database!!! 
      //**** Then rsmd.getColumnCount() throw NPE !!
      
      TypeHandler<?>[] typeHandlers = null;
      if (keyProperties != null && rsmd.getColumnCount() >= keyProperties.length) {
        for (Object parameter : parameters) {
          // there should be one row for each statement (also one for each parameter)
          if (!rs.next()) {
            break;
      ...
}

• Handle

if (keyProperties != null && rsmd != null && rsmd.getColumnCount() >= keyProperties.length)

[harawata]

Hi @hepengju ,

As you didn't post how to reproduce the problem nor what actually happened, I assumed that you were trying to retrieve generated keys and getting an exception.

DB2 indeed returns null from getMetaData() when executing batch insert [1].
And you seem to suggest that MyBatis should silently skip assigning keys, but throwing an exception is the expected behavior because assigning keys failed.

Please let me know if I am misunderstanding something.

[1] Verified with this JDBC app.

[hepengju]

Hi @harawata ,

Your code accurately described my issue.
I suggest MyBatis should silently skip assigning keys, otherwise we can't execute batch insert in DB2 when we just want insert the imported values from excel or other sources.

// **baseService method in our project**
public boolean insertBatch(List<T> entityList, int batchSize) {
    if (CollectionUtils.isEmpty(entityList)) {
        throw new IllegalArgumentException("Error: entityList must not be empty");
    }
    
    // **openSession(ExecutorType.BATCH)** 
    try (SqlSession batchSqlSession = sqlSessionBatch()) {
        int size = entityList.size();
        String sqlStatement = sqlStatement(SqlMethod.INSERT_ONE);
        for (int i = 0; i < size; i++) {
            batchSqlSession.insert(sqlStatement, entityList.get(i));
            if (i >= 1 && i % batchSize == 0) {
            
                // **flush limited size** 
                batchSqlSession.flushStatements();
            }
        }
        batchSqlSession.flushStatements();
    } catch (Throwable e) {
        throw new MybatisPlusException("Error: Cannot execute insertBatch Method. Cause", e);
    }
    return true;
}

[harawata]

Hi @hepengju ,

So, you do NOT need to assign generated keys to the parameter objects?
MyBatis would not try to use Jdbc3KeyGenerator when useGeneratedKeys is false which is the default.

[hepengju]

Hi @harawata,
I got what you means.
Now I think I should suggest Mybatis-Plus slove this problem. Mybatis-Plus issue
But It maybe depend on Mybatis throw a GeneratedKeysException replaced NPE in this situation.

[harawata]

GeneratedKeysException is not a MyBatis class.
Could you post the stack trace?

[hepengju]

Hi @harawata ,

Yes, GeneratedKeysException is not a Mybatis class.
I thought again, this is not very suitable (create a new Class GeneratedKeysException ).
I don't know how to solve this problem perfectly.
Maybe it's perfect If there is a configurable option that tell Mybatis try to set the generatedKeys if DB support and ignore if DB don't support.

[harawata]

Hi @hepengju ,

I don't know how to solve this problem perfectly.

The exception is thrown because you or MyBatis-Plus sets useGeneratedKeys=true, so avoiding it is the solution.

Note that useGeneratedKeys can be set both globally and locally (i.e. per-statement).
http://www.mybatis.org/mybatis-3/configuration.html#settings
http://www.mybatis.org/mybatis-3/sqlmap-xml.html#insert_update_and_delete

Maybe it's perfect If there is a configurable option that tell `Mybatis' try to set the generatedKeys if DB support and ignore if DB don't support.

But DB2 does support generatedKeys.
As the JDBC app shows, getGeneratedKeys().getMetadata() returns null only in batch operations.

[harawata]

Hi @hepengju ,

I noticed that the issue you reported to MyBatis-Plus was closed.
baomidou/mybatis-plus#332
So, may I assume that your problem was resolved?

[hepengju]

@harawata
Sorry for late reply.
After several discusses finally I modified the source code of mybatis and then send it to our nexus with a new GAV to solve this "bug" in our project temporarily.

// add rsmd != null By HPJ
if (keyProperties != null && rsmd != null && rsmd.getColumnCount() >= keyProperties.length) {
...
}

[harawata]

@hepengju ,

That's unfortunate... I hope mybatis-plus team finds a better solution soon.
Anyway, I appreciate your follow-up!

I'll close this as I think MyBatis itself provides a way for users to configure this behavior already.