EJB3 And Stored Procedures

Stored procedures support is present in EJB3 - or at least in the Hibernate implementation in JBoss 4.0.4CR2. However, what's missing is the documentation. The Hibernate 3 Annotations reference only states that stored procedures are supported by setting the callable property of the @org.hibernate.annotations.NamedNativeQuery annotation to true. I'm going to document here an example of using a stored procedure this way because it was non-obvious to me how I might go about it.

First, let’s define a stored procedure we want to call:

CREATE PROCEDURE fooProc
@bar varchar(14),
@i integer
as
select
CURRENT_TIMESTAMP as When,
@i as SomeNumber,
@bar as SomeText
from somedummytable

Then, an entity POJO that represents the resultset:
@Entity
public class Foo {
private Date when;

public Date getWhen() {
return when;
}

private int someNumber;

private String someText;

}

You get the idea.

Next, you'll want a session bean to use to access instances of Foo:

public interface IDAOFoo {
public Foo getAFoo(string bar, int i);
}

@Stateless
public class FooHome implements IDAOFoo {
@PersistenceContext EntityManager em;

public Foo getAFoo(string bar, int i) {
em....what do we do here?
}
}

Oh yeah. Well, we need to go back to our entity class and fix it up a little.

@Entity
@NamedNativeQuery(name="baz", query="call fooProc(:bar, :i)", callable=true, readOnly=true, resultClass=Foo.class)
public class Foo {
private Date when;
[....]
}

now we can use it:

@Stateless
public class FooHome implements IDAOFoo {
@PersistenceContext EntityManager em;

public Foo getAFoo(string bar, int i) {
Foo result = (Foo)em.createNamedQuery("baz").setParameter("bar", bar).setParameter("i", i).getSingleResult();
return result;
}
}

That’s really it. The important bits are the call fooProc(:bar, :i) - you have to use the SQL CALL statement, followed by your procedure name, followed in parenthesis by a list of all the named parameters.

Tags: EJB3 Java JBoss Hibernate Stored Procedures