Java/Hibernate: how to write DAO code for complex SQLs

Question

Hi all,

My current workplace uses the standard Spring/Hibernate/JSP mix to serve content to its Flex client via XML. There are many ways in which the data is accessed, but the most prevalent one is via direct SQL calls to the database and a manual conversion to XML.

The trouble is that as the application grew bigger, the SQLs became much more complex and hard to maintain. As if it wasn't hard enough to maintain SQLs that were created using StringBuilders, now it's even worse, that the SQLs are constructed dynamically using many if statements and loops.

I know that usually the right way to go is to fetch items using Hibernate queries and entities. However, in some of our requests the results can't be mapped to a single Hibernate entity and I'm afraid direct SQL needs to be used.

What would be the right way to go about this? Is there a way to make dynamic sql queries more legible? Is there a way to do it with Hibernate entities?

I'm sorry for the abstract nature of this question. I hope you have good input nonetheless ;)

Appreciate your comments!

Answer 1

You could consider moving some of the SQL-logic into the database and accessing the data via views: that presents its own problems, of course (you now have business logic in two places) but might be worth considering.

Answer 2

HQL supports queries that fetch more than one entity. Joins are also supported. For example:

SELECT new com.package.model.SearchResultEntry(product, price) FROM Product product, 
    IN(product.variantPrices) price WHERE ....

The above returns a new (non-entity!) object that is composed of two entities - product and price. You can also use a List to put different entities from the same result.

Give this tutorial a thourough read.

Make these queries with dynamic parameters, and use the most appropriate classes to store them as @NamedQueries

Answer 3

Assuming you can't do what you need in HQL take a look at ibatis It will allow you to set up mappings using specific SQL queries and stored procedures. It will also be another dependency on your project so take that into account as well.