views:

498

answers:

0

This is similar, but not identical, to:

http://stackoverflow.com/questions/1528352/hibernate-criteria-query-on-different-properties-of-different-objects

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"