You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Br...@mdsuk.com on 2007/04/02 17:54:10 UTC
Caching problems

Hi all,

        I'm having a bit of a problem with caching.  I have (I believe) 
set it up properly in my SqlMap file (see below).  I have a program that 
executes the 'getAllFull' query twice, however, and both times I am seeing 
the query being executed in the output log messages (see a little further 
below).

        Any help in getting this caching working would be greatly 
appreciated.

        A couple of additional points.  All of my attempts have been done 
using iBATIS 2.3.0.677.  I initially tried using Spring DAOs (using Spring 
2.0.3), and saw the same thing.  In order to eliminate the possibility 
that the problem originated in the Spring/iBATIS interface, I then 
re-implemented my DAO using iBATIS DAOs (2.2.0-638), however I am still 
seeing the same thing.  I am using the JDBC transaction manager and Oracle 
10.0.2 for my database.  The table I'm using is the EMP table in Oracle's 
scott/tiger demo data (well, and the DEPT table, to get the department 
name).  FYI, I've tried both with and without explicit transactions, and 
have seen no difference.  I'm fairly certain that I'm doing something 
wrong, I just don't know what.....

        Thanks in advance,

                Brendan


        Snippet from my SqlMap file:

        <cacheModel id='employee-cache' type='MEMORY' readOnly='true' 
serialize='false'>
                <flushInterval minutes='90'/>
                <flushOnExecute statement='updateEmployee'/>
                <property name='reference-type' value='WEAK'/>
        </cacheModel>
 
        <select id='getAllFull' resultMap='empl-full' cacheModel=
'employee-cache'>
                select a.empno, a.ename, a.job, b.ename, a.hiredate, 
a.sal, a.comm, c.dname
           from emp a
                   left outer join emp b on a.mgr = b.empno
                   join dept c on a.deptno = c.deptno
                   order by c.dname
        </select>

        Log output: 
2007-04-02 16:36:14,156 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] - 
Created connection 2814081.
2007-04-02 16:36:14,156 DEBUG [java.sql.Connection] - {conn-100000} 
Connection
2007-04-02 16:36:14,156 DEBUG [java.sql.Connection] - {conn-100000} 
Preparing Statement:    select a.empno, a.ename, a.job, b.ename, 
a.hiredate, a.sal, a.comm, c.dname         from emp a      left outer join 
emp b on a.mgr = b.empno      join dept c on a.deptno = c.deptno order by 
c.dname 
2007-04-02 16:36:14,375 DEBUG [java.sql.PreparedStatement] - {pstm-100001} 
Executing Statement:    select a.empno, a.ename, a.job, b.ename, 
a.hiredate, a.sal, a.comm, c.dname         from emp a      left outer join 
emp b on a.mgr = b.empno      join dept c on a.deptno = c.deptno order by 
c.dname 
2007-04-02 16:36:14,406 DEBUG [java.sql.PreparedStatement] - {pstm-100001} 
Parameters: []
2007-04-02 16:36:14,406 DEBUG [java.sql.PreparedStatement] - {pstm-100001} 
Types: []
2007-04-02 16:36:14,437 DEBUG [java.sql.ResultSet] - {rset-100002} 
ResultSet
2007-04-02 16:36:14,468 DEBUG [java.sql.ResultSet] - {rset-100002} Header: 
[EMPNO, ENAME, JOB, HIREDATE, SAL, COMM]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7782, CLARK, MANAGER, 1981-06-09, 2450, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7839, KING, PRESIDENT, 1981-11-17, 5000, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7934, MILLER, CLERK, 1982-01-23, 1300, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7566, JONES, MANAGER, 1981-04-02, 2975, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7902, FORD, ANALYST, 1981-12-03, 3000, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7876, ADAMS, CLERK, 1987-05-23, 1100, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7369, SMITH, CLERK, 1980-12-17, 800, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7788, SCOTT, ANALYST, 1987-04-19, 3630, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7521, WARD, SALESMAN, 1981-02-22, 1250, 500]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7844, TURNER, SALESMAN, 1981-09-08, 1500, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7499, ALLEN, SALESMAN, 1981-02-20, 1600, 300]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7900, JAMES, CLERK, 1981-12-03, 950, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7698, BLAKE, MANAGER, 1981-05-01, 2850, 0]
2007-04-02 16:36:14,484 DEBUG [java.sql.ResultSet] - {rset-100002} Result: 
[7654, MARTIN, SALESMAN, 1981-09-28, 1250, 1400]
2007-04-02 16:36:14,500 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] - 
Returned connection 2814081 to pool.
Found 14 employees
{Employee: {id=7782 name=CLARK title=MANAGER manager=KING hiredate=Tue Jun 
09 00:00:00 BST 1981 salary=2450 commission=null department=ACCOUNTING}}
{Employee: {id=7839 name=KING title=PRESIDENT manager=null hiredate=Tue 
Nov 17 00:00:00 GMT 1981 salary=5000 commission=null 
department=ACCOUNTING}}
{Employee: {id=7934 name=MILLER title=CLERK manager=CLARK hiredate=Sat Jan 
23 00:00:00 GMT 1982 salary=1300 commission=null department=ACCOUNTING}}
{Employee: {id=7566 name=JONES title=MANAGER manager=KING hiredate=Thu Apr 
02 00:00:00 BST 1981 salary=2975 commission=null department=RESEARCH}}
{Employee: {id=7902 name=FORD title=ANALYST manager=JONES hiredate=Thu Dec 
03 00:00:00 GMT 1981 salary=3000 commission=null department=RESEARCH}}
{Employee: {id=7876 name=ADAMS title=CLERK manager=SCOTT hiredate=Sat May 
23 00:00:00 BST 1987 salary=1100 commission=null department=RESEARCH}}
{Employee: {id=7369 name=SMITH title=CLERK manager=FORD hiredate=Wed Dec 
17 00:00:00 GMT 1980 salary=800 commission=null department=RESEARCH}}
{Employee: {id=7788 name=SCOTT title=ANALYST manager=SCOTT hiredate=Sun 
Apr 19 00:00:00 BST 1987 salary=3630 commission=null department=RESEARCH}}
{Employee: {id=7521 name=WARD title=SALESMAN manager=BLAKE hiredate=Sun 
Feb 22 00:00:00 GMT 1981 salary=1250 commission=500 department=SALES}}
{Employee: {id=7844 name=TURNER title=SALESMAN manager=BLAKE hiredate=Tue 
Sep 08 00:00:00 BST 1981 salary=1500 commission=0 department=SALES}}
{Employee: {id=7499 name=ALLEN title=SALESMAN manager=BLAKE hiredate=Fri 
Feb 20 00:00:00 GMT 1981 salary=1600 commission=300 department=SALES}}
{Employee: {id=7900 name=JAMES title=CLERK manager=BLAKE hiredate=Thu Dec 
03 00:00:00 GMT 1981 salary=950 commission=null department=SALES}}
{Employee: {id=7698 name=BLAKE title=MANAGER manager=KING hiredate=Fri May 
01 00:00:00 BST 1981 salary=2850 commission=null department=SALES}}
{Employee: {id=7654 name=MARTIN title=SALESMAN manager=BLAKE hiredate=Mon 
Sep 28 00:00:00 BST 1981 salary=1250 commission=1400 department=SALES}}
Done once...

2007-04-02 16:36:14,546 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] - 
Checked out connection 2814081 from pool.
2007-04-02 16:36:14,546 DEBUG [java.sql.Connection] - {conn-100003} 
Connection
2007-04-02 16:36:14,546 DEBUG [java.sql.Connection] - {conn-100003} 
Preparing Statement:    select a.empno, a.ename, a.job, b.ename, 
a.hiredate, a.sal, a.comm, c.dname         from emp a      left outer join 
emp b on a.mgr = b.empno      join dept c on a.deptno = c.deptno order by 
c.dname 
2007-04-02 16:36:14,546 DEBUG [java.sql.PreparedStatement] - {pstm-100004} 
Executing Statement:    select a.empno, a.ename, a.job, b.ename, 
a.hiredate, a.sal, a.comm, c.dname         from emp a      left outer join 
emp b on a.mgr = b.empno      join dept c on a.deptno = c.deptno order by 
c.dname 
2007-04-02 16:36:14,546 DEBUG [java.sql.PreparedStatement] - {pstm-100004} 
Parameters: []
2007-04-02 16:36:14,546 DEBUG [java.sql.PreparedStatement] - {pstm-100004} 
Types: []
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} 
ResultSet
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Header: 
[EMPNO, ENAME, JOB, HIREDATE, SAL, COMM]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7782, CLARK, MANAGER, 1981-06-09, 2450, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7839, KING, PRESIDENT, 1981-11-17, 5000, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7934, MILLER, CLERK, 1982-01-23, 1300, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7566, JONES, MANAGER, 1981-04-02, 2975, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7902, FORD, ANALYST, 1981-12-03, 3000, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7876, ADAMS, CLERK, 1987-05-23, 1100, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7369, SMITH, CLERK, 1980-12-17, 800, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7788, SCOTT, ANALYST, 1987-04-19, 3630, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7521, WARD, SALESMAN, 1981-02-22, 1250, 500]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7844, TURNER, SALESMAN, 1981-09-08, 1500, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7499, ALLEN, SALESMAN, 1981-02-20, 1600, 300]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7900, JAMES, CLERK, 1981-12-03, 950, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7698, BLAKE, MANAGER, 1981-05-01, 2850, 0]
2007-04-02 16:36:14,562 DEBUG [java.sql.ResultSet] - {rset-100005} Result: 
[7654, MARTIN, SALESMAN, 1981-09-28, 1250, 1400]
2007-04-02 16:36:14,562 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] - 
Returned connection 2814081 to pool.
Found 14 employees
{Employee: {id=7782 name=CLARK title=MANAGER manager=KING hiredate=Tue Jun 
09 00:00:00 BST 1981 salary=2450 commission=null department=ACCOUNTING}}
{Employee: {id=7839 name=KING title=PRESIDENT manager=null hiredate=Tue 
Nov 17 00:00:00 GMT 1981 salary=5000 commission=null 
department=ACCOUNTING}}
{Employee: {id=7934 name=MILLER title=CLERK manager=CLARK hiredate=Sat Jan 
23 00:00:00 GMT 1982 salary=1300 commission=null department=ACCOUNTING}}
{Employee: {id=7566 name=JONES title=MANAGER manager=KING hiredate=Thu Apr 
02 00:00:00 BST 1981 salary=2975 commission=null department=RESEARCH}}
{Employee: {id=7902 name=FORD title=ANALYST manager=JONES hiredate=Thu Dec 
03 00:00:00 GMT 1981 salary=3000 commission=null department=RESEARCH}}
{Employee: {id=7876 name=ADAMS title=CLERK manager=SCOTT hiredate=Sat May 
23 00:00:00 BST 1987 salary=1100 commission=null department=RESEARCH}}
{Employee: {id=7369 name=SMITH title=CLERK manager=FORD hiredate=Wed Dec 
17 00:00:00 GMT 1980 salary=800 commission=null department=RESEARCH}}
{Employee: {id=7788 name=SCOTT title=ANALYST manager=SCOTT hiredate=Sun 
Apr 19 00:00:00 BST 1987 salary=3630 commission=null department=RESEARCH}}
{Employee: {id=7521 name=WARD title=SALESMAN manager=BLAKE hiredate=Sun 
Feb 22 00:00:00 GMT 1981 salary=1250 commission=500 department=SALES}}
{Employee: {id=7844 name=TURNER title=SALESMAN manager=BLAKE hiredate=Tue 
Sep 08 00:00:00 BST 1981 salary=1500 commission=0 department=SALES}}
{Employee: {id=7499 name=ALLEN title=SALESMAN manager=BLAKE hiredate=Fri 
Feb 20 00:00:00 GMT 1981 salary=1600 commission=300 department=SALES}}
{Employee: {id=7900 name=JAMES title=CLERK manager=BLAKE hiredate=Thu Dec 
03 00:00:00 GMT 1981 salary=950 commission=null department=SALES}}
{Employee: {id=7698 name=BLAKE title=MANAGER manager=KING hiredate=Fri May 
01 00:00:00 BST 1981 salary=2850 commission=null department=SALES}}
{Employee: {id=7654 name=MARTIN title=SALESMAN manager=BLAKE hiredate=Mon 
Sep 28 00:00:00 BST 1981 salary=1250 commission=1400 department=SALES}}
Done second time.