Using Stored Procedures with Hibernate

J2EE provides entity EJBs as a mechanism for mapping Java objects to database tables. In CMP the J2EE system itself generates the SQL necessary to create the EJBs from the database and to update the database to reflect changes made to the EJBs. The generic term for a framework that synchronizes program objects with relational database data in this manner is an Object-Relational Mapping (ORM) framework .

J2EE and the EJB model have its supporters as well as its detractors, but almost everyone agrees that it is mainly suitable for large-scale distributed applications. To get the benefits of ORM for non-J2EE applications, programmers typically adopt an alternative ORM framework, the most popular of which is Hibernate (http://www.hibernate.org).

Database stored programs and ORM are not necessarily a perfect fit. Gavin King the creator of Hibernatewas quoted as saying:

Stored procedures are essentially a nonrelational view of a relational database ... my view, currently, is that the goal of an object-relational mapping tool should be to map between tables and objects, not between objects and "some other stuff."[*]

[*] http://www.theserverside.com/talks/videos/GavinKing/interview.tss?bandwidth=dsl

It's true that programmers who are building applications that make widespread use of stored procedures will get less benefit from Hibernate than those working with native SQL; in particular, Hibernate cannot auto-generate stored procedure calls, so the programmer needs to configure Hibernate with every stored procedure call that might be required.

However, demand for stored procedures in Hibernate has remained high, and their use is now fully supported. This support allows Hibernate to be used with legacy applications that rely on stored procedures and also allows new applications to take advantage of both Hibernate and stored procedures where appropriate.

In this section we will provide a brief overview of using Hibernate with MySQL stored procedures. We're going to assume you have some basic familiarity with Hibernateif you are new to Hibernate, you will find a review of Chapter 2 ("Introduction to Hibernate") of the Hibernate Reference Documentation helpful. Our examples in this section are based on the Event class described in that chapter.

14.4.1. Hibernate Support for MySQL Stored Procedures

For every supported RDBMS, Hibernate includes a Dialect definition that defines the capabilities and configurations that the RDBMS supports. At the time of writing, the Hibernate (3.1rc3) MySQLDialect definition did not include a reference to stored procedures and, consequently, Hibernate would generate the following error when configured to use a MySQL stored procedure:

 [java] Hibernate: { call getEvent(?) }
 [java] Exception in thread "main" java.lang.UnsupportedOperationException: org.
 hibernate.dialect.MySQLDialect does not support 
 resultsets via stored procedures.

Modifying the Hibernate MySQLDialect.java file to reflect MySQL 5.0's ability to execute stored procedures is relatively simple, and we have submitted a modified version of this file to the Hibernate team for inclusion in an upcoming release of Hibernate (JIRA key HHH-1244, scheduled for 3.1 production). You can also obtain this file from this book's web site, where we will also include information about the current status of Hibernate support for MySQL stored procedures.

14.4.2. Using a Stored Procedure to Load an Object

The load( ) method of the Hibernate session object allows you to create a Hibernate object using the Hibernate mappings. Under the hood, Hibernate will generate a SELECT statement to extract the appropriate data from the database. Example 14-28 shows us creating and loading an Event object for the event #1.

Example 14-28. Loading a Hibernate object in a Java application

Long id = new Long(1);
Event event = (Event) session.load(Event.class, id);

We can load the Event object using a stored procedure. A simple stored procedure to retrieve details for a specific event is shown in Example 14-29.

Example 14-29. Stored procedure to load an Event object

CREATE PROCEDURE getEvent (in_event_id INTEGER)
BEGIN
 SELECT event_id, title, event_date
 FROM events
 WHERE event_id = in_event_id;

END;

To use this stored procedure, we need to create a definition for it in the mapping document and add a loader entry to the class definition. Example 14-30 shows the changes we made to the mapping document (Events.hbm.xml) to enable our stored procedure loader.

Example 14-30. Defining the loader stored procedure in the Hibernate mapping document

1 
2 
3 
4 
5 
6 
7 
8
9 
10 
11
12 
13 
14 
15 
16 
17 
18 { call getEvent(?) }
19 

Let's look at the important parts of this document:

Line(s)	Explanation
9	The mapping tag loader defines the SQL that will be used when the data for a class is first loaded. query-ref refers to a named query defined elsewhere in the mappingin this case getEventSP.
12-19	The sql-query section defines a named SQL query that can be used elsewhere in the mapping or from Java code.
12	The name property allows you to provide a meaningful name for the SQL query. The callable propertyif set to true indicates that the SQL query should be executed as a JDBC CallableStatementi.e., it is a stored procedure or function.
13-17	The return section provides details about the result set that will be returned by the sql-query section.
13	The alias property provides an alias that can be used to prefix column names in the SQL and is not of much interest for a callable SQL. The class property indicates that the SQL will return properties relating to the specified class (in this case the Event class).
18	The SQL code that is executed by this sql-query. For a callable SQL, this should be in the same format used in the prepareCall( ) method of the Connection interface, as described earlier in this chapter.

Once we rebuild our application, all subsequent load( ) calls will use the getEvent( ) stored procedure to retrieve event data from the database.

14.4.3. Hibernate Queries

It is typical for an application to generate lists of matching objects by issuing Hibernate queries . For instance, to create a List object that includes all events, we might include the code shown in Example 14-31 in our application.

Example 14-31. Simple Hibernate query to retrieve all objects

List result = session.createQuery("from Event").list( );

We could retrieve all Events objects raised since yesterday with the Hibernate query shown in Example 14-32.

Example 14-32. Hibernate query with WHERE clause

List result =
 session.createQuery("from Event as e where e.date > ?")
 .setDate(0, yesterday).list( );

Let's implement the query expressed in Example 14-32 through a stored procedure call. A stored procedure to return events raised after a specified date is shown in Example 14-33.

Example 14-33. Stored procedure to support a Hibernate query

CREATE PROCEDURE getRecentEvents(in_event_date DATETIME)
BEGIN
 SELECT event_id AS EVENT_ID, title AS EVENT_TITLE, event_date AS EVENT_DATE
 FROM events
 WHERE event_date > in_event_date;
END;

As in the previous example, we need to add a definition for the stored procedure call to the mapping file. Example 14-34 shows the mapping for our new stored procedure.

Example 14-34. Mapping for our query stored procedure

 
 
 
 
 
 { call getRecentEvents(?) }

Now we can use that named query in our Java code. Instead of using the createQuery( ) method, we use the getNamedQuery( ) method, supplying the name we have given our stored procedure call in the mapping file and supplying any necessary parameters. Example 14-35 shows the technique.

Example 14-35. Using a stored procedure to execute a Hibernate query in Java code

List result = session.getNamedQuery("getRecentEventsSP")
 .setDate(0,yesterday).list( );

14.4.4. Using Stored Procedures for Persistence

By default, Hibernate constructs and issues INSERT, UPDATE, and DELETE statements, as appropriate, to persist the contents of Java objects in the database. However, we can configure Hibernate to use stored procedure calls instead.

For a stored procedure to be used with Hibernate it must accept the same parametersin the same orderas the SQL that Hibernate would generate by default. For instance, in the case of a stored procedure to replace an INSERT statement, the stored procedure will have to provide parameters representing every column in Hibernate's INSERT statement, and these parameters must appear in the same order as the columns appear in that INSERT statement. The easiest way of determining this sequence is to log the SQL generated by Hibernate before converting it to a stored procedure call.

For UPDATE and DELETE, the stored procedure must return the number of rows affected by the operation as either a function return value or as the first parameter (which will, of course, need to be an OUT parameter).

The Hibernate documentation implies that a stored function should be used to implement UPDATE and DELETE functionality and that the stored function should return the number of rows affected. Unfortunately, Hibernate treats stored function return values in a way that works for SQL Server but not for MySQL, so for now it is necessary to implement the UPDATE or DELETE through a stored procedure.

Example 14-36 shows stored procedures designed to replace the Hibernate-generated DML statements to maintain Event objects. Note that in the case of the updateEvent and deleteEvent procedures, the first parameter is an OUT parameter that returns the number of rows affected by the DML operation. This parameter is neither required nor permitted for the createEvent procedure.

Example 14-36. Stored procedure to implement a Hibernate update operation

CREATE PROCEDURE updateEvent
 (OUT row_count INTEGER, in_event_date DATETIME,
 in_title VARCHAR(60), in_event_id INTEGER)
BEGIN
 UPDATE events
 SET title = in_title, event_date = in_event_date
 WHERE event_id = in_event_id;

 SET row_count = ROW_COUNT( );
END $$

CREATE PROCEDURE deleteEvent(OUT row_count INTEGER, in_event_id INTEGER)
BEGIN
 DELETE FROM events
 WHERE event_id = in_event_id;

 SET row_count = ROW_COUNT( );
END$$

CREATE PROCEDURE createEvent
 ( InEventDate DATE, InEventTitle VARCHAR(60), InEventId INT )
BEGIN
 INSERT INTO events (event_date, title, event_id)
 VALUES(InEventDate, CONCAT(InEventId, InEventTitle), InEventId);
END$$

To ensure that Hibernate uses these stored procedures in place of its self-generated SQL, we need to add entries in the mapping document to associate the specific operation with the stored procedure call. Example 14-37 shows the entries we added to the Event class definition (in Event.hbm.xml) to enable the stored procedures.

Example 14-37. Configuring Hibernate to use stored procedures for UPDATE, INSERT, and DELETE

{call createEvent (?, ?, ?)}
{call updateEvent(?,?,?,?)}
{call deleteEvent(?,?)}

Once we rebuild our application, Hibernate will use these stored procedure calls in place of the INSERT, UPDATE, or DELETE SQL statements that it would normally generate.

We have now completely converted the Event mapping to use stored procedures. Hibernate will now use MySQL stored procedures exclusively when querying, loading or modifying objects of the Event class.