因此,这是我第一次尝试使用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)时。使用数字,单列,自动生成的主键,一切将变得更加简单和高效。

09-10 07:47
查看更多