Tuesday, October 20, 2015

Collection-Based DAO on Pure JDBC and Java 8 Without JPA or Hibernate. Do You Wish a Piece of Functional Magic?

Hello everyone! I glad to see you in my modest blog and ask you to let me tell about some codes hacks. A couple of months ago I developed an integration application that very intensive used collection-based API for systems communication. 'Oh!', spoke I to myself, 'I'm using collections, why not use a piece of functional programming magic everywhere where possible?" Really, why not? And now I can to share my experience of 'Collection-oriented' DAO building. Let me start!

The Query Object pattern

Firstly I need to do some preface about the Query Object pattern, which is used in the DAO implementation. In a generic integration application we must solve two main problems: retrieve object collections from one store, i.e. relational database management system, and store them in another one. So we have only two operations: load and save, but there are a lot of object types and loading/saving algorithms. Exactly, we can put all loading/saving operations in one big class, which contains N*2 methods (N means the objects types number). Thus we will just implement the God-Object anti-pattern. If the God object was divided by functional (MDM, pricing, operational data, billing data, etc.), there would be some the largest parts i.e. DAO for MDM. The DAO for MDM would contain a lot of method for every dictionary used in the system and there would a few classes with 2-3 methods and one big class with 20-... methods. This is not the good approach.

My solution is the QueryObject pattern using. Let's encapsulate a load logic for each object type into its own class, also do with saving logic. We will have N*2 small classes with only one business method in each one. The classes can be very easy tested and maintained. Also, the likelihood of conflicts while working with an SCM tool decreases.

The load-logic classes implement the TypedLoadQuery interface. The save-logic classes implement the TypedSaveQuery interface. The interfaces:

The interfaces are used in the same way as the classic DAO in services:

In the above example, the interfaces and their implementations are injected using CDI, but you can use your favourite IoC framework. The implementations of this interfaces can be developed upon any access-layer approaches i.e. JPA, Hibernate or pure JDBC. In other parts of the article I'll describe how to implement the TypedLoadQuery and TypedSaveQuery interfaces using only pure JDBC with some functional hacks.

How to prepare Oracle stored procedures parameters

So, you can call an Oracle stored procedure from your DAO using one of the two ways: get a DB connection in the DAO code and just use it or write a wrapper, getting all common tasks for each QueryObject.

In my application, I use an external API, built on Oracle stored procedures. Each procedure gets a couple of user-defined type collections and each collection contains a primitive attribute of an object from some list. Sounds strange, exactly.

Let me show an example: Client Types Dictionary. Each client type has an Id, a Name and a Business branch of customer's organization. In Java we can write it so:

Also we can assume how a list of client types could look like:

In the API, the stored procedure for customer types processing gets three parameters, each one is an array: an array of Ids, an array of Names and an array of business branches:

Therefore, now we have a task: how to transform a collection of structures to an array of collections of primitives and then call a stored procedure. The idea is to divide the classes responsibility: one class will encapsulate the stored procedures call preparation logic and another one will just call a DB. Also cycles and iterations will be replaced by Java 8 functional magic.

Let's start from Oracle data-types:

And introduce the ArgArray class, holds a collection of primitives. This class has two fields: the first is an Oracle data type of held primitives and the second is a held primitives list.

The heart of the class is the fill method. This factory method builds ArgArray instances filling them by getting the primitive values of the collection beans collection using the mapper functional interface. Therefore, we do not have to run at cycle for each element from the beans collection and call something like "ArgArray#getValues().add(...)". We just declarative describe a parameter mapping algorithm. Also we can return an unmodified list from the getValues() method.

Because the ArgArray class describes only one parameter of the Oracle stored procedures API, we need the collection of ArgArrays. Let's add another class ArgArrayList, encapsulates this collection. Also let's add the ArgArrayListBuilder internal builder class.

The builder class encapsulates the ArgArrayList creation algorithm. In the private constructor, the class gets a beans collection. The collection will be mapped into the Oracle stored procedures API when the build method is called. Method with just brings some syntax sugar for the collection closure. The add method is the most important part of the class, because there is a place to set new declarative described parameters mapping algorithms.

Now we can to do declarative describing of the ArgArrays building algorithm just by the following way:

The code constructions similar like above are the best thing in the new Java version :).

How to call Oracle stored procedures from DAO

The right time to write a class for Oracle stored procedures calling. The call method of the class gets two parameters: a stored procedure name and an ArgArrayList instance. The classes ArrayDescriptor and ARRAY from the JDBC driver are used for make communication with Oracle Database.

Other parts of the class are readable without comments, I think.


I think there is shown how to implement the Data Access Layer without any ORM tool using. The needed amount of code is not so large but this way gives you a chance to significant improve your application performance because ORM tools bring some overhead. I can say more, the reason of ORM using usually is just unfamiliar and misunderstood SQL and database details by java developers. So, developers use ORM tools sometimes not for to hide the Data Access Layer details from other parts of a developed application, but just to hide their own lack of Database understanding.

If you shape an architecture of a high-performance application, or an OLAP-application, or an integration one, I mean if the main grain of data is not an object but a collection of objects, the ORM-less solution is the best choice for you. Please, let you time to think about it.