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.

Although SQL-2003 defines the MERGE operator, PostgreSQL 9.5 favored the UPSERT operation instead.

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 that 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 features 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.

However, JPQL and HQL were never meant to fully replace SQL. Otherwise, why do you think that both the EntityManager and the Hibernate Session offer support for executing native SQL queries?

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:

• extended identifier generators (hi/lo, pooled, pooled-lo)
• transparent prepared statement batching
• customizable CRUD (@SQLInsert, @SQLUpdate, @SQLDelete) statements
• static/dynamic entity/collection filters (e.g. @FilterDef, @Filter, @Where)
• mapping attributes to SQL fragments (e.g. @Formula)
• immutable entities (e.g. @Immutable)
• more flush modes (e.g. FlushMode.MANUAL, FlushMode.ALWAYS)
• querying the second-level cache by the natural key of a given entity
• entity-level cache concurrency strategies
  (e.g. Cache(usage = CacheConcurrencyStrategy.READ_WRITE))
• versioned bulk updates through HQL
• exclude fields from optimistic locking check (e.g. @OptimisticLock(excluded = true))
• versionless optimistic locking
• support for skipping (without waiting) pessimistic lock requests
• support for multitenancy

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.

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW