2

maybe I am overlooking something crucial when working with JPA/EclipseLink (Postgresql 9.2) and InheritanceStrategy.JOINED but this does not make sense to me.

I have a Hierarchy like this:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@ClassExtractor(ProjectClassExtractor.class)
public class Project extends AbstractEntity {

    @ManyToOne
    Company c;
}

and another Entity

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class MoreSpecificProject extends Project {
}

I now try to query for all Projects (including MoreSpecificProjects) that are assigned to a company given a set of companies.

The Criteria Code is as follows:

public List<Project> getProjectsWithCompany(Company company) {
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();

    CriteriaQuery<Project> query = cb.createQuery(Project.class);
    Root<Project> projectRoot = query.from(Project.class);
    query.select(projectRoot);

    query.where(cb.equal(projectRoot.get(Project_.c), company));
    query.distinct(true);
    return getEntityManager().createQuery(query).getResultList();
}

I have one Project in the database and the MoreSpecificProject table is empty. The Project has the company c set properly.

Now eclipseLink (Glassfish4.0) produces the following SQL (remark: I removed the long list of irrelevant columns):

SELECT DISTINCT t0.*, t1.*
FROM PROJECT t0, MORESPECIFICPROJECT t1 
WHERE ((t0.C_ID= 664) AND (t0.ID=t1.ID))

Obviously this returns no result, since there is no matching entry in MoreSpecificProject. But it should return the one Project that is in the Project table.

How do I get Eclipselink to generate the proper SQL (e.g. using a LEFT JOIN on MoreSpecificProject)?

Please help me on this one.

Thanks in advance.

EDIT: I found out that if I limit the result with 

 TypedQuery<Project> tQuery = entityManager.
            createQuery(query);
 tQuery.setFirstResult(0).setMaxResults(5);
 return tQuery.getResultList();

the correct SQL is beeing created:

SELECT  t0.*, t1.* FROM PROJECT t0 LEFT OUTER JOIN MORESPECIFICPROJECT  t1 ON (t1.ID = t0.ID) WHERE (t0.C_ID = 664) LIMIT 5 OFFSET 0

and the resultin list contains the correct project. Why does limiting the result set, lead to a correct query from the criteria API? May this be a bug?

drame
  • 435
  • 1
  • 4
  • 14
  • This isn't the only SQL it generates is it? By default, it should query the main table for the types that would be returned, then issue queries on the subclasses as needed. – Chris Jun 26 '14 at 15:25
  • Just noticed you are using a class extractor which makes it more difficult. Each class in the heirarchy should still be queried individually, so the Project instances should still be returned. – Chris Jun 26 '14 at 15:36
  • Well sadly it is. And the list returned from the method is empty. – drame Jun 26 '14 at 20:36

1 Answers1

1

According to http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Entities/Inheritance#Outer_Joining_Subclasses the default is to query all subclasses and join the results in memory. So you should see a query for each class that would be returned, not just the MoreSpecificProject class.

Chris
  • 20,138
  • 2
  • 29
  • 43
  • I should but the criteria API works in mysterious ways in this case. I only see this one SQL statement. The resulting list of getResultList() is empty. Even if there where more SQL statements, the result remains incomplete. – drame Jun 26 '14 at 20:37
  • I didn't try with JPQL since I wanted it to work with the criteria API. With JPQL I would have to specifiy the type of Join myself, or am I wrong? so I guess it would work with JPQL. But this doesn't help on the case of the criteria API producing wrong SQL/behaving strangely. – drame Jun 27 '14 at 06:18
  • I know successfully tried setting the query hint "eclipselink.inheritance.outer-join"="true" on the TypedQuery and also using a DescriptorCustomizer as proposed in the link you supplied. Both leads to the desired result and the correct join. – drame Jun 27 '14 at 07:15