Hibernate HQL

Posted on by

Getting list of Users from users Table

     sessionFactory = createSessionFactory();
     Session objSession = sessionFactory.openSession();
     objSession.beginTransaction();
		
     Query objQuery = objSession.createQuery("from Users");
     List<Users> arrUsers = objQuery.list();
		
     objSession.getTransaction().commit();
     objSession.close();
		
     System.out.println(arrUsers.size());

     for (Users users : arrUsers) {
	System.out.println(users.getUserName());
     }

Pagination Using HQL

    Query objQuery = objSession.createQuery("from Users");		
    objQuery.setFirstResult(5);
    objQuery.setMaxResults(2);
    List<Users> arrUsers = objQuery.list();
				
    objSession.getTransaction().commit();
    objSession.close();
		
    System.out.println(arrUsers.size());
		
    for (Users users : arrUsers) {
	System.out.println(users.getUserName());
    }

Note: In Pagination the Starting record is specified by setFirstResult and ending record is specified by setMaxResults.

Taking a Specific Column for Entity

	Query objQuery = objSession.createQuery("select UserName from Users");		
	objQuery.setFirstResult(5);
	objQuery.setMaxResults(2);
	List<String> arrUsers = (List<String>)objQuery.list();

	objSession.getTransaction().commit();
	objSession.close();

	System.out.println(arrUsers.size());

	for (String users : arrUsers) {
		System.out.println(users);
	}

Note :
The Object Name in entity should be same as specified in class including Case. username will not work in select query but UserName does.

Parameter Binding in Hibernate
Method 1

  Query objQuery = objSession.createQuery("from Users where UserId >?");
  objQuery.setParameter(0, 5);		
  List<Users> arrUsers = (List<Users>)objQuery.list();

  for (Users users : arrUsers) {
	System.out.println(users.getUserName());
  }

Method 2

     Query objQuery = objSession.createQuery("from Users where UserId > :limit");
     objQuery.setInteger("limit", 5);
		
     List<Users> arrUsers = (List<Users>)objQuery.list();
				
     objSession.getTransaction().commit();
     objSession.close();
	
     for (Users users : arrUsers) {
	System.out.println(users.getUserName());
     }

NamedQuery vs NamedNativeQuery
NamedQuery helps to consolidate all query at particular place.

users.java

@Entity
@NamedQuery(name="Users.byUserId", query="from Users where UserId=?")
public class Users {
	@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
	private int UserId;
	private String UserName;
	
	public int getUserId() {
		return UserId;
	}
	public void setUserId(int userId) {
		UserId = userId;
	}
	public String getUserName() {
		return UserName;
	}
	public void setUserName(String userName) {
		UserName = userName;
	}	
}

CreationScript.java

	sessionFactory = createSessionFactory();
	Session objSession = sessionFactory.openSession();
	objSession.beginTransaction();
		
	Query objQuery = objSession.getNamedQuery("Users.byUserId");
        objQuery.setInteger(0, 5);
		
	List<Users> arrUsers = (List<Users>)objQuery.list();
	
	for (Users users : arrUsers) {
	   System.out.println(users.getUserName());
	}

NativeQueries helps us to query the table directly by using table name instead of querying through Entity like one in NamedQuery.This is useful when we use stored procedure to take our resultSets.

users.java

@Entity
@NamedNativeQuery(name="Users.byUserId", query="SELECT * from Users where UserId=?", resultClass=Users.class)
public class Users {
	@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
	private int UserId;
	private String UserName;
	
	public int getUserId() {
		return UserId;
	}
	public void setUserId(int userId) {
		UserId = userId;
	}
	public String getUserName() {
		return UserName;
	}
	public void setUserName(String userName) {
		UserName = userName;
	}	
}

Note: resultClass=Users.class should be specified or else object class cast exception would be thrown.

Comments are closed.