java cirteria_java – 简单的JPA CriteriaQuery的条件
所以这是我第一次尝试使用JPA和CriteriaQuery.
我有以下(简化)实体：
@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 hoursList;
... more attributes, getters and setters
@XmlTransient
public List getHoursList() {
return hoursList;
}
public void setHoursList(List hoursList) {
this.hoursList = hoursList;
}
}
基本上,培训师会进行培训,培训所花费的时间存储在“小时”实体中.小时表的PK是(trainer_id,date_held),因为每位培训师每天只进行一次培训.
我正在尝试创建一个CriteriaQuery来获取特定月份的培训师的所有时间.这是我的尝试：
EntityManagerFactory emf = ...
EntityManager em = emf.createEntityManager();
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery c = builder.createQuery(Hours.class);
Root root = c.from(Hours.class);
Calendar cal = Calendar.getInstance();
cal.set(2014, 0, 1);
Expression from = builder.literal(cal.getTime());
cal.set(2014, 1, 1);
Expression 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 q = em.createQuery(c);
List resultList = q.getResultList();
我使用Hibernate 4.3.1作为JPA提供程序,上面的代码失败,但有异常：
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)
除了这个看起来非常复杂的查询甚至SQL新手可以在几分钟内编写,我不知道如何在上述查询的小时表中为trainer_id列提供正确的值.
我也尝试过：
Predicate who = builder.equal(root.get("trainer_id"), "foobar");
但是失败的例外是：
java.lang.IllegalArgumentException: Unable to locate Attribute with the the given name [trainer_id] on this ManagedType [persistence.Hours]
当我获得映射到“foobar”id的实际实体实例时,它可以工作：
CriteriaQuery cq = builder.createQuery(Trainer.class);
Root trainerRoot = cq.from(Trainer.class);
cq.where(builder.equal(trainerRoot.get(Trainer_.id), "foobar"));
TypedQuery trainerQuery = em.createQuery(cq);
Trainer foobarTrainer = trainerQuery.getSingleResult();
....
Predicate who = builder.equal(root.get(Hours_.trainer), foobarTrainer);
但这似乎是一种非常愚蠢(和缓慢)的方式.
我确定我错过了一些非常明显的东西,但我找不到它.