combining 2 or more properties in Hibernate Restrictions criteria query

Question

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

What "didn't work" about it? Can you be more specific? What you've got there looks right. — chiastic-security, Aug 28 '14 at 11:16
that criteria query separately checking two column and 'ORing' result.I want to combine both column and then perform search operation on it . — sar, Aug 28 '14 at 11:22

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

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.

edited May 23 '17 at 12:33

Community

1
1

answered Aug 28 '14 at 11:27

chiastic-security

20,430
4
39
67

score 2 · Answer 2 · edited Jun 08 '18 at 11:40

2

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

edited Jun 08 '18 at 11:40

LuFFy

8,799
10
41
59

answered Jun 08 '18 at 11:21

szymi

21
4

This should be in a comment rather answer box. Either provide a proper answer or it will be deleted. – Ashish Ratan Jun 08 '18 at 12:29

score 1 · Answer 3 · answered Aug 28 '14 at 11:25

1

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

answered Aug 28 '14 at 11:25

Siva Kumar

1,983
3
14
26

score 0 · Answer 4 · answered Oct 11 '16 at 13:49

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;

if || don't work use: concat(concat(FST_NAME, ' '), LAST_NAME) — Diego87, Oct 11 '16 at 13:50

combining 2 or more properties in Hibernate Restrictions criteria query

4 Answers4

Linked