[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 704
  • Last Modified:

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.
0
SaiLoveAll
Asked:
SaiLoveAll
1 Solution
 
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now