http://www.steveify.com/uncategorized/querying-selecting-from-multiple-tables-using-jpa/
Firstly, an example of querying a single table using the JPA Query Language (JPQL). Assume in all example that the em object is an instantiated EntityManager.
Query q = em.createQuery( "select c from Customer c" ); List<Customer> list = q.getResultList();
Here we have an entity called Customer, which represents our underlying customer table in our database. The above query selects all rows from the customer table. The resulting List could be used like this:
for (Customer cust : list)
{
System.out.println( "customer's name = " + cust.name );
}
We can iterate over the List and print the name value from each Customer entity.
Now, assuming we want to read from the customer and store tables in a single query but they have no foreign key relationship. Our query would now be:
Query q = em.createQuery( "select cus, st from Customer cus, Store st where cus.visited = st.name" ); List<Object[]> list = q.getResultList();
The query looks similar but we name the Customer and Store entities and define the join with thewhere clause. To use the ResultSet List is now also a little different as the List no longer contains Customer objects, each element is now an array containing a Customer and a Store. We can use it like:
for (Object[] objects : list)
{
Customer cust = (Customer)objects[0];
Store store = (Store)objects[1];
System.out.println( "Customer's name = " + cust.name );
System.out.println( "Store's address = " + store.address );
}
This technique is useful when creating HTML tables of search results that span across multiple tables that don’t have foreign keys to easily join them.
