Mysql-Mybatisselect does not obtain the updated data (Cache ?)

I wrote a simple mybatisdemo without integrating other transaction management functions and connecting to the mysql database. This nested SQL statement is defined in the mapper of the table file_resources. the postinfo and poststatus tables & quot; selectcount (*) fromfile_resourceswhereobjectNamein (distinct (selectPostLinkfrom... mysqlmybatis caches the namespace database

I wrote a simple mybatis demo and did not integrate other transaction management functions to connect to the mysql database.

This nested SQL statement is defined in the mapper of the table file_resources. the postinfo and poststatus tables are used.

       select count(*)    from file_resources    where objectName in (SELECT PostID  FROM postinfo where PostLink not in (select PostLink from poststatus)                             group by postid having count(1) = 1)   

I have another thread to execute some processing. after one thread is completed, it commit one by one, and the number of poststatus data increases, so that the count obtained by the preceding SQL statement is continuously reduced, when I run this SQL statement on the mysql workbench console, the count is continuously reduced.

However, when I open a new thread and run the count SQL statement in the program, I find that the updated data cannot be obtained. the correct count is returned only when I run the first time, this count has not changed since then (but it is continuously reduced when running on the mysql workbench console)

This is the first version. sqlsession obtains the reusable open session with a single instance. at the beginning, it obtains mapper and then runs the SQL statement cyclically. Therefore, the updated count cannot be obtained.

        SqlSession refreshSession = SessionFactorySingleton.getInstance().getOpenSession();        FileResourcesMapper fileMapper = refreshSession.getMapper(FileResourcesMapper.class);        while(true){            int count = fileMapper.selectPendingPostItemCount();            Thread.sleep(50000);        }

Intuition is suspected to be a cache problem. Gu CODU Niang made some modifications and the problem persists.
1: sqlsession is not obtained using a single instance. Instead, the new open session is obtained using the SqlSessionFactoryBuilder method again, and the mapper is re-Retrieved each time to run the select statement. The problem persists.
2: After the selection is run every time, the problem persists.
3: modify mapper. in selectPendingPostItemCount, define flushCache and useCache to control level 1 and Level 2 Cache. the problem persists.

          select count(*)    from file_resources    where objectName in (SELECT PostID  FROM postinfo where PostLink not in (select PostLink from poststatus)                             group by postid having count(1) = 1)   

 //      SqlSession refreshSession = SessionFactorySingleton.getInstance().getOpenSession();        SqlSession refreshSession = getNewOpenSession(); //get new open session from new SqlSessionFactoryBuilder        FileResourcesMapper fileMapper = refreshSession.getMapper(FileResourcesMapper.class);        while(true){            int count = fileMapper.selectPendingPostItemCount();            refreshSession.clearCache();            refreshSession.commit();            fileMapper = refreshSession.getMapper(ColafileResourcesMapper.class);            Thread.sleep(50000);        }

After that, I went to view the code executed by the cache and SQL.

The cache obtained by MappedStatement in CachingExecutor is null, and clearLocalCache () in BaseExecutor is also called.
Finally, BaseExecutor also involve SimpleExecutor's doQuery (), which is the basic jdbc operation. the execute method of PreparedStatementHandler executes the SQL statement. It has nothing to do with the cache.
But the returned value is the old one .....

The debug log can confirm that the SQL statement has been submitted to connect to the database. at the same time, I also saw the execution of the SQL statement in the background. However, the returned value read from the program is not updated. (The same SQL statement is updated directly on the mysql interface in the background)

Our current method is to directly close the connection (refreshSession. close () after running the selectPendingPostItemCount () method of er ()). In the next query, you can re-obtain the sqlsession to run the select statement.

Do children's shoes with this convenient experience know what the problem is? Or you can give us some advice or direction to troubleshoot it. thank you very much.

I am checking whether it is a namespace problem. other files are nested in it. are the postinfo and poststatus tables cached?

 
     
        
     。。。。。。