Simple where condition for JPA CriteriaQuery

Question

So this is my first attempt to use JPA and a CriteriaQuery.

I have the following (simplified) entities:

@Entity
@Table(name = "hours")
@XmlRootElement
public class Hours implements Serializable
{
    @EmbeddedId
    protected HoursPK hoursPK;

    @Column(name = "total_hours")
    private Integer totalHours;

    @JoinColumn(name = "trainer_id", referencedColumnName = "id", nullable = false, insertable = false, updatable = false)
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    private Trainer trainer;

    public Hours()
    {
    }

    ... getter and setter for the attributes
}

@Embeddable
public class HoursPK implements Serializable
{
    @Basic(optional = false)
    @Column(name = "date_held", nullable = false)
    @Temporal(TemporalType.DATE)
    private Date dateHeld;

    @Basic(optional = false)
    @Column(name = "trainer_id", nullable = false, length = 20)
    private String trainerId;

    @Column(name = "total_hours")
    private Integer totalHours;


    public HoursPK()
    {
    }

    ... getter and setter ...
}

@Entity
@Table(name = "trainer")
public class Trainer implements Serializable
{
    @Id
    @Basic(optional = false)
    @Column(name = "id", nullable = false, length = 20)
    private String id;

    @Basic(optional = false)
    @Column(name = "firstname", nullable = false, length = 200)
    private String firstname;

    @Basic(optional = false)
    @Column(name = "lastname", nullable = false, length = 200)
    private String lastname;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "trainer", fetch = FetchType.LAZY)
    private List<Hours> hoursList;

    ... more attributes, getters and setters

    @XmlTransient
    public List<Hours> getHoursList() {
       return hoursList;
    }

    public void setHoursList(List<Hours> hoursList) {
      this.hoursList = hoursList;
    }
}

Essentially a Trainer holds trainings and the hours spent in the trainings are stored in the Hours entity. The PK for the hours table is (trainer_id, date_held) as each trainer only holds one training per day.

I am trying to create a CriteriaQuery to fetch all hours of a trainer for a specific month. This is my attempt:

EntityManagerFactory emf = ...
EntityManager em = emf.createEntityManager();
CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery<Hours> c = builder.createQuery(Hours.class);

Root<Hours> root = c.from(Hours.class);

Calendar cal = Calendar.getInstance();
cal.set(2014, 0, 1);
Expression<Date> from = builder.literal(cal.getTime());

cal.set(2014, 1, 1);
Expression<Date> to = builder.literal(cal.getTime());

Predicate who = builder.equal(root.get(Hours_.trainer), "foobar"); // it fails here

Predicate gt = builder.greaterThanOrEqualTo(root.get(Hours_.hoursPK).get(HoursPK_.dateHeld), from);
Predicate lt = builder.lessThan(root.get(Hours_.hoursPK).get(HoursPK_.dateHeld), to);

c.where(gt,lt,who);
c.orderBy(builder.asc( root.get(Hours_.hoursPK).get(HoursPK_.dateHeld)  ));

TypedQuery<Hours> q = em.createQuery(c);

List<Hours> resultList = q.getResultList();

I'm using Hibernate 4.3.1 as the JPA provider and the above code fails with the exception:

Exception in thread "main" java.lang.IllegalArgumentException: Parameter value [foobar] did not match expected type [persistence.Trainer (n/a)] at org.hibernate.jpa.spi.BaseQueryImpl.validateBinding(BaseQueryImpl.java:885)

Apart from the fact that this seems awfully complicated for a query that even a SQL newbie could write in a few minutes, I have no clue, how I can supply the correct value for the trainer_id column in the hours table in the above query.

I also tried:

Predicate who = builder.equal(root.get("trainer_id"), "foobar");

But that fails with the exception:

java.lang.IllegalArgumentException: Unable to locate Attribute with the the given name [trainer_id] on this ManagedType [persistence.Hours]

It works, when I obtain an actual entity instance that maps to the "foobar" id:

CriteriaQuery<Trainer> cq = builder.createQuery(Trainer.class);
Root<Trainer> trainerRoot = cq.from(Trainer.class);
cq.where(builder.equal(trainerRoot.get(Trainer_.id), "foobar"));
TypedQuery<Trainer> trainerQuery = em.createQuery(cq);  
Trainer foobarTrainer = trainerQuery.getSingleResult();
....
Predicate who = builder.equal(root.get(Hours_.trainer), foobarTrainer);

But that seems a pretty stupid (and slow) way to do it.

I'm sure I'm missing something really obvious here, but I can't find it.

Answer 1

First of all, JPA queries always use class and field names. Never column names. So trying to use trainer_id won't work.

builder.equal(root.get(Hours_.trainer), "foobar");

You're trying to compare the trainer field of the Hours entity with the String "foobar". trainer is of type Trainer. A Trainer can't be equal to a String. Its ID, it firstName, or its lastName, all of type String, can be compared to a String. SO you probably want

builder.equal(root.get(Hours_.trainer).get(Trainer_.id), "foobar");

That said, as you noticed, the Criteria API is extremely complex and leads to unreadable, hard to maintain code. It's useful when you have to dynamically compose a query from several optional criteria (hence the name), but for static queries, you should definitely go with JPQL, which is even easier and shorter than SQL:

select h from Hours h 
where h.trainer.id = :trainerId
and h.hoursPK.dateHeld >= :from
and h.hoursPK.dateHeld < :to
order by h.hoursPK.dateHeld

I would strongly advise against using composite keys, especially when one of its components is a functional data (dateHeld) that could have to change. Use numeric, single-column, autogenerated primary keys, and everything will be much simpler, and more efficient.