jpa query join fetch from a onetomany

Question

I have the following 2 classes using JTA transaction type with openjpa & a derby embedded db. What I want is to get the parent Organisation & the requested WSpace in one query when I only have the WSpace id. I am quite new to JPA so am learning as I go and have been struggling with 'q2' query. I have been using queries 'q0' & 'q1' to debug and check the items do exist in the db. 'q0' returns 1 object as does 'q1', whereas 'q2' returns 0 objects

I have tried a variety of entity setups and different queries but nothing has worked yet.

Orignally the WSpace class did not have an Organisation field as it didn't seem necessary for persisting or selecting, but I added it (along with the mappedby parameter) incase it was needed for the query to work, but nothing has changed.

back to the original question how can I get this to work so it returns the parent object with the single child being requested

SELECT o FROM Organisation o JOIN FETCH o.spaces w WHERE w.id = :id

Here are my classes

@Entity
public class Organisation implements MyObjects
{
    @Id
    @NotNull
    private Integer id;
    private String name;
    @OneToMany( mappedBy = "organisation",
        cascade = { CascadeType.PERSIST, CascadeType.MERGE } )
    private List<WSpace> spaces;

    //getters/setter below
}

And

@Entity
public class WSpace implements MyObjects
{
    @Id
    @NotNull
    private Integer id;
    private String name;
    @ManyToOne
    private Organisation organisation;
    @OneToMany
    private List<Application> apps;

    //getters/setter below
}

class DAO
{
    ...

    public void foo( Integer id )
    {
        ....
        String q0 = "SELECT o FROM Organisation o WHERE o.id = 49068";
        List<Organisation> res0 = em.createQuery( q0, Organisation.class ).getResultList();
        String q1 = "SELECT w FROM WSpace w WHERE w.id = " + id;
        List<WSpace> res1 = em.createQuery( q1, WSpace.class ).getResultList();
        String q2 = "SELECT o FROM Organisation o "
                + "JOIN FETCH o.spaces w WHERE w.id = " + id;
        List<Organisation> res2 = em.createQuery( q2, Organisation.class ).getResultList();
        ...
    }
}

doesn't make a difference, I also tried removing the ManyToOne back referece and used a JoinTable which is still returning 0. I am begining to suspect it isn't possible — APW, Mar 10 '16 at 14:41
for now I am just going to keep the ManyToOne back reference and call the `SELECT w FROM WSpace w WHERE w.id = :id` to get the Organisation from the WSpace. — APW, Mar 10 '16 at 16:16

score 0 · Answer 1 · answered Mar 10 '16 at 12:40

0

Have you tried to look in the logs for output of your q2 query? I am learning JPA too and was dealing with Criteria and QL queries quite recently. So after having pretty same problems with joins, I started checking logs and it was pretty clear, what the issues were, since logs showed up translated to SQL queries. Another thing to look, how are you generating your Entities? I used Netbeans generating it for me. Also, many to many relations mignt have helper class generated too, I saw it in one of the projects. Hope it helps..

answered Mar 10 '16 at 12:40

Mykola

192
7

where are the logs stored? nothing is getting pushed to command line? – APW Mar 10 '16 at 14:43
In my case I had Unit tests for all my CRUD methods, so SQL queries, issued by JPA were outputted into console (if I am not mistaken, or into the Glassfish server log ) In your case you can take a look on this post http://stackoverflow.com/a/4367283 or this one http://stackoverflow.com/a/5129495 for logging into IDE output window – Mykola Mar 10 '16 at 18:20

score 0 · Answer 2 · answered Mar 10 '16 at 22:27

The query you're looking for is probably this:

SELECT w FROM WSpace w LEFT JOIN FETCH w.organisation where w.id=:id

with query.setParameter("id", id); to bind the id parameter to the query. This effectively tells the persistence provider to fetch the WSpace.organisation relation while querying for WSpace entities in the same query. Using the LEFT [OUTER] keword (OUTER being optional) in front of the JOIN FETCH keywords tells your persistence provider to select WSpace objects even if there are no matching Organisation records for your WSpace.organisation relation.

jpa query join fetch from a onetomany

2 Answers2