Hibernate Query using joins

I was tring to get the below sql query using hibernate query

select u.username,uf.email from user u
INNER JOIN userInfo uf WITH (NOLOCK) ON uf.userinfoid = u.userid
where
u.username = 'joe'

I have started some thing like the below.

public User getUsersByName(String uname, String email) {
                           User user = null;
                  try {
            Session session = HibernateUtils.currentSession();
            user = (User)session.createCriteria(User.class)
                                          .add(Restrictions.eq("userName", uname)) // this is from user tabel
            .add(Restrictions.eq("email", email)) // this field is from userinfo tabel
            .uniqueResult();
                                          HibernateUtils.closeSession();
                  } catch (Exception e) {
                        System.out.println(e);
                  }
                  return user;      
            }

had no success. Can any body direct me in the right way that i can make it work . Thanks.
SaiLoveAllAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anilallewarCommented:
Do you have relationship set up in your HBM files between userInfo and User table? If so, then it would be simple; else you might need a transformer which is a VO to which the results are stored.
For Joined entities using one-to-many relationship

Criteria criteria = session.createCriteria(User.class).add(
Restrictions.eq("userName", uname)).setFetchMode("userInfoSet", FetchMode.JOIN);
-----------------------------------
Using transformer - This is assuming you have UserReturn bean with 2 attributes username and email. 

String hQuery = "select u.username,uf.email from user u INNER JOIN userInfo uf WITH (NOLOCK) ON uf.userinfoid = u.userid where u.username = ?";
System.out.println("hQuery:" + hQuery);

Query operatorQuery = session.createSQLQuery(hQuery).addScalar("username", Hibernate.STRING)
					.addScalar("email", Hibernate.STRING).setResultTransformer(
							Transformers.aliasToBean(UserReturn.class));

operatorQuery.setString(0, "joe");

List results = operatorQuery.list();

Open in new window

0
SaiLoveAllAuthor Commented:
I have the Mapping files and figured it out by joining them in hbm.xml file. And its simple as you said anil.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.