SQL Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 248 posts at DZone. You can read more from them at their website. View Full User Profile

Popular ORMs Don't do SQL

11.15.2013
| 8376 views |
  • submit to reddit

I’m contemplating about what has happened in the ISO / IEC SQL standard during the last 15 years. We’ve had quite a few new features added to our beloved SQL language. Check this out:

  • With the ISO/IEC SQL:1999 standard, we could take advantage of grouping sets and (recursive) common table expressions.
  • With the ISO/IEC SQL:2003 standard, we’ve had the very sophisticated window functions and the MERGE statement.
  • With the ISO/IEC SQL:2008 standard, we could perform partitioned JOINs.
  • With the ISO/IEC SQL:2011 standard, we can now interoperate with temporal databases (implemented so far in IBM DB2 and Oracle).

And obviously, there’s much more goodness hiding in the almost unreadable 1423-page-long documents.

But JPA…

Now, does any of these awesome features appear in JPA? Nope. Will the next SQL standard introduce new awesome features? I’m sure it will! I could imagine that the Oracle / CUBRID CONNECT BY clause, or the Oracle / SQL Server PIVOT / UNPIVOT clauses are good candiadates for standardisation. I’d go absolutely nuts if Oracle’s crazy MODEL clause would make it, too.

While exciting things happen at these ends, the ORM impedance mismatch will further deepen and confirm Charles Humble’s recent findings from the QCon, where he’s observed an increasing number of people who are unhappy with popular ORMs’ ever increasing complexity. A complexity example: NamedEntityGraph!

@NamedEntityGraph(
  name="ExecutiveProjects",
  attributeNodes={
    @NamedAttributeNode("address"),
    @NamedAttributeNode(
      value="projects",
      subgraph="projects"
    )
  },
  subgraphs={
    @NamedSubgraph(
      name="projects",
      attributeNodes={
        @NamedAttributeNode("properties")
      }
    ),
    @NamedSubgraph(
      name="projects",
      type=LargeProject.class,
      attributeNodes={
        @NamedAttributeNode("executive")
      }
    )
  }
)

Man, did this really have to be added to JPA? Stack Overflow cannot display that many annotations on a single screen! Well, if this is JEE’s answer to SQL’s recent evolutions, then I’m glad I don’t do too much JEE these days. I’m doing SQL, which is an awesome language if let running loose.

Published at DZone with permission of Lukas Eder, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Fabien Bergeret replied on Fri, 2013/11/15 - 5:26am

ORMs are based on the illusion that you can use a powerfull tools such as a modern RDBMS without having to understand what's under the hood.

Personnally, I prefer tools like MyBatis (some say they are ORMs, some say they are not) which let you use fully the power of the underlying DBMS along with the possibility to define the SQL-to-Java mapping in a convenient way.


Denis Robert replied on Fri, 2013/11/15 - 9:21am

By your definition, RDMBS' don't do SQL either:

Many RDBMS' support for the features listed is spotty, at best. CTEs are supported by many but with so many caveats as to make them nearly impossible to use in a consistent fashion across RDBMS systems, and the rest is iffy. MySQL supports pretty much none of them.

JPA is built to work with all RDBMS systems in a relatively consistent fashion. So it has to forego many of the goodies that later SQL standards brought, because these fail more than they succeed at standardizing anything. The reality is that there's only a very small subset of features that are consistently (or almost consistently) implemented, and those are the only features that you can realistically support with something like JPA. 

If you want your application to be tied to a specific RDBMS, then by all means code in raw SQL, especially if you have the skills you need to hand-write SQL that won't hang the server. But I really like having the ability to code to a relatively stable common core, and to fall back to raw SQL only when I have to (because, you know, JPA allows you to use native SQL when you must).

Lukas Eder replied on Fri, 2013/11/15 - 9:43am in response to: Denis Robert

MySQL supports pretty much none of them.

You're right, but I've been "privileged" in the past, being able to use Oracle. And if it wasn't Oracle, I'd still prefer SQL Server or PostgreSQL over MySQL. It is of course every RDBMS vendor's choice to implement, or at least bend the SQL standard towards something useful.

Steven Goldsmith replied on Tue, 2013/11/19 - 1:04pm in response to: Denis Robert

You present a couple of straw men here. #1 it's pretty easy to create cross platform SQL since most of the common operations fit the ANSI standard. Also, you should be externalizing your SQL, so if there are deviations you simply use the vendor specific configuration. #2 If you write SQL code that hangs a server you need to be fired. If you have poorly performing SQL then you need to use an explain plan or chat with the DBA. Any decent developer worth their salt knows SQL.

We used to generate Spring code from database metadata for DTO, DAO and service layer including transactions, unit tests, etc. Metagenerating your code makes life a lot easier.

Abhijith Nagarajan replied on Wed, 2013/11/20 - 5:57am

JPA, in general any ORM provides easy way to access database for java developers with out digging more into SQL. SQL is beautiful and every backend application developer is expected to know about SQL, but JPA makes it simple. 

If you want make use of some specific SQL features, then you JPA and other ORM gives an option to execute native sql queries. 

You cannot completely take JPA away just to use new SQL features. How many of these new features would you be using in the code. Are those features really necessary?

When most of the new features are standardized and follows uniform syntax and then I would expect JPA specification will include the new features as well.

I feel the article came bit too early.

Lukas Eder replied on Wed, 2013/11/20 - 6:04am in response to: Abhijith Nagarajan

I feel the article came bit too early.

SQL grouping sets and common table expressions were introduced in SQL-99. As in 14 years ago. Way before JPA 1.0 :-)

Of course, I agree that JPA makes a lot of things simpler. Mostly CRUD. But SQL isn't just about CRUD. And "native SQL" works, but it's not a bit more advanced than JDBC, which we've already had in the JDK 1.1.

Abhijith Nagarajan replied on Wed, 2013/11/20 - 6:52am in response to: Lukas Eder

Agreed on your point old sql standards are not supported in JPA. 

On any given day, I would prefer using JPA over JDBC, unless I have strong reason to do otherwise. Just see how much effort does JPA reduces in mapping the bean fields with column names. Additionally, option to validate the fields against the database, making relationship transcend to java from RDBMS. 

JPA has greatly simplified the database programming in the java world. I do not deny the shortcomings of it though, but I feel, JPA addresses majority of the Java/J2EE based application's database programming.

Fabien Bergeret replied on Wed, 2013/11/20 - 6:53am in response to: Abhijith Nagarajan

every backend application developer is expected to know about SQL

Well yes, but I've realised one interesting thing considering SQL queries and backend developers (which have generally more a J2EE background than a SQL one): 

  • if you suggest them a SQL query to perform a given use-case, they won't generally criticize it
  • if you ask them to write a SQL query in order to support a given use-case, they'll generally think carefully about it.
It means that they won't challenge default created Hibernate queries, even if their hand-written SQL would have been much more efficient.

Fabien Bergeret replied on Wed, 2013/11/20 - 7:25am in response to: Abhijith Nagarajan

Just see how much effort does JPA reduces in mapping the bean fields with column names.
Have you ever tried MyBatis? SQL is hand written, mapping is parameterized. Have you ever tried Spring JDBC?  SQL is hand written, mapping is delegated to RowMapper (manually written, once per DAO) which maps a row of the resultset to a POJO.

In a functional point of view, I agree that JPA addresses majority of the Java/J2EE based application's database programming but the cost (from program management point of view) is not the same : 

  • JPA is more difficult to setup (-)
  • Code is written faster with JPA (+)
  • Issues (such as performance) show up late in SDLC with JPA (--)
I don't say that ORM have to be thrown away, but one have to consider the choice depending on the seniority and the experience of the dev team.

Lukas Eder replied on Wed, 2013/11/20 - 7:11am in response to: Fabien Bergeret

I really like your way of putting this:
    • if you suggest them a SQL query to perform a given use-case, they won't generally criticize it
    • if you ask them to write a SQL query in order to support a given use-case, they'll generally think carefully about it.

May I cite this on my blog, eventually, in a future article?

Fabien Bergeret replied on Wed, 2013/11/20 - 7:24am in response to: Lukas Eder

Sure, no problem.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.