SQL, HQL, SpEL - The Good and The Bad

SQL is the standard when you work with a relational database, but with higher level abstractions we have made similar abstractions of SQL, some still very close to the actual SQL and others much less. These abstractions can be very helpful to not have to deal to much with SQL yourself, but it comes at a cost. In this blog post I will share you my opinion about these SQL abstractions.

Hibernate HQL and Spring SpEL are fairly similar and their actual difference is not the main point of this blog post, as a result I will treat them in one go as an SQL abstraction. The abstractions here are still very close to SQL and can normally be translated to plain SQL without much effort. However it is this translation that is the first issue I have with these abstractions. If you ever have a situation where one of these queries does not return the expected result, you can't execute the query directly on the database and experiment with it. It could be that your translation results in a slightly different query and thus your debugging becomes more difficult.

There is a workaround for this problem, where by logging the query on the database side you can still see exactly which query was being executed. This query can then be investigated, tested and fixed until you achieve the result you need. However, once you have the query that gives the result you require, you to translate it to a HQL query. For more complex queries this may not be that straight forward.

Another issue I have with Hibernate and other abstractions, is that they can easily obfuscate the cost of your query. A plain query typically gives you a rather good idea of how expensive the query will be (not including the absence of indices) regarding joins and data you work on.

Moreover, if you have a query that is costly, fixing it can be nearly impossible. Especially since frameworks such as Hibernate work with objects and map that to the data in the database. There is no way to start a query on a join table as this is typically not mapped to an entity and instead will require a more complex query (and potentially far less performant).

The usage of entities makes dealing with data from a database much easier, but having to load all data can be costly as well. Luckily frameworks such as Spring allow you to map a subset of the data to a view rather than a real entity to avoid having to always fetch all data.

Perhaps a final minor issue with these abstractions is that you can not (at least not you yourselves) can use the custom functions of your database. Most abstractions rely on the function provided by the standard SQL as they need to be able to run on any database, which means you can miss out on some helpful functions.

For this reason, all these frameworks typically still offer you the option to write your own native SQL queries, and I have come to the conclusion that for most advanced use cases, this is what you should be doing. Basic queries can still be handled by the frameworks directly though.

A last note on Spring and creating queries based on method names, although I find this a very useful feature, this should even more be limited to very basic queries. Not only because it abstracts the query even more, but also because the method names tend to becomes very long and very difficult to understand.

I know this is not a popular opinion, but I like to have control when it matters, and I will by no means try to do everything with Hibernate. On the contrary, I might rather quickly jump to native SQL queries. Am I the only one who does this?