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
- Hibernate 3.6.10.Final
- JPA 2.0
- Spring 4.0.0
- Oracle JDBC Driver version 10.2.0.3.0