Java Persistence Done Right

blog-post-img
By Simon Martinelli
07.06.2019

When it comes to accessing relational databases with Java, people usually think of two options:

1. SQL (Structured Query Language)
2. ORM (Object Relational Mapping)

Because the usage of SQL with the Java API JDBC (Java Database Connectivity) is painful and error-prone the first choice is usually an ORM like JPA/Hibernate.

ORM

Let’s have a look at the definition of ORM on Wikipedia:

Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language.

The idea behind an ORM framework is to hide the database access from the user. Another goal is to introduce the capability to the database access layer that does not exist in a relational database like inheritance. But this abstraction is leaky and leads to the so-called impedance mismatch:

The Impedance Mismatch

The object-relational impedance mismatch is a set of conceptual and technical difficulties that are often encountered when a relational database management system (RDBMS) is being served by an application program (or multiple application programs) written in an object-oriented programming language or style, particularly because objects or class definitions must be mapped to database tables defined by a relational schema. Source: Wikipedia

The problem with ORM is that the user by default does not have full control over the database access and this can cause several problems. The most common problem is poor performance caused by the fact that developers usually don’t deep dive into the details of a framework. This naive approach usually leads to too many SQL statements executed by the ORM framework.

The fact that it’s possible to define parent-child relationships in ORM raises the question when and how the children are loaded. By default, this is done in a lazy way. So let’s assume that we have a customer order with many items and we want to fetch the customer orders the generated SQL statements will look like this:

select * from customer_order;

The above query returns all customer orders (e.g. 1,2,3,4). If the program accesses the children the ORM framework will produce a query per customer order:

select * from item where customer_order_id = 1;
select * from item where customer_order_id = 2;
select * from item where customer_order_id = 3;
select * from item where customer_order_id = 4;

This problem is called n+1 select problem and happens in every application that uses ORM. The ORM usually provides techniques to overcome this problem but as initially said developers usually are not ORM experts.

What are the alternatives?

As you can see ORM may not be the silver bullet you’re looking for. But as initially mentioned using SQL can be very painful. Luckily there are two popular alternatives.

1. MyBatis (former iBatis)
2. jOOQ

MyBatis

MyBatis was first released in 2001 under the name iBatis and the idea behind this framework is to map SQL statements to Java objects. In contrast to ORM where the SQL statements are generated by the framework, you have full control over the SQL statements because you have to write them on your own.

The code examples show how you write the SQL statement in an annotation (it also supports XML) and that the method returns a Java object and not a JDBC ResultSet:

public interface DepartmentMapper {

    @Select("select id, name from department WHERE name = #{name}")
    Department findByName(String name);
}

The downside of MyBatis is that there is a lot of mapping work to do. To overcome this disadvantage there is a generator that can help with this task.
But the biggest disadvantage of MyBatis is the lack of type safety. SQL statements are written in Strings and also the mapping is just strings this may cause problems during runtime because the mapping and the SQL are not checked during compile time.

jOOQ

jOOQ is a framework that embraces SQL and makes SQL the primary language to speak to the database from Java in a typesafe and fluent way. jOOQ provides a domain-specific language (DSL). All the artifacts you use with this DSL are generated from the database meta-model.

The difference to MyBatis is that you don’t write SQL in plain text and therefore the compiler can check your SQL statements and you have full code completion in your IDE.

DepartmentDTO department = dsl
        .select(DEPARTMENT.ID, DEPARTMENT.NAME)
        .from(DEPARTMENT)
        .where(DEPARTMENT.NAME.eq("IT"))
        .fetchOneInto(DepartmentDTO.class);

As you can see in the above example you really write SQL! The types in capital letters are constants that are generated from the database meta-model and provide the type-safety with the DSL.

Should I still use ORM?

As usual, the answer is “it depends”. But because of the impedance mismatch and the fact you have to be an expert in ORM and SQL you really should think twice if it’s worth investing in this technology.
With jOOQ you will get a great alternative plus full control over the database access!

What’s Next?

In the next blog post, I will introduce jOOQ as the best way to use SQL in Java applications.
Stay tuned!

Simon Martinelli
Follow Me
Latest posts by Simon Martinelli (see all)