Click here to Skip to main content
15,881,863 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
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

You can use Restrictions (Hibernate API Documentation)[^] calling And method to define multiple conditions having AND.
 
Share this answer
 
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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900