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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With