我正在尝试编写查询,以返回未分配到路线的司机列表。
我的数据库设置如下。
Route:
route_id
user_id//specified as driver
User:
user_id
role // need to select user, which is Driver role
只有路线可以看到用户(司机),用户(司机)看不到路线。
这是我尝试编写此类查询的方法。
public List<User> getUnsignedDrivers(){
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);
Root<User> user = query.from(User.class);
query.select(user);
Subquery<Route> subquery = query.subquery(Route.class);
Root<Route> subRootEntity = subquery.from(Route.class);
Predicate correlatePredicate = criteriaBuilder.equal(subRootEntity.get("Route_.User"), user);
subquery.where(correlatePredicate);
query.where(criteriaBuilder.not(criteriaBuilder.exists(subquery)));
TypedQuery<User> typedQuery = entityManager.createQuery(query);
return typedQuery.getResultList();
}
我是JPA的新手,所以这是我的问题。
更具体地说,我需要选择未设置到任何路线的司机角色用户。
我的实体设置如下:
@Entity
public class Route {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@OneToOne(fetch = FetchType.EAGER, cascade = {})
@JoinColumn(name = "user_id", nullable = true)
private User driver;
.....
@Entity
public class User {
public static enum Role {
ADMIN, MANAGER, DRIVER;
}
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Enumerated(EnumType.STRING)
@Column(nullable = false)
private Role role;
更新: 当前的查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);
Root<User> user = query.from(User.class);
Predicate predicateRole = criteriaBuilder.equal(user.get("role"), User.Role.DRIVER);
query.where(predicateRole);
query.select(user);
Subquery<Route> subquery = query.subquery(Route.class);
Root<Route> subRootEntity = subquery.from(Route.class);
Predicate correlatePredicate = criteriaBuilder.equal(subRootEntity.get("driver"), user);
subquery.where(correlatePredicate);
query.where(criteriaBuilder.not(criteriaBuilder.exists(subquery)));
TypedQuery<User> typedQuery = entityManager.createQuery(query);
return typedQuery.getResultList();
问题仍然存在。
我得到了这个异常:
java.lang.IllegalStateException: No explicit selection and an implicit one cold not be determined
at org.hibernate.ejb.criteria.QueryStructure.locateImplicitSelection(QueryStructure.java:296)
at org.hibernate.ejb.criteria.QueryStructure.render(QueryStructure.java:249)
at org.hibernate.ejb.criteria.CriteriaSubqueryImpl.render(CriteriaSubqueryImpl.java:282)
at org.hibernate.ejb.criteria.predicate.ExistsPredicate.render(ExistsPredicate.java:58)
at org.hibernate.ejb.criteria.QueryStructure.render(QueryStructure.java:258)
at org.hibernate.ejb.criteria.CriteriaQueryImpl.render(CriteriaQueryImpl.java:340)
at org.hibernate.ejb.criteria.CriteriaQueryCompiler.compile(CriteriaQueryCompiler.java:217)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:587)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:240)
at $Proxy25.createQuery(Unknown Source)
在这一行代码中,throws指的是抛出异常。 TypedQuery<User> typedQuery = entityManager.createQuery(query);
这段代码会创建一个类型为TypedQuery的对象,并执行查询操作。
解决方法 以下方法对我来说非常有效。 我写了这个解决方案,因为我不能使用反向关系。
public List<User> getUnsignedDrivers(){
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<User> query = criteriaBuilder.createQuery(User.class);
Root<User> user = query.from(User.class);
Predicate predicateRole = criteriaBuilder.equal(user.get("role"), User.Role.DRIVER);
query.where(predicateRole);
query.select(user);
TypedQuery<User> typedQuery = entityManager.createQuery(query);
List<User> allDrivers = typedQuery.getResultList();
List<User> notAssignedDrivers = new ArrayList<User>();
List<Route> haveDriverRoutes = getRouteWithNoDrives();
for (User driver : allDrivers){
if (!isDriverAssigned(haveDriverRoutes,driver.getId())){
notAssignedDrivers.add(driver);
}
}
return notAssignedDrivers;
}
private boolean isDriverAssigned(List<Route> haveDriverRoutes, long driverId){
for(Route route : haveDriverRoutes){
if (route.getDriver().getId() == driverId){
return true;
}
}
return false;
}
@SuppressWarnings("unchecked")
public List<Route> getRouteWithNoDrives() {
Query query = entityManager.createQuery("SELECT o FROM " + type.getSimpleName() + " o WHERE o.driver != null");
return query.getResultList();
}
subRootEntity.get("driver");
这个路径是可行的,现在我改成了Predicate correlatePredicate = criteriaBuilder.equal(subRootEntity.get("driver").get("id"), user.get("id"));
但是它抛出了java.lang.IllegalStateException: No explicit selection and an implicit one cold not be determinedat org.hibernate.ejb.criteria
异常。有什么建议吗? - kuldarimPredicate correlatePredicate = criteriaBuilder.equal(subRootEntity.get("driver"), user);
但是它抛出了java.lang.IllegalStateException: No explicit selection and an implicit one cold not be determined
异常。 - kuldarim@OneToOne
,因为司机不应该关心路线,只有路线应该知道司机。司机是用户,用户有3个角色:经理、司机和管理员,所以如果必须使用反向的@OneToOne
,那将会很遗憾。 - kuldarim