[dnedrow]

OK, this has me baffled.

First, several simple questions…

1) Is it possible to represent a stored procedure as one would a table, using annotations?

2) If yes, can these annotations be used to create an object (DAO, interface, etc.) as one would with a table?

Assuming the answer to both questions is yes, then…

Clearly, for stored procedures we’re not generally talking about something that needs to be persisted.

Here’s what I have…

A legacy database containing a number of tables and two stored procedures. I’m tasked with creating a “library” for java apps (and servlets) that need access to this data. In many cases, calling one of the two SPs will suffice. As it stands, Spring is a big player for several existing tools that talk to the DB, with each using its own access methods.

I’m done with the tables and have created interfaces, dao, class, etc.

I’m stuck trying to decide how to handle the stored procedures. I could write POJOs to handle them, but then I lose some of the built-in convenience features of JPA/Hibernate/Spring/etc. I would prefer that developers using the library be able to access everything in a similar manner, whether table or SP.

Documentation on SPs is often unclear, missing, or less relevant (ie., lots of examples for Hibernate XML mapping files, but nothing re: annotations).

Let’s assume the following (simple) stored procedures, and, for the sake of simplicity, all input and output columns are INT….

CREATE PROCEDURE noParams
BEGIN
  SELECT distinct column1,column2
    FROM results
END
GO

CREATE PROCEDURE withParams (item INT)
BEGIN
  SELECT distinct column1,column2
    FROM results
    WHERE column1=item
END
GO

Can these be represented via annotated java class files?

-David

[Riyad Kalla]

1) Is it possible to represent a stored procedure as one would a table, using annotations?

I *think* I checked the JPA docs on this once, and you can’t do it via annotations… atleast not yet. You have to use a Native Query.

Assuming the answer to both questions is yes, then…

It’s actually “no” right now until the spec is updated to support it with annotations… so I’m guessing the rest of the questions answers are “no”?

I’m stuck trying to decide how to handle the stored procedures. I could write POJOs to handle them, but then I lose some of the built-in convenience features of JPA/Hibernate/Spring/etc. I would prefer that developers using the library be able to access everything in a similar manner, whether table or SP.

Well if you make your DAOs the entry point into your library for all callers, you can mask the fact that you are using NativeQueries to access the SPs from the caller and still return them an entity. Same goes for tables that are mapped with annotations, the caller doesn’t care, he just wants back an entity right?

[Author]

Posts