Request EntityManager by joinColumn

Question

I have a Login object and a Customer object. Login.username is the foreign key in the client table. Hence the next line in Java Customer POJO

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

My question is this: is there an easy way to query the Customer table with username ? Or should I first get Login on username and then Customer on Login ?

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

 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 Path<String> path = root.<String>get("username") throws an exception saying that username ... is not present.

Answer 1

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

Answer 2

 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: all this applies to objects, not the underlying database. So the names represent classes, instances and fields, and they are all CaseSensitive ... The error you received meant that your Customer class did not have a userName field - this is true because the input has this ... Unfortunately, I cannot verify it, but the logic and intent should be clear.