EntityManager query by joinColumn

Question

I have a Login entity and a Customer entity. Login.username is a foreign key in the customer table. Hence the following line in the Java Customer POJO

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "username", nullable = false)
private Login login;

My question is this: Is there a simple way to query the customer table using username? Or must I first fetch login by username and then customer by login?

Here is the JPA criteria query. And, yes, I would prefer to use criteria query.

public Customer getCustomerByUsername(String username) throws EntityNotFoundException {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
    Root<Customer> root = criteriaQuery.from(Customer.class);
    Path<String> path = root.<String>get("username");
    criteriaQuery.where(criteriaBuilder.equal(path, username));
    return entityManager.createQuery(criteriaQuery).getSingleResult();
}

The line Path<String> path = root.<String>get("username") is throwing an exception saying that username ... is not present.

score 8 · Accepted Answer · answered Oct 19 '12 at 18:56

8

The correct solution with JPQL is

Query q = entityManager.createQuery("SELECT c FROM Customer c WHERE c.login.username = :username");
    q.setParameter("username", username);
    return (Customer) q.getSingleResult();

answered Oct 19 '12 at 18:56

kasavbere

5,873
14
49
72

score 3 · Answer 2 · edited Mar 13 '18 at 18:25

3

Query q = entityManager.createQuery("SELECT c FROM Customer c JOIN Login l ON c.login=l WHERE l.username = :username");
q.setParameter("username",username);
List<Customer> customerList = q.getResultList();

There are a few tricks to keep in mind: this all is about the objects, and not the underlying DB. So the names represent classes, instances, and fields, and they are all CaseSensitive... The error you got meant that your Customer class didn't have a userName field - which is true, since Login has that... Sadly, I can't test it, but the logic and intentions should be clear.

edited Mar 13 '18 at 18:25

Menuka Ishan

5,164
3
50
66

answered Oct 16 '12 at 06:53

ppeterka

20,583
6
63
78

After much research, I finally caved in and decided to use your suggestion. I get the following error: `... Caused by: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Error compiling the query [SELECT c FROM Customer c WHERE c.username = :username], line 1, column 33: unknown state or association field [username] of class [com.mysite.Customer].` Recall that my original problem is the `Login` and not `username` is a field in `Customer`. – kasavbere Oct 19 '12 at 18:23
1

I figured it out. As I mention in my response, the following works fine: `SELECT c FROM Customer c WHERE c.login.username = :username`. Up vote for helping. – kasavbere Oct 19 '12 at 20:21
@kasavbere hmm, this easy and nice solution didn't occur to me, even though I've been using JPA for years. But this is **exactly** how JPA is to be used! You can accept your own answer, it is allowed! – ppeterka Oct 20 '12 at 11:44

EntityManager query by joinColumn

2 Answers2