How to not persist sqlSession.selectOne returned value?

My goal is to not persist the SqlSession.selectOne returned value. The problem is, if you do a sqlSession.selectOne (using an API endpoint) and then you edit the database directly for example using the MySQL workbench. After you edit the database and do another API endpoint request, the returned value will not change.

This is how you recreate the problem:

1. Do get request to the endpoint. The returned value is false.
2. Update the database so if you do a get request again, the returned value should be true. Instead it returned false.

The only way to fix this is to restart the server, which is not feasible.

This is how I do a sqlSession.selectOne:

1. boolean value = sqlSession.selectOne(params);
2. sqlSession.commit();
3. return value;

@Repository
public class Admin_LoginImpl {
  private SqlSession sqlSession;

  public Admin_LoginImpl() {
    Reader reader = null;
    try {
      reader = Resources.getResourceAsReader("mybatis-config.xml");
    } catch (IOException e) {
      e.printStackTrace();
    }
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    sqlSession = sqlSessionFactory.openSession();
  }

  public void createTableIfNotExists() {
    sqlSession.update("Admin_Login.createTableIfNotExists");
    sqlSession.commit();
  }

  public boolean exist(String User_ID) {
    boolean isExist = sqlSession.selectOne("Admin_Login.exist", User_ID);
    sqlSession.commit();
    return isExist;
  }
}

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="application.properties"/>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${spring.datasource.driver-class-name}"/>
                <property name="url" value="${spring.datasource.url}"/>
                <property name="username" value="${spring.datasource.username}"/>
                <property name="password" value="${spring.datasource.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="builder/User_LoginMapper.xml"/>
        <mapper resource="builder/User_InfoMapper.xml"/>
        <mapper resource="builder/User_DetailMapper.xml"/>
        <mapper resource="builder/TransactionsMapper.xml"/>
        <mapper resource="builder/Transactions_ProgressMapper.xml"/>
        <mapper resource="builder/StatementsMapper.xml"/>

        <mapper resource="builder/admin/Admin_LoginMapper.xml"/>
        <mapper resource="builder/admin/Admin_TransactionsMapper.xml"/>
    </mappers>
</configuration>

build/admin/Admin_LogginMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Admin_Login">
    <update id="createTableIfNotExists">
        CREATE TABLE IF NOT EXISTS `admin_login` (
        `ID` int NOT NULL AUTO_INCREMENT,
        `User_ID` char(12) NOT NULL,
        PRIMARY KEY (`ID`),
        UNIQUE KEY `User_ID_UNIQUE` (`User_ID`)
        ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    </update>
    <select id="exist" parameterType="String" resultType="boolean">
        SELECT EXISTS(SELECT 1 FROM admin_login WHERE User_ID=#{User_ID})
    </select>
</mapper>

What I’ve did:

1. tried to do sqlSession.close(); // this will lead to error.
2. tried to do sqlSession.clearCache(); // does not help.

I can’t afford to:

1. Remove the @Repository and manually create new object Admin_LoginImpl for every rest endpoint request. This will significantly increase the response time.

Answer

This problem happens because you create one SqlSession in Admin_LoginImpl and reuse it. This is not the recommended way to use mybatis. By default mybatis uses cache that is bound to session so if you repeat the same query it will return cached result.

Session creation is not a heavy weight operation and should be done once per request. The problem with performance that you faced is caused not by session creation by because SqlSessionFactory creation takes time.

To solve the problem you can keep SqlSessionFactory creation in the constructor of you repository but create SqlSession for every transaction.

Yet a better solution is to use MyBatis-Spring integration which will give you proper integration with spring and session management for free.