Implementing a custom unit sometimes means querying the database in the unit service class. This article wants to give you an introduction on how to retrieve the hibernate session in the unit service and how to use Hibernate efficiently to query the database.
Introduction
Hibernate is an object/relational mapping tool for Java environments. The term object/relational mapping (ORM) refers to the technique of mapping a data representation from an object model to a relational data model with a SQL-based schema. Hibernate not only takes care of the mapping from Java classes to database tables (and from Java data types to SQL data types), but also provides data query and retrieval facilities and can significantly reduce development time otherwise spent with manual data handling in SQL and JDBC.
Hibernate is integrated in WebRatio 5, so that anytime you generate the data model of your Web project, the tool creates all the Hibernate files needed to handle the connection to the database and its querying. So, the first thing to do when you are going to use Hibernate in an unit service class is to check that the tables you want to query are mapped into the data model. If not, WebRatio can not generate the hibernate configuration file and the bean classes for these tables, so you can not use Hibernate to do the query but you have to use a standard SQL query instead.
In order to use Hibernate in the unit service we need to place in the Unit project all the hibernate configuration files and the bean classes related to the data model. So you have to generate at least the data model and to copy all these file in a new package that you can name as you want. It's important that you remember to overwrite these files any time you make changes to the data model and generate it.
At this point you are ready to write the unit service.
Retrieve the Hibernate Session
First of all, you have to correctly declare the unit service, depending on the unit type.
- For a content unit, the service must extend the AbstractDBService class and implement the RTXContentUnitService interface
- For an operation unit, the service must extend the AbstracDBService class and implement the RTXOperationUnitService and the RTXTransactionAware interfaces
In both cases you have to declare a private attribute "hbService" of type HibernateService. In the unit service constructor, you have to retrieve the Hibernate service. There are two different ways to do this. If the unit descriptor has the "Database" node, you can get this node and than retrieve the service using the following code
String dbId = DescriptorHelper.getChildValue(descr, "Database", true, this);
hbService = (HibernateService) mgr.getService(dbId + ".hibernate", HibernateService.class);
Otherwise you can use only the second line of code using as first parameter of the getService method the string representing the name of the hibernate configuration file that specifies the connection properties.
Now you have to retrieve the Hibernate session in order to make queries. This is also different depending on the unit type. For a content unit you have to write this piece of code, which opens a new session and closes it after you made all your queries.
Session hbSession = null;
try {
hbSession = openSession(hbService, pageContext, sessionContext);
/* executes the query */
return bean;
} catch (Throwable e) {
throw new RTXException(e);
} finally {
commitAndClose(hbSession, true);
}
For an operation unit, use the following code, which retrieves the connection from the transaction (if any) in which the unit is placed.
Session hbSession = null;
try {
// obtains an Hibernate session
hbSession = locateTransactionSession(hbService, operationContext);
if (hbSession != null) {
inTransaction = true;
} else {
hbSession = openSession(hbService, operationContext, sessionContext);
}
/* executes the query */
return bean;
} finally {
commitAndClose(hbSession, !inTransaction);
}
Moreover, due to the fact that the operation unit service implements the RTXTransactionAware interface, you must define the "inTransaction" method, that you can copy from the following code:
public void inTransaction(Map operationContext, Map sessionContext) throws RTXException {
/* locate the db transaction */
DBTransaction tr = locateDBTransaction(hbService, operationContext);
if (tr == null) {
createAndStoreDBTransaction(hbService, operationContext);
}
}
Construct the query
Hibernate provides different methods to query the database. In this section you will see which one is better to be used and in which situation.
The simplest query you may want to do is to retrieve a single record given its unique identifier. There are two different methods that you can use to retrieve the record. The first one is the "load" method, which has to be used only if you are sure that a row with the given identifier exists. This restriction is due to the fact that, if the record does not exists, this method throws an ObjectNotFoundException. If you are not sure of the record existence, you have to use the "get" method instead. Here is the syntax for both of them. Both methods request two parameters: the first one is the class file of the entity object you want to retrieve and the second one is its identifier. The identifier must be of the same Java type declared in the hibernate entity configuration file.
User aUser = hbSession.load(User.class, new Integer("1"));
User aDifferentUser = hbSession.get(User.class, new Integer("1"));
If your entity uses a composite key, than you have to construct an object that contains all the primary keys and than use that object as the identifier. Suppose to have the User class with two primary keys, the name and the surname. Then we have to write this piece of code:
User userPK = new User();
userPK.setName("aName");
userPK.setSurname("aSurname");
User aUser = hbSession.load(User.class, userPK);
User aDifferentUser = hbSession.get(User.class, userPK);
Hibernate allows to construct more complex queries using the Criteria interface. Using this interface you can specify conditions and ordering clauses for the query. To create a Criteria for the entity you have to use the "createCriteria" method.
Criteria firstCriteria = hbSession.createCriteria(User.class);
Once created a Criteria you can add conditions to the query using the "add" method. This method has one parameter that is a Criterion. Here are some examples about how to add conditions to the query.
// retrieves the user having this particular username
User aUser = (User) hbSession.createCriteria(User.class).add(Restrictions.eq("username","aUsername")).uniqueResult();
// retrieves the user having this particular username and password
User aUser = (User) hbSession.createCriteria(User.class)
.add(Restrictions.and(Restrictions.eq("username","aUsername"),Restrictions.eq("password","aPassword")))
.uniqueResult();
// retrieves the user having one of the given usernames
User aUser = (User) hbSession.createCriteria(User.class)
.add(Restrictions.or(Restrictions.eq("username","aUsername"),Restrictions.eq("username","aDifferentUsername")))
.uniqueResult();
// retrieves the list of user belonging to the group with 1 as identifier*/
List users = (List) hbSession.createCriteria(User.class)
.createCriteria("user2Group")
.add(Restriction.idEq(1)).list();
The Restriction class allows to specify different conditions using different methods for different predicates.
For other examples please look at the official Hibernate documentation.
You can also use the HQL query language to construct the query. In this case you have to use the createQuery(..) method of the Session object which returns an Hibernate Query object. The Hibernate Query permits you to set query parameters using wildcards.
Query query = hbSession.createQuery("from Product p where p.price > :price");
query.setParameter("price", new Float(1000));
List products = query.list();
for (Iterator iter = products.iterator(); iter.hasNext();) {
Product aProduct = (Product) iter.next();
String name = aProduct.getName();
Float price = aProduct.getPrice();
}
Otherwise you can use the traditional SQL query language using
SQLQuery query = hbSession.createSQLQuery("select p.name, p.price from PRODUCTTABLE as p where p.price > :price");
query.setParameter("price", new Float(1000));
List result = query.list();
for (Iterator iter = result.iterator(); iter.hasNext();) {
Object[] row = (Object[]) iter.next();
String name = (String) row[0];
Float price = (Float) row[1];
}
Manage the query result
The result of the query is different according to the number of records extract by the query. If the result is a single instance, then, casting the result into the object class of your choice, you can use all the get methods defined for that class to retrive information. Here is an example.
// retrieves a single property of the loaded object
String username = aUser.getUsername();
String password = aUser.getPassword();
// retrieves the user group navigating the one-to-many relationship
Group group = aUser.getUser2Group();
// retrieves the group name using the get method of the related class
String groupName = group.getGroupName();
If the result is a List of object, you have to check whether the list is null or empty and then you can iterate on the results,cast each result into the object class of your need and use the get methods as shown in the previous paragraph. Here is a sample code.
List users = (List) hbSession.createCriteria(User.class)
.createCriteria("user2Group")
.add(Restriction.idEq(1)).list();
for (Iterator iter = users.iterator(); iter.hasNext();) {
User aUser = (User) iter.next();
String username = aUser.getUsername();
String password = aUser.getPassword();
}
You can also make changes to an object retrieved by the query. To do this you just have to set the object properties using the set methods of the object class and to save the changes.
User aUser = (User) hbSession.createCriteria(User.class).add(Restrictions.eq("username","aUsername")).uniqueResult();
aUser.setPassword("aPassword");
hbSession.save();
hbSession.flush();