ORM vs. SQL: When should I use a SQL-centric persistence layer?

One of the most common tasks when writing a Java application is to query a relational database through some persistence layer. In a typical setup, this persistence layer consists of a number of DAOs (data access objects) which perform specific queries through Java’s JDBC interface. Most projects use a JPA-based framework for this task, such as Hibernate or EclipseLink. These object-relational mapping (ORM) tools take care of creating valid SQL statements and mapping our domain objects on the relational model of the database and vice versa. By that, they take a great burden off of our shoulders.

Even though ORM tools are very well suited for a lot of use cases, I am convinced, however, that in some situations you have good reasons to refrain from using an ORM tool and instead stick to writing your own database queries. In this article, I’m going to argue for writing SQL statements yourself instead of using an ORM tool in certain situations and will provide the advantages as well as the drawbacks for this approach.

Of course, when you decide to write the database queries for your application yourself, you should then steer clear from using plain JDBC and SQL String literals. Writing plain SQL statements by hand as String literals is extremely error-prone, and the result will be very hard to handle and to maintain in the future. So, instead of crafting all SQL statements yourself, you will rather want to use one of the excellent libraries available out there, which provide you with a clean, Java-based DSL (Domain-Specific Language) for your queries, such as QueryDSL or JOOQ. Using such a library, you write SQL queries and statements as Java code using a fluent API which mirrors the structure of a SQL statement. The library will then create database-specific SQL code from these API calls.

Let’s look at an example written with QueryDSL. Here, a join select statement selects data from two tables and returns the result as a Tuple list:

List<Tuple> shippingAddressList =
  new SQLQuery(connection, new OracleTemplates())
    .from(QUsers.users)
        .join(QAddress.address)
        .on(QUsers.users.shippingAddressID
            .eq(QAddress.address.id))
    .where(QUsers.users.lastName.eq("Smith"))
        .and(QUsers.users.isActive.eq("Y"))
    .list(QAddress.address.all());

In this example, QUsers.users and QAddress.address are proxy objects for the two Oracle database tables USERS and ADDRESS, respectively. As you can see, this statement performs a join of these two tables to select the shipping addresses for all active users with last name “Smith”. The database table proxy classes can be automatically created from the database schema, for example with the QueryDSL Maven plugin.

This approach has some big advantages compared to writing SQL String literals:

  • The database table proxies mirror the current database schema in an exact and type-safe way. This means that if you update the database schema and recreate the table proxy classes, all of your queries which do not match the new schema will not compile anymore. Your IDE will thus point out every query that needs to be adapted. For example, if you rename the database column USERS.LAST_NAME to USERS.SURNAME, the corresponding field in class QUsers is renamed accordingly, so it will be caught by the compiler if you forget to update the statement above.
  • The second advantage is that the type-safe fluent API makes it a lot easier to write database statements since your IDE can give you meaningful content assistance. You don’t have to remember the names and data types of all the database table fields. Your IDE will always provide you with a context-sensitive list of code completions.
  • Since you’re not using any String literals but instead proxy objects and methods, you have a much better overview of where you’re using which database objects. Using your IDE’s search facilities, you can very easily search for all places where one of the fluent API’s objects or methods is referenced or called. Finding the code where some particular database table is queried is thus much simpler and much more concise than having to use a full text search.

ORM tools vs. plain SQL

So, in which situations could it be beneficial to write SQL statements by hand (using a Java DSL as shown above) instead of having them generated by JPA? The following list gives you some ideas of what issues to take into account when trying to decide which approach fits best with your requirements.

  • When you have a preexisting database schema that was initially created without explicitly having usage with an OR-mapper in mind, it could be quite a challenge to find a suitable OR-mapping for this schema. This even more so, if heavy use of stored procedures or triggers is made by the schema.
  • When you have to handle very huge data sets in your database it might be better to implement data manipulation features in the database using stored procedures instead of routing these data sets through your application.
  • You could decide on defining the database as an own service layer to keep all query and database logic in the database. The application will then only be able to call predefined stored procedures which represent the interface to your database backend. This has the advantage that you can change the database schema in the background (to a certain extent) without having to subsequently adapt and redeploy your application code. Furthermore, you can make full use of the performance benefits which can be gained by keeping all database related processing in the DBMS, since you can then use every special proprietary feature that your database offers.
  • If your team has very strong database skills but only weak or not-so-good Java skills, it could be similarly beneficial to keep all database logic in the database. Imagine the situation that you have some very capable database ninjas in your team who are proficient with all the nuts and bolts of the specific database product you are using. Or you might have team members who intimately know the existing database schema and know exactly how they have to query it. In this case it might be much better to let those team members write all queries and database logic with the tools they already know and are most comfortable with, for example using SQL developer and PL/SQL in an Oracle database. You would then get the most efficient persistence layer without having to let those team members work with a technology (e. g. JPA) which might be new to them or clumsy to use.
  • If your application’s persistence layer only consists of relatively few and simple operations, or, in other words, if you only need a few simple CRUD operations, the overhead of creating an OR-mapping for the database schema might not be worth the effort. This is especially the case when your database operations are mainly read-only or your application logic is not very complex and you don’t have the need of a sophisticated data model (e. g., think of a web service that only performs SQL select queries and routes query results through to the service user).
  • When you have a performance critical application it might be better to write your queries with SQL. Of course JPA allows to fine-tune all generated queries in every detail, but nothing beats a well-written SQL query.
  • When two or more of the above items apply to your project, the idea not to use JPA might be worth a second thought.

Drawbacks

There is one major drawback when you decide to use the SQL-based approach: You have to take care of mapping all query results onto your data model yourself. While JPA will take over the task of properly creating your domain objects and filling them with the query result data, you will need to assemble all domain objects yourself. A query formulated with QueryDSL, such as in the example above, returns a list of Tuples. A Tuple is basically a list of single-valued data corresponding to the table columns of your database schema. Depending on the query you executed, a Tuple may contain data from more than one table and thus needs to be distributed over several domain objects. When you have to construct your domain objects from such a SQL result list, employing the Builder Design Pattern is a good choice to help you with this task.

Additionally, using this approach, altering your data model will be more involved than with JPA. If you decide to add a new column to a database table, you have to adapt some or all of the affected queries, and you have to add the new field to the corresponding builder class and include this field in all domain object construction code. Using JPA, you basically only have to add the new field to the corresponding entity class and you’re done.

Conclusion

The article has shown that there are good reasons to do without an OR-mapping tool in some situations and instead use plain SQL. When you decide to go this way, make sure that you use one of the available database query DSL libraries, so that you don’t have to write your SQL queries as String literals.

As with all technological questions and decisions, here, too, it is not possible to state an absolute truth about which solution is the best one. In the end, it all depends on your specific requirements, environment and circumstances. To come to a decision which solution will work best for your use case, it is good to know the options that you have.

Short URL for this post: http://wp.me/p4nxik-2CL
Roland Krüger

About Roland Krüger

Software Engineer at Orientation in Objects GmbH. Find me on Google+, follow me on Twitter.
This entry was posted in Java Persistence and tagged , , , , . Bookmark the permalink.

Leave a Reply