Enhancing Apache DBUtils when column names do not match getter & setter names

In a recent post on running stored procedures I made use of Apache's DBUtils package as a basic ORM.

I had a problem where certain fields in my POJO were not getting populated.
The reason being that DBUtils does not make use of the '@Column' annotation and therefore some extra code is needed.

In our case it occurred for every column that had an underscore in the name but not in the POJO getters & setters.

A snipped from my POJO could look like this.


Using the same Dao in the post I refferred to above the ResultSetHandler will now be created as follows:

Hibernate - accessing database data using views

I have recently been working on a database for which I had no control
of the structure and it certainly was not laid out as I would have desired.

Interestingly the application that we were replacing had created convenience Views to access the data.
These Views were often complex typically including JOINS across several tables.

So it may be obvious but I decided to map some of my POJOs to these views.

A few provisos:

• The view needs to be designed such that there is still a column(s) that can be mapped to an ID.
• The views are clearly critical to the application so just as in the creation of tables the creation of these views MUST be under version control

Stored Procedures or functions that return POJO objects using hibernate

I have an application that needs to run some stored procedures that want to return List but I struggled with how to get this working.

To quote from the hibernate docs this is an area that they need to work on. It says:

27.5. Database functions

Warning

This is an area in Hibernate in need of improvement. In terms of portability concerns, this function handling currently works pretty well from HQL; however, it is quite lacking in all other aspects.

This is my eventual solution.

First off a Dao class

Here is the interface that the Dao will implement

Then the generic Dao MetOfficeDao
Note the following:

1. The use of ResultSetHandler from Apache is a basic ORM
2. JDBC parameters start from '1'
3. getSession().connection(); is deprecated

Just for completeness - here is an example stored procedure for SQLServer
Note the line:
SET NOCOUNT ON
This is needed so that the procedure just returns the data and not the number of rows returned

SQL info for oracle/sqlserver/mysql

While performing some testing to ensure my app was compliant with multiple db flavours I came across this site.

It is a useful reference to quickly see how the different flavours perform some common operations.

http://www.sqlinfo.net/