Hibernate Composite key Criteria Join
I'm trying to perform multiple joins over a composite key. I'm using aliases to force the join creation however it seems like the join is not generated by Hibernate. I don't know why this is the case. I can get it to work with a native SQL query, but not while using criteria.
I suspect it might have to do with the way how the composite key definitions are mapped (cf the associationOverrides on BusinessServiceUser )
Below are my domain model classes and query info. Any ideas are welcome :)
BusinessService
@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;
}
...
}
User
@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);
...
}
Role
@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 Query
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 generated query
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
Error
java.sql.SQLException: ORA-00904: "ROLEALIAS3_"."NAME": invalid identifier
Working Native SQL query
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();
Specs
First, why don't you try to reduce for minimalistic example? You sample involves many entities and relationships, why not reduce it, even just for the sake of your own troubleshooting time?
Second, your code is not complete, it misses id on User and other entities. For answer purposes I'll assume that id is defined somewhere.
I'll provide the answer without business service and roles, I guess similar solution will apply.
How do we approach to solve it?
First, reduce to simplest criteria and entity set. For example a restriction on 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"));
Generated SQL query:
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=?
Obviously, the expected join is missing (so you don't need complex example to reproduce the problem).
Looking into BusinessServiceUserId, it uses @Embedded with @ManyToOne. Note this is Hibernate specific extension, in general you should not use @ManyToOne in @Embedded. Let's try plain query instead of criteria:
Query q = session.createQuery("from BusinessServiceUser as u left outer join u.pk.user where u.pk.user.emailAddress='test@test'");
q.list();
Generated 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, the join is there. So you got here at least one solution - use query instead of criteria. More complex queries can be crafted with fetch join, etc.
Now to the Criteria. First, let's check with conventional standard mapping. With standard mapping you can't reference that way @ManyToOne defined in @Embedded. Let's add mapping to the BusinessServiceUser class itself instead of @Transient
@ManyToOne(fetch=FetchType.LAZY)
public User getUser() {
return getPk().getUser();
}
Note this additional mapping does not cost you.
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"));
Generated 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=?
So here you got solution 2 with criteria. Add mappings in the entity and use them in criteria instead of complex pk.
Though I'm not aware of a setup to use @EmbeddedId pk with @AssotiationOverride, criteria and join fetch in exactly the same way you try to do, probably it is not the best approach anyway.
链接地址: http://www.djcxy.com/p/32764.html上一篇: Electron在后台时避免应用程序节流
下一篇: Hibernate复合关键标准加入