Hi Larry,
Thanks for taking the time and assisting me.
Changing readOnly="false" has as results that the cache isn't hit anymore:
DEBUG - Cache 'Employee.selectEmpNameCache': cache miss
DEBUG - {conn-100291} Preparing Statement: SELECT ....
DEBUG - {pstm-100294} Executing Statement: SELECT ....
DEBUG - {pstm-100294} Parameters: [2]
DEBUG - {pstm-100294} Types: [java.lang.Integer]
DEBUG - {rset-100295} ResultSet
DEBUG - {rset-100295} Header: [EmpId, ..
DEBUG - {rset-100295} Result: [2, ..
DEBUG - Cache 'Employee.selectEmpNameCache': stored object
'[EMAIL PROTECTED]'
When I ask for EmpId=2 again I get the same cache miss
Here is the full Log for two subsequent calls to the same statement with the
same parameter (1):
DEBUG [http-8084-Processor25] - Cache 'Employee.selectEmpNameCache': cache
miss
DEBUG [http-8084-Processor25] - {conn-100127} Preparing Statement:
SELECT EmplMain.EmpId, CompanyId, EmplName, Surname, NickName, Code, Email,
Telephone1, Telephone2, EmplMain.Occupation as Department FROM
EmplMain JOIN EmplProfile ON EmplProfile.EmpId = EmplMain.EmpId
WHERE EmplMain.EmpId=? AND EmplMain.IsActiveEmployee=1
DEBUG [http-8084-Processor25] - {pstm-100130} Executing Statement:
SELECT EmplMain.EmpId, CompanyId, EmplName, Surname, NickName, Code, Email,
Telephone1, Telephone2, EmplMain.Occupation as Department FROM
EmplMain JOIN EmplProfile ON EmplProfile.EmpId = EmplMain.EmpId
WHERE EmplMain.EmpId=? AND EmplMain.IsActiveEmployee=1
DEBUG [http-8084-Processor25] - {pstm-100130} Parameters: [1]
DEBUG [http-8084-Processor25] - {pstm-100130} Types: [java.lang.Integer]
DEBUG [http-8084-Processor25] - {rset-100131} ResultSet
DEBUG [http-8084-Processor25] - {rset-100131} Header: [EmpId, CompanyId,
EmplName, Surname, NickName, Code, Email, Telephone1, Telephone2,
Department]
DEBUG [http-8084-Processor25] - {rset-100131} Result: [1, 0, John, Brown,
John, 1, [EMAIL PROTECTED], , , null]
DEBUG [http-8084-Processor25] - Cache 'Employee.selectEmpNameCache': stored
object '[EMAIL PROTECTED]'
DEBUG [http-8084-Processor25] - Returned connection 18628899 to pool.
DEBUG [http-8084-Processor25] - Checked out connection 18628899 from pool.
DEBUG [http-8084-Processor25] - {conn-100132} Connection
DEBUG [http-8084-Processor25] - {conn-100132} Preparing Statement:
SELECT Appointment.*, OutStatus.Name as 'outStatus.name', OutStatus.Color as
'outStatus.color', OutStatus.backgroundColor as
'outStatus.backgroundColor', OutStatus.NotInOffice as
'outStatus.notInOffice', Resource.CompId as 'resource.compId',
Resource.Name as 'resource.name', Resource.Description as
'resource.description' ,AppointmentXEmployee.State FROM
Appointment JOIN AppointmentXEmployee ON
AppointmentXEmployee.AppointmentId= Appointment.AppointmentId
LEFT JOIN OutStatus ON OutStatus.OutStatusId = Appointment.OutStatusId
LEFT JOIN Resource ON Resource.ResourceId = Appointment.ResourceId
LEFT JOIN EmplMain as Creator ON Creator.EmpId = Appointment.EmpId
WHERE (
( (StartDateTime between ? and ?) or
(StopDateTime between ? and ?) )
OR ( StartDateTime<? and
StopDateTime>? ) )
AND AppointmentXEmployee.EmpId = ? AND
Creator.CompanyId = ? ORDER BY StartDateTime
DEBUG [http-8084-Processor25] - {pstm-100133} Executing Statement:
SELECT Appointment.*, OutStatus.Name as 'outStatus.name', OutStatus.Color as
'outStatus.color', OutStatus.backgroundColor as
'outStatus.backgroundColor', OutStatus.NotInOffice as
'outStatus.notInOffice', Resource.CompId as 'resource.compId',
Resource.Name as 'resource.name', Resource.Description as
'resource.description' ,AppointmentXEmployee.State FROM
Appointment JOIN AppointmentXEmployee ON
AppointmentXEmployee.AppointmentId= Appointment.AppointmentId
LEFT JOIN OutStatus ON OutStatus.OutStatusId = Appointment.OutStatusId
LEFT JOIN Resource ON Resource.ResourceId = Appointment.ResourceId
LEFT JOIN EmplMain as Creator ON Creator.EmpId = Appointment.EmpId
WHERE (
( (StartDateTime between ? and ?) or
(StopDateTime between ? and ?) )
OR ( StartDateTime<? and
StopDateTime>? ) )
AND AppointmentXEmployee.EmpId = ? AND
Creator.CompanyId = ? ORDER BY StartDateTime
DEBUG [http-8084-Processor25] - {pstm-100133} Parameters: [2007-03-12
00:00:00.0, 2007-03-18 23:59:59.0, 2007-03-12 00:00:00.0, 2007-03-18
23:59:59.0, 2007-03-12 00:00:00.0, 2007-03-18 23:59:59.0, 1, 0]
DEBUG [http-8084-Processor25] - {pstm-100133} Types: [java.sql.Timestamp,
java.sql.Timestamp, java.sql.Timestamp, java.sql.Timestamp,
java.sql.Timestamp, java.sql.Timestamp, java.lang.Integer,
java.lang.Integer]
DEBUG [http-8084-Processor25] - {rset-100134} ResultSet
DEBUG [http-8084-Processor25] - Cache 'Employee.selectEmpNameCache': cache
miss
DEBUG [http-8084-Processor25] - {conn-100132} Preparing Statement:
SELECT EmplMain.EmpId, CompanyId, EmplName, Surname, NickName, Code, Email,
Telephone1, Telephone2, EmplMain.Occupation as Department FROM
EmplMain JOIN EmplProfile ON EmplProfile.EmpId = EmplMain.EmpId
WHERE EmplMain.EmpId=? AND EmplMain.IsActiveEmployee=1
DEBUG [http-8084-Processor25] - {pstm-100135} Executing Statement:
SELECT EmplMain.EmpId, CompanyId, EmplName, Surname, NickName, Code, Email,
Telephone1, Telephone2, EmplMain.Occupation as Department FROM
EmplMain JOIN EmplProfile ON EmplProfile.EmpId = EmplMain.EmpId
WHERE EmplMain.EmpId=? AND EmplMain.IsActiveEmployee=1
DEBUG [http-8084-Processor25] - {pstm-100135} Parameters: [1]
DEBUG [http-8084-Processor25] - {pstm-100135} Types: [java.lang.Integer]
DEBUG [http-8084-Processor25] - {rset-100136} ResultSet
DEBUG [http-8084-Processor25] - {rset-100136} Header: [EmpId, CompanyId,
EmplName, Surname, NickName, Code, Email, Telephone1, Telephone2,
Department]
DEBUG [http-8084-Processor25] - {rset-100136} Result: [1, 0, John, Brown,
John, 1, [EMAIL PROTECTED], , , null]
DEBUG [http-8084-Processor25] - Cache 'Employee.selectEmpNameCache': stored
object '[EMAIL PROTECTED]'
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Larry Meadors
Sent: 16 March 2007 01:23 PM
To: [email protected]
Subject: Re: Retrieving wrong result out of the Cache.
Can you try changing your cache model definition to include and try it
again?
<cacheModel id="selectEmpNameCache" readOnly="false" type="LRU">
My guess is that between the first fetch and the second, the cached
data is altered, and then the altered version is fetched again.
Larry
On 3/14/07, Meindert <[EMAIL PROTECTED]> wrote:
>
>
>
>
> Hi there,
>
>
>
> My cache is returning the wrong ID, for instance the statement
>
> Select ID from X where ID=3 is returning not 3!
>
>
>
> Here are the code sniplets I have that are producing the wrong data (after
> the cache has been populated)
>
>
>
> Requesting the employee with diary.getDiaryEmpId()=3
>
> EmpName empName = (EmpName) queryForObject("selectEmpName",
> diary.getDiaryEmpId());
>
>
>
> The SQL
>
> <cacheModel id="selectEmpNameCache" type="LRU">
>
> <flushInterval hours="12"/>
>
> <flushOnExecute statement="updateEmployee"/>
>
> <property name="cache-size" value="25"/>
>
> </cacheModel>
>
> <select id="selectEmpName" parameterClass="int" resultClass="EmpName"
> cacheModel="selectEmpNameCache">
>
> SELECT EmplMain.EmpId, CompanyId, EmplName, Surname, NickName, Code,
> Email, Telephone1, Telephone2
>
> FROM EmplMain
>
> JOIN EmplProfile ON EmplProfile.EmpId = EmplMain.EmpId
>
> WHERE EmplMain.EmpId=#value# AND EmplMain.IsActiveEmployee=1
>
> </select>
>
>
>
> Results in fetching Employee out of cache
>
> DEBUG [http-8084-Processor22] - Cache 'Employee.selectEmpNameCache':
> retrieved object '[EMAIL PROTECTED]'
>
>
>
> The resulting object has as Empid 1 and not 3!
>
> All other field values are correct.
>
>
>
>
>
> MEINDERT HOVING
>
>
>
>
