Enter jOOQ, stage left. I loved the stuff I read on their home page:
I also loved their tag line:
- SQL was never meant to be abstracted. To be confined in the narrow boundaries of heavy mappers, hiding the beauty and simplicity of relational data.
- SQL was never meant to be object-oriented. SQL was never meant to be anything other than... SQL!
jOOQ: A peace treaty between SQL and Java
So, I started converting my home-grown CMS (Content Management System) from JPA to jOOQ and got very excited. jOOQ was simpler than JPA, smaller than JPA, and more efficient than JPA. I'm almost done with my conversion and absolutely love what I can do with jOOQ. So I spent the last week seeing if I could convert the very simple dynamic SQL builder framework I had written into jOOQ.
I had to start with some work to add iSeries DB2 support to jOOQ, which it lacked, but that only took a day so I was off and running. At first, there was the honeymoon period and I was really loving how the conversion was going. Then in testing I got into some issues - not unexpected. I couldn't control the scroll type or concurrency for the prepared statements via the jOOQ API, but no problem - I just added them. But then the @$%# hit the fan. Some of the application code that was using my SQL builder framework generated some pretty complicated where clauses. And despite spending about 2 days trying coax jOOQ into generating the same or equivalent conjunctives, I couldn't make it work. I tried modifying jOOQ to do what I wanted, but it quickly became apparent that was a rabbit hole I didn't have time to go down. So, I've abandoned that effort.
Still, for my home projects where life is simpler, I really love using jOOQ, and am really happy I discovered it and am using it. But for work, in the words of Maxwell Smart: "Missed it by that much"!