How to achieve equi join using hibernate criteria from hbm.xml files

Question

how to write criteria for this query in hibernate,

select u.userID, ur.authority from users u, user_roles ur where u.userID = ur.userID and u.userName ="sandy"

i tried this way but i am getting an empty list,

DetachedCriteria ownerCriteria = DetachedCriteria.forClass(users.class);
ownerCriteria.setProjection(Property.forName("userID"));
ownerCriteria.add(Restrictions.eq("userName", "sandy"));

Criteria criteria = session.createCriteria(user_roles.class);
criteria.add(Property.forName("userID").in(ownerCriteria));
System.out.println(criteria.list());

Can some one help me in this issue?

Answer 1

I can see you have u.userID = ur.userID in your query, doesnt that mean there is a one-to-many relationship between user and user_roles?

if the answer is yes, you can do something like this:

Criteria criteria = session.createCriteria(user_roles.class,"ur")
    .createCriteria("userDto","u")
    .add(Restrictions.eq("userName", "sandy"));

Then add the projection like in your query:

criteria.setProjection( Projections.projectionList()
    .add(Projections.property("u.userID").as("userID"))
    .add(Projections.property("ur.authority").as("authority")) );

And you can itarete in the resulting list lke this:

for(Object[] item:criteria.list()){
    System.out.println( (String)item[0] ); //User id
    System.out.println( (String)item[1] ); //Authority
}

About your code:

criteria.add(Property.forName("userID").in(ownerCriteria));

You're comparing a property against a subquery, so it's probably not doing what you think it does, but i might be wrong with my guessing.