Hello,
I have a Song class and a Value class who have a many-to-many relationship between them (through the table songvalue).
Now i want to select all songs that contain a certain value object with the criterion api. i can't just use a bidirectional binding, by just doing value.getSongs() or something, because i need it as a Criterion in an algorithm to create one bigger query...
To give an example on what i want to achieve i wrote this HQL query:
Code:
Query qvs = DBConnectionManager.currentSession().createQuery(
"from Song song where :val member of song.values");
qvs.setParameter("val", val);
This works correct. but i need a criterion object... The next code DOES NOT WORK (because there is no contains method), but just as an example of something im looking for:
Code:
Criteria crit = HibernateUtil.currentSession().createCriteria(Song.class);
crit.add(Restrictions.contains("values", val);
does anyone know how to express something like this in the Criteria API?
Hibernate version: 3.0.1
Mapping documents:Code:
<class name="datamonkey.hibernate.Value" table="values" schema="public">
<id name="valueId" type="integer">
<column name="value_id" not-null="true" unique="true" sql-type="integer" />
<generator class="sequence">
<param name="sequence">values_value_id_seq</param>
</generator>
</id>
<many-to-one name="parameter" class="datamonkey.hibernate.Parameter">
<column name="param_id" not-null="true" />
</many-to-one>
<property name="valueName" type="java.lang.String">
<column name="valuename" not-null="true" sql-type="varchar" />
</property>
<set name="songs" table="songvalue" inverse="true">
<key column="value_id"/>
<many-to-many column="song_id" class="datamonkey.hibernate.Song"/>
</set>
</class>
<class name="datamonkey.hibernate.Song" table="songs" schema="public">
<id name="songId" type="integer">
<column name="song_id" not-null="true" unique="true" sql-type="integer" />
<generator class="sequence">
<param name="sequence">songs_song_id_seq</param>
</generator>
</id>
<set name="values" table="songvalue">
<key column="song_id"/>
<many-to-many column="value_id" class="datamonkey.hibernate.Value"/>
</set>
</class>
</hibernate-mapping>
PostgreSQL 8.0.3
The generated SQL (show_sql=true):This is the sql query that has been generated out of the HQL query:
Code:
12:44:31,703 DEBUG SQL:324 - select song0_.song_id as song1_ from public.songs song0_ where ? in (select value2_.value_id from songvalue values1_, public.values value2_ where song0_.song_id=values1_.song_id and values1_.value_id=value2_.value_id)
Login
FAQ
Search