Hibernate help with a criteria join query

Question

I have four tables, USER, CONTACT, CONACT_TYPE, and USER_CONTACT

USER_CONTACT stores all of the contacts that a user has tables populated with dummy data are as follows

USER TABLE

USER_ID(int)| FIRST_NAME(varchar(2) | LAST_NAME(varchar(2) |
------------------------------------------------------------
|    1      |       TEST            |       USER           |
------------------------------------------------------------

USER_CONTACT

USER_CONTACT_ID(int) | USER_ID(int) | CONTACT_ID(int) |
-------------------------------------------------------
|     1              |       1      |         1       |
|     2              |       1      |         2       |
|     3              |       1      |         3       | 
-------------------------------------------------------

CONTACT

CONTACT_ID(int) |  CONTACT_TYPE_ID(int) | CONTACT(varchar(2)|
-------------------------------------------------------------
|      1        |         2             |  (555) 555-5555   |
|      2        |         2             |  (555) 593-3938   |
|      3        |         1             |  test@oracle.com  | 
-------------------------------------------------------------

CONTACT_TYPE

CONTACT_TYPE_ID(int) | CONTACT_TYPE | 
-------------------------------------
|       1            |   EMAIL      | 
|       2            |   PHONE      | 
-------------------------------------

What I am trying to do is create a query that will return a List that has contains only the PHONE CONACT_TYPE here is my hibernate function so far

public List<UserContact> getUserContactByType(Integer userId, String contactType) {
      Session session = getSessionFactory().openSession();

      try {
           Criteria criteria = session.createCriteria(UserContact.class, "USER_CONACT");

           criteria.add(Restrictions.eq("USER_CONACT.userId, userId");

           criteria.add(Restrictions.eq("USER_CONTACT.contact.contactType.contactType", contactType);

           return (List<UserContact>)criteria.list();

      }

}

Each table is mapped to a model class. The important class information is as follows.

Contact.java

Contains a @ManyToOne relationship to ContactType.java class

@ManyToOne(optional = false, fetch = FetchType.EAGER)
private ContactType contactType;

UserContact.java

Contains a @ManyToOne relationship to Contact.java class and a @ManyToOne on the User.java class

@ManyToOne(optional = false, fetch = FetchType.LAZY)
private Contact contact;

@ManyToOne(optional = false, fetch = FetchType.LAZY)
private User user;

All of the classes have the standard getters and setters for all of the column attributes for the tables above also.

I keep getting an error stating that it cannot resolve the property contact.contactType of my UserContact class. Anyone know how to properly execute something like this in hibernate?

Answer 1

Now you can use use Spring-Data-JPA with QueryDSL support!

Answer 2

I figured it out, I was unaware of a createAlias function. Solution is below if anyone is wondering.

public List<UserContact> getUserContactByType(Integer userId, String contactType) {
      Session session = getSessionFactory().openSession();

      try {
           Criteria criteria = session.createCriteria(UserContact.class, "USER_CONACT");

           criteria.add(Restrictions.eq("USER_CONACT.userId, userId");

           criteria.createAlias("USER_CONACT.contact", "c");

           criteria.add(Restrictions.eq("c.contactType.contactType", contactType);

           return (List<UserContact>)criteria.list();

      }

}