Hibernate check equal of two unrelated column

user_1097800 0
Viewed 447

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?

hibernate-criteria criteria hibernate
3 Answers

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();
user_80357980

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

user_23790460

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()
user_10978000
Related