Hibernate复合关键标准加入
我试图通过复合键执行多个连接。 我使用别名来强制连接创建，但看起来连接不是由Hibernate生成的。 我不知道为什么会这样。 我可以让它使用原生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>();
...
}
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
工作原生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();
眼镜
首先，你为什么不尝试减少简约的例子？ 您抽样涉及许多实体和关系，为什么不减少它，即使只是为了您自己的故障排除时间？
其次，你的代码不完整，它在用户和其他实体上没有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'
Whoala，加入是在那里。 所以你至少得到了一个解决方案 - 使用查询而不是标准。 更复杂的查询可以通过获取连接等来制作
现在到标准。 首先，我们来看看传统的标准映射。 使用标准映射，您不能在@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的设置，标准和联接提取的方式与您尝试的方式完全相同，但它可能不是最好的方法。
链接地址: http://www.djcxy.com/p/32763.html上一篇: Hibernate Composite key Criteria Join
下一篇: Crossfilter: Order crossfilter dimension based on another dimension