Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Query Results Cache with OneToMany and "Fetch Join"
Query Results Cache with OneToMany and "Fetch Join" [message #1729552] Fri, 15 April 2016 12:05 Go to next message
Rainer Schmand is currently offline Rainer SchmandFriend
Messages: 4
Registered: March 2016
Junior Member
the following scenario does not work like expected:

i am using eclipselink 2.6.2 and i have problem with my entity-mappings for translated content.

consider two tables "country" and "translation"

country:
COUNTRY_ID                      CODE                    TRANSLATION
1                                 DE                 100
2                                 FR                  200


translation:
TRANSLATION_ID                       LANGUAGE_ID                    CONTENT
         100                                  1                           Deutschland
         100                                  2                              Germany
         100                                  3                             Allemagne

         200                                  1                             Frankreich
         200                                  2                              France
         200                                  3                               France


Entity-Mapping "Country"
@NamedQueries({	
	@NamedQuery(
			  name = "Country.getAll", 
			  query = "SELECT c FROM Country c JOIN FETCH c.translations t WHERE t.languageId = :language",
			  hints={ 
				  @QueryHint(name = QueryHints.QUERY_RESULTS_CACHE, value = HintValues.TRUE)				
			  }
	)
})	
@Entity
public class Country  {

	@Id
	private String id;
	@Column
	private String code;
	
	@OneToMany
        @JoinColumn(name = "translationId",referencedColumnName="translation" )
        private List<Translation>translations;
	
	
	public static List<Country> getAll(final int languageID) {
		final Query userQuery = EntityManagerUtil.getEntityManager().createNamedQuery("Country.getAll");
		userQuery.setParameter("language", languageID);
		final List<Country>country= (List<Country>) userQuery.getResultList();
		return country;
	}	

       //	getters and setters omitted
}


Entity-Mapping "Translation"
@Entity
public class Translation {		
	
	@Id
        private Long translationId;
	@Id
       private int languageId;		
	@Column
	private String content;			
	
	//getters and setters	ommited
}


when calling "Country.getAll(1)" multiple times, everything is ok - the database is only hit once. but when the user changes the language and i call "Country.getAll(2);" translations never change; they always keep the previous ones although the right sql is logged.

when i take the sql with the logged parameters, the database gives me the desired results.

logged sql:
SELECT t1.COUNTRY_ID, t1.CODE, t0.TRANSLATION_ID, t0.LANGUAGE_ID, t0.CONTENT
  FROM TRANSLATION t0, COUNTRY t1   
  
  WHERE ( (t0.LANGUAGE_ID = 2) ) AND (t0.TRANSLATION = t1.TRANSLATION_ID) ) 


how to get the updated values in my entity? any hint or tip here would be very appreciated. thanks in advance!

rainer





[Updated on: Fri, 15 April 2016 12:16]

Report message to a moderator

Re: Query Results Cache with OneToMany and "Fetch Join" [message #1729600 is a reply to message #1729552] Fri, 15 April 2016 19:12 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
I'm not quite sure what you are saying. The query returns all country instances that have a Translation with the languageId specified - the returned country will have all the Translations associated to it at the time the query was first issued, not just the translation with the languageId specified.

If you want the translations filtered, you should not be using fetch joining; JPA requires queries to return the results as they appear in the database, while you seem to be asking for JPA to only build you a subsection of the model based on your query. Since JPA has to manage changes, it would never be able to tell what to do if you add a Translation to the returned Country - is it one that already exists in the database, or does it need to do something?

What you can instead do is query for the list of Translations, and use those to get the countries if needed.
"SELECT t, c FROM Country c JOIN c.translations t WHERE t.languageId = :language"
This will give you a list of Object[]s containing the Translations with the languageId you are looking paired with its associated Country, with no need to fetch the Country's list of Translations.
Re: Query Results Cache with OneToMany and "Fetch Join" [message #1729670 is a reply to message #1729600] Sun, 17 April 2016 16:10 Go to previous messageGo to next message
Rainer Schmand is currently offline Rainer SchmandFriend
Messages: 4
Registered: March 2016
Junior Member
Thank you for your clear answer. The only problem with your suggested query is, that the defintion for the result-cache will remain unaffected; The database is hit every time when using a query like "SELECT n1, n2, n3... FROM".

"Country.getAll", 
			  query = "SELECT t, c FROM Country c JOIN c.translations t WHERE t.languageId = :language",
                         
			  hints={ // this has no effect for above query
				  @QueryHint(name = QueryHints.QUERY_RESULTS_CACHE, value = HintValues.TRUE)				
			  }



I can't figure out how to to enable the results-cache for such queries. Caching is required because the the countrycode from country-table and the translated countrynames have to be shown on many web-pages.
The results-cache only works when using
"SELECT c FROM Country WHERE x = :y"

[Updated on: Sun, 17 April 2016 17:49]

Report message to a moderator

Re: Query Results Cache with OneToMany and "Fetch Join" [message #1729770 is a reply to message #1729670] Mon, 18 April 2016 18:24 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
I'm not able to check if/why the "SELECT t, c FROM Country c JOIN c.translations t WHERE t.languageId = :language" wouldn't be cached in the query cache, but you could either just return the translations and then query for the Country instances separately, or give the Translation a ManyToOne backpointer, and use fetching : "SELECT t FROM Translations t JOIN FETCH t.country c WHERE t.languageId = :language" .
Re: Query Results Cache with OneToMany and "Fetch Join" [message #1729787 is a reply to message #1729770] Tue, 19 April 2016 06:09 Go to previous message
Rainer Schmand is currently offline Rainer SchmandFriend
Messages: 4
Registered: March 2016
Junior Member
thank you chris. finally i am happy with the suggested ManyToOne Reference in the Translations-Entity:
@NamedQueries({			
	  @NamedQuery(
			  name = "Translations.getAllCountriesByLanguage", 
			  query = "SELECT t FROM Translations t JOIN FETCH t.country c WHERE t.languageId = :language",
			  hints={ 
				  @QueryHint(name = QueryHints.QUERY_RESULTS_CACHE, value = HintValues.TRUE),				  
			  }
	  )
	 })

@Entity
public class Translation {			
	@Id
        private Long translationId;
	@Id
       private int languageId;		
	@Column
	private String content;				

	@ManyToOne
      @JoinColumn(name = "translationId",referencedColumnName="countryName",   insertable=false, updatable=false )	
    private Country country;

}


@Entity
public class Country  {
	@Id
	private String id;
	@Column
	private String code;
       @Column
       private Long countryName; //references Translations.translationId	
}

[Updated on: Tue, 19 April 2016 06:11]

Report message to a moderator

Previous Topic:Problem when try to use @XmlInverseReference where reference is on abstract class
Next Topic:Non-foreign key added in batchfetch select
Goto Forum:
  


Current Time: Thu Mar 28 13:59:52 GMT 2024

Powered by FUDForum. Page generated in 0.03669 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top