In the previous section,we tried to find a persisted user by user id which was primary key in that case. What if we want to search a user by user name,age or email and none of these attributes is a primary key. Hibernate provides a query language similar to the standard SQL to perform operations on the Hibernate objects. The advantage of using HQL instead of standard SQL is that SQL varies as different databases are adopted to implement different solutions,but HQL remain the same as long as you are within the domain of Hibernate; no matter which database you are using. Let us learn to embed HQL in our example to find the users with age greater than 30. Before doing this,make sure that you have some of the users over the age of 30. For example,i issued the following query on my Oracle console to update the age of few users.
update users set age=31 where user_id in(5,8,9,11,13,14);
Modify this query to include some of the records in your users table.
Let's write the logic to find the users in our business component. Add the following method in UserManager.
public java.util.List getUsersByAge(int minAage) { org.hibernate.Query q = session.createQuery("from User u where u.age >= :minAge"); q.setInteger("minAge", minAage); return q.list(); }
Note that the query uses "User" which is the object name and not the table name which is "Users". Similarly the "u.age" is an attribute of the User object and not the column name of the table. So whatever the name of the table may be,and whatever the presentation of that table or columns at the database level may be,we will use the same standard syntax while communicating with the hibernate. Also note the parameter "minAge". The parameters in an HQL query are represented with ":"(colon) character and can be bound using the index or the parameter name in org.hibernate.Query.
Let's write a piece of ode to test this functionality. Add the following method in TestClient.
public void testFindByAge(UserManager manager) { java.util.Iterator users = manager.getUsersByAge(30).iterator(); while(users.hasNext()) { User user = (User)users.next(); System.out.println("User found with ID=" user.getUserId() "\n" "\tName=" user.getLastName() "\n" "\tEmail=" user.getEmail() ""); java.util.Iterator numbers = user.getPhoneNumbers().iterator(); while(numbers.hasNext()) { PhoneNumber phone = (PhoneNumber)numbers.next(); System.out.println("\t\tNumber Type:" phone.getNumberType() "\n" "\t\tPhone Number:" phone.getPhone()); } } }
Add the call to this method in main. To do this,add the following line in main.
client.testFindByAge(manager);
Run the program and see that all the users we modified above are displayed with respective phone numbers.
Following points should be kept in mind when working with HQL.
Queries are case insensitive,except the Java class and attribute names. Hence SELECT and select are the same,but User and user are not.
If the class or package does not find a place in imports,then the objects have to be called with the package name. For example,if com.visualbuilder.hibernate.User is not imported,then the query would be "from com.visualbuilder.hibernate.User" and so on.
For more details on HQL,visit http://www.hibernate.org/hib_docs/v3/reference/en/html/queryhql.html
Total Members
