Hibernate check equal of two unrelated column

Question

I have sample entity as following

class Book{
  int id
  String name
  int sid
}

class Author {
   int id
   String name
   List<Book> books
   int mid
}

Its not good design, I know that just trying to explain a scenario.

Now my questions is, Is there any way I can check equal of two unrelated column

e.g

session.createCriteria(Author.class, "author")
       .createAlias("books", "book")
       .add(Restrictions.eq("author.mid","book.sid")
       .list()

Can it be done?

score 1 · Answer 1 · answered Sep 17 '18 at 12:36

1

In my opinion Criteria API doesn't support join unrelated entities. Try to use HQL as follows instead of Criteria API.

session.createQuery("from Book as book INNER JOIN Author as author WHERE book.sid = author.mid").list();

answered Sep 17 '18 at 12:36

Seymur Asadov

612
5
19

you do not have `on` part for join. this query wont work. update your answer – LynAs Sep 17 '18 at 17:28

Narayan Yerrabachu · Answer 2 · 2018-09-17T18:19:29.440

1

I think do not require Restrictions.eq("author.mid","book.sid"). I hope this helps you.

Normal Sql Statement:

SELECT A.*, B.* FROM Author A, Books b WHERE A.ID=B.id ;

Criteria join:

Criteria criteria = session.createCriteria(Author.class);
    criteria.setFetchMode("books", FetchMode.JOIN)
    List list = criteria.list();

if you have further restrictions then you have to add restrictions

 .add(Restrictions.eq("id", 2));

Example Criteria

Subquery-Criteria

Similar issue

edited Sep 17 '18 at 18:19

answered Sep 17 '18 at 12:58

Narayan Yerrabachu

1,714
1
19
31

1

I do need it. I am giving just a sample here. in actual query mid=sid is required – LynAs Sep 17 '18 at 17:27
if requries ,we can also the condition .add(Restrictions.eq("author.mid","book.sid") – Narayan Yerrabachu Sep 17 '18 at 18:19
Then your ex should work : session.createCriteria(Author.class, "author") .createAlias("books", "book") .add(Restrictions.sqlRestriction("author.mid","book.sid") .list() – Narayan Yerrabachu Sep 18 '18 at 07:27

score 1 · Accepted Answer · answered Sep 18 '18 at 05:43

This is probably not the recommended way but Restriction.sqlRestriction worked for me to get the desired output

session.createCriteria(Author.class, "author")
       .createAlias("books", "book")
       .add(Restrictions.sqlRestriction("author.mid","book.sid")
       .list()

Hibernate check equal of two unrelated column

3 Answers3