Porting existing Forms application with C(R)UD stored procedures to ADF

At the last ADF Workshop, Mehmet asked if it is possible to reuse ther existing CRUD stored procedures while porting their Forms solution to ADF. In this post, I’ll  just cover CUD, as R is a bit more specific if we want to retain all the ADF BC goodies.

Mehmet’s existing CUD stored procedures have the following signatures:
SUBTYPE DEPARTMENTS_rt IS DEPARTMENTS%ROWTYPE;
SUBTYPE DEPARTMENT_ID_t IS DEPARTMENTS.DEPARTMENT_ID%TYPE;

PROCEDURE ins (rec_in IN DEPARTMENTS_TP.DEPARTMENTS_rt);
PROCEDURE upd (rec_in IN DEPARTMENTS_TP.DEPARTMENTS_rt);
PROCEDURE del (department_id_in IN DEPARTMENTS_TP.DEPARTMENT_ID_t);
Many developers have worked on the code base and they didn’t all follow the same naming convention for package and procedure names – that means we can not deduce procedure names simply from table or entity names.

The solution
is quite simple. We need to override the doDml method and execute existing stored procedures. One of the problems is that Pl/Sql row type can’t be mapped to Java easily. But – we can declare a row type variable and initialize each member before we call the procedure:
DECLARE
rec_in DEPARTMENTS_TP.DEPARTMENTS_RT;
BEGIN
rec_in.DEPARTMENT_ID := ?;
rec_in.DEPARTMENT_NAME := ?;
rec_in.MANAGER_ID := ?;
rec_in.LOCATION_ID := ?;
DEPARTMENTS_CP.INS(rec_in);
END;
With this idea on hand, we can implement a Pl/Sql code generator in no time. CudEntityImpl (click for complete listing) is a generic base class which fits the requirements. All a developer has to do is to extend his entities from this base class and specify (either through custom properties or by overriding procedure name getters) stored procedure names.

You can download the complete sample project with subversion client:
svn checkout http://adfcommunity.googlecode.com/svn/trunk/AdfCrudSpDemo adfcommunity-read-only