From the JPA Spec: "An entity is a lightweight persistent domain object." Right there, on page 17 of the
spec, is evidence that hard-core, efficient querying is not something that JPA was designed to do. Not convinced? Page 22 offers another nail in the coffin: "Every entity must have a primary key." This is a problem because complex queries tend not to return
Entities--they return fragments of
Entities and the results of functions applied to their properties. The resulting cursor thus does not map to one or more
Entities, which makes hacking the result set into a spec-violating fake-o
Entity via
@SqlResultMapping with a fake
@Id a bad idea. The spec makes no allowance for cursors whose columns are just data, not
Entities. Any result set mapping has to refer to at least one
Entity. Violating this requirement in Hibernate gives you
org.hibernate.cfg.NotYetImplementedException: Pure native scalar queries are not yet supported.
A SQL query returns a result set. A row in a result set is often not persistent because it's the result of a join of some number of tables. Furthermore, these virtual rows are just that--virtual--and thus don't have a primary key. I'm not dissing JPA; I'm just saying that it's not the right tool to use when your queries require multiple table joins. Put another way, if you are stuck with a hyper-normalized database, where you can't build a meaningful piece of information without joining a half dozen tables, JPA is not going to help you with queries. If you have the luxury of not having to do joins to extract useful business objects, consider yourself lucky.
JPA is great at what it's designed to do: make it easy to new-up some objects and cram them into the database, or update some existing records. I find it misleading, though, that JPA claims that Entities are POJOs because I am always fighting "detached" entities (
org.hibernate.LazyInitializationException: could not initialize proxy - the owning Session was closed) resulting from lazily instantiated fields. Maybe we haven't configured Spring properly. Maybe something about the way we're using HiveMind in Tapesty 4.0 is violating the spirit of container-managed persistence. But I think it's insincere to claim JPA Entities are POJOs. They're closer to POJOs that previous EJBs, but they still have their encumbrances.
JPA is a huge help to my tests, where most of the time performance isn't a concern, and traipsing over half dozen object relationships just ain't no thang. But when I care deeply about performance, I'm always going to revert to SQL. I know SQL very well, and I know database optimization very well. EJQL just doesn't have the power of SQL, and I doubt it ever will. The spec allows one to escape to "native" SQL, but whenever you do this to write a query, you end up returning a
List<Object[]>. Ugh. Anytime you see that as your return signature, you know you're at the edge of the framework. It's a necessary evil for JPA, given the database vendor neutrality that the spec is trying to give us, but it's an enormous downer, and is one reason why I'm still looking for a good O/R tool that lets me write plain SQL but gives me a type-safe way to interrogate the cursor that comes out of a query.
One of my colleagues actually wrote just such a tool. It's phenomenal. You write a simple stored procedure, and the tool inspects Oracle's metadata to autogenerate a type safe "bean" class that describes the columns in the cursor. Using event-driven architecture, you listen for rows, or you can get them all at once as a List. It's dead simple to use, and I'm surprised Hibernate and TopLink don't have something like it. I need to change it to be a bit more declarative, but basically here's how it works:
List<ListFoo.RefCursor1> fooRows = new ListFoo("Param1",34.2).executeQuery();ListFoo.RefCursor1 is an autogenerated bean that echoes the columns in the ref cursor defined by the stored procedure.
Nice and neat. Yeah, you gotta know SQL. But some of us actually think that knowing SQL
and Java makes you a kick ass programmer.
