Where possible, should SQL queries be eliminated in favour of utilising Hibernate?

Question

Having read theory of Hibernate, I've encountered the idea several times that in an ideal world, you should not mix Hibernate with raw SQL queries, for several reasons such as Hibernate being unable to recognise changes that have been performed in this manner which can have an impact when it comes to flushing properly.

I have been given the task of attempting to optimise and improve several classes which communicate with the database but do so without going through Hibernate. They directly use the JdbcTemplate to run the query, and I've noticed that this has been more commonly done for update statements.

Is this deliberate and good design, for some reason such as only a select number of fields are being updated and the object in the database is quite large, or should I aim to refactor these classes so that they utilise DAO layer classes to perform these update queries?

Answer 1

Personally, after some years with hibernate (which I tent to love a lot more than hate), I see two main reason for when you want to make hibernate only queries. Potentially moving to a different database, caching of the queries would be another. And both, to me, are very valid reasons, if this matters for you. You will probably get more answers here why this is a good thing, so I'm not going to tell you more about this. Instead, I might tell you why this is not always possible with cases that I actually faced.

Spatial queries - geometry and geography data types; there are lots of databases that support this and sometimes it is a very handy feature - I really like MSSQL in this regard, their indexing strategy, how this is handled, etc. There is no way to make these queries in hibernate, that has very limited facilities for this.

Hibernate sometimes hides a lot more that it should (it's not it's fault); if you don't understand what queries will hibernate generate, you might be often very surprised. I suggest you check the actual queries all the time. Another example is when you work with pessimistic/optimistic locks - always look at the queries, it's not always the case where hibernate might generate what you would expect... (AzureSQL does not have select for update - it uses hints, like with row lock, etc - giving you zero assurance that a row will be locked, without a proper index and few resources an entire table or page could be locked)

That being said, I always try to favor Hibernate in this regard, if something does not work as I expect - I use native queries and such IMHO this perfectly shaped world simply does not exist.

Answer 2

There are many benefits in using hibernate with HQL (Hibernate Query Language) instead of Native Queries (or Hibernate instead of JDBC), I am stating 3 of them:

1. Multi-database support: If you are using different databases, this is a requirement, but in your case we can clarify that with an example. Let's say you'll decide to change your database engine someday. If your application is fully on HQL, you just have to switch a parameter in your hibernate conf, and it will take care of "reworking" all your queries, so they are compatibile with your new database. Just this.
2. Java Objects connection: you quite self-defined this aspect (Hibernate being unable to recognise changes that have been performed in this manner which can have an impact when it comes to flushing properly). Having your classes and fields directly connected to database allow you to manage better code and database changes. If your class or fields inside of it are changed, the compiler will check if everything is fine (I mean hibernate side). And if you are using powerful ide like IntelliJ, it will work in the other direction: if you delete or change a field in your database, your ide will warn you that something in your class is wrong (not a valid column, not valid type, etc).
3. Chaching and security features: for example one over all, sql injection. A bad written query can expose you to security issues, but with hibernate it will take care of it. And it also has a powerful caching system... another thing that he will do for you effortless.

Hope this helps