Task - pick a persistence design/tool for a large scale web based system that is mostly read-only.
-Rewrite existing non-java app with existing DB model - not very well designed - current SQL is VERY complex. SQL Server in use.
-Adequate Java experience in the shop. Ability to make any combo of struts, spring, persistence work.
-There will be a DAO layer, separated from the business and controller layers.
Hibernate is more popular than ever - BUT - My concern for running with that is that it seems to me that SQL abstractions like EJB-QL and HQL seem like solutions to problems that don't really exist. That whole SQL thing is pretty solid. The ability to drop SQL into Query Analyzer or to have a third party DBA review/write complex SQL is fundamental. Am afraid we would lose that using HQL and wind up spending more time working the tool than we are the problem set to solve. I did not embrace EJB-QL for my previous EBJ2.x projects.
One thought...mandate HQL for update/delete operations and use straight JDBC (Ibatis perhaps?) for the read only operations. Another thought...bite the bullet and hope HQL can solve all the problems.
Please post experience and solutions with complex SQL via HQL or issues/concerns/best practices in mixing jdbc/hibernate.
My Opinion :
If a person wants to choose between learning SQL, HQL or ?QL..., preference is learn SQL.
Advantages of SQL:
a) Testing & Optimizing the SQL is easy.
b) Developer has more control on the code i.e. SQL & constructing objects out of the result set.
c) DB Specific optimizations can also be done on SQL, in which case your SQL gets tied up with the specific database, anyway, follow the DAO design pattern to overcome this.
Disadvantages of HQL/?QL etc :
a) Debugging is quite difficult, although we have options to view the query generated during runtime, still...the alias used etc..is not convincing
b) Obtaining exactly what we want is sometimes difficult, the tool will try to fetch other unnecessary data too
c) Developer has no control on how the objects are created from the result set or even the query itself.
d) And this is yet another learning curve, with limited scope.
In my projects where I am expected to use hibernate, I use hibernate only for persisting data, wherever I want to fetch data, I use SQL queries, and reflectively construct objects which I find more interesting as well as provided good performance.