I am fairly new to hibernate, and at the moment struggeling with firing a query with the use of criteria.
What i want to achieve is, selecting all records that contain a certain string in either the first or lastname columns.
Here is what I have. It should give a better idea.
Session session = HibernateUtil.openSession();
Criteria criteria = session.createCriteria(Contact.class);
if(keywords != null) {
for(String keyword : keywords) {
if(keyword != null && keyword.length() > 0) {
criteria.add(Restrictions.like("firstName", keyword, MatchMode.ANYWHERE));
criteria.add(Restrictions.or(Restrictions.like("lastName", keyword, MatchMode.ANYWHERE), null));
}
}
}
searchMatches = criteria.list();
so in the end the query should be something along the lines of:
SELECT * FROM contacts WHERE (firstname LIKE '%oe% OR lastname LIKE '%oe%'), and if multiple keywords are given, something like:
SELECT * FROM contacts WHERE (firstname LIKE '%oe% OR lastname LIKE '%oe%') OR (firstname LIKE '%roo% OR lastname LIKE '%roo%')
this would select the record containing "oe" inside either the first or lastname columns. How ever I get a crash with the code when criteria.list(); is run.
Here is the stack trace. I can not figure out what it is I am doing wrong.
Caused by: java.lang.NullPointerException
at org.hibernate.criterion.LogicalExpression.toSqlString(LogicalExpression.java:60)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:419)
at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:123)
at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:92)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:93)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1464)
at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374)
at com.joey.wishlist.dao.impl.ContactDAOImpl.searchContacts(ContactDAOImpl.java:34)
at com.joey.wishlist.services.impl.ContactServiceImpl.searchContacts(ContactServiceImpl.java:22)
at com.joey.wishlist.presenters.ContactSearchPresenter.search(ContactSearchPresenter.java:75)
EDIT: I am one step closer.
I fixed the error. The error was caused by a malfunctioning or.
The code now looks like this:
if(keywords != null) {
for(String keyword : keywords) {
if(keyword != null && keyword.length() > 0) {
criteria.add(
Restrictions.or(
Restrictions.like("lastName", keyword, MatchMode.ANYWHERE),
Restrictions.like("firstName", keyword, MatchMode.ANYWHERE)
));
}
}
}
Which produces this query, which is almost what i want. I just want the "AND" to be "OR". So:
Hibernate:
/* criteria query */ select
this_.id as id0_0_,
this_.firstname as firstname0_0_,
this_.lastname as lastname0_0_
from
contactmanager this_
where
(
this_.lastname like ?
or this_.firstname like ?
)
and (
this_.lastname like ?
or this_.firstname like ?
)
I want to be:
Hibernate:
/* criteria query */ select
this_.id as id0_0_,
this_.firstname as firstname0_0_,
this_.lastname as lastname0_0_
from
contactmanager this_
where
(
this_.lastname like ?
or this_.firstname like ?
)
or ( //Notice difference here
this_.lastname like ?
or this_.firstname like ?
)
Every time you call criteria.add
, it adds an and
to your statement.
What you want to to create a disjunction:
Disjunction matchDisjunction = Restrictions.disjunction()
for(String keyword : keywords) {
if(keyword != null && keyword.length() > 0) {
matchDisjunction.add(
Restrictions.or(
Restrictions.like("lastName", keyword, MatchMode.ANYWHERE),
Restrictions.like("firstName", keyword, MatchMode.ANYWHERE)
));
}
}
criteria.add(matchDisjunction);
Or simply:
Disjunction matchDisjunction = Restrictions.disjunction()
for(String keyword : keywords) {
if(keyword != null && keyword.length() > 0) {
matchDisjunction.add( Restrictions.like("lastName", keyword, MatchMode.ANYWHERE) );
matchDisjunction.add( Restrictions.like("firstName", keyword, MatchMode.ANYWHERE) );
}
}
criteria.add(matchDisjunction);