ORM/jOOQ: Doing SQL right
JCrete 2014
ORM/jOOQ: Doing SQL right
| ORM/jOOQ: Doing SQL right | |||
|---|---|---|---|
| Convenor: Alvaro Hernández | |||
Participants:
| |||
Summary:
This session was an open discussion about how databases are accessed from Java, what is being used, what are people happy about, what are people unhappy about. A quick survey of the participants (a good 20) showed that only about 2 people were actively using jOOQ. At least one person seemed an expert on Hibernate. EJBs, JDBC and aBatis (dead project according to [1] but its code now included into https://github.com/tobykurien/xtendroid xtendroid]) are also used (but weren't really discussed here). The ways databases are accessed can be very different and lead to very different access patterns. Some examples given were
In such a diverse problem domain there may be no solution which fits all, and specific solutions may need to be sought. In Java 8 you can get rid of the data transfer objects and work directly with the streams. Only in recent years have SQL alternatives gotten real attention (see also [2]). Often which database to use is a company-wide decision, on which a developer has no influence. A question raised but not discused was: Should we be using SQL databases? ORMs Alvaro concedes to being somewhat opionated about the topic. In his opinion, most ORMs try to hide the power of SQL, but to leverage the power of SQL it is necessary to expose it. A lot of database operations are much faster in SQL than in Java. Because many ORMs try to be compatible with a maximum number of different relataional database backends they reduce SQL to an absolute minimum. In Alvaro's opinion, ORM is a bad design patter. It may still work in some circumstances. The drawbacks of Hibernate, for example, are, that it may soon run into performance problem because of the way hibernate transforms Java into SQL. If you want to load data from more than one database table, you'll have to decide between eager and lazy loadding, and the ORM may generate massive amounts of SQL queries for the database. This may lead to exponential increase in queries leading to performance problems. When using ORMs, some decisions with regard to how to access the database (lazy, eager) need to be taken at compile time, but this may not be the right time to make this decision. Optimising at query level may increase performance for one use case, but may completely decrease it for another. ORMs provide an abstraction. If you frequently need to break the abstraction, then it is not a good thing to use it in the first place (see also [3]). Most ORMs think they are the only ones accessing the database. But this may not be the case. jOOP jOOP on the other hand leverages the power of SQL. It allows you to write SQL in Java almost like you would when using it on the database directly. In addition, jOOP is typesafe, which is not always the case for similar frameworks. jOOP supports SQL windows functions (see for example http://www.postgresql.org/docs/9.3/static/tutorial-window.html), which can return more than one output. The window limits how many rows are looked at and/or returned. Window functions are simple to use and powerful (e.g. support sorting on return set), but ORMs don't normally support this (jOOP does). jOOQ does the change tracking, but it doesn't do identity management, so if you need it, you'd have to write that code yourself. Alvaro's jOOQ usecase keeps data from the database in memory for a short time before it is written it back to the database. If one uses the database just as a persistence mechanism, then jOOQ may not be the right framework. An example was given of some software which renders comments on pages. It was first implemented with a ORM and MySQL. Many queries to the database were required to render just a single page with comments. Then switched to PostgreSQL and dropped the ORM, because PostgreSQL supports recursive queries, so that all comments could be rendered with a single query to the database. Current ORMs don't support recursive queries. jOOQ supports many SQL dialects. But if you use database specific SQL extensions will you be locked into a specific database. The advantage of jOOQ is also its drawback. jOOQ exposes the real SQL to the developer. This means that as a Java developer using jOOQ you also need to be fluent in SQL. If you use an ORM, you don't need to know SQL and you will usually end up with reasonable SQL. jOOQ does not yet support all SQL. A function which returns an array, for example, is not yet suppotred. In jOOQ, you retrieve a context object, then you can do queries. You can clearly recognise the SQL statements in the Java code. Debugging can be done by logging the return query. One advantage of jOOQ is that it validates the SQL code at compile time. Simple integration test would instantly tell you wether the statement actually works. Hibernate Hibernate tracks which objects had been modified and make the appropriate updates into the database. If your application's ORM is the only one accessing the database, then Hibernate has an evolving domain model which allows to autogenerate a schema. Only when deploying to production, it is necessary to analyse how to migrate - sometimes you could let Hibernate do this automagcially, sometimes you need to actually update some tables through a real migration. If you work in a DBA dominated enterprise (lots of others are using the database we use), then you probably shouldn't let Hibernate just update the database schema, as this may break other applications. | |||
Recommendations:
| |||
