This is similar, but not identical, to:
I have a SpecChange record, which has a set of ResponsibleIndividuals; these are User records mapped by a hibernate join-table association. I want to create a Criteria query for a SpecChange which has the specified User in its ResponsibleIndividuals set, OR some other condition on the SpecChange.
I'm omitting most of the code for clarity, just showing the relevant annotations:
@Entity
class SpecChange {
@OneToMany
@JoinTable(name = "ri_id_spec_change_id", joinColumns = { @JoinColumn(name = "spec_change_id") }, inverseJoinColumns = @JoinColumn(name = "ri_id"))
@AccessType("field")
public SortedSet<User> getResponsibleIndividuals() { ... }
@Id
@Column(name = "unique_id")
@AccessType("field")
public String getId() { ... }
}
@Entity
class User { ... }
//user does not have a SpecChange association (the association is one-way)
What I want to do:
User currentUser = ...;
Criteria criteria = session.createCriteria(SpecChange.class);
...
criteria.add(Restrictions.disjunction()
.add(Restrictions.eq("responsibleIndividuals", currentUser))
.add(...)
);
criteria.list();
This generates wrong SQL:
select ... from MY_DB.dbo.spec_change this_ ... where ... (this_.unique_id=?)
...and fails:
java.sql.SQLException: Parameter #2 has not been set.
(I omitted another condition in the where clause, hence parameter #2 is the one shown. I am sure that 'currentUser' is not null.)
Note that the restriction references the wrong table: this_, which is SpecChange, not the User table
I tried a dozen different tricks to make it work correctly (including creating an alias, as mentioned in the previous post above). If there is a way to do it using the alias, I wasn't able to determine it.
The following DOES work (but doesn't accomplish what I need, since I can't use it in a disjunction): criteria.createCriteria("responsibleIndividuals").add(Restrictions.idEq(currentUser.getId()));
[Edit: a workaround for what seems like a bug in Hibernate, using HQL]
select sc
from com.mycompany.SpecChange sc
left join fetch sc.responsibleIndividuals as scResponsibleIndividuals
where scResponsibleIndividuals = :p1 or sc.updUser = :p1
order by sc.updDate desc
This won't work without the alias "scResponsibleIndividuals"