On Java Development

All things related to Java development, from the perspective of a caveman.

Creating Summary Data using SQL Count(*) and Hibernate

without comments


This post shows how to implement a SQL statement with a Count(*) function using Hibernate. This creates a record set that doesn’t map to an existing entity, so the coding needed to pull the data out of the resulting list and to then put them into a container class is a bit different than usual when using Hibernate and mapped entities.

This post also goes beyond being a simple presentation about an SQL statement inside a DAO. Along the way, it explains many Java development concepts that every developer should know. It also exposes forces that should cause the developer to recognize the need for a certain Java Design Pattern.

Maybe this post should have been called “How to Design an Application to use a Mini-Architecture including Services, Business Objects, Data Access and Transfer Objects while using Hibernate to summarize records.“, but the focus is still about how to use the Count(*) function with Hibernate.


The Data To Be Summarized with a Count

Presented below is the data to be summarized. While there are only two records in the file so far, the idea is to count the records belonging to the group identified by the field PSC#01, which will be renamed to PSCNUM01 to make it legal for SQL. No matter how many there are, the SQL execution will result in 1 record for each group of records having the same value in field PSC#01. In this data set, since there is one record assigned to the group assigned with 030AH00996 and one assigned with 1307141760, the result will be two records containing this field with these values and a new column created to contain the count, which will be one in both records.


This is the SQL that will be used to count the records for each group …

… and this is the result.
Since it is necessary to contain the count in its own column, the execution results in one being created for it and since it is the 2nd column for the resulting record, SQL assigned the column name as 00002 .


Putting this SQL to Work

Now that we have a working SQL statement, Hibernate will be used to implement it. We need two things; a container to hold the resulting summary records and the Java class to perform the execution which will place the results into the container class. These are the driving forces involved that call for the use of the Data Access Object design pattern.

The Transfer Object

Shown below is the Java class RATEFILEXBillControlCount that will contain the results. It has only the two fields needed to hold the field values for the record created by the SQL execution. Since this is a simple POJO, there’s not much to say about it other than to say that under this context, this kind of class is known as a Transfer Object (TO). Under a different context, this kind of class is also known as a Value Object (VO).

For the RPG developer, think of the TO as a data structure designed to contain the values of each field of a record in a file. When a collection of TO’s are added to a Java ArrayList object, it becomes analogous to a Multiple Occurrence Data Structure and RPG data structure arrays, with each occurrence representing a single record. ArrayList object are commonly used as the container for multiple TOs and other objects as well and has become the container of choice among the Java developer community. So, expect DAO methods to return ArrayList objects.

One last thing about this TO; normally, in a Hibernate environment, these classes are annotated to tell Hibernate they are to be mapped to the fields of the related file. The @Entity annotation declares the class as a persistent class, associating it with the file to which the contained data is written. (If this were a normal Hibernate HQL statement we would pass the persistent class to Hibernate so that it retrieves the data and places the values of each field into the properties of the TO.) Other annotations define embedded key fields while others define associations to other files, creating joins. All together, these are referred to as “mapping” and in previous releases of Hibernate, this mapping was done using XML files.

The reason for the absence of annotations for this TO is due to the fact that the result list of the SQL does not correspond to a file in the DB. It is also why the logic of the DAO has to manually pull the field values and then cast them into their associated types before storing them into the TO. If this was a mapped entity, Hibernate would have done that work for us and then the method would not be using SQL, using HQL instead.

More about Entities can be read here.

The DAO Method

Shown below is the method used to execute the SQL statement and to process the returned list by placing each record into their own TO.

This method resides in the DAO class among other methods that provide other CRUD operations to the same file. When an application uses multiple files, there will be a corresponding number of TO’s and DAO’s.

What follows is a description of the processing taking place in the DAO.

Beginning at line 12, Java’s StringBuffer is being employed to help build an SQL statement.

Line 17 uses the EntityManager retrieved from the EntityManagerFactory encapsulated in baseproject‘s ApplicationContextManager class. The entityManager object contains the connection to the database and is used to create and execute SQL statements. (The same thing occurs under the covers with an RPG program, but us RPG developers have been conditioned to not have to think about it.)

Lines 19 and 20 are creating ArrayLists to contain the the query execution results and to contain the TOs into which the record’s fields are placed. The resultList object contains the raw records and is analogous to being a JDBC ResultSet object. (Thankfully, with Hibernate, we don’t have to directly use JDBC.) The data elements must be isolated and then placed into a corresponding TO. Since we’re not using a mapped entity, mainly because it is simply a summary product of an SQL execution, we have to manage this process ourselves. When using mapped entities, Hibernate takes care of this for us, placing each data element into their corresponding properties of the TO.

Line 23 executes the query statement, the result of which are placed into the resultList object already described above.

What follows is where all the magic happens.

Line 29 tests to see of there are records to be processed while lines 30 and 31 pulls the object list from the resultList object and obtains the size of the array.

The for-loop at line 32 uses the array’s size to limit the loop where each iteration will be operating upon the resultList object using the index value isolated by the for-statement construct.

Line 35 illustrates the fact that each entry of the resultList object is itself another array of objects. In this case each of these objects are the record’s fields.

Line 37 creates a RATEFILEXBillControlCount object, cleverly calling it record to prepare for the process of isolating the fields from the innerObject.

Lines 38 and 39 isolate the two fields of the record. The fields are not properly represented by their types e.g. String, int, Long, etc. As a consequence they mist be cast into their proper types as they are pulled from innerObject. If this didn’t happen in that manner, then an error would be thrown when trying to call the setter method of the record object so they can be stored in the TO.

With the record’s field now properly placed into the TO, line 42 adds the TO into an ArrayList.

When the for-loop completes this processing for each record, line 55 returns the ArrayList of TOs to the caller.

There. That about does it. That’s everything there is to know about executing an SQL statement that summarizes data into records not part of a mapped Hibernate Entity.

But wait. There’s more.

The Service Layer
Although it is not a focal point of this post, the topic of the Service Layer should be mentioned so that the complete picture of the design pattern is made.

A service class must be included as part of the application’s design to provide a way for a client to interact with some functionality in the application, in this case, the DAO. Therefore, the service’s methods are typically public, with some business meaning. A service class also coordinates the use of each DAO within a commitment control boundary.

The use of business logic/rules are included at this layer of the design which also creates the potential to employ a rules engine. In more complicated designs this logic is often encapsulated in Business Objects.

It is common to see the Service Layer classes packaged with the TOs and DAOs making for a simple and logical organization strategy. The Service layer of an application separates its business logic away from the client or presentation layer and the DAO or persistence layer. This separation of design concerns ensure there is the highest possible potential for reuse across multiple application that may need to use the classes in these layers. Placing business logic into a DAO should set off red flags that indicate the integrity of the design pattern is being compromised.

This post showed how to use Hibernate’s SQL support to summarize data and to extract the results into Transfer Objects to be returned back to the client. It also showed how the classes presented are actually part of a larger design pattern that is used in Java application design. These patterns are widely recognized by the Java development community and therefore there is also a large body of documentation about them.

The design patterns presented in this post may at first appear to be violating the KISS principle, especially when coming from other languages such as RPG. However, when design patterns such as these are not used, there must still some kind of organization of logic to be made. Without these patterns, there exists the undesired potential for unorthodox, unproven and undocumented design approaches that end up being deployed across multiple applications. When multiple developers work without standard approaches to design problems there is then no guarantee the solutions to these common problems will be the same. When the forces and concerns are properly assessed the simplicity of the Service and DAO design pattern become apparent.

With the fact that Java is an object oriented technology, it is extremely important to capitalize on its potential for reuse. Design patterns that separate areas of concerns and organized into layers create the greatest potential for reuse that will also perform well when the application is required to support multiple users. This helps to ensure the application’s maintainability which also helps to ensure the application remains robust and reliable over its lifetime.

Relevant Reading
Domain Driven Design and Development In Practice
Service Layer Guidelines
Decoupling RPG database IO using Rational Open Access: RPG Edition


Leave a Reply

You must be logged in to post a comment.