Song Of The Day: Breathe - Artist: The Prodigy
Update (2/19/2008): The source code is now available here.
Thank you for your patience!
Eric
 |
 |
 |
This week I thought I’d post a few things about Hibernate 3.x, stored procedures, and even the Spring Framework. BTW consider this Part 2 to this previous post on the Spring Framework and MVC.
More after the jump…
Hibernate
Hibernate is a Java-based persistence API. Most folks who would care about that already know it. However, there is one thing that I wish Hibernate’s stored procedure mapping would allow:
Use <loader query-ref=”getUser”></loader> for all snap shot selects.
And…maybe even allow custom version selects as well.
Maybe there is a good reason for all this currently (e.g., batch updates and cascades), but let me walk through a recent problem I considered.
Let’s say I want to have a Java/Hibernate object abstract (or represent) two relational database tables. The Java object represents a user within the application and the schema, including stored procedures, already exists for a legacy application.
How can Hibernate 3.x be used to bridge this object-relational gap?
Hibernate 3 and MySQL Stored Procedures
The answer is that it can, but it requires some work.
By design, and as a philosophy, Hibernate is best as a single-object-to-single-table mapping tool. As long as your tables are represented well by real-world objects, then there is probably not much use in worrying about stored procedures.
However, if not then refactoring of the existing schema may be necessary to have a decent straight object-to-table mapping. This is why persistence APIs like iBATIS exist. iBATIS allows mapping of stored procedure results–not necessarily tables– to objects.
I could have gone the iBATIS route, but decided to discover how extensible Hibernate could be on the topic.
Although I briefly touched on this, why care about this? Well, mostly it is because of legacy systems and traditional relational DB programming practices. Many systems map objects (i.e., objects in philosophy, not necessary in reality) to stored procedure results because stored procedures can be faster and easier-to-maintain–at least in some cases. There is debate regarding the validity of those claims, but the fact still remains that there are a lot of systems out there that use stored procedures. Furthermore, since Hibernate has a lot of nice object-relational features, and community support, it would be nice to have its persistence API work with existing stored procedures relational paradigms, while also allowing the more standard object-to-table binding.
An Outline of An Example
So let’s see how far Hibernate can go using stored procedures and an example.
Actually, one can go pretty far as long as one avoids Hibernate’s pre-generated static sql, specifically snap shot selects and version selects as mentioned earlier.
For example let’s say we want to represent a User in Java. we may define something like:
public class User {
private String id;
private String gender;
protected final Logger log = LogManager.getLogger(this.getClass());
public User() {
}
public void setId(String id) {
log.info("Set id: " + id);
this.id = id;
}
public String getId() {
return this.id;
}
public void setGender(String gender) {
log.info("Set gender: " + gender);
this.gender = gender;
}
public String getGender() {
return this.gender;
}
}
MySQL Schema Tables
Pretty straightforward. Now, let’s say we have two tables in MySQL that collectively represent the User object:
DROP TABLE IF EXISTS User;
CREATE TABLE User (
id VARCHAR (50) NOT NULL,
PRIMARY KEY(id)
);
DROP TABLE IF EXISTS Pref;
CREATE TABLE Pref (
User_id VARCHAR (50) NOT NULL,
gender VARCHAR (2) NOT NULL,
PRIMARY KEY (User_id),
FOREIGN KEY (User_id) REFERENCES User (id)
);
Although the above example with the gender would be better as a 1-to-1 field within one table (i.e., the User table), one could imagine some other real-world examples using 1:N relationships using phone numbers, usernames, etc. So how can we map and bind these two tables to one object using stored procedures?
Hibernate 3.x Mapping File
First we must provide a mapping file to Hibernate. It looks like the following:
<hibernate-mapping>
<class name=”User” table=”User”>
<id name=”id” column=”id” type=”java.lang.String”>
<generator class=”assigned”/>
</id>
<property name=”gender” column=”gender” type=”java.lang.String”/>
<loader query-ref=”getUser”>
<sql-insert callable=”true”>{call createUser (?,?)}
<sql-update callable=”true”>{call updateUser (?,?)}
<sql-delete callable=”true”>{call deleteUser (?)}
</class>
<sql-query name=”getUser” callable=”true”>
<return alias=”user” class=”User”>
<return-property name=”id” column=”id” />
<return-property name=”gender” column=”gender” />
</return>
{ call getUser(?) }
</sql-query>
</hibernate-mapping>
For anyone who has read older documents on Hibernate notice the lack of OUT parameters to MySQL. Hibernate 3.x apparently uses JDBC and possibly some introspection to figure out row counts, etc.
MySQL Stored Procedures
The stored procedures in the MySQL schema look like the following:
DELIMITER // DROP PROCEDURE IF EXISTS getUser; // /* * Get a user. */ CREATE PROCEDURE getUser (IN inId VARCHAR(50)) BEGIN SELECT u.id, p.gender FROM User AS u, Pref AS p WHERE u.id = inId AND p.User_id = u.id; END // DROP PROCEDURE IF EXISTS createUser; // /* * Create a user profile. */ CREATE PROCEDURE createUser (IN inGender VARCHAR(2), IN inId VARCHAR(50)) BEGIN INSERT INTO User (id) VALUES (inId); INSERT INTO Pref (User_id, gender) VALUES (inId, inGender); END // DROP PROCEDURE IF EXISTS updateUser; // /* * Update a user profile. */ CREATE PROCEDURE updateUser (IN inGender VARCHAR(2), IN inId VARCHAR(50)) BEGIN UPDATE Pref SET gender = inGender WHERE User_id = inId; END // DROP PROCEDURE IF EXISTS deleteUser; // /* * Delete the user. */ CREATE PROCEDURE deleteUser (IN inId VARCHAR(50)) BEGIN DELETE from Pref WHERE User_id = inId; DELETE from User WHERE id = inId; END //
Now, that doesn’t look too bad, but honestly it was a bit of a pain wading through docs, code, and trial-and-error to figure things out!
So what does the code within our data access object in Java look like? Although it uses the Spring Framework HibernateDAOSupport class, more on that in a minute, here is the code:
DAO Java Code
public boolean createUser(User user) {
boolean status = false;
Session session = this.getSessionFactory().openSession();
session.clear();
session.beginTransaction();
session.save(user);
session.getTransaction().commit();
session.close();
status = true;
return status;
}
/**
* Get a user.
*/
@SuppressWarnings("unchecked")
public User getUser(User user) {
User u = null;
Session session = this.getSessionFactory().openSession();
Query query = session.getNamedQuery("getUser");
query.setString(0, (String)user.getId());
List result = query.list();
if (result == null) {
log.info("Could not retrieve user.");
} else {
Iterator i = result.iterator();
if (i.hasNext()) {
u = (User) i.next();
}
if (i.hasNext()) {
log.fatal("DB returned too many users.");
u = null;
}
}
session.close();
return u;
}
/**
* Update a user profile.
*/
public boolean updateUser(User user) {
boolean status = false;
User luser;
String gender;
if (user == null) {
log.error("Cannot process invalid user.");
return status;
}
gender = user.getGender();
Session session = this.getSessionFactory().openSession();
session.clear();
session.beginTransaction();
Query query = session.getNamedQuery("getUser");
query.setString(0, (String)user.getId());
List result = query.list();
if (result == null) {
log.error("Could not retrieve user.");
session.close();
return status;
}
Iterator i = result.iterator();
if (i.hasNext()) {
luser = (User) i.next();
} else {
log.error("Could not retrieve user from list.");
session.close();
return status;
}
luser.setGender(gender);
session.update(luser);
session.getTransaction().commit();
session.close();
status = true;
return status;
}
/**
* Delete
*/
public boolean deleteUser(User user) {
boolean status = false;
User luser;
if (user == null) {
log.error("Cannot process invalid user.");
return status;
}
Session session = this.getSessionFactory().openSession();
session.beginTransaction();
Query query = session.getNamedQuery("getUser");
query.setString(0, (String)user.getId());
List result = query.list();
if (result == null) {
log.error("Could not retrieve user.");
session.close();
return status;
}
Iterator i = result.iterator();
if (i.hasNext()) {
luser = (User) i.next();
} else {
log.error("Could not retrieve user from list.");
session.close();
return status;
}
log.info("Now deleting user...");
session.delete(luser);
session.getTransaction().commit();
session.close();
status = true;
return status;
}
Possible Errors In Tomcat Logs
Okay. So what was the early reference to snap shot and version selects? Well, within a Hibernate transaction if one does not perform a fetch of the object before updating or deleting it, then Hibernate will use static selects to fetch the object. The problem is that the static selects look for object-to-single-table mappings and they fail.
The way around it is to explicitly call stored procedure operations before issuing updates or deletes. As an example of the types of errors from a Tomcat log, one will see the issue:
[DEBUG:logStaticSQL:AbstractEntityPersister.java:2738:13-12 14:09:35]
Static SQL for entity: User
[DEBUG:logStaticSQL:AbstractEntityPersister.java:2743:13-12 14:09:35]
Version select: select id from User where id =?
[DEBUG:logStaticSQL:AbstractEntityPersister.java:2746:13-12 14:09:35]
Snapshot select: select user_.id as id4_, user_.gender as gender4_, from User user_ where user_.id=?
[DEBUG:logStaticSQL:AbstractEntityPersister.java:2749:13-12 14:09:35]
Insert 0: {call createUser (?)}
[DEBUG:logStaticSQL:AbstractEntityPersister.java:2750:13-12 14:09:35]
Update 0: {call updateUser (?,?)}
[DEBUG:logStaticSQL:AbstractEntityPersister.java:2751:13-12 14:09:35]
Delete 0: {call deleteUser (?)}
.....
[ WARN:logExceptions:JDBCExceptionReporter.java:77:13-12 15:35:27]
SQL Error: 1054, SQLState: 42S22
[ERROR:logExceptions:JDBCExceptionReporter.java:78:13-12 15:35:27]
Unknown column 'user_.gender' in 'field list'
Although I hand-edited the log output, and hopefully avoided errors, the main interest is the last line. It is expecting the gender column as a part of the User table, which doesn’t exist. It exists in the Pref table defined above.
Entire Example Source
This has gotten pretty long, but we basically mapped stored procedure results to objects in Hibernate like an iBATIS-type API would do. Also, I have some great news IMHO. The schema, config files, and code for the test example–the entire Eclipse project–is online with a BSD-style license! So don’t worry about having to read-between-the-lines concerning how the Spring Framework and HibernateDAOSupport work in the example. Check out the code. It is fairly readable.
I don’t know if you’re jazzed about that initially, but I think it is one of the most complete and update-to-date examples using Spring Framework 2.x, Web MVC, Hibernate 3.x, and stored procedures with MySQL on the Web today. I wish I had the bundle when I started!
Perhaps I can explain more of what it does later, but it accepts a User Id and Gender from an HTML/Spring MVC form and creates, updates, and deletes the user form the database using Hibernate (screenshot below).
Source Code and Eclipse Project
Screen shot of browser hitting Tomcat 5.5.x.
Download the Eclipse Test Example here with Java Source
The example ran on the following system, but should run on most recent Tomcat environments:
Platform OS: Mac OS X 10.5 Leopard
Tomcat Server: 5.5.23.0
JVM Version: 1.5.0_13-b05-237
Hibernate: 3.2.5ga
Spring Framework: 2.5.0
Tags: Eric O’Laughlen, Hibernate 3.x and Stored Procedures, Hibernate 3.x and Spring Framework Integration
Hi Eric,
Thanks for the article. I am really looking forward to your eclipse project source to browse
and understand how this is done.
Could you please post it as soon as you can?
thanks
Shyam,
Thanks for your prompting. The source in the article have been posted again at:
http://joggame.com/software/hibernate.html
The links in the entry have been adjusted as well.
Eric