Mapping between Oracle Packages and Java Packages

In my database interfacing library jOOQ, I would like to add support for Oracle (or DB2, etc) packages. I have already implemented stored procedure/function support where every stored object is modelled as a generated Java class. For example, this stored function

CREATE FUNCTION f_author_exists (author_name VARCHAR2) RETURNS NUMBER;

will generate a class that can be used like this (note, there are also lots of convenience methods, this example just shows the general design):

// A new "function call instance". The function needs to be instanciated
// once per call
FAuthorExists f = new FAuthorExists();

// Set the function parameters on the call instance and call it
f.setAuthorName("Paulo");
f.execute(connection);

// Fetch the result from the function call instance
BigDecimal result = f.getReturnValue();

The reason I chose a mapping SQL function -> Java Class is because stored procedures allow complex return values (several OUT, or IN OUT parameters) that I want to be able to fetch one by one after calling the procedure:

p.getOutParam1();
p.getOutParam2();

Now this design works fine with stored functions / procedures, where overloading is not possible. Within Oracle's (or DB2's) packages, however, I can have several functions with the same name, like

CREATE PACKAGE my_package IS
  FUNCTION f_author_exists (name VARCHAR2) RETURNS NUMBER;
  FUNCTION f_author_exists (name VARCHAR2, country VARCHAR2) RETURNS NUMBER;
END my_package;

When I generate a class per function (or procedure), I will have naming clashes with several FAuthorExists Java classes. A lame solution is to add an index to the class name, such as FAuthorExists2, FAuthorExists3. Another lame solution is to generate some sort of hash value (or the value itself) from the parameter names/types directly into the classname, such as FAuthorExistsVARCHAR2, FAuthorExistsVARCHAR2VARCHAR2. Neither solution is desirable for obvious reasons.

Does anyone have a simple solution to this problem? Or maybe an Idea of a better overall design which would not produce such function name overloading issues?

Any feedback appreciated!

6
задан Lukas Eder 18 December 2010 в 17:11
поделиться