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

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

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

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

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

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

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

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

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

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).

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

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

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.