EntityManager query by joinColumn

kasavbere picture kasavbere · Oct 16, 2012 · Viewed 12.2k times · Source

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.

Answer

kasavbere picture kasavbere · Oct 19, 2012

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();