I'm having trouble understanding what I need to do to get what I want out of a HibernateHelper.getSession().createCriteria() query.
Below is an example of my current query. If it helps to understand, it's for a 'quick search' where a user can type in "Bad Boy Bill" and get any media objects that contain all three words in either the title, artist, or label.
Code:
Criteria criteria = HibernateHelper.getSession().createCriteria(Media.class);
criteria.createAlias("artist", "a");
criteria.createAlias("label", "l");
String[] split = searchContext.get(SearchContext.MEDIA_QUICKSEARCH).toString().split(" ");
for (int i = 0 ; i < split.length ; i++) {
criteria.add(
Expression.or(
Expression.like("name", split[i], MatchMode.ANYWHERE),
Expression.or(
Expression.like("a.name", split[i], MatchMode.ANYWHERE),
Expression.like("l.name", split[i], MatchMode.ANYWHERE)
)
)
);
}
The Media object variables used above are initiated as:
Code:
private Artist artist;
private Label label;
private string title;
Now, the Media object also has
Code:
private List Artists;
Artists (a java.util.List) contains 0-n AssociatedArtist objects. It's a list of additional artists involved in remixing, featuring, vocals by, ect, on the same Media object.
Here is the artists list within the Media object mapping:
Code:
<list name="artists" table="`MEDIAARTISTS`" cascade="all-delete-orphan">
<key column="`MEDIAID`"/>
<index column="`SORTINGID`"/>
<one-to-many class="vitalvinyl.model.media.AssociatedArtist"/>
</list>
And here's the hibernate file for AssociatedArtist:
Code:
<hibernate-mapping>
<class name="vitalvinyl.model.media.AssociatedArtist" table="`ASSOCIATEDARTIST`">
<id name="ID" column="`ID`" type="long" unsaved-value="-1">
<generator class="native"/>
</id>
<version name="versionID" column="`VERSIONID`" type="long" unsaved-value="negative"/>
<many-to-one name="artist" column="`ARTISTID`" class="vitalvinyl.model.media.Artist"/>
<many-to-one name="role" column="`ROLEID`" class="vitalvinyl.model.media.ArtistRole"/>
</class>
</hibernate-mapping>
Don't worry about the ArtistRole object. I don't need to search on that.
So, what I'm trying to do, is search for Artist.getName() on any Artist objects in this AssociatedArtists list. Artist.getName() returns a string. I want to use the same logic in my first java criteria example. It'd need to be an Outer Join as it's a 0-n relation.
Ex:
Code:
SELECT *
FROM blah blah blah blah
WHERE ( (title like "bad") or (label like "bad") or (artist like "bad") or (artists like "bad") )
and ( (title like "boy") or (label like "boy") or (artist like "boy") or (artists like "boy") )
and ( (title like "bill") or (label like "bill") or (artist like "bill") or (artists like "bill") )
Obviously the SQL would look different. I hope you can get the point of what I'm going for here. Please let me know if there's anything I can do to clarify this issue for you.
Thanks
Login
FAQ
Search