0

Good morning. I have been looking all over trying to answer this question.

If you have a table that has foreign keys to another table, and you want results from both tables, using basic sql you would do an inner join on the foreign key and you would get all the resulting information that you requested. When you generate your JPA entities on your foreign keys you get a @oneToone annotation, @oneToMany, @ManyToMany, @ManyToOne, etc over your foreign key columns. I have @oneToMany over the foreign keys and a corresponding @ManyToOne over the primary key in the related table column I also have a @joinedON annotation over the correct column... I also have a basic named query that will select everything from the first table. Will I need to do a join to get the information from both tables like I would need to do in basic sql? Or will the fact that I have those annotations pull those records back for me? To be clear if I have table A which is related to Table B based on a foreign key relationship and I want the records from both tables I would join table A to B based on the foreign key or

Select * From A inner Join B on A.column2 = B.column1

Or other some-such non-sense (Pardon my sql if it is not exactly correct, but you get the idea)... That query would have selected all column froms A and B where those two selected column... Here is my named query that I am using....

@NamedQuery(name="getQuickLaunch", query = "SELECT q FROM QuickLaunch q")

This is how I am calling that in my stateless session bean...

try
    {
        System.out.println("testing 1..2..3");
        listQL = emf.createNamedQuery("getQuickLaunch").getResultList();
        System.out.println("What is the size of this list: number "+listQL.size());
        qLaunchArr = listQL.toArray(new QuickLaunch[listQL.size()]);            

    }

Now that call returns all the columns of table A, but it lack's the column's of table B. My first instinct would be to change the query to join the two tables... But that kind of makes me think what is the point of using JPA then if I am just writing the same queries that I would be writing anyway, just in a different place. Plus, I don't want to overlook something simple. So what say you stack overflow enthusiasts? How does one get back all the data of joined query using JPA?

SoftwareSavant
  • 9,467
  • 27
  • 121
  • 195

1 Answers1

1

Suppose you have a Person entity with a OneToMany association to the Contact entity.

When you get a Person from the entityManager, calling any method on its collection of contacts will lazily load the list of contacts of that person:

person.getContacts().size();
// triggers a query select * from contact c where c.personId = ?

If you want to use a single query to load a person and all its contacts, you need a fetch in the SQL query:

select p from Person p 
left join fetch p.contacts
where ...

You can also mark the association itself as eager-loaded, using @OneToMany(lazy = false), but then every time a person is loaded (vie em.find() or any query), its contacts will also be loaded.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • would that be a fetch in the named query? – SoftwareSavant Apr 16 '12 at 14:30
  • Whether the query is named or not doesn't change anything. JPQL is JPQL. If you want a named query to do a fetch, then add the join fetch to the named query. – JB Nizet Apr 16 '12 at 14:40
  • Do I necessarily need a where there? Would I need to specify the ID join column that is required? So far, all the example that I have seen are a little vague on that – SoftwareSavant Apr 16 '12 at 15:14
  • If the collections are empty, it means that the mapping is wrong or that there are no child entities in the database, not that you missed a fetch join. – JB Nizet Apr 16 '12 at 16:20
  • I am getting some weird exception that I can't figure out... java.lang.InternalError: java.lang.reflect.InvocationTargetException I Don't know if you have every seen that when working with JPA's But I am stumped. – SoftwareSavant Apr 16 '12 at 18:02