Hi again,
Regarding your first problem, I researched this a while ago, using the
Criteria class, and ran into a roadblock. I'm afraid I can't remember what the roadblock was, so I'm not sure if it still exists, but to get around it I used the
sqlRestriction(...) method from the
Restrictions class, which lets you add a criteria-restriction using native SQL. This isn't the most efficient way to do this, but to me it was worth it to be able to continue to use the
Criteria object.
I'm adapting my code to your example, assuming that each
Keyword has a
name property. This also assumes that you've created a
Book object filled with all your search criteria -- e.g.
getKeywords() returns a list of all the keywords selected via a search form by your applicaton's user. And, finally, this assumes that you're passing in a
Criteria object created on the
Book entity.
Code:
private void handleKeywords(final Book book, Criteria criteria) {
List<Keyword> keywords = book.getKeywords();
/*
* Adding type/name pairs for each Keyword
*/
ArrayList<String> names = new ArrayList<String>();
ArrayList<Type> types = new ArrayList<Type>();
for (Keyword keyword : keywords) {
names.add(keyword.getName());
types.add(new StringType());
}
/*
* Building the query -- {alias} refers to the Book entity the
* criteria is based on -- this notation must be used because
* Hibernate needs to be able to replace it with its own
* alias for the Book table.
*/
StringBuffer sql = new StringBuffer("EXISTS (SELECT name "
+ "FROM BookKeywords "
+ "INNER JOIN Keywords "
+ "ON BookKeywords.keywordID = "
+ "Keywords.keywordID "
+ "WHERE bookID = {alias}.bookID "
+ "AND name IN (null");
for (int i = 0; i < keywords.size(); ++i) {
sql.append(", ?");
}
sql.append("))");
/*
* Setting the parameters to the query
*/
criteria.add(Restrictions.sqlRestriction(
sql.toString(),
names.toArray(new String[0]),
types.toArray(new Type[0])));
}
-- Jim Steinberger
Login
FAQ
Search
