Rochelle Nicholas-Booth

Learning Java ORM – Resilient Software and PL/SQL

Learning Java ORM

One of the reference books I used when I was learning about ORM about 10 years ago is over 800 pages long. It’s the seminal reference Java Persistence with Hibernate by Christian Bauer and Gavin King (Manning, 2007). It’s a really great book, filled with expert knowledge and lots of code examples, but it takes a great deal of energy and commitment to complete.

Nowadays, a lot of the effort in getting to grips with ORM is handled by development tools, frameworks, and programming language features and supports such as Java annotations. Getting started with this enterprise-critical technology is less work than it used to be, but it’s definitely not a trivial task.

Java-based ORM technology, such as Hibernate and Java Persistence API (JPA), necessarily aim at database portability and interoperability with a wide range of Java tools and technologies. This is a key difference between them and PL/SQL solutions. PL/SQL only has to work with Oracle products, which allows for a smaller footprint and an easier learning experience. Because PL/SQL is owned by Oracle, it is carefully managed across the different versions of the Oracle Database products.

Complex Data-Centric Workflows

Concerning the use of Java/C# ORM technologies, the main antipattern I’d avoid is using high-level languages to directly orchestrate complex data-driven workflows. In other words, the high-level language interacts with database objects. There’s no technical reason to avoid this, but it may result in poorer performance than a hybrid approach with both high-level language(s) and PL/SQL. An example of this is a large, multidatabase, nightly batch job where data is extracted from a wide range of tables, views, and ancillary systems (such as data warehouses).

There may instead be a strong combined business and technical case for doing the bulk of this type of work in a language such as PL/SQL. Why? One reason is that a PL/SQL solution can be considered to provide good separation of concerns. This is because all of the database work is handled in code that is close to (i.e., resides within) the database. It shouldn’t come as a surprise that Oracle Databases provide excellent support for PL/SQL. For example, scheduled jobs can be configured to run in isolation from other systems. Using these facilities in the database avoids the need to implement handcrafted, potentially fragile, high-level language alternatives.

Using PL/SQL skillfully can increase resilience because the heavy lifting is left inside the database. To summarize, the disadvantages of implementing extremely complex data processing batch jobs exclusively in high-level languages are:

  • Complexity
  • Brittle code
  • Future code maintenance cost
  • Poor performance
  • Poor separation of concerns
  • The fact that some high-level frameworks make use of database version–specific features

While most of these points are self-evident, the last one merits a mention. I’ve seen one case of a high-level language object mapping framework where it was only possible to get the database-centric application code to work by making use of a database driver version–specific feature.

At the time, it occurred to me that, aside from the unnecessary complexity and integration effort, the finished code was then tied to that particular version of the database driver. A change to the latter (e.g., for security reasons) might then result in breaking the application code. Mixing database infrastructure and logic in application code is not without its risks.

Tip

The rationale for using a high-level language technology ORM product might even be more political than technical. If the database business logic resides solely inside Java code, then there may be no need to communicate with the database administrators (DBAs). For security reasons, communication might still be required, though, when deploying stored procedure code to the database.

I’ve seen a case like this where a developer didn’t want to have to deal with DBAs and opted instead for a high-level language persistence layer solution. This approach was risky because it was not clear at the time of implementation if the solution would meet the organizational performance requirements. It is better to aim to deliver the best solution for the organization and not make technical decisions that are based on political considerations.

So, what are some of the advantages of using languages like PL/SQL? Here are a few:

  • Keeping database logic in the database
  • Security: PL/SQL code resides inside the database
  • Performance
  • Better management of database exceptions
  • Better management of database business logic errors
  • Encapsulation of business logic
  • Reuse (e.g., PL/SQL procedures, functions, and packages)
  • Separation of concerns
  • Easy access to programming abstractions

I’ll go through these key points in the later sections and chapters. They’re just introduced here to set the context for the discussion. However, I do want to emphasize that the first point about keeping database logic inside the database is extremely important and is often overlooked.

Let’s now look at why SQL is not a good choice for business logic. It’s also a little like trying to cut a pizza with a wrench.