Java / Hibernate: how to write DAO code for complex SQL queries

Question

My current workstation uses the standard Spring / Hibernate / JSP mix to serve content to the Flex client through XML. There are many ways to access data, but the most common is to call SQL directly into a database and manually convert to XML.

The problem is that as the application gets larger, SQL has become much more complex and more difficult to maintain. As if it weren’t difficult enough to support SQL queries created with StringBuilders, it’s even worse now that SQL arrays are built dynamically using many if statements and loops.

I know that usually the right way is to fetch elements using Hibernate queries and entities. However, in some of our queries, the results cannot be mapped to a single Hibernate object, and I'm afraid that direct SQL should be used.

What will be the right way? Is there a way to make dynamic SQL queries more readable? Is there a way to do this with Hibernate objects?

I apologize for the abstract nature of this issue. I hope you have a good contribution nonetheless;)

Appreciate your comments!

Answer 1

HQL supports queries that retrieve more than one object. Connections 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 object (non-entity!) Consisting of two objects - the product and the price. You can also use List to put different objects from the same result.

Give this tutorial you read.

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

Answer 2

You could move some of the SQL logic into the database and access the data through the views: this, of course, presents its problems (now you have business logic in two places), but it may be worth considering.

Answer 3

Assuming you cannot do what you need in HQL, look at ibatis. This will allow you to configure mappings using specific SQL queries and stored procedures. It will also be another dependency on your project, so keep this in mind as well.