What is the easiest way to wrap a Stored Procedure in a Web Service?

Question

We have a Legacy system that contains a number of stored procedures that we'd like to expose to other applications as web services. We are building the web services with JBoss. The stored procedures can run from Oracle, DB2 or MS SQL Server databases.

JDeveloper has a wizard to generate the stubs and deploy to an ear file that would run on Oracle App Server (OC4J). We are in the process of migrating to JBoss and use Eclipse as our IDE of choice.

I'd rather use a framework then rebuild and maintain the infrastructure myself. Ideally I'd like to use an open-source library or a JBoss tool/IDE to generate the web service based on the connection pool definition and stored procedure name.

What are my options?

Answer 1

If the stored procedures were not written with the intention of being directly exposed as web service operations, then it may be a very bad idea to expose them. They may be making assumptions that will not be true if they are being directly called.

An alternative is to design an external API, based on requirements. If it turns out that the best way to implement one particular operation is to call a stored procedure, then do that. If it turns out that the best way to implement all of the operations is to call the existing stored procedures, then you were right, and I've just wasted your time.

But I think it's likely that there will be some operations that are not best implemented by directly calling the existing stored procedures.

Answer 2

I found an open source (Apache licensed) framework called Data Services from WS02. Has anyone tried this? Have any advice/tips/warnings?

Answer 3

Not sure you will find exactly what you are looking for, so, if writing something yourself is out of the question, here are two alternatives:

1. Use a generic web service that will receive SP name, params and invoke the procedure. This is not very friendly to the caller and not type-safe, but it will get the job done.

2. Use a two-step approach. First step - wrap your stored procedure in a Java class (either POJO or EJB). Second step - expose the class as a web service using Apache Axis2, which is very popular, tried and tested.

A quick search came us with this framework for wrapping SPs in EJBs, but I haven't tested it myself. Besides, I think some vendors have proprietary tools for that purpose. It is a much simpler question, for sure.

Answer 4

You might want to investigate the usage of EJBs in this context. All J2EE 1.4 containers should support JSR 109, which allows for EJBs to be exposed as web services.

In particular, you could wrap your stored procedure calls inside stateless session EJBs, and then expose the EJBs as web services.

OC4J 10.1.3 and JBoss 4 support this model of deployment. And so will any other J2EE 1.4 compliant container. You just have to look for supporting documentation on how to reconfigure (as opposed to recode) the application on that container.

Answer 5

Under JBoss the easiest way to expose these services will be to use an EJB3 stateless session bean. Using this model you gain access to many additional features that can be of assistance (resource pooling, transactional support, etc.).

@WebService
@SOAPBinding(style = Style.RPC)
public interface StoredProcedureWrapper extends Remote {
        String invokeStoredProc();
}

@Stateless
@WebService(endpointInterface = "path.to.StoredProcedureWrapper")
@Remote(StoredProcedureWrapper.class)
public class StoredProcedureWrapperBean {
        public String StoredProcedureWrapper() {
                // do business logic
                return "success";
        }
}