CODEWITHSUNDEEP
javahibernate
sar
sar

Reputation: 1287

combining 2 or more properties in Hibernate Restrictions criteria query

I want to perform search action on entered string. whatever user enter it should search on first name and last name column.

   //pseudo hql query
     Select   E.someproperty ....  from  Entity E    where   upper(E.firstName || '' || E.lastName)  like  upper(+userEntered+'%')  //

In above code I combined two column and did search on it. I want similar functionality using hibernate criteria. I have tried following query but it didn`t work

 empCriteria.add(Restrictions.disjunction().add(Restrictions.ilike("firstName",  
   userEntered, MatchMode.START)).add(Restrictions.ilike("lastName", userEntered, 
MatchMode.START)));

I know it is just or condition..please let me know is there way to combine two column and do search on using criteria query

Upvotes: 1

Views: 4038

Answers (4)

szymi
szymi

Reputation: 21

You can use Resrtictions.sqlRestriction() with SQL clause.

Upvotes: 2

Diego87
Diego87

Reputation: 1757

There is a way, for example add in your mapping hbm.xml file this( || means concat in oracle):

<property name="completeName"
   formula="FST_NAME || ' ' || LAST_NAME"/>

simply add in java bean, the new proprieties:

private String completeName;
public String getCompleteName() {
    return completeName;
}
public void setCompleteName(String completeName) {
    this.completeName = completeName;
}

After finally you can get this concatenation in your restrinction:

Criteria.add(Restrictions.like("completeName","%"+userEntered+"%").ignoreCase();

ps: if you are mapping directly on java bean us the formula in this way:

@Formula(value="FST_NAME || ' ' || LAST_NAME")
private String completeName;

Upvotes: 0

chiastic-security
chiastic-security

Reputation: 20520

You can't do this with Restrictions.

I'd strongly recommend doing it in HQL rather than with an SQL restriction in a Criteria instance. You're in danger of breaking the portability if you start using SQL: the point of Hibernate is to abstract that layer away.

See this SO question for more details.

Upvotes: 2

Siva Kumar
Siva Kumar

Reputation: 2006

Its not possible to do like that. Since You need to merge two columns. So Please try in sqlRestrinctions.

Upvotes: 1

Related Questions