MyBatis Inner query

Question

In my code base, we have a MyBatis query like below,

INSERT INTO TABLE_A(
        ID,
        NAME,
        VERSION
    )
    VALUES  (
        #id#,
        #name#,
        (select NVL(max(version), 0 ) + 1 from SAE_PROC_SETTING where name = #name#)
    )

The unique constraint is on columns name and version. If multiple users are inserting values at same time, sometimes we are getting unique constraint error. When we checked, the name and version is coming as same and throwing the error as expected.

My question is, how MyBatis handles the inner query

(select NVL(max(version), 0 ) + 1 from TABLE_Awhere name = #name#)

Does this query is submitted by MyBatis to Oracle and then oracle first executes the SELECT and then INSERT query OR Does MyBatis executes the SELECT first, then substitute the value in INSERT and submits the INSERT query in Oracle (2 steps by MyBatis) ?

If it's handled like second option, when 2 people are inserting with same name at same TIME, there is a probability that both got the same version. Then when trying to insert, we will get UniqueConstraint error.

Please let me know how MyBatis handles this internally. Any pointers are fine.

Thanks, SD

Pau · Answer 1 · 2016-07-26T15:58:19.463

MyBatis sends the full statement (insert with inner select) to JDBC driver and is the database (in your case Oracle) which does this operation (first executes the SELECT and then INSERT). MyBatis just replaces your query params and then passes the query by a prepareStatement.

The log show the insert as one statement.

2016-07-26 17:34:23.776 DEBUG 91036 --- [           main] sample.mybatis.mapper.CityMapper.insert  : ==>  Preparing: insert into city (name, state, country) values ((select DISTINCT 'a' from city), 'CA', 'US'); 
2016-07-26 17:34:23.777 DEBUG 91036 --- [           main] sample.mybatis.mapper.CityMapper.insert  : ==> Parameters: 
2016-07-26 17:34:23.779 DEBUG 91036 --- [           main] sample.mybatis.mapper.CityMapper.insert  : <==    Updates: 1

Then I debug it and when It is called this method (source github) just pass for INSERT case:

public Object execute(SqlSession sqlSession, Object[] args) {
    Object result;
    switch (command.getType()) {

      case INSERT: {
        Object param = method.convertArgsToSqlCommandParam(args);
        result = rowCountResult(sqlSession.insert(command.getName(), param));
        break;
      }
      case UPDATE: {
        Object param = method.convertArgsToSqlCommandParam(args);
        result = rowCountResult(sqlSession.update(command.getName(), param));
        break;
      }
      case DELETE: {
        Object param = method.convertArgsToSqlCommandParam(args);
        result = rowCountResult(sqlSession.delete(command.getName(), param));
        break;
      }
      case SELECT:
       .....
          Object param = method.convertArgsToSqlCommandParam(args);
          result = sqlSession.selectOne(command.getName(), param);
       ....
        }
        break;
      case FLUSH:
        result = sqlSession.flushStatements();
        break;
      default:
        throw new BindingException("Unknown execution method for: " + command.getName());
    }
    ....
  }

MyBatis Inner query

1 Answers1