When it comes to accessing relational databases with Java, people usually think of two options
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 a ORM framework is to hide the database access from the user. Another goal is to introduce capability to the database access layer that is do not exits 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 raise 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 an 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 order (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 the full control over the SQL statements because you have to write it by your own.
The code examples shows 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 are just strings and this may cause problems during runtime because the mapping and the SQL is 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 provides 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 to invest in this technology.
With jOOQ you will get a great alternative plus the 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!
