Click here to Skip to main content
13,732,381 members
Rate this:
 
Please Sign up or sign in to vote.
I have a table with columns as rows.
Now I want to apply restriction on multiple rows with "and" condition.how can we do this using hibernate criteria?

Example :Employee table

Pk  ColumnName.   Value
1.          Empid.               10
1        EmpName.           Sachin
1.        Empsalary.          10,000
2         Empid.               20
2       EmpName.           Dhoni
2.        Empsalary.          8000

So now i want to fetch pk's which have 
EmpName as sachin and empsal as 10,000

So it should return pk as 1.


What I have tried:

I have a table with columns as rows.
Now I want to apply restriction on multiple rows with "and" condition.how can we do this using hibernate criteria?

Example :Employee table

Pk  ColumnName.   Value
1.          Empid.               10
1        EmpName.           Sachin
1.        Empsalary.          10,000
2         Empid.               20
2       EmpName.           Dhoni
2.        Empsalary.          8000

So now i want to fetch pk's which have 
EmpName as sachin and empsal as 10,000

So it should return pk as 1.
Posted 16-Apr-18 17:25pm
Updated 16-Apr-18 17:34pm
Comments
Wendelius 16-Apr-18 23:25pm
   
Instead of posting a new question, please use the "Improve question" link under your question.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You can use Restrictions (Hibernate API Documentation)[^] calling And method to define multiple conditions having AND.
  Permalink  
Comments
Member 13783074 16-Apr-18 23:36pm
   
No you are wrong..i have tried with that approach..practically its not possible to fetch restricting two rows right? Just think
Member 13783074 16-Apr-18 23:37pm
   
I mean u can restrict multiple columns but not rows
Wendelius 17-Apr-18 13:06pm
   
Do you mean that you have data for the single employee on multiple rows? If that is the case, you would need some kind of exists structure having two conditions inside the exists. Similar to java - hibernate criteria with exists clause - Stack Overflow[^]

However, if the assumption above is correct I would consider having the data for a single employee on a single row. This would make life much easier.
Member 13783074 17-Apr-18 14:02pm
   
Can u write the sql for the above mentioned scenario with exists clause.
Wendelius 17-Apr-18 14:07pm
   
As said I'm not sure if I understand your scenario correctly, but have a try with

SELECT e.Pk
FROM Employee e
WHERE e.ColumnName = 'EmpName'
AND e.Value = 'Sachin'
AND EXISTS (SELECT 1
FROM Employee e2
WHERE e2.Pk = e.Pk
AND e2.ColumnName = 'Empsalary'
AND e2.Value = '10,000')

But as said if the above is correct, I would consider changing the schema.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04-2016 | 2.8.180920.1 | Last Updated 16 Apr 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100