Is HibernateCallback best for executing SQL/procedures?

Question

I'm working on a web based application that belongs to an automobil manufacturer, developed in Spring-Hibernate with MS SQL Server 2005 database.

There are three kind of use cases:

1) Through this application, end users can request for creating a Car, Bus, Truck etc through web based interfaces. When a user logs in, a HTML form gets displayed for capturing technical specification of vehicle, for ex, if someone wanted to request for Car, he can speify the Engine Make/Model, Tire, Chassis details etc and submit the form. I'm using Hibernate here for persistence, i.e. I've a Car Entity that gets saved in DB for each such request.

2) This part of the application deals with generation of reports. These reports mainly dela with number of requests received in a day and the summary. Some of the reports calculate Turnaround time for individual Create vehicle requests.

I'm using plain JDBC calls with Preparedstatement (if report can be generated with SQLs), Callablestatement (if report is complex enough and needs a DB procedure/Function to fetch all details) and HibernateCallback to execute the SQLs/Procedures and display information on screen.

3) Search: This part of application allows ensd users to search for various requests data, i.e. how many vehicle have been requested in a Year etc. I'm using DB procedure with CallableStatement..Once again executing these procedures within HibernateCallback, populating and returning search result on GUI in a POJO.

I'm using native SQL in (2) and (3) above, because for the reporting/search purpose the report data structure to display on screen is not matching with any of my Entity. For ex: Car entity has got more than 100 attributes in itself, but for reporting purpose I don't need more than 10 of them.. so i just though loading all 100 attributes does not make any sense, so why not use plain SQL and retrieve just the data needed for displaying on screen.

Similarly for Search, I had to write procedures/Functions because search algorithm is not straight forward and Hibernate has no way to write a stored procedure kind of thing.

This is working fine for proto type, however I would like to know

a. If my approach for using native SQLs and DB procedures are fine for case 2 and 3 based on my judgement. b. Also whether executing SQLs in HibernateCallback is correct approach?

Need expert's help.

Answer 1

You say

For ex: Car entity has got more than 100 attributes in itself, but for reporting purpose I don't need more than 10 of them.. so i just though loading all 100 attributes does not make any sense

but HQL in hibernate allows you to do a projection (select only a subset of the columns back). You don't have to pull the entire entity if you don't want to.

Then you get all the benefits of HQL (typing of results, HQL join syntax) but you can pretty much write SQLish code.

See here for the HQL docs and here for the select syntax. If you're used to SQL it's pretty easy.

So to answer you directly

a - No, I think you should be using HQL b - Becomes irrelevant if you go with my suggestion for a.

Answer 2

You should strive to use as much HQL as possible, unless you have a good argument (like performance, but do a benchmark first). If the use of native queries becomes to excessive, you should consider whether Hibernate has been a good choice.

Note a few things:

• you can have native queries and stored procedures that result in Hibernate entities. You just have to map the query / storproc call to a class and call it by session.createSQLQuery(queryName)

• If you really need to construct native queries at runtime, the newest version of hibernate have a doWork(..) method, by which you can do JDBC work.

Answer 3

I would like to know (...) if my approach for using native SQLs and DB procedures are fine for case 2 and 3 based on my judgment

Nothing forces your to use a stored procedure for case 2, you could use HQL and projections as already pointed out:

select f.id, f.firstName from Foo f where ...

Which would return an Object[] or a List<Object[]> depending on the where condition.

And if you want type safe results, you could use a SELECT NEW expression (assuming you're providing the appropriate constructor):

select new Foo(f.id, f.firstName) from Foo f

And you can even return non entities

select new com.acme.LigthFoo(f.id, f.firstName) from Foo f

For case 3, the situation seems different. Just in case, note that the Criteria API is more appropriate than HQL to build dynamic queries. But it looks like this won't help here.

I would like to know (...) whether executing SQLs in HibernateCallback is correct approach?

First of all, there are several restrictions when using stored procedures and I prefer to avoid them when possible. Secondly, if you want to return entities, it isn't the only way and simplest solution as we saw. So for case 2, I would consider using HQL.

For case 3, since you aren't returning entities at all, I would consider not using Hibernate API but the JDBC support from Spring which offers IMHO a cleaner API than Session#connection() and the HibernateCallback.

More interesting readings:

References

• Hibernate Core reference guide
  • 14.6. The select clause (about the select new)
  • 16.1.5. Returning non-managed entities (about ResultTransformer)
  • 16.2.2. Using stored procedures for querying

Resources

• Hibernate 3.2: Transformers for HQL and SQL

Related questions

• hibernate SQLquery extract variable
• hibernate query language or using criteria