JPQL select from multiple tables

Question

I'm new to JPQL and having some trouble with what I think might be quite a simple query. Please could someone explain if the following is possible.

I have two unrelated tables: 'customers' and 'merchants'. They both contain a field called 'username'. Is there a query that would return me a 'username' record that appears in either table and that matches a value I supply?

Query query = entityManager.createQuery("select c from Customers c where c.username = :username");                 
query.setParameter("username", username_);

Something like this but for both tables.

Possible duplicate of http://stackoverflow.com/questions/10758169/joining-two-unrelated-tables-in-hibernate — Jayasagar, Nov 19 '13 at 05:20

score 0 · Answer 1 · answered Nov 19 '13 at 15:45

0

If this really does not work: (I think it does, at least with CriteriaQuery I am sure it does) Query query = entityManager.createQuery("select c from Customers c, Merchants m where c.username = :username and m.username = c.username");
query.setParameter("username", username_);

You can always do this: Query query = entityManager.createQuery("select c from Customers c where c.username = :username and c.username in (select m.username from Merchants m)");
query.setParameter("username", username_);

answered Nov 19 '13 at 15:45

Pau

803
1
6
12

Thanks for the answer and apologies for my delayed response. I can tell from your solution that I didn't explain my question well enough, though, so I'll have another go. I want to check whether my input matches either a customer **or** a merchant - i.e if there is a record in the customer table with the correct username, return that record. If there isn't then check the merchant table, and if there isn't a match there either then return nothing. I hope that is clearer. – Caiman Nov 24 '13 at 22:30

score 0 · Accepted Answer · answered Nov 26 '13 at 19:50

It sounds like you want to match a given username to either a Customer or a Merchant. Since the two tables are unrelated, to do this with a single query, I believe you would need to form a cartesian product (join every row of one table with every row in the other). As you might imagine, this could be very inefficient. You're better off doing two simple queries:

try {
  Customer c = em.createQuery("SELECT c FROM Customer c WHERE c.username = :username", Customer.class)
                 .setParameter("username", username)
                 .getSingleResult();

  // ... do something with the customer ...
} catch (NoResultException e) {
  // user wasn't found in the customer table, try merchant:
  try {
    Merchant m = em.createQuery("SELECT m FROM Merchant m WHERE m.username = :username", Merchant.class)
                   .setParameter("username", username)
                   .getSingleResult();

    // ... do something with the merchant ...
  } catch (NoResultException e) {
    // user wasn't found in either table
  }
}

_{Note: I'm assuming that username is unique within Customer and Merchant.}

Notice that this approach will only execute the second query if we don't find the user with the first. If you don't need to return the object (i.e. just need to check if it exists) then you could simplify the above with two count queries. Count queries always return a result, so you wouldn't need to catch the NoResultExceptions

I just want to add that if it were me, running into a situation like this would raise a red flag that perhaps I should rethink the design. For example, why don't `Customer` and `Merchant` both extend a common `User` class? If they did, you could simply query `User`. — DannyMo, Nov 26 '13 at 21:47
Thanks for the answer. I actually have a very similar solution, I just wondered if there was a more concise way to do it in JPQL. Regarding extending a common class, I'm confused as to how that would reduce the amount of JPQL code required. Are you saying that I could map all of the user tables to a `User` class and then run something like: `select u from User where u.username = :username`? — Caiman, Nov 27 '13 at 00:22
@Caiman Yeah, assuming `Customer` and `Merchant` _are_ users, you could move the common stuff such as username to a `User` superclass. You would still have `Customer` and `Merchant` which contain any of their specific attributes. I don't have the entire picture, so it might turn out that it's not a good change...it was just a thought. Check out the [tutorial](http://docs.oracle.com/javaee/7/tutorial/doc/persistence-intro002.htm#BNBQN) for more info. — DannyMo, Nov 27 '13 at 00:34

JPQL select from multiple tables

2 Answers2