Hibernate Composite key Criteria Join

Question

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

Answer 1

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", "[email protected]"));

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", "[email protected]"));

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.