因此,这是我第一次尝试使用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<Hours> hoursList;
... more attributes, getters and setters
@XmlTransient
public List<Hours> getHoursList() {
return hoursList;
}
public void setHoursList(List<Hours> hoursList) {
this.hoursList = hoursList;
}
}
本质上,
Trainer
保存培训,并且在培训中花费的时间存储在Hours
实体中。 hours
表的PK为(trainer_id, date_held)
,因为每个培训师每天仅进行一次培训。我正在尝试创建
CriteriaQuery
,以获取特定月份教练的所有时间。这是我的尝试: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();
我使用Hibernate 4.3.1作为JPA提供程序,以上代码失败,但出现以下异常:
除了对于一个查询(即使是SQL新手也可以在几分钟内完成)的查询而言,这似乎非常复杂之外,我不知道如何为上述查询的
trainer_id
表中的hours
列提供正确的值。我也尝试过:
Predicate who = builder.equal(root.get("trainer_id"), "foobar");
但这以失败告终:
当我获得一个映射到
"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);
但这似乎是一种非常愚蠢(缓慢)的方法。
我确定我在这里确实遗漏了一些明显的东西,但是找不到。
最佳答案
首先,JPA查询始终使用类名和字段名。从不列名。因此,尝试使用trainer_id
是行不通的。
builder.equal(root.get(Hours_.trainer), "foobar");
您正在尝试将Hours实体的Trainer字段与字符串“foobar”进行比较。培训师是培训师类型。培训师不能等于弦乐。可以将其ID(名称为firstName或lastName)(均为String类型)与一个String进行比较。所以你可能想要
builder.equal(root.get(Hours_.trainer).get(Trainer_.id), "foobar");
就是说,正如您所注意到的那样,Criteria API非常复杂,并且导致难以阅读且难以维护的代码。当您必须根据几个可选条件动态地组成一个查询(因此得名)时,它很有用,但是对于静态查询,您绝对应该使用JPQL,它比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
我强烈建议您不要使用组合键,尤其是当其组成部分之一是可能需要更改的功能数据(dateHeld)时。使用数字,单列,自动生成的主键,一切将变得更加简单和高效。