2 Replies Latest reply: May 1, 2013 7:34 AM by JamesSutherland RSS

    Query - Hibernate to JPA/TopLink Conversion

    1005511
      Hi,

      I have some problems with a query. My query works fine in Hibernate but I have to work with TopLink.

      I have created the query below.

      int Language = 1;

      String hql = "SELECT DISTINCT g "
      +    "FROM GUI g "
      +    "JOIN FETCH g.gUINameAllocation gna "
      +    "JOIN FETCH g.elementAllocation gea "
      +    "JOIN FETCH gea.elementTextAllocation geta "
      +    "WHERE g.gUIID = " + java.lang.Integer.toString(GUI) + " "
      +    "AND gna.languageAllocation.languageID = " + java.lang.Integer.toString(Language)
      +    "AND geta.languageAllocation.languageID = " + java.lang.Integer.toString(Language);
      Query query = session.createQuery(hql);
      java.util.List<GUI> results = (java.util.List<GUI>)query.list();

      I have tried to translate the query to JPQL but it does not allow alias names for fetch joins. I have tried to translate it to the Criteria API classes but I will not work. I have tried the Criteria API with the join class and the fetch class.
      I can not use the fetch class in crit.where(...) expression. I can only use there the join classes as far as I understood.

      int Language = 1;

      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<GUI> crit = cb.createQuery(GUI.class);
      crit.distinct(true);
      Root<GUI> c = crit.from(GUI.class);
      Join<GUI, GUIName> guiname = c.join("gUINameAllocation", JoinType.LEFT);
      Join<GUIName, Language> language = guiname.join("languageAllocation");
      Join<GUI, Element> guielement = c.join("elementAllocation", JoinType.LEFT);
      Join<Element, ElementText> guielementtext = guielement.join("elementTextAllocation", JoinType.LEFT);
      Join<ElementText, Language> language1 = guielementtext.join("languageAllocation");
      crit.where(cb.equal(language.get("languageID"), Language));
      crit.where(cb.equal(language1.get("languageID"), Language));


      Has somebody an idea for a solution?


      Thx in advance
        • 1. Re: Query - Hibernate to JPA/TopLink Conversion
          Cdelahun-Oracle
          JPA does not allow alias names on fetch joins, I suspect partially because they could then be used in the filtering in the query - which is exactly what you are doing. This could cause potential caching issues, as the entity that is built might end up missing references that don't conform to the conditions in the where clause, as discussed here:
          http://www.coderanch.com/t/486454/ORM/databases/Join-Fetch-EclipseLink

          According to bug https://bugs.eclipse.org/bugs/show_bug.cgi?id=293775 this has been changed and is supported in EclipseLink 2.4 to some degree.

          If you cannot use EclipseLink 2.4 or later, the way to do this in JPQL is to use a separate join for the alias to be used in the query, and one for the fetch join.
          "SELECT DISTINCT g FROM GUI g "
          + "JOIN FETCH g.gUINameAllocation "
          + "JOIN FETCH g.elementAllocation "
          + "JOIN g.gUINameAllocation gna "
          + "JOIN g.elementAllocation gea "
          + "JOIN gea.elementTextAllocation geta "
          + "WHERE g.gUIID = " + java.lang.Integer.toString(GUI) + " "
          + "AND gna.languageAllocation.languageID = " + java.lang.Integer.toString(Language)
          + "AND geta.languageAllocation.languageID = " + java.lang.Integer.toString(Language);

          Unfortunately JPA does not allow nested fetch joins, so the only way to fecth gea.elementTextAllocation is through a query hint:
          query.setHint("eclipselink.join-fetch", "g.elementAllocation.elementTextAllocation");
          EclipseLink/TopLink's join-fetch query hint is described here:
          http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Query_Hints#Join_Fetch

          Best Regards,
          Chris
          • 2. Re: Query - Hibernate to JPA/TopLink Conversion
            JamesSutherland
            Both aliasing a join fetch, and nested join fetches are supported in EclipseLink 2.4.

            See,
            http://java-persistence-performance.blogspot.com/2012/05/jpql-vs-sql-have-both-with-eclipselink.html

            http://java-persistence-performance.blogspot.com/2012/04/objects-vs-data-and-filtering-join.html