java - Hibernate Composite key Criteria Join

Question

我正在尝试通过复合键执行多个连接。我正在使用别名来强制创建连接，但似乎连接不是由 Hibernate 生成的。我不知道为什么会这样。我可以让它与 native SQL 查询一起使用，但在使用条件时无法使用。

我怀疑这可能与复合键定义的映射方式有关(参见 BusinessServiceUser 上的 associationOverrides)

下面是我的域模型类和查询信息。欢迎任何想法:)

商务服务

@Entity
@Table(name = "business_services")
public class BusinessService extends AbstractEntity implements Serializable {
  @Column(name = "name", unique = true, nullable = false, length = 255)
  private String name;

  @OneToMany(fetch = FetchType.LAZY, mappedBy = "pk.businessService", cascade = CascadeType.ALL)
  @ForeignKey(name = "FK_BUSINESS_SERVICE_USERS")
  private Set<BusinessServiceUser> businessServiceUsers = new HashSet<BusinessServiceUser>();
...
}

业务服务用户

@Entity
@Table(name = "REL_BUSINESS_SERVICE_USER")
@AssociationOverrides({
    @AssociationOverride(name = "pk.businessService", joinColumns = @JoinColumn(name = "BUSINESS_SERVICE_ID")),
    @AssociationOverride(name = "pk.user", joinColumns = @JoinColumn(name = "USER_ID")) })
public class BusinessServiceUser implements Serializable {

  private BusinessServiceUserId pk = new BusinessServiceUserId();
  private Boolean master;

  public BusinessServiceUser() {

  }

  @EmbeddedId
  public BusinessServiceUserId getPk() {
    return pk;
  }

  public void setPk(BusinessServiceUserId pk) {
    this.pk = pk;
  }

  @Transient
  public User getUser() {
    return getPk().getUser();
  }

  public void setUser(User user) {
    getPk().setUser(user);
  }

  @Transient
  public BusinessService getBusinessService() {
    return getPk().getBusinessService();
  }

  public void setBusinessService(BusinessService businessService) {
    getPk().setBusinessService(businessService);
  }

  public boolean isMaster() {
    return master;
  }

  public void setMaster(boolean master) {
    this.master = master;
  }
...
}

BusinessServiceUserId

@Embeddable
public class BusinessServiceUserId implements Serializable {

  private User user;
  private BusinessService businessService;

  @ManyToOne
  public User getUser() {
    return user;
  }

  public void setUser(User user) {
    this.user = user;
  }

  @ManyToOne
  public BusinessService getBusinessService() {
    return businessService;
  }

  public void setBusinessService(BusinessService businessService) {
    this.businessService = businessService;
  }
...
}

用户

@Entity
@Table(name = "USERS")
public class User extends AbstractEntity implements Serializable {

  @Column(name = "first_name", nullable = false, length = 50)
  private String firstName;

  @Column(name = "last_name", nullable = false, length = 100)
  private String lastName;

  @Column(name = "email_address", unique = true, nullable = false, length = 150)
  private String emailAddress;

  @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.DETACH, targetEntity = Role.class)
  @JoinTable(name = "REL_USER_ROLE", joinColumns = @JoinColumn(name = "USER_ID", nullable = false) , inverseJoinColumns = @JoinColumn(name = "ROLE_ID", nullable = false) )
  @ForeignKey(name = "FK_USER_ROLE")
  private Set<Role> roles = new HashSet<Role>(0);

  @OneToMany(fetch = FetchType.LAZY, mappedBy = "pk.user")
  @ForeignKey(name = "FK_USER_BUSINESS_SERVICE")
  private Set<BusinessServiceUser> businessServiceUsers = new HashSet<BusinessServiceUser>(0);

...
}

作用

@Entity
@Table(name = "role")
public class Role extends AbstractEntity implements Serializable {

  @Enumerated(EnumType.STRING)
  @Column(name = "name", unique = true, nullable = false)
  private RoleType name;

  @Column(name = "code", unique = true, nullable = false)
  private String code;

  @ManyToMany(fetch = FetchType.LAZY, mappedBy = "roles")
  @ForeignKey(name = "FK_ROLE_USERS")
  private List<User> users = new ArrayList<User>(0);
...
}

DAO 条件查询

Criteria criteria = getSession().createCriteria(
            BusinessServiceUser.class);

criteria.setFetchMode("pk.user", FetchMode.JOIN);
criteria.createAlias("pk.user", "userAlias", Criteria.LEFT_JOIN);

criteria.setFetchMode("pk.businessService", FetchMode.JOIN);
criteria.createAlias("pk.businessService", "bsAlias", Criteria.LEFT_JOIN);

criteria.setFetchMode("userAlias.roles", FetchMode.JOIN);
criteria.createAlias("userAlias.roles", "roleAlias");

criteria.add(Restrictions.eq("bsAlias.name", businessService.getName()));
criteria.add(Restrictions.eq("roleAlias.name", RoleType.ROLE1));

criteria.addOrder(Order.asc("master"));
return criteria.list();

SQL 生成查询

DEBUG org.hibernate.SQL - 
select
    this_.BUSINESS_SERVICE_ID as BUSINESS2_3_0_,
    this_.USER_ID as USER3_3_0_,
    this_.master as master3_0_ 
from
    REL_BUSINESS_SERVICE_USER this_ 
where
    bsalias2_.name=? 
    and rolealias3_.name=? 
order by
    this_.master asc
Hibernate: 
select
    this_.BUSINESS_SERVICE_ID as BUSINESS2_3_0_,
    this_.USER_ID as USER3_3_0_,
    this_.master as master3_0_ 
from
    REL_BUSINESS_SERVICE_USER this_ 
where
    bsalias2_.name=? 
    and rolealias3_.name=? 
order by
    this_.master asc

错误

java.sql.SQLException: ORA-00904: "ROLEALIAS3_"."NAME": invalid identifier

工作 native SQL 查询

 List<Object[]> result = getSession()
     .createSQLQuery(
     "select "
     + "  bsu.BUSINESS_SERVICE_ID as bsId, "
     + "  bsu.USER_ID as userId, "
     + "  bsu.master as master, "
     + "  bs.name as business_service, "
     + "  u.first_name as first_name, "
     + "  u.last_name as last_name, "
     + "  u.email_address as email, "
     + "  r.name as role "
     + "from "
     + "  REL_BUSINESS_SERVICE_USER bsu "
     + "  left outer join users u ON bsu.user_id = u.id "
     + "  left outer join business_services bs ON bsu.business_service_id = bs.id "
     + "  left outer join rel_user_role rur ON u.id = rur.user_id "
     + "  left outer join role r ON rur.role_id = r.id "
     + "where " 
     + "  bs.name = '" + businessService.getName() + "' "
     + "  and r.name like '" + RoleType.ROLE1 + "' "
     + "order by master asc")
   .list();

规范

• hibernate 3.6.10.Final
• JPA 2.0
• Spring 4.0.0
• Oracle JDBC 驱动程序版本 10.2.0.3.0

Answer 1

首先，你为什么不尝试减少 minimalistic example ？你的样本涉及很多实体和关系，为什么不减少它，即使只是为了你自己的故障排除时间？

其次，您的代码不完整，它缺少 User 和其他实体的 id。出于回答的目的，我假设 id 是在某处定义的。

我将提供没有业务服务和角色的答案，我想类似的解决方案将适用。

我们如何解决它？

首先，简化为最简单的条件和实体集。例如对 BusinessServiceUser.User.emailAddress 的限制:

Criteria criteria = session.createCriteria(
            BusinessServiceUser.class, "bu");
criteria.setFetchMode("bu.pk.user", FetchMode.JOIN);
criteria.createAlias("bu.pk.user", "userAlias", Criteria.LEFT_JOIN);
criteria.add(Restrictions.eq("userAlias.emailAddress", "test@test.com"));

生成的 SQL 查询:

select
    this_.BUSINESS_SERVICE_ID as BUSINESS3_33_0_,
    this_.USER_ID as USER2_33_0_,
    this_.master as master33_0_ 
from
    REL_BUSINESS_SERVICE_USER this_ 
where
    useralias1_.email_address=?

显然，缺少预期的连接(因此您不需要复杂的示例来重现该问题)。

查看 BusinessServiceUserId，它使用 @Embedded 和 @ManyToOne。请注意，这是 Hibernate 特定的扩展，通常您不应在 @Embedded 中使用 @ManyToOne。让我们尝试使用普通查询而不是条件:

    Query q = session.createQuery("from BusinessServiceUser as u left outer join u.pk.user where u.pk.user.emailAddress='test@test'");
    q.list();

生成的 SQL:

select
    businessse0_.BUSINESS_SERVICE_ID as BUSINESS2_33_0_,
    businessse0_.USER_ID as USER3_33_0_,
    user1_.id as id54_1_,
    businessse0_.master as master33_0_,
    user1_.email_address as email2_54_1_,
    user1_.first_name as first3_54_1_,
    user1_.last_name as last4_54_1_ 
from
    REL_BUSINESS_SERVICE_USER businessse0_ 
left outer join
    USERS user1_ 
        on businessse0_.USER_ID=user1_.id 
where
    user1_.email_address='test@test'

哇哦，加入了。所以你至少得到了一种解决方案——使用查询而不是标准。可以使用 fetch join 等构建更复杂的查询。

现在来看标准。首先，让我们检查传统的标准映射。使用标准映射，您无法引用@Embedded 中定义的@ManyToOne。让我们将映射添加到 BusinessServiceUser 类本身而不是 @Transient

 @ManyToOne(fetch=FetchType.LAZY)
 public User getUser() {
   return getPk().getUser();
 }

请注意，此额外的映射不会花费您。

Criteria criteria = session.createCriteria(
            BusinessServiceUser.class, "bu");
criteria.setFetchMode("bu.user", FetchMode.JOIN);
criteria.createAlias("bu.user", "userAlias", Criteria.LEFT_JOIN);
criteria.add(Restrictions.eq("userAlias.emailAddress", "test@test.com"));

生成的 SQL:

select
    this_.BUSINESS_SERVICE_ID as BUSINESS3_33_1_,
    this_.USER_ID as USER2_33_1_,
    this_.master as master33_1_,
    this_.user_id as user2_33_1_,
    useralias1_.id as id54_0_,
    useralias1_.email_address as email2_54_0_,
    useralias1_.first_name as first3_54_0_,
    useralias1_.last_name as last4_54_0_ 
from
    REL_BUSINESS_SERVICE_USER this_ 
left outer join
    USERS useralias1_ 
        on this_.user_id=useralias1_.id 
where
    useralias1_.email_address=? 

现在您得到了带标准的解决方案 2。在实体中添加映射并在条件中使用它们而不是复杂的 pk。

虽然我不知道使用 @EmbeddedId pk 和 @AssotiationOverride 的设置，条件和加入获取的方式与您尝试做的完全相同，但它可能不是最好的方法。