JPA条件:对待实体上的左连接的QuerySyntaxException [英] JPA Criteria: QuerySyntaxException for left join on treat entity

问题描述

型号:

@Entity
public class User {

    @Id
    private Integer id;

    @JoinColumn(name = "user_id")
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Project> projects;
}
@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "Type")
public abstract class Project {

    @Id
    private Integer id;

    private String name;
}
@Entity
@DiscriminatorValue("Administrative")
public class AdminProject extends Project {

    private String departmentName;
}
@Entity
@DiscriminatorValue("Design")
public class DesignProject extends Project {

    private String companyName;
}

我正在尝试使用JPA的条件api基于Project实现的属性来查询User实体.例如，使用"SOME_NAME"查询具有项目的所有用户.部门(该字段在DesignProject上不存在).

I am trying to use JPA's criteria api to query for User entities based on an attribute of an implementation of Project. For example, query all users that have a project with "SOME_NAME" department (that field does not exist on DesignProject).

我看到有一种方法可以通过对查询的Project实体进行向下转换来实现.我正在尝试类似的东西:

I see there is a way of doing so via downcasting of the Project entity for the query. I am trying something similar to:

CriteriaBuilder cb...
Root<User> userRoot...
root = ((From) root).join("projects", JoinType.LEFT);
root = cb.treat(root, AdminProject.class);
root = root.get("departmentName");

例外:

org.springframework.dao.InvalidDataAccessApiUsageException:org.hibernate.hql.internal.ast.QuerySyntaxException:无效路径:"generatedAlias2.departmentName" [从io.github.perplexhub.rsql.model.User中选择generatedAlias0作为generateAlias0左联接generateAlias0.projects作为generateAlias1，其中Treat(GeneratedAlias2 as io.github.perplexhub.rsql.model.AdminProject).departmentName =:param0];嵌套的异常是java.lang.IllegalArgumentException:org.hibernate.hql.internal.ast.QuerySyntaxException:无效的路径:'generatedAlias2.departmentName'[从io.github.perplexhub.rsql.model.User中选择generateAlias0，作为generateAlias0的用户，请左加入generateAlias0.项目为generateAlias1，其中，好对待(generatedAlias2为io.github.perplexhub.rsql.model.AdminProject).departmentName =:param0]

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias2.departmentName' [select generatedAlias0 from io.github.perplexhub.rsql.model.User as generatedAlias0 left join generatedAlias0.projects as generatedAlias1 where treat(generatedAlias2 as io.github.perplexhub.rsql.model.AdminProject).departmentName=:param0]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias2.departmentName' [select generatedAlias0 from io.github.perplexhub.rsql.model.User as generatedAlias0 left join generatedAlias0.projects as generatedAlias1 where treat(generatedAlias2 as io.github.perplexhub.rsql.model.AdminProject).departmentName=:param0]

我想念什么?与连接有关，还是之后如何向下转换?

What am I missing? Is it something related to the join, or how the downcasting occurs afterwards?

在@ K.Nicholas回答之后，我设法使查询在孤立的情况下工作，但不适用于我的应用程序. 但是，我注意到entityManager.createQuery(query)调用在首次调用时会引发上面的异常，并且如果我再次调用它而又不更改查询对象，则它会起作用.这是第二次调用时生成的查询(此查询从数据库中找到我想要的对象):

After the answer by @K.Nicholas, I have managed to make the query work on an isolated scenario, but not on my app. But, I noticed that the entityManager.createQuery(query) call throws the exception above when called for the first time, and it works if I call it again without changing the query object. Here is the query generated on the second call (this query finds the objects I want from the database):

从用户中选择generatedAlias0，作为generatedAlias0左联接，将generatedAlias0.projects作为generatedAlias2，其中treat(generatedAlias2作为io.github.perplexhub.rsql.model.AdminProject).departmentName =:param0

select generatedAlias0 from User as generatedAlias0 left join generatedAlias0.projects as generatedAlias2 where treat(generatedAlias2 as io.github.perplexhub.rsql.model.AdminProject).departmentName=:param0

为什么实体管理器连续两次调用时会创建两个不同的查询?

推荐答案

我会做Entitys稍有不同.主要问题是您将User用作根，并加入了Projects列表.这是一个问题，因为您应该在Project类上具有外键，并将projects字段用作仅查询字段.那就是我所做的.这样效果更好.这也是一个问题，因为您必须执行join fetch而不是join，以便将projects与users一起获取.

I would do the Entitys a little different, as you will see. The main concern is that you are using User as your root with a join to a list of Projects. This is a concern because you should have the foreign key on the Project class and use the projects field as a query only field. That is what I have done. It works better that way. It is also a concern because you have to do a join fetch instead of a join so that the projects get fetched along with the users.

所以，首先，实体就像这样:

So, first, the entities are like so:

@Entity
public class User { 
    @Id
    private Integer id; 
    @OneToMany(mappedBy="user")
    private List<Project> projects;
}

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "Type")
public abstract class Project {
    @Id
    private Integer id;
    private String name;    
    @ManyToOne
    private User user;
}

@Entity
@DiscriminatorValue("Administrative")
public class AdminProject extends Project {
    private String departmentName;
}

@Entity
@DiscriminatorValue("Design")
public class DesignProject extends Project {    
    private String companyName;
}

经过一番挖掘，我发现了可以解决问题的JPQL查询.这是一个起点:

After a bit a digging I found a JPQL query that does the trick. This was a starting point:

List<User> users = entityManager.createQuery("select distinct(u) from User u join fetch u.projects p where TYPE(p) = 'Administrative' and p.departmentName = 'dept1'", User.class).getResultList();

经过进一步的挖掘，我发现treat如果正确执行就可以正常工作，并且在JPA 2.1中，应该使用EntityGraph来获取join来执行fetch.

After a bit more digging I found that the treat worked fine if you do it correctly and that with JPA 2.1 you should use an EntityGraph do get the join to do a fetch.

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<User> query = builder.createQuery(User.class);
Root<User> root = query.from(User.class);
Join<User, Project> join = root.join("projects");
query.select(root).where(builder.equal(builder.treat(join, AdminProject.class).get("departmentName"), "dept1"));
EntityGraph<User> fetchGraph = entityManager.createEntityGraph(User.class);
fetchGraph.addSubgraph("projects");
users = entityManager.createQuery(query.distinct(true)).setHint("javax.persistence.loadgraph", fetchGraph).getResultList();

请注意，查询生成的内容略有不同，但是我对它们的关注并不那么密切.你应该.

As a side note the queries generated as slightly different but I didn't look that closely at them. You should.

这篇关于JPA条件:对待实体上的左连接的QuerySyntaxException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！