CODEWITHSUNDEEP
jakarta-eejpaeclipselinkglassfish-4
drame
drame

Reputation: 545

Select with JOINED Inhertiance leads to no results

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?

Upvotes: 2

Views: 193

Answers (1)

Chris
Chris

Reputation: 21145

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.

Upvotes: 1

Related Questions