Java JPA CriteriaQuery 的简单 where 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21961393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simple where condition for JPA CriteriaQuery
提问by a_horse_with_no_name
So this is my first attempt to use JPA and a CriteriaQuery.
所以这是我第一次尝试使用 JPA 和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 Trainerholds trainings and the hours spent in the trainings are stored in the Hoursentity. The PK for the hourstable is (trainer_id, date_held)as each trainer only holds one training per day.
本质上是一个Trainer举办培训，并且在培训中花费的时间存储在Hours实体中。hours表的PK是（trainer_id, date_held)因为每个培训师每天只进行一次培训。
I am trying to create a CriteriaQueryto fetch all hours of a trainer for a specific month. This is my attempt:
我正在尝试创建一个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();
I'm using Hibernate 4.3.1 as the JPA provider and the above code fails with the exception:
我使用 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)
线程“main”中的异常 java.lang.IllegalArgumentException：参数值 [foobar] 与 org.hibernate.jpa.spi.BaseQueryImpl.validateBinding(BaseQueryImpl.java:885) 处的预期类型 [persistence.Trainer (n/a)] 不匹配)
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_idcolumn in the hourstable in the above query.
除了对于即使是 SQL 新手也可以在几分钟内编写的查询来说这似乎非常复杂这一事实之外，我不知道如何为上述查询trainer_id中的hours表中的列提供正确的值。
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]
java.lang.IllegalArgumentException：无法在此 ManagedType [persistence.Hours] 上找到具有给定名称 [trainer_id] 的属性
It works, when I obtain an actual entity instance that maps to the "foobar"id:
它有效，当我获得映射到"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.
我确定我在这里遗漏了一些非常明显的东西，但我找不到它。
采纳答案by JB Nizet
First of all, JPA queries always use class and field names. Never column names. So trying to use trainer_idwon't work.
首先，JPA 查询总是使用类名和字段名。从不列名。所以尝试使用是trainer_id行不通的。
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
您正在尝试将 hours 实体的 trainer 字段与字符串“foobar”进行比较。trainer 是 Trainer 类型。Trainer 不能等于 String。它的 ID，它的 firstName 或它的 lastName，都是 String 类型，可以与 String 进行比较。所以你可能想要
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:
也就是说，正如您所注意到的，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
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.
我强烈建议不要使用复合键，尤其是当它的一个组件是可能必须更改的功能数据 (dateHeld) 时。使用数字、单列、自动生成的主键，一切都会变得更简单、更高效。
