spag4j - Stored Procedure Generator for Java:
=============================================
spag4j is a code generator written in Java to produce Java code for accessing a stored procedure layer
from a database. Currently it supports stored procedures written in an Oracle. It produces either an
EJB3 Java layer or simple static access methods.
The Problem:
============
There are several OR Mapping Tools for simply accessing tables and views in the open source world. For
accessing an stored procedure layer all this tools are not enough. It's time to access stored procedures
from Java without having to code them every time from scratch.
Because of the specific dialects from one database vendor to another it really hard to generate access
methods for them. spag4j currently focuses on the special needs to produce access code for Oralce PL/SQL
Stored Procedures. It also focuses on generating Java POJOs and PL/SQL Wrapper procedures from PL/SQL
Records which can not be accessed via the Java JDBC Bridge (see Oracle in deep).
Oracle in deep (http://download.oracle.com/docs/cd/B10500_01/java.920/a96654/ref.htm#1007714):
==============================================================================================
It is not possible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL RECORD,
BOOLEAN, or table with non-scalar element types. As a workaround to PL/SQL RECORD, BOOLEAN, or non-scalar table
types, create wrapper procedures that handle the data as types supported by JDBC.
--> Therefore I started to write spag4j
Additional Features:
===================
Has a dictonary functionality for getting beautiful Java class, mehtod and variablen names
Uses open architecture technology (http://www.openarchitectureware.org/)
How does it work:
=================
1. spag4j analysis the datastructur of the underlaying oracle database schema
2. spag4j generates a metamodel of the structure
3. spag4j uses the metamodel information to generate java and oracle access code
4. spag4j produces an EJB3 for an oracle stored procedure package
5. spag4j produces all needed POJOs
6. After all these steps you can access every PL/SQL Procudure from Java
Sample:
Given PL/SQL Package:
CREATE OR REPLACE PACKAGE SAMPLE AS
TYPE Person IS RECORD (
ID NUMBER,
NAME VARCHAR(250),
DATEOFBIRTH TIMESTAMP
);
TYPE ArrayOfPerson IS TABLE OF Person INDEX BY BINARY_INTEGER;
FUNCTION FindAllPersons (
AllPersons OUT ArrayOfPerson) RETURN NUMBER;
FUNCTION StorePerson (
PersonToStore IN Person) RETURN NUMBER;
FUNCTION GetTimestamp RETURN DATE;
END SAMPLE;
CREATE OR REPLACE PACKAGE BODY SAMPLE AS
FUNCTION FindAllPersons (
AllPersons OUT ArrayOfPerson) RETURN NUMBER IS
RetVal NUMBER;
BEGIN
-- do something
RetVal := 0;
END;
FUNCTION StorePerson (
PersonToStore IN Person) RETURN NUMBER IS
RetVal NUMBER;
BEGIN
-- do something
RetVal := 0;
END;
FUNCTION GetTimestamp RETURN DATE IS
BEGIN
RETURN SYSDATE;
END GetTimestamp;
END SAMPLE;
Following Java code will be created (I only publish snippets):
public Double callFindallpersons(ObjectHolder allPersons)
throws SAMPLEException {
Connection con = null;
CallableStatement stmt = null;
try {
con = getConnection();
stmt = con.prepareCall(
"begin ? := SAMPLE_LAYER0.Findallpersons(?); end;");
stmt.registerOutParameter(1, java.sql.Types.NUMERIC);
stmt.registerOutParameter(2, java.sql.Types.ARRAY,
"SAMPLEAROFPERSON");
stmt.executeUpdate();
ObjectHolder returnValue = new ObjectHolder();
returnValue.setObject(stmt.getDouble(1));
Array outArray2 = stmt.getArray(2);
Object[] outArrayObj2 = (Object[]) outArray2.getArray();
ArrayOfPerson outObj2 = convertToArrayOfPerson(outArrayObj2);
allPersons.setObject(outObj2);
stmt.close();
return returnValue.getObject();
} catch (Exception e) {
throw new SAMPLEException(e.getMessage(), e);
}
}
public java.util.Date callGettimestamp() throws SAMPLEException {
Connection con = null;
CallableStatement stmt = null;
try {
con = getConnection();
stmt = con.prepareCall(
"begin ? := SAMPLE_LAYER0.Gettimestamp(); end;");
stmt.registerOutParameter(1, java.sql.Types.TIMESTAMP);
stmt.executeUpdate();
ObjectHolder returnValue = new ObjectHolder();
returnValue.setObject(JdbcTypeConverter.convertFromDate(
stmt.getTimestamp(1)));
stmt.close();
return returnValue.getObject();
} catch (Exception e) {
throw new SAMPLEException(e.getMessage(), e);
}
}
public Double callStoreperson(Person personToStore)
throws SAMPLEException {
Connection con = null;
CallableStatement stmt = null;
try {
con = getConnection();
stmt = con.prepareCall(
"begin ? := SAMPLE_LAYER0.Storeperson(?); end;");
stmt.registerOutParameter(1, java.sql.Types.NUMERIC);
// setInputVariable PlSqlRecordType
Struct inStruct2 = convertFromPerson(personToStore, con);
stmt.setObject(2, inStruct2);
stmt.executeUpdate();
ObjectHolder returnValue = new ObjectHolder();
returnValue.setObject(stmt.getDouble(1));
stmt.close();
return returnValue.getObject();
} catch (Exception e) {
throw new SAMPLEException(e.getMessage(), e);
}
}
private ArrayOfPerson convertToArrayOfPerson(Object[] rawData)
throws SQLException {
ArrayOfPerson collection = new ArrayOfPersonImpl();
for (int i = 0; i < rawData.length; i++) {
Struct struct = (Struct) rawData[i];
Person element = convertToPerson(struct);
collection.add(element);
}
return collection;
}
private Person convertToPerson(Struct struct) throws SQLException {
Object[] rawData = struct.getAttributes();
Person obj = new PersonImpl(JdbcTypeConverter.convertToDouble(
rawData[0]), (String) rawData[1],
JdbcTypeConverter.convertToDate(rawData[2]));
return obj;
}
private Object[] convertFromArrayOfPerson(ArrayOfPerson coll, Connection con)
throws SQLException {
Object[] result = new Object[coll.size()];
int i = 0;
for (Person obj : coll) {
result[i++] = convertFromPerson(obj, con);
}
return result;
}
private Struct convertFromPerson(Person obj, Connection con)
throws SQLException {
Object[] objArray = new Object[3];
objArray[0] = JdbcTypeConverter.convertFromDouble(obj.getId());
objArray[1] = obj.getFullName();
objArray[2] = JdbcTypeConverter.convertFromDate(obj.getDateofbirth());
Struct struct = jdbcHandler.createStruct(objArray, "SAMPLEPERSON", con);
return struct;
}
Following PL/SQL Wrapper code will be created:
CREATE TYPE SamplePerson AS OBJECT (Idd NUMBER,FullName VARCHAR2(250),Dateofbirth TIMESTAMP)
/
CREATE TYPE SampleArOfPerson AS VARRAY(2147483647) OF SamplePerson
/
CREATE OR REPLACE PACKAGE SAMPLE_LAYER0
AS
FUNCTION Findallpersons (AllPersons OUT SampleArOfPerson)
RETURN NUMBER;
FUNCTION Gettimestamp
RETURN DATE;
FUNCTION Storeperson (PersonToStore IN SamplePerson)
RETURN NUMBER;
FUNCTION CnvPersonRec (rec IN SAMPLE.PERSON)
RETURN SamplePerson;
FUNCTION CnvArOfPersonTbl (tbl IN SAMPLE.ARRAYOFPERSON)
RETURN SampleArOfPerson;
FUNCTION CnvPersonObj (obj IN SamplePerson)
RETURN SAMPLE.PERSON;
FUNCTION CnvArOfPersonCol (col IN SampleArOfPerson)
RETURN SAMPLE.ARRAYOFPERSON;
END SAMPLE_LAYER0;
/
CREATE OR REPLACE PACKAGE BODY SAMPLE_LAYER0
AS
FUNCTION Findallpersons (AllPersons OUT SampleArOfPerson)
RETURN NUMBER
IS
-- OUT params
tmpAllPersons SAMPLE.ARRAYOFPERSON;
-- return value
tmpRetVal NUMBER;
BEGIN
-- call wrapped function
tmpRetVal := SAMPLE.FINDALLPERSONS(tmpAllPersons);
-- convert OUT params
AllPersons := CnvArOfPersonTbl(tmpAllPersons);
RETURN tmpRetVal;
END;
FUNCTION Gettimestamp
RETURN DATE
IS
-- return value
tmpRetVal DATE;
BEGIN
-- call wrapped function
tmpRetVal := SAMPLE.GETTIMESTAMP();
RETURN tmpRetVal;
END;
FUNCTION Storeperson (PersonToStore IN SamplePerson)
RETURN NUMBER
IS
-- IN params
tmpPersonToStore SAMPLE.PERSON;
-- return value
tmpRetVal NUMBER;
BEGIN
-- convert IN params
tmpPersonToStore := CnvPersonObj(PersonToStore);
-- call wrapped function
tmpRetVal := SAMPLE.STOREPERSON(tmpPersonToStore);
RETURN tmpRetVal;
END;
FUNCTION CnvPersonRec (rec IN SAMPLE.PERSON)
RETURN SamplePerson IS
result SamplePerson;
BEGIN
result := SamplePerson(
rec.ID,rec.NAME,rec.DATEOFBIRTH);
RETURN result;
END;
FUNCTION CnvArOfPersonTbl (tbl IN SAMPLE.ARRAYOFPERSON)
RETURN SampleArOfPerson IS
result SampleArOfPerson;
rec SAMPLE.PERSON;
NumOfEntries NUMBER;
BEGIN
result := SampleArOfPerson();
NumOfEntries := 1;
IF tbl.Count > 0 THEN
FOR Entry IN tbl.FIRST ..tbl.LAST LOOP
rec := tbl(Entry);
result.extend;
result(NumOfEntries) := CnvPersonRec(rec);
NumOfEntries := NumOfEntries + 1;
END LOOP;
END IF;
RETURN result;
END;
FUNCTION CnvPersonObj (obj IN SamplePerson)
RETURN SAMPLE.PERSON IS
result SAMPLE.PERSON;
BEGIN
result.ID := obj.Idd;
result.NAME := obj.FullName;
result.DATEOFBIRTH := obj.Dateofbirth;
RETURN result;
END;
FUNCTION CnvArOfPersonCol (col IN SampleArOfPerson)
RETURN SAMPLE.ARRAYOFPERSON IS
result SAMPLE.ARRAYOFPERSON;
rec SamplePerson;
NumOfEntries NUMBER;
BEGIN
NumOfEntries := 0;
IF col.Count > 0 THEN
FOR Entry IN col.FIRST ..col.LAST LOOP
rec := col(Entry);
result(NumOfEntries) := CnvPersonObj(rec);
NumOfEntries := NumOfEntries + 1;
END LOOP;
END IF;
RETURN result;
END;
END SAMPLE_LAYER0;
/