java barcode generator Stored procedures in Java

Printer Denso QR Bar Code in Java Stored procedures

A shortcoming in earlier releases of Hibernate was the lack of support for stored procedures. Thankfully, Hibernate 3 addresses this problem. Stored procedures are defined in the mapping document and declare the name of the stored procedure as well as the return parameters. Let s look at an example. Suppose we have the following Oracle stored procedure:

CREATE FUNCTION selectEvents RETURN SYS_REFCURSOR AS sp_cursor SYS_REFCURSOR; BEGIN OPEN st_cursor FOR SELECT id, event_name, start_date, duration FROM events; RETURN sp_cursor; END;

You can see that the stored procedure retrieves four columns from the events table, which is used to populate an Event instance. Before you can use it, however, you have to declare the stored procedure in the mapping file for the Event class:

<sql-query name="selectEvents_SP" callable="true"> <return alias="ev" class="Event"> <return-property name="id" column="id"/> <return-property name="name" column="event_name"/> <return-property name="startDate" column="start_date"/> <return-property name="duration" column="duration"/> </return> { = call selectEvents() } </sql-query>

Executing the stored procedure is the same as using a named HQL query:

Query query = session.getNamedQuery("selectEvents_SP"); List results = query.list();

If your stored procedures take parameters, you can set them using the Query.setParameter(int, Object) method. Your stored procedures must return a result set to be usable by Hibernate. If you have legacy procedures that don t meet this requirement, you can execute them using the JDBC Connection, accessed by session.connection(). Stored procedures are an interesting addition to Hibernate and are useful in organizations that prefer to perform the majority of their database queries as procedures.

One of the problems with HQL is testing the query to make sure it works. This is typically a problem when you re new to HQL or trying out new features. Hibern8IDE provides a simple interface to your mapping definitions and an HQL console for executing queries interactively. (Of course, you ll also want to add unit tests to your code base for repeatability.) Hibern8IDE loads the Hibernate configuration file (either hibernate.cfg.xml or hibernate.properties) and the mapping definitions for your persistent objects. Once you have loaded the configuration file and mapping definitions, you can enter HQL queries in the HQL Commander tab. Hibern8IDE also supports executing named queries defined in the mapping documents. After you execute a query, the results are presented in a table that you can browse to ensure the correct objects and properties are returned. Hibern8IDE is designed to be used from the command line, but you can also start it from an Ant build file:

Summary

<target name="hibern8" description="Starts Hibern8IDE."> <java classname="net.sf.hibern8ide.Hibern8IDE classpathref="project.class.path" fork="true"/> </target>

Hibern8IDE is a useful tool for exploring the query language, especially when you re first starting out with HQL. It is relatively easy to use and provides all of the necessary features for querying your objects. Hibern8IDE only works with Hibernate 2. The project has been rebranded as HibernateConsole for Hibernate 3. HibernateConsole is a plug-in for the Eclipse IDE.

6.6 Summary

The Hibernate Query Language abstracts queries from the underlying database. While the language is similar to SQL, HQL is object oriented and has features to support querying object graphs. There are two common methods used to execute an HQL statement. The Session interface provides an overloaded find method that can execute queries and return the results. The Query interface also offers the ability to execute queries, but it provides more fine-grained control of the query, such as limiting the number of returned objects. Both the Query and Session interfaces allow results to be returned as a List or as an Iterator. The key difference between the two is that the Iterator actually retrieves objects when the next() method is called. When a List is returned, all of the contained objects are populated when the query is executed. Like JDBC PreparedStatements, HQL queries can take positional parameters, denoted with a question mark. However, HQL also supports the concept of named parameters. The Criteria class is used to create queries programmatically. It s handy when you don t know what the exact query will be, as in an