11:18 PM - Cayenne and Joins
I've been experimenting with Apache Cayenne for work. Earlier today, I got stumped by a complex database relationship in terms of Cayenne. I can easily write a six table join to accomplish the query, but i wanted to find an object mapping approach instead.
I've learned several things about Cayenne. First, it does not support outer joins from the built in object mapping. It's possible to create outer and left joins with either SQLTemplate or performing a JDBC query directly.
A SQLTemplate is a method to write a "real" SQL statement and either execute it as a non returning query or a select mapped to an existing Cayenne object. You can even have it return raw rows back. My first thought was that it would suck to do that because you'd lose the auto generating code for multiple database feature. Actually, you can write alternate manual queries and register them for each major JDBC provider. You write a generic default one that can work in most cases and then a tuned version (or whatever) for a specific database.
The other option is to write a direct JDBC query and circumvent most of Cayenne. You can still use it's database connection pool though.
DataSource ds = context.getParentDataDomain().getNode ("mydatanode").getDataSource(); Connection c = ds.getConnection();