Previous replicated result in getResultList()

[Michael Goossens]

Hello,

I execute this following code two times once with pk = 4754 and once with pk = 4763. The variable result however will both times contain the exact same object, the one with pk 4754. How is this possible? Does JPA keep the previous result in its cache or something?

	public ProductArrangementHisto findHistoByPKAndDate(Long pk, DateTime startDate) {
		ProductArrangementHisto result = null;
		Query query = em.createNativeQuery(
				"SELECT * FROM CRS.V_CRS_PRODUCT_ARR" +
				" WHERE CRS_PRAR_PK = " + pk +
				" AND CRS_TRANSACTION_START <= ?" +
				" AND (PRAH_HISTO_CREATION > ? OR PRAH_HISTO_CREATION IS NULL)", "ProductArrangementView");
		query.setParameter(1, startDate.toDate(), TemporalType.TIMESTAMP);
		query.setParameter(2, startDate.toDate(), TemporalType.TIMESTAMP);

		List<ProductArrangementHisto> arrangementsFound = query.getResultList();
		if(!arrangementsFound.isEmpty()){
			result = arrangementsFound.get(0);
		}
		return result;
	}

[James Sutherland]

You seem to be using a ResultSetMapping, most likely an issue with it. Please include it.

Try executing the query without the ResultSetMapping.

[Michael Goossens]

This is the result mapping

@SqlResultSetMapping(name="ProductArrangementView", 
    entities={
        @EntityResult(entityClass=ProductArrangementHisto.class, 
        		discriminatorColumn = "VIEW",
        		fields={
            		@FieldResult(name="pk", column="CRS_PRAH_PK"),
		            @FieldResult(name="creationTime", column="PRAR_TMS_CREATION"),
		            @FieldResult(name="creationUserId", column="PRAR_ORIGIN_CREATION"), 
		            @FieldResult(name="lastUpdateTime", column="CRS_TRANSACTION_START"), 
		            @FieldResult(name="lastUpdateUserId", column="PRAR_ORIGIN_UPDATE"), 
		            @FieldResult(name="isBasicDataIncomplete", column="PRAR_IS_BASIC_DATA_INCOMPLETE"), 
		            @FieldResult(name="arrangementID", column="PRAR_ARRANGEMENT_ID"), 
		            @FieldResult(name="lifecycleStatus", column="PRAR_LIFE_CYCLE_STATUS"), 
		            @FieldResult(name="termType", column="PRAR_TERM_TYPE"), 
		            @FieldResult(name="validity.startDate", column="START_DATE"), 
		            @FieldResult(name="validity.endDate", column="END_DATE"), 
		            @FieldResult(name="version", column="PRAR_VERSION"), 
		            @FieldResult(name="histoCreationDate", column="PRAH_HISTO_CREATION"), 
		            @FieldResult(name="productArrangementPk", column="CRS_PRAR_PK"), 
		            @FieldResult(name="productType", column="PRAR_PRODUCT_TYPE"), 
		            @FieldResult(name="arrangementB", column="CRS_PRAR_PRAR_FK"), 
		            @FieldResult(name="currency.currencyCode", column="PRAR_CURRENCY"), 
		            @FieldResult(name="postalAddress", column="CRS_PRAR_POAD_FK"), 
		            @FieldResult(name="interestReroutingAccount", column="CRS_PRAR_PRAR_REAC_FK"), 
		            @FieldResult(name="language", column="PRAR_LANGUAGE")
            	})
           	}	
)

[Michael Goossens]

Something strange to note. The date in startDate is the same for both requests. And when I do -1 second on the date(for both requests) the result is correct.

[Michael Goossens]

Ok I think I have it. The primary key of the result is actually not unique. When we do -1 second on the date the keys returned in the two calls will be different. When I do not do the -1 second the keys will be the same. So I think JPA will say: "oh the same key" so we do not retrieve the second object, we just return the one in cache.

Is there a way to force a retrieve of the attributes on a getResultList() even if the primary key is the same as one of the objects in memory?

[James Sutherland]

You should not use a column as your Id that is not unique.
Instead of getting the entities, you could just get the data with your native query, and build the objects with the data if you need to.

If you are using EclipseLink, you can bypass the persistence context using the query hint, "eclipselink.maintain-cache"="false".