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!
Hibernate provide option to execute native SQL queries through the use of SQLQuery object. Hibernate SQL Query is very handy when we have to execute database vendor specific queries that are not supported by Hibernate API. For example query hints or the CONNECT keyword in Oracle Database.
Data Access Objects (or DAOs for short) are used as a direct line of connection and communication with our database. DAOs are used when the actual CRUD (CRUD = Create, Read, Update, Delete) operations are needed and invoked in our Java code. These data access objects also represent the “data layer” of our application.
createQuery(hql); List results = query. list();
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With