We went from hibernate/eclipselink to MyBatis - and have never looked back. Needing to deal with the idiomatic behaviour of JPA, HQL and the target SQL is a nightmare. We’ve literally removed tens of thousands of lines of Java code by dropping JPA.
Also now that we need auditing there’s a pile of work we could have have gotten mostly free with an Envers annotation.
In fact, an important part of our transition away from ORM was to invert the ORM-centric relationship between the application and the database. We wanted our tech people to be able to manipulate the database natively via SQL, which is generally much easier and more efficient than writing and deploying Java code to do the same thing.
For us, this meant moving most data manipulation logic into the database using PL/PGSQL. Doing so has empowered a team of non Java admins, massively improved performance, and significantly reduced LOC. I’d say that we now have about 30% (1/3rd) of the LOC in PL/PGSQL than we had in Java+JPA. Obviously this means less bugs, but performance is literally 100x in some cases.
I think that if you take a Java-first view of your application then all the complexity of JPA is part and parcel of any solution, and that’s fine for you. But we’ve found that taking an SQL-first approach has been great for our use cases, and has had lots of ongoing benefits.
Of course, we needed to build a bunch of tools to help with this. For example, we built a gradle plugin that treats SQL code like Java code so we can write libraries in SQL with transitive dependencies. We also needed to build testing tools and tools to automate schema migrations for CD.
I do think the lack of tooling around managing large SQL-based projects is a blocker to wider adoption of our approach, but the benefits of going against the ORM grain have been very significant for us.
I will also concede that under some circumstances it's too slow to bring the data to the code, and instead you have to bring the code to the data (i.e. PL/PGSQL). The sort of speedup is mostly from eliminating round trips and data marshaling, however. That's not quite a like-for-like comparison of an ORM vs a mapper.
The reason I stick to ORMs is mostly because of RAD tools that save me so much time. For instance, JHipster generates liquibase migrations and JPA entities. This gives me a relational schema very quickly. To avoid any "surprise" queries that tank performance, I do turn Hibernate's statement logging on when developing new features.
Next time if I can find some more tools to help with an SQL-only + stored procedure approach, I'll give it a deeper consideration. Maybe convince your company to open source some of tools it has developed!
I definitely agree with you that tooling is a big deal. We did spend a lot of time manually proving it out before we spent the time on the tools. It was a bit of a leap of faith but it quickly became obvious that we were onto something.
And you’re right, the point of our approach was to move the code to the data. We deal with reasonably large, complex real time data sets Sotheby’s round trips and marshalling become the dominating contributor to total time. Hence our ability to improve some operations by 100x.
I can see that if you have a familiar and reliable tool chain and a compatible use case then ORMs could be great. I think our problem was that we had neither, so it was never going to work out too well for us!