Spring+hibernate Native Query Execution:

Today I find very interesting way to execute the Native SQL Query in spring using Hibernate Template.

As we know hibernate Template support built in method to execute frequently queries like find all find by property

getHibernateTemplate().find("from "+className+" where "+propertyName+"="+value);

it’s cool but how we can execute the custom SQL queries having join etc. Long story in short below is my custom query having join and I execute it using hibernate template in Sping framework. I show full code of my DAO method.

/**
     * 
     * @param loginId
     * @return
     */
    public DetailUserInfoDTO getDetailUserInfo(String loginId) 
    {
    final String sQryToFind = "select users.*,type_desc,school.school_id,school.school_desc from users "+
    " join user_type on user_type.user_type_id=users.user_type_id"+
    " join school_users on users.user_id=school_users.user_id "+
    " join school on school_users.school_id=school.school_id "+
    " where login='"+loginId+"'";

    List ResultList = (List)abstractSpringDao.getHibernateTemplate().execute(
    new HibernateCallback() {
    public Object doInHibernate(Session session) throws HibernateException {
    SQLQuery sq =session.createSQLQuery(sQryToFind);
    return sq.list();
    }});
    DetailUserInfoDTO detailUserInfo=new DetailUserInfoDTO();
    
    if(ResultList.size()>0){
        for(int i=0;i<=ResultList.size();i++){
        Object[] row = (Object[]) ResultList.get(i);
        detailUserInfo.setFirstName((String)row[1]);
        detailUserInfo.setLastName((String)row[2]);
        detailUserInfo.setLogin((String)row[3]);
        detailUserInfo.setSchoolDesc((String)(String)row[7]);
        detailUserInfo.setSchoolId((Integer)row[6]);
        detailUserInfo.setUserId((Integer)row[0]);
        detailUserInfo.setUserTypeDesc((String)row[5]);
        detailUserInfo.setUserTypeId((Integer)row[4]);
        break;
        }
    }
    return detailUserInfo;

    }

here is my DTO with name DetailUserInfoDTO 

/**
 * @DetailUserInfoDTO:  this DTo is used to map the DataBase user information
 * @author noman.sadiq
 * @date: 12-01-2011
 *
 */
public class DetailUserInfoDTO {
    
    public int userId;
    public String firstName;
    public String lastName;
    public String login;
    public int userTypeId;
    public String userTypeDesc;
    public int schoolId;
    public String schoolDesc;

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getLogin() {
        return login;
    }

    public void setLogin(String login) {
        this.login = login;
    }

    public int getUserTypeId() {
        return userTypeId;
    }

    public void setUserTypeId(int userTypeId) {
        this.userTypeId = userTypeId;
    }

    public String getUserTypeDesc() {
        return userTypeDesc;
    }

    public void setUserTypeDesc(String userTypeDesc) {
        this.userTypeDesc = userTypeDesc;
    }

    public int getSchoolId() {
        return schoolId;
    }

    public void setSchoolId(int schoolId) {
        this.schoolId = schoolId;
    }

    public String getSchoolDesc() {
        return schoolDesc;
    }

    public void setSchoolDesc(String schoolDesc) {
        this.schoolDesc = schoolDesc;
    }
}
 

little bit explanation of the query,

  for(int i=0;i<=ResultList.size();i++){
        Object[] row = (Object[]) ResultList.get(i);
        detailUserInfo.setFirstName((String)row[1]);
        detailUserInfo.setLastName((String)row[2]);
        detailUserInfo.setLogin((String)row[3]);
        detailUserInfo.setSchoolDesc((String)(String)row[7]);
        detailUserInfo.setSchoolId((Integer)row[6]);
        detailUserInfo.setUserId((Integer)row[0]);
        detailUserInfo.setUserTypeDesc((String)row[5]);
        detailUserInfo.setUserTypeId((Integer)row[4]);
        break;
        }

I want a single object from database so end the loop after 1 iteration depend of your data in database table. ok now keep in mind setting the value in iteration will be same hierarchy as you find the result when run your query in database this is the only rule. Like I received the result from above query in this pattern  userId,FirstName,LastName...

Thanks