Issue with caching when using select type for an insert (and returning OUTPUT INSERTED.key)

[GoogleCodeExporter]

What version of the MyBatis are you using? Java  myBatis version 3.0.4

Please describe the problem.
I am using the functionality of being able to insert a record using the <select 
/> functionality in order to return the DB key.  I have places in my code where 
I am looping over a dataset and inserting multiple records, if any two records 
have identical data, myBatis will only insert the first one, any subsequent 
inserts with identical data it will just return me the cached key and not 
attempt to insert the data.

I have tried passing in the useCache=false and flushCache=true on the select 
statement but they are ignored.

Also these inserts are being used inside of a SQL transaction.  The only way I 
have found around this issue is to commit after each insert, but this is the 
incorrect way for us to do this as we then have to manually account for errors 
inserting and manually perform a rollback.

Please provide any additional information below.

Sample of the loop being used to insert multiple records (pseudo code, may not 
compile, actual code is working correctly besides the bug I am reporting):

SqlSessionFactory factory = Persistence.getSqlSessionFactory(dbProperties);
SqlSession session = factory.openSession(ExecutorType.REUSE, false);
try {
    MyDataObjectMapper mapper = session.getMapper(MyDataObjectMapper.class);
    while (i.hasNext()) {
        MyDataObject sci = (MyDataObject) i.next();

        // If sci is identical between inserts, it will not insert a new record, but return back the key from the previous insert
        String returnedKey = mapper.insert(sci);
    }
} catch (Exception ex) {

}

Here is a sample myBatis query I am using with the above code, once again, this 
is pseudo code:

<select id="insert" parameterType="map" resultType="string" useCache="false" 
flushCache="true">
    INSERT INTO
      FormLayout
        (
          Name,
          Value,
          Size
        )
        OUTPUT INSERTED.DAKey 
      VALUES
        (
          #{MyDataObject.Name,javaType=string,jdbcType=VARCHAR},
          #{MyDataObject.Value,javaType=FormFieldType,jdbcType=VARCHAR},
          #{MyDataObject.Size,javaType=int,jdbcType=NUMERIC}
        )
  </select> 

Original issue reported on code.google.com by mweid...@gmail.com on 3 Oct 2011 at 7:49

[GoogleCodeExporter]

There is a workaround better than commit.
Before each such @Select("INSERT ...")
you may perform @Update("SELECT 1") @Options(flushCase=true).
Sure, not so pretty, but at leat you can save your transaction logic.

Original comment by dmitry.m...@muranosoft.com on 16 Oct 2011 at 1:34

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I suppose this is failing because of local session cache. Use 
SqlSession.clearCache() after each statement.

You may also try with Jdbc3 key generation.

Original comment by eduardo.macarron on 6 Feb 2012 at 3:15

• Changed state: Invalid

[GoogleCodeExporter]

This issue is fixed in 3.0.6,
http://code.google.com/p/mybatis/issues/detail?id=404
@Options(useCache = false) should work for you.
You should probably check it.

Original comment by dmitry.m...@muranosoft.com on 9 Feb 2012 at 5:57

[GoogleCodeExporter]

Even mybatis3.1.1 has the bug.
Select query is getting cached. There is no way to disable cache of select 
query. Below is a test case,

1. open a mybatis session.
2. execute a select query.
3. Leave the connection open in some static variable.
4. delete all the records from table on which above select query is executed.
5. execute the same select query on above open mybatis session. It will show 
the same result, whereas actually all data from the table is deleted.

useCache in select tag does not work in this case also in code I've tried to 
clear cache using SqlSession.clearCache(). But even then I get same old value.

Please suggest what to do?

Original comment by shan2005...@gmail.com on 4 Jun 2012 at 8:00