August 25, 2004
There's been a certain amount of noise recently surrounding simple JDBC frameworks
like iBATIS. I've liked the idea of iBATIS myself, for use in applications which
don't need an object-oriented domain model, and don't work with deep graphs of
associated entities in a single transaction. A JDBC framework also makes good
sense if you are working with some kind of "insane" legacy database;
ORM solutions tend to assume that associations are represented as nice clean foreign
keys with proper referential integrity constraints (Hibernate3 much less so than
Hibernate 2.x).
Some people even suggest that JDBC frameworks are a suitable alternative to
ORM, even for those systems to which ORM is best suited: object-oriented applications
with clean relational schemas. They argue that you are always better off with
hand-written SQL than generated SQL. Well, I don't think this is true, not only
because the overwhelming bulk of SQL code needed by most applications is of
the tedious kind, and simply does not require human intervention, but also because
a JDBC framework operates at a different semantic level to ORM. A solution like
iBATIS knows a lot less about the semantics of the SQL it is issuing, and of
the resulting datasets. This means that there is much less opportunity for performance
optimizations such as effficent caching. (By "efficient", I am referring
mainly to efficient cache invalidation strategies, which are crucial to the
usefulness of the cache.) Furthermore, whenever we have seen handwritten SQL,
we have seen N+1 selects problems. It is extremely tedious to write a new SQL
query for each combination of associations I might need to fetch together. HQL
helps significantly here, since HQL is much less verbose than SQL. For a JDBC
framework to be able to make the kind of optimizations that an ORM can make,
it would have to evolve to a similar level of sophistication. Essentially, it
would need to become an ORM, minus SQL generation. In fact, we already start
to see this evolution taking place in existing JDBC frameworks. This begins
to erode one of the stated benefits: the claimed simplicity.
It also raises the following interesting thought: if, by gradually adding stuff,
a JDBC framework will eventually end up as ORM, minus SQL generation, why not
just take an existing ORM solution like, ooh, um ... Hibernate, maybe ... and
subtract the SQL generation?
The Hibernate team has long recognized the need to mix and match generated
SQL with the occasional handwritten query. In older versions of Hibernate, our
solution was simply to expose the JDBC connection Hibernate is using, so you
can execute your own prepared statement. This started to change a while ago,
and Max Andersen has recently done a lot of work on this. Now, in Hibernate3,
it is possible to write an entire application with no generated SQL, while still
taking advantage of all of Hibernate's other features.
Do we really expect or intend people to use Hibernate in this way? Well, not
really - I doubt there are many people out there who really enjoy writing tedious
INSERT, UPDATE, DELETE statements all day. On the other hand, we do think that
quite a few people need to customize the occasional query. But to prove a point,
I'll show you how you can do it, if you really want to.
Let's take a simple Person-Employment-Organization domain model. (You can find
the code in the org.hibernate.test.sql package, so I'm not going to reproduce
it here.) The simplest class is Person; here's the mapping:
<class name="Person" lazy="true">
<id name="id" unsaved-value="0">
<generator class="increment"/>
</id>
<property name="name" not-null="true"/>
<loader query-ref="person"/>
<sql-insert>INSERT INTO PERSON (NAME, ID) VALUES ( UPPER(?), ? )</sql-insert>
<sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE ID=?</sql-update>
<sql-delete>DELETE FROM PERSON WHERE ID=?</sql-delete>
</class>
The first thing to notice is the handwritten INSERT, UPDATE and DELETE statements.
The ? order of the parameters matches to the order in which properties are listed
above (we'll have to eventually support named parameters, I suppose). I guess
there is nothing especially interesting there.
More interesting is the <loader> tag: it defines a reference to a named
query which is to be used anytime we load a person using get(), load(), or lazy
association fetching. In particular, the named query might be a native SQL query,
which it is, in this case:
<sql-query name="person">
<return alias="p" class="Person" lock-mode="upgrade"/>
SELECT NAME AS {p.name}, ID AS {p.id} FROM PERSON WHERE ID=? FOR UPDATE
</sql-query>
(A native SQL query may return multiple "columns" of entities; this
is the simplest case, where just one entity is returned.)
Employment is a bit more complex, in particular, not all properties are included
in the INSERT and UPDATE statements:
<class name="Employment" lazy="true">
<id name="id" unsaved-value="0">
<generator class="increment"/>
</id>
<many-to-one name="employee" not-null="true" update="false"/>
<many-to-one name="employer" not-null="true" update="false"/>
<property name="startDate" not-null="true" update="false"
insert="false"/>
<property name="endDate" insert="false"/>
<property name="regionCode" update="false"/>
<loader query-ref="employment"/>
<sql-insert>
INSERT INTO EMPLOYMENT
(EMPLOYEE, EMPLOYER, STARTDATE, REGIONCODE, ID)
VALUES (?, ?, CURRENT_DATE, UPPER(?), ?)
</sql-insert>
<sql-update>UPDATE EMPLOYMENT SET ENDDATE=? WHERE ID=?</sql-update>
<sql-delete>DELETE FROM EMPLOYMENT WHERE ID=?</sql-delete>
</class>
<sql-query name="employment">
<return alias="emp" class="Employment"/>
SELECT EMPLOYEE AS {emp.employee}, EMPLOYER AS {emp.employer},
STARTDATE AS {emp.startDate}, ENDDATE AS {emp.endDate},
REGIONCODE as {emp.regionCode}, ID AS {emp.id}
FROM EMPLOYMENT
WHERE ID = ?
</sql-query>
The mapping for Organization has a collection of Employments:
<class name="Organization" lazy="true">
<id name="id" unsaved-value="0">
<generator class="increment"/>
</id>
<property name="name" not-null="true"/>
<set name="employments"
lazy="true"
inverse="true">
<key column="employer"/> <!-- only needed for DDL generation -->
<one-to-many class="Employment"/>
<loader query-ref="organizationEmployments"/>
</set>
<loader query-ref="organization"/>
<sql-insert>
INSERT INTO ORGANIZATION (NAME, ID) VALUES ( UPPER(?), ? )
</sql-insert>
<sql-update>UPDATE ORGANIZATION SET NAME=UPPER(?) WHERE ID=?</sql-update>
<sql-delete>DELETE FROM ORGANIZATION WHERE ID=?</sql-delete>
</class>
Not only is there a <loader> query for Organization, but also for its
collection of Employments:
<sql-query name="organization">
<return alias="org" class="Organization"/>
SELECT NAME AS {org.name}, ID AS {org.id} FROM ORGANIZATION
WHERE ID=?
</sql-query>
<sql-query name="organizationEmployments">
<return alias="empcol" collection="Organization.employments"/>
<return alias="emp" class="Employment"/>
SELECT {empcol.*},
EMPLOYER AS {emp.employer}, EMPLOYEE AS {emp.employee},
STARTDATE AS {emp.startDate}, ENDDATE AS {emp.endDate},
REGIONCODE as {emp.regionCode}, ID AS {emp.id}
FROM EMPLOYMENT empcol
WHERE EMPLOYER = :id AND DELETED_DATETIME IS NULL
</sql-query>
When I was writing this code, I really started to feel the advantages of having
Hibernate write the SQL for me. In just this simple example, I would have eliminated
more than 35 lines of code that I would have to later maintain.
Finally, for ad hoc querying, we can use a native SQL query (a named query,
or one embedded in the Java code). For example:
<sql-query name="allOrganizationsWithEmployees">
<return alias="org" class="Organization"/>
SELECT DISTINCT NAME AS {org.name}, ID AS {org.id}
FROM ORGANIZATION org
INNER JOIN EMPLOYMENT e ON e.EMPLOYER = org.ID
</sql-query>
Personally, I prefer to program in Java than in XML, so all this stuff is much
too XML-heavy for my liking. I think I'll stick with SQL generation, wherever
I can, which is almost everywhere. It's not that I don't like SQL. In fact,
I am a great fan of SQL, and just love watching the queries scroll past when
I turn Hibernate's logging on. It's just that Hibernate is much better at writing
SQL than I am.
About the author
Gavin King
gavin@hibernate.org
Blog: http://blog.hibernate.org/
Gavin King is the founder of the Hibernate project. He is co-author of the book Hibernate in Action, to be published by Manning Publications. He is currently involved in the JDO expert group and is employed by JBoss Inc, where he is redesigning the JBoss CMP engine.
|