How to use database-specific or Hibernate-specific features without sacrificing portability

Introduction

Like other non-functional requirements, portability is a feature. While portability is very important when working on an open-source framework that will be used in a large number of setups, for end systems, portability might not be needed at all.

This article aims to explain that you don’t have to avoid database or framework-specific features if you want to achieve portability.

Database-specific features

Although the SQL standard has been available for more than 30 years, not all relational databases implement it fully. Some database systems offer non-standard features or different ways of handling a certain feature defined by the SQL standard.

The lowest common denominator of many RDBMS is a superset of the SQL-92 standard. SQL-99 supports Common Table Expressions (CTE), but MySQL 5.7 does not. Only MySQL 8 adds support for CTE.

By adhering to a SQL-92 syntax, one could achieve a higher degree of database portability, but the price of giving up database-specific features can take a toll on application performance.

If you’re developing an enterprise application which is supposed to use Oracle or SQL Server, there is a very good chance that changing the database is not going to be an easy task. That’s because, apart from requiring to do code changes, all operation tasks have to be changed as well.

So, switching to a new database will require:

changing backup scripts

setup replication

monitoring the new database system

So, if your enterprise application is tied to a certain database system, you should take advantage of all feature that it offers.

Avoiding using these database-specific features just because, one day, a different database system might be used, might hurt application performance for no compelling reason after all.

Supporting multiple database systems

Now, let’s assume that you really need to support multiple database systems. Let’s assume that your application can run on both Oracle and MySQL. Portability can be addressed either by generalization or through specialization.

Portability by generalization

You can achieve portability by subtracting non-common features. If we choose portability by generalization, then, for every feature, we have to pick an SQL query that works on both Oracle and MySQL. However, this means we can’t use database-specific features which might be more efficient than a generic SQL query.

This is exactly how JPQL or HQL works. Instead of supporting all database-specific features, JPQL provides only a superset of features that are supported by the vast majority of relational database systems.

JPQL and HQL are meant to fetch entities that need to be modified by the application. However, if you need a DTO projection that requires using Window Functions or CTE, a native SQL query is much more suitable.

Portability by specialization

Another way of addressing portability is to provide adaptors for every specific framework or system that has to be supported.

So, assuming we need to build a report that has to run on both Oracle and MySQL, we can abstract the DAO methods and expose them via interfaces, and have multiple database-specific implementations.

This way, you can achieve portability without sacrificing database-specific features.

JPA providers

JPA is only a specification.

It describes the interfaces that the client operates with and the standard object-relational mapping metadata (Java annotations or XML descriptors). Beyond the API definition, JPA also explains (although not exhaustively) how these specifications are ought to be implemented by the JPA providers. JPA evolves with the Java EE platform itself (Java EE 6 featuring JPA 2.0 and Java EE 7 introducing JPA 2.1).

Hibernate was already a full-featured Java ORM implementation by the time the JPA specification was released for the first time.
Although it implements the JPA specification, Hibernate retains its native API for both backward compatibility and to accommodate non-standard features.

Even if it is best to adhere to the JPA standard, in reality, many JPA providers offer additional features targeting a high-performance data access layer requirements.
For this purpose, Hibernate comes with the following non-JPA compliant features:

By layering the application, it is already much easier to swap JPA providers, if there is even a compelling reason for switching one mature JPA implementation to another.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

In reality, it is more common to encounter enterprise applications facing data access performance issues than having to migrate from one technology to the other (be it a relational database or a JPA provider).

Therefore, not every application will ever need to migrate from one database system to another or support multiple database systems. But even if you do need to support multiple database systems, you can still address portability by specialization.

Subscribe to our Newsletter

* indicates required

Email Address *

10 000readers have found this blog worth following!

If you subscribeto my newsletter, you'll get:

A free sampleof my Video Course about running Integration tests at warp-speed using Docker and tmpfs