Jump to: navigation, search

Difference between revisions of "EclipseLink/Development/DBWS/MetadataGenerationFromDDLMetaModel"

(Open Issues)
(Open Issues)
Line 885: Line 885:
 
# Query names - what would be the default name for a query generated from a stored procedure, etc.?  Perhaps the user could provide a Map of query names > procedure names.  Currently, the query name is the stored proc/function name.
 
# Query names - what would be the default name for a query generated from a stored procedure, etc.?  Perhaps the user could provide a Map of query names > procedure names.  Currently, the query name is the stored proc/function name.
 
# For non-Oracle databases, JDBCHelper returns meta-model objects that extend the DDLParser meta-model - should this functionality be taken into consideration here as well?
 
# For non-Oracle databases, JDBCHelper returns meta-model objects that extend the DDLParser meta-model - should this functionality be taken into consideration here as well?
# Automatic CRUD operation generation for tables;  by default, DBWS generates CRUD query operations for a given database table - is this something we should include by default?  Perhaps it could be configurable.
+
# Automatic CRUD operation generation for tables;  by default, DBWS generates CRUD query operations for a given database table - is this something we should include by default?  Perhaps it could be configurable. RESOLVED.  See Below.
 
# Relationships - there is currently nothing in place to allow the user to define relationships between tables.  What information would the user need to provide in order to have entity relationships represented in the generated metadata?
 
# Relationships - there is currently nothing in place to allow the user to define relationships between tables.  What information would the user need to provide in order to have entity relationships represented in the generated metadata?
 
# JAXB generation - which of the methods mentioned [http://wiki.eclipse.org/EclipseLink/Development/DBWS/MetadataGenerationFromDDLMetaModel#JAXB_Metadata_Generation above] is preferred?  Are there other options?
 
# JAXB generation - which of the methods mentioned [http://wiki.eclipse.org/EclipseLink/Development/DBWS/MetadataGenerationFromDDLMetaModel#JAXB_Metadata_Generation above] is preferred?  Are there other options?

Revision as of 14:05, 25 July 2013

Generating JAXB and JPA Metadata From DDL Parser Meta-model

The EclipseLink DBWS design time component (a.k.a. DBWSBuilder) generates OR and OX projects for use by the runtime component - these project instances are built using DDLParser generated meta-model objects. Since EclipseLink 2.5, DBWS generates and writes out JAXB and JPA metadata (DBWS Metadata Support) based on the OR/OX projects. When considering JAXB/JPA metadata generation consolidation among various components, it would make sense to have the ability to generate metadata directly from the DDLParser generated metamodel, then bootstrap DBWS builder, JPA-RS, etc. from this metadata. This would allow the DDLParser and metadata generation piece proposed here to be standalone components, available to whomever requires this generated metadata.

Basic DDLParser Usage

The DatabaseTypeBuilder class (part of the DDLParser package) is responsible for generating the required statement to be used to retrieve the DDL from the database, then parsing the returned DDL into the meta-model objects. The type builder can build TableTypes, ProcedureTypes, and PLSQLPackageTypes based on user provided schema(s) and table/procedure/package name(s), and a database connection. The build methods can take a single schema & table/procedure/package name, or lists of each when multiple artifacts are to be parsed. When passing in lists of each, it is assumed that there is a 1-1 mapping of list indicies between the two lists.

Table

// use DatabaseTypeBuilder to generate a list of TableTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
try {
    List<TableType> dbTables = dbTypeBuilder.buildTables(conn, "SCOTT", "EMPLOYEE_TABLE");
    ...
} catch (ParseException e) {
    // handle exception
}

Top-level StoredProcedure

// use DatabaseTypeBuilder to generate a list of ProcedureTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
try {
    List<ProcedureType> dbProcedures = dbTypeBuilder.buildProcedures(conn, "TOPLEVEL", "GetAllEmployees");
    ...
} catch (ParseException e) {
    // handle exception
}

Top-level StoredFunction

// use DatabaseTypeBuilder to generate a list of FunctionTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
try {
    List<FunctionType> dbFunctions = dbTypeBuilder.buildFunctions(conn, "TOPLEVEL", "GetEmployeeById");
    ...
} catch (ParseException e) {
    // handle exception
}

PL/SQL Stored Functions/Procedures

Retrieving meta-model instances for PL/SQL procedures and functions involves retrieving the PL/SQL package meta-model object, then getting the procedure and/or function objects from it.

// add desired PL/SQL stored function/procedure names
List<String> procedurePatterns = new ArrayList<String>();
// PL/SQL stored procedures
procedurePatterns.add("COPYTABLE");
procedurePatterns.add("COPYPHONECOLLECTION");  
// PL/SQL stored functions
procedurePatterns.add("CREATETABLE");
procedurePatterns.add("GETALLTABLES");  
 
// use DatabaseTypeBuilder to generate a list of PLSQLPackageTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
try {
    List<ProcedureType> dbProcedures = new ArrayList<ProcedureType>();
    // process the package
    List<PLSQLPackageType> packages = dbTypeBuilder.buildPackages(conn, "SCOTT", "PACKAGE2");
    for (PLSQLPackageType pkgType : packages) {
        // now get the desired procedures/functions from the processed package
        for (ProcedureType procType : pkgType.getProcedures()) {
            if (procedurePatterns.contains(procType.getProcedureName())) {
                dbProcedures.add(procType);
            }
        }
    }
    ...
} catch (ParseException e) {
    // handle exception
}

Multiple PL/SQL Packages

When more than one PL/SQL package is to be parsed, lists of schema names and associated package names can be passed to the parser. It is assumed that there is a direct correlation between the lists.

// schema names
List<String> schemaNames = new ArrayList<String>();
schemaNames.add("HR");
schemaNames.add("SCOTT");
schemaNames.add("SCOTT");
 
// package names
List<String> packageNames = new ArrayList<String>();
packageNames.add("HR_DATA");
packageNames.add("MATH_FUNCTIONS");
packageNames.add("EMPLOYEE");
 
// procedure names
List<String> procedurePatterns= new ArrayList<String>();
procedurePatterns.add("findAllEmployees");
procedurePatterns.add("findEmployeeByPK");
procedurePatterns.add("getPersonalData");
procedurePatterns.add("removeEmployee");
procedurePatterns.add("calculateMaxAngle");
 
// use DatabaseTypeBuilder to generate a list of PLSQLPackageTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
try {
    // process the packages
    List<PLSQLPackageType> packages = dbTypeBuilder.buildPackages(conn, schemaNames, packageNames);
    for (PLSQLPackageType pkgType : packages) {
        // now get the desired procedures/functions from the processed package
        for (ProcedureType procType : pkgType.getProcedures()) {
            if (procedurePatterns.contains(procType.getProcedureName())) {
                dbProcedures.add(procType);
            }
        }
    }
    ...
} catch (ParseException e) {
    // handle exception
}

JPA Metadata Generation

The EclipseLink JPA meta-model will be utilized to generate the JPA metadata. The proposed JPAMetadataGenerator class will be responsible for building an instance of org.eclipse.persistence.internal.jpa.metadata.xml.XMLEntityMappings. The entity mapping writer class (org.eclipse.persistence.internal.jpa.metadata.xml.XMLEntityMappingsWriter) could then be used to marshal the generated metadata for use by the DBWS runtime component, JPA-RS, etc.

JPAMetadataGenerator

The JPA model generator class (org.eclipse.persistence.tools.metadata.generation.JPAMetadataGenerator) is responsible for generating the EclipseLink JPA meta-model from a List of DDLParser meta-model objects.

API

The following constructors will be provided:

/**
 * Default constructor.  Sets the default package name to null, and dbPlatform to
 * org.eclipse.persistence.platform.database.oracle.Oracle11Platform.
 * 
 * The default package name will be prepended to generated class names for database
 * artifacts that are not in a PL/SQL package.
 * 
 * The database platform is used to get class names for database types, i.e. 
 * java.math.BigDecimal for DECIMAL.
 * 
 * @see org.eclipse.persistence.platform.database.oracle.Oracle11Platform
 * @see org.eorg.eclipse.persistence.internal.databaseaccess.DatabasePlatform
 */
public JPAMetadataGenerator()
 
/**
 * This constructor allows setting the default package name and database platform.
 * 
 * @param defaultPackage package name to be prepended to generated class names for artifacts
 * not in a PL/SQL package such as an Entity (to avoid having classes in the default package)
 * @param platformClassName class name of the DatabasePlatform to be used to get class names 
 * for database types, i.e. java.math.BigDecimal for DECIMAL.
 * @see org.eorg.eclipse.persistence.internal.databaseaccess.DatabasePlatform
 */
public JPAMetadataGenerator(String defaultPackage, String platformClassName)   
 
/**
 * This constructor allows setting the default package name and database platform.
 * 
 * @param defaultPackage package name to be prepended to generated class names for artifacts
 * not in a PL/SQL package such as an Entity (to avoid having classes in the default package)
 * @param dbPlatform DatabasePlatform to be used to get class names for database types, i.e. 
 * java.math.BigDecimal for DECIMAL.
 * @see org.eorg.eclipse.persistence.internal.databaseaccess.DatabasePlatform
 */
public JPAMetadataGenerator(String defaultPackage, DatabasePlatform dbPlatform)

The generator will have a method that takes a List of org.eclipse.persistence.tools.oracleddl.metadata.CompositeDatabaseType type instances, and return an org.eclipse.persistence.internal.jpa.metadata.xml.XMLEntityMappings instance.

/**
 * Generate an XMLEntityMappings instance based on a given list of meta-model database types.
 * 
 * @param databaseTypes the list of meta-model database types to be used to generate an XMLEntityMappings
 * @see org.eclipse.persistence.tools.oracleddl.metadata.CompositeDatabaseType
 */
public XMLEntityMappings generateXmlEntityMappings(List<CompositeDatabaseType> databaseTypes)

Key DDL to JPA meta-model mappings

This section will provide a high-level outline of what JPA meta-model objects are generated from the key DDL Parser meta-model objects.

TableType
  • Maps to an EntityAccessor
ProcedureType
  • Maps to a named-query, i.e. one of:
    • NamedStoredProcedureQueryMetadata
    • NamedStoredFunctionQueryMetadata
    • NamedPLSQLStoredProcedureQueryMetadata
    • NamedPLSQLStoredFunctionQueryMetadata
Composite types
  • Map to an EmbeddableAccessor
  • Examples of composites: VarrayType, PLSQLRecordType, etc.
  • In addition, metadata objects will be generated for each record, table, object and array (each references its associated Embeddable):
    • PLSQLRecordMetadata
    • PLSQLTableMetadata
    • OracleObjectTypeMetadata
    • OracleArrayTypeMetadata
Nested composites
  • Map to an EmbeddedAccessor
  • An example of a nested composite would be a PL/SQL record with a PL/SQL record field
%ROWTYPE
  • Requires special handling: a PL/SQL record and associated Embeddable will be generated to hold the ROWTYPE data

PL/SQL Types and JDBC

Oracle's JDBC drivers do not support PL/SQL types (Oracle FAQ), so it is required that a 'shadow' top-level (i.e. outside of the PL/SQL Package) JDBC type exists. For the following PL/SQL package DDL:

CREATE OR REPLACE PACKAGE EMP_RECORD_PACKAGE AS
    TYPE EmpRec IS RECORD (
        emp_id NUMBER(4),
        emp_name VARCHAR(25)
    );
    FUNCTION get_emp_record (pId IN NUMBER) RETURN EmpRec;
END EMP_RECORD_PACKAGE;

The required JDBC shadow type DDL would be:

CREATE OR REPLACE TYPE EMP_RECORD_PACKAGE_EMPREC AS OBJECT (
    emp_id NUMBER(4),
    emp_name VARCHAR(25)
)

The expected naming convention for the shadow JDBC type is PL/SQL package name + "_" + PL/SQL record name. This is required as anonymous PL/SQL is executed on the database schema at runtime which is responsible for converting between the shadow JDBC type and the PL/SQL type; the JDBC type name isn't provided, so this assumption about the shadow type name is necessary. More information regarding the anonymous PL/SQL that is generated can be found here.

Naming Convention for Entities and Embeddables

The following code will be used to generate the class name for an Entity or Embeddable:

/**
  * Returns a Java class name based on a given name and package.  The returned
  * string  will be  in the format  'packagename.Name'.   For  example,  given
  * the name 'EMPLOYEE'  and packageName 'TEST', the  method would return  the
  * string 'test.Employee'.
  *
  */
public static String getGeneratedJavaClassName(String name, String packageName) {
    String first = name.substring(0, 1).toUpperCase();
    String rest = name.toLowerCase().substring(1);
    return packageName.toLowerCase() + '.' + first + rest;
}

Note that 'packageName' could be either the name of the PL/SQL package (for PL/SQL types) or the user-provided 'defaultPackage'.

Examples

The following examples are intended to show how DatabaseTypeBuilder can be used to get DDLParser meta-model objects and the JPA metadata that is generated for them.

Simple Table

// Table DDL
/*
CREATE TABLE TABLETYPE (
    EMPNO DECIMAL(4,0) NOT NULL,
    ENAME VARCHAR(10),
    HIREDATE DATE,
    PRIMARY KEY (EMPNO)
)
*/
 
static final String DEFAULT_PKG = "metadatagen";
static final String DB_PLATFORM = "org.eclipse.persistence.platform.database.oracle.Oracle11Platform";
 
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
List dbTables;
try {
    dbTables = dbTypeBuilder.buildTables(conn, "%", "TABLETYPE");
} catch (ParseException e) {
    // handle parse exception
}
// instantiate the JPA metadata generator
JPAMetadataGenerator gen = new JPAMetadataGenerator(DEFAULT_PKG , DB_PLATFORM);
 
// generate the XMLEntityMappings instance
XMLEntityMappings mappings = gen.generateXmlEntityMappings(dbTables);
 
// marshal the metadata to System.out
XMLEntityMappingsWriter writer = new XMLEntityMappingsWriter();
writer.write(mappings, System.out);
 
...

The following JPA metadata would be marshalled:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:entity class="metadatagen.Tabletype" access="VIRTUAL">
      <orm:table name="TABLETYPE"/>
      <orm:attributes>
         <orm:id name="empno" attribute-type="java.math.BigInteger">
            <orm:column name="EMPNO"/>
         </orm:id>
         <orm:basic name="ename" attribute-type="java.lang.String">
            <orm:column name="ENAME"/>
         </orm:basic>
         <orm:basic name="hiredate" attribute-type="java.sql.Date">
            <orm:column name="HIREDATE"/>
         </orm:basic>
      </orm:attributes>
   </orm:entity>
</orm:entity-mappings>

Simple TopLevel Stored Procedure

// Procedure DDL
/*
CREATE PROCEDURE OUTININOUTARGSSP(T OUT VARCHAR, U IN VARCHAR, V IN OUT NUMERIC) IS
BEGIN
    ...
END OUTININOUTARGSSP;
*/
 
static final String DEFAULT_PKG = "metadatagen";
static final String DB_PLATFORM = "org.eclipse.persistence.platform.database.oracle.Oracle11Platform";
 
// use DatabaseTypeBuilder to generate a list of ProcedureTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
List dbProcedures;
try {
    dbProcedures = dbTypeBuilder.buildProcedures(conn, "TOPLEVEL", "OUTININOUTARGSSP");
} catch (ParseException e) {
    // handle parse exception
}
 
// instantiate the JPA metadata generator
JPAMetadataGenerator gen = new JPAMetadataGenerator(DEFAULT_PKG, DB_PLATFORM);
 
// generate the XMLEntityMappings instance
XMLEntityMappings mappings = gen.generateXmlEntityMappings(dbProcedures);
 
// marshal the metadata to System.out
XMLEntityMappingsWriter writer = new XMLEntityMappingsWriter();
writer.write(mappings, System.out);
 
...

The following JPA metadata would be marshalled:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:named-stored-procedure-query name="OUTININOUTARGSSP" procedure-name="OUTININOUTARGSSP" returns-result-set="false">
      <orm:parameter mode="OUT" name="T" type="java.lang.String" class="java.lang.String" jdbc-type="12" jdbc-type-name="VARCHAR"/>
      <orm:parameter mode="IN" name="U" type="java.lang.String" class="java.lang.String" jdbc-type="12" jdbc-type-name="VARCHAR"/>
      <orm:parameter mode="INOUT" name="V" type="java.math.BigInteger" class="java.math.BigInteger" jdbc-type="2" jdbc-type-name="NUMERIC"/>
   </orm:named-stored-procedure-query>
</orm:entity-mappings>

Oracle Object Type

// Object Type and Stored Procedure DDL
/*
CREATE OR REPLACE TYPE DBWS_PHONE_TYPE AS OBJECT (HOME VARCHAR2(20),CELL VARCHAR2(20))
CREATE OR REPLACE PROCEDURE CREATE_PHONE_TYPE(HOME_NUMBER IN VARCHAR2, CELL_NUMBER IN VARCHAR2, RESULT OUT DBWS_PHONE_TYPE) AS
BEGIN
    ...
END CREATE_PHONE_TYPE;
*/
 
static final String DEFAULT_PKG = "metadatagen";
static final String DB_PLATFORM = "org.eclipse.persistence.platform.database.oracle.Oracle11Platform";
 
// use DatabaseTypeBuilder to generate a list of ProcedureTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
List dbProcedures;
try {
    dbProcedures = dbTypeBuilder.buildProcedures(conn, "TOPLEVEL", "CREATE_PHONE_TYPE");
} catch (ParseException e) {
    // handle parse exception
}
 
// instantiate the JPA metadata generator
JPAMetadataGenerator gen = new JPAMetadataGenerator(DEFAULT_PKG, DB_PLATFORM);
 
// generate the XMLEntityMappings instance
XMLEntityMappings mappings = gen.generateXmlEntityMappings(dbProcedures);
 
// marshal the metadata to System.out
XMLEntityMappingsWriter writer = new XMLEntityMappingsWriter();
writer.write(mappings, System.out);
 
...

The following JPA metadata would be marshalled:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:named-stored-procedure-query name="CREATE_PHONE_TYPE" procedure-name="CREATE_PHONE_TYPE" returns-result-set="false">
      <orm:parameter mode="IN" name="HOME_NUMBER" type="java.lang.String" class="java.lang.String" jdbc-type="12" jdbc-type-name="VARCHAR"/>
      <orm:parameter mode="IN" name="CELL_NUMBER" type="java.lang.String" class="java.lang.String" jdbc-type="12" jdbc-type-name="VARCHAR"/>
      <orm:parameter mode="OUT" name="RESULT" type="metadatagen.Dbws_phone_type" class="metadatagen.Dbws_phone_type" jdbc-type="2002" jdbc-type-name="DBWS_PHONE_TYPE"/>
   </orm:named-stored-procedure-query>
   <orm:oracle-object name="DBWS_PHONE_TYPE" java-type="metadatagen.Dbws_phone_type">
      <orm:field name="HOME" database-type="VARCHAR_TYPE"/>
      <orm:field name="CELL" database-type="VARCHAR_TYPE"/>
   </orm:oracle-object>
   <orm:embeddable class="metadatagen.Dbws_phone_type" access="VIRTUAL">
      <orm:struct name="DBWS_PHONE_TYPE">
         <orm:field>HOME</orm:field>
         <orm:field>CELL</orm:field>
      </orm:struct>
      <orm:attributes>
         <orm:basic name="home" attribute-type="java.lang.String">
            <orm:column name="HOME"/>
         </orm:basic>
         <orm:basic name="cell" attribute-type="java.lang.String">
            <orm:column name="CELL"/>
         </orm:basic>
      </orm:attributes>
   </orm:embeddable>
</orm:entity-mappings>

Varray Type

// Varray Type and Stored Procedure DDL
CREATE OR REPLACE TYPE VCARRAY AS VARRAY(4) OF VARCHAR2(20)
CREATE OR REPLACE PROCEDURE GETVCARRAY(T IN VARCHAR, U OUT VCARRAY) AS
BEGIN
   ...
END GETVCARRAY;
*/
 
static final String DEFAULT_PKG = "metadatagen";
static final String DB_PLATFORM = "org.eclipse.persistence.platform.database.oracle.Oracle11Platform";
 
// use DatabaseTypeBuilder to generate a list of ProcedureTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
List dbProcedures;
try {
    dbProcedures = dbTypeBuilder.buildProcedures(conn, "TOPLEVEL", "GETVCARRAY");
} catch (ParseException e) {
    // handle parse exception
}
 
// instantiate the JPA metadata generator
JPAMetadataGenerator gen = new JPAMetadataGenerator(DEFAULT_PKG, DB_PLATFORM);
 
// generate the XMLEntityMappings instance
XMLEntityMappings mappings = gen.generateXmlEntityMappings(dbProcedures);
 
// marshal the metadata to System.out
XMLEntityMappingsWriter writer = new XMLEntityMappingsWriter();
writer.write(mappings, System.out);
 
...

The following JPA metadata would be marshalled:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:named-stored-procedure-query name="GETVCARRAY" procedure-name="GETVCARRAY" returns-result-set="false">
      <orm:parameter mode="IN" name="T" type="java.lang.String" class="java.lang.String" jdbc-type="12" jdbc-type-name="VARCHAR"/>
      <orm:parameter mode="OUT" name="U" type="metadatagen.Vcarray" class="metadatagen.Vcarray" jdbc-type="2003" jdbc-type-name="VCARRAY"/>
   </orm:named-stored-procedure-query>
   <orm:oracle-array name="VCARRAY" java-type="metadatagen.Vcarray" nested-type="VARCHAR_TYPE"/>
   <orm:embeddable class="metadatagen.Vcarray" access="VIRTUAL">
      <orm:attributes>
         <orm:array name="items" target-class="VARCHAR2" attribute-type="java.util.ArrayList" database-type="VARCHAR2">
            <orm:column name="ITEMS"/>
         </orm:array>
      </orm:attributes>
   </orm:embeddable>
</orm:entity-mappings>

PL/SQL Stored Function that returns a PL/SQL Record

// PL/SQL Package DDL
/*
CREATE OR REPLACE PACKAGE PACKAGE2 AS
    TYPE EMPREC IS RECORD (EMP_ID NUMERIC(4), EMP_NAME VARCHAR2(25));
    FUNCTION COPYRECORD_FUNC(OLDREC IN EMPREC) RETURN EMPREC;
END PACKAGE2;
 
// JDBC Shadow types for PL/SQL records
CREATE OR REPLACE TYPE PACKAGE2_EMPREC AS OBJECT(EMP_ID NUMERIC(4), EMP_NAME VARCHAR2(25));
*/
 
static final String DEFAULT_PKG = "metadatagen";
static final String DB_PLATFORM = "org.eclipse.persistence.platform.database.oracle.Oracle11Platform";
 
// use DatabaseTypeBuilder to generate a list of PackageTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
List dbProcedures = new ArrayList();
List<PLSQLPackageType> packages;
try {
    // process the package
    packages = dbTypeBuilder.buildPackages(conn, "SCOTT", "PACKAGE2");
} catch (ParseException e) {
    // handle parse exception
}
// we expect one package containing one stored function (and a record)
for (PLSQLPackageType pkgType : packages) {
    // now get the desired procedures/functions from the processed package
    for (ProcedureType procType : pkgType.getProcedures()) {
        if (procType.getProcedureName().equals("COPYRECORD_FUNC")) {
            dbProcedures.add(procType);
        }
    }
}
 
// instantiate the JPA metadata generator
JPAMetadataGenerator gen = new JPAMetadataGenerator(DEFAULT_PKG, DB_PLATFORM);
 
// generate the XMLEntityMappings instance
XMLEntityMappings mappings = gen.generateXmlEntityMappings(dbProcedures);
 
// marshal the metadata to System.out
XMLEntityMappingsWriter writer = new XMLEntityMappingsWriter();
writer.write(mappings, System.out);
 
...

The following JPA metadata would be marshalled:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:named-plsql-stored-function-query name="COPYRECORD_FUNC" function-name="PACKAGE2.COPYRECORD_FUNC">
      <orm:parameter direction="IN" name="OLDREC" database-type="PACKAGE2.EMPREC"/>
      <orm:return-parameter name="RESULT" database-type="PACKAGE2.EMPREC"/>
   </orm:named-plsql-stored-function-query>
   <orm:plsql-record name="PACKAGE2.EMPREC" compatible-type="PACKAGE2_EMPREC" java-type="package2.Emprec">
      <orm:field name="EMP_ID" database-type="NUMERIC_TYPE"/>
      <orm:field name="EMP_NAME" database-type="VARCHAR_TYPE"/>
   </orm:plsql-record>
   <orm:embeddable class="package2.Emprec" access="VIRTUAL">
      <orm:struct name="PACKAGE2_EMPREC">
         <orm:field>EMP_ID</orm:field>
         <orm:field>EMP_NAME</orm:field>
      </orm:struct>
      <orm:attributes>
         <orm:basic name="emp_id" attribute-type="java.math.BigInteger">
            <orm:column name="EMP_ID"/>
         </orm:basic>
         <orm:basic name="emp_name" attribute-type="java.lang.String">
            <orm:column name="EMP_NAME"/>
         </orm:basic>
      </orm:attributes>
   </orm:embeddable>
</orm:entity-mappings>

PL/SQL Stored Procedure With a PL/SQL Table OUT argument

// PL/SQL Package DDL
/*
CREATE OR REPLACE PACKAGE PACKAGE2 AS
    TYPE TAB1 IS TABLE OF VARCHAR2(111) INDEX BY BINARY_INTEGER;
    PROCEDURE COPYTABLE(OLDTAB IN TAB1, NEWTAB OUT TAB1);
END PACKAGE2;
 
// JDBC Shadow types for PL/SQL records
CREATE OR REPLACE TYPE PACKAGE2_TAB1 AS TABLE OF VARCHAR2(111);
*/
 
static final String DEFAULT_PKG = "metadatagen";
static final String DB_PLATFORM = "org.eclipse.persistence.platform.database.oracle.Oracle11Platform";
 
// use DatabaseTypeBuilder to generate a list of PackageTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
List dbProcedures = new ArrayList();
List<PLSQLPackageType> packages;
try {
    // process the package
    packages = dbTypeBuilder.buildPackages(conn, "SCOTT", "PACKAGE2");
} catch (ParseException e) {
    // handle parse exception
}
// we expect one package containing one stored function (and a record)
for (PLSQLPackageType pkgType : packages) {
    // now get the desired procedures/functions from the processed package
    for (ProcedureType procType : pkgType.getProcedures()) {
        if (procType.getProcedureName().equals("COPYTABLE")) {
            dbProcedures.add(procType);
        }
    }
}
 
// instantiate the JPA metadata generator
JPAMetadataGenerator gen = new JPAMetadataGenerator(DEFAULT_PKG, DB_PLATFORM);
 
// generate the XMLEntityMappings instance
XMLEntityMappings mappings = gen.generateXmlEntityMappings(dbProcedures);
 
// marshal the metadata to System.out
XMLEntityMappingsWriter writer = new XMLEntityMappingsWriter();
writer.write(mappings, System.out);
 
...

The following JPA metadata would be marshalled:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:named-plsql-stored-procedure-query name="COPYTABLE" procedure-name="PACKAGE2.COPYTABLE">
      <orm:parameter direction="IN" name="OLDTAB" database-type="PACKAGE2.TAB1"/>
      <orm:parameter direction="OUT" name="NEWTAB" database-type="PACKAGE2.TAB1"/>
   </orm:named-plsql-stored-procedure-query>
   <orm:plsql-table name="PACKAGE2.TAB1" compatible-type="PACKAGE2_TAB1" java-type="package2.Tab1" nested-type="VARCHAR_TYPE" nested-table="false"/>
   <orm:embeddable class="package2.Tab1" access="VIRTUAL">
      <orm:attributes>
         <orm:array name="items" target-class="PACKAGE2_TAB1" attribute-type="java.util.ArrayList" database-type="PACKAGE2_TAB1">
            <orm:column name="ITEMS"/>
         </orm:array>
      </orm:attributes>
   </orm:embeddable>
</orm:entity-mappings>

PL/SQL Stored Function that returns a REF CURSOR

// PL/SQL Package DDL
/*
CREATE OR REPLACE PACKAGE CURSORTYPE AS
    TYPE WEAKLY_TYPED_REF_CURSOR IS REF CURSOR;
    FUNCTION GET_EMP_FUNC(EMP_NAME VARCHAR) RETURN WEAKLY_TYPED_REF_CURSOR;
END CURSORTYPE;
*/
 
static final String DEFAULT_PKG = "metadatagen";
static final String DB_PLATFORM = "org.eclipse.persistence.platform.database.oracle.Oracle11Platform";
 
// use DatabaseTypeBuilder to generate a list of PackageTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
List dbProcedures = new ArrayList();
List<PLSQLPackageType> packages;
try {
    // process the package
    packages = dbTypeBuilder.buildPackages(conn, "SCOTT", "CURSORTYPE");
} catch (ParseException e) {
    // handle parse exception
}
// we expect one package containing one stored function (and a record)
for (PLSQLPackageType pkgType : packages) {
    // now get the desired procedures/functions from the processed package
    for (ProcedureType procType : pkgType.getProcedures()) {
        if (procType.getProcedureName().equals("GET_EMP_FUNC")) {
            dbProcedures.add(procType);
        }
    }
}
 
// instantiate the JPA metadata generator
JPAMetadataGenerator gen = new JPAMetadataGenerator(DEFAULT_PKG, DB_PLATFORM);
 
// generate the XMLEntityMappings instance
XMLEntityMappings mappings = gen.generateXmlEntityMappings(dbProcedures);
 
// marshal the metadata to System.out
XMLEntityMappingsWriter writer = new XMLEntityMappingsWriter();
writer.write(mappings, System.out);
 
...

The following JPA metadata would be marshalled:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:named-plsql-stored-function-query name="GET_EMP_FUNC" function-name="CURSORTYPE.GET_EMP_FUNC">
      <orm:parameter direction="IN" name="EMP_NAME" database-type="VARCHAR_TYPE"/>
      <orm:return-parameter direction="OUT_CURSOR" name="CURSOR" database-type="CURSORTYPE.WEAKLY_TYPED_REF_CURSOR"/>
   </orm:named-plsql-stored-function-query>
</orm:entity-mappings>

PL/SQL Record with %TYPE fields

// Table and PL/SQL Package DDL
/*
CREATE TABLE EMPTYPEX (
    EMPNO NUMERIC(4) NOT NULL,
    ENAME VARCHAR(25),
    PRIMARY KEY (EMPNO)
)
 
create or replace PACKAGE EMP_RECORD_PACKAGE AS
    type EmpRec is record (
        emp_id   EMPTYPEX.EMPNO%TYPE,
        emp_name EMPTYPEX.ENAME%TYPE
    )
    function get_emp_record (pId in number) return EmpRec;
END EMP_RECORD_PACKAGE;
*/
 
static final String DEFAULT_PKG = "metadatagen";
static final String DB_PLATFORM = "org.eclipse.persistence.platform.database.oracle.Oracle11Platform";
 
// use DatabaseTypeBuilder to generate a list of PackageTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
List<CompositeDatabaseType> dbTypes;
try {
    // process the table
    List<TableType> dbTables = dbTypeBuilder.buildTables(conn, "SCOTT", "EMPTYPEX");
 
    // process the package
    List<ProcedureType> empRecPkgProcedures = new ArrayList<ProcedureType>();
    List<PLSQLPackageType> packages = dbTypeBuilder.buildPackages(conn, "SCOTT", "EMP_RECORD_PACKAGE");
    for (PLSQLPackageType pkgType : packages) {
        // now get the desired procedures/functions from the processed package
        for (ProcedureType procType : pkgType.getProcedures()) {
            if (procType.getProcedureName().equals("get_emp_record")) {
                empRecPkgProcedures.add(procType);
            }
        }
    }
 
    // combine tables and procedures to pass to the metadata generator
    dbTypes = new ArrayList();
    dbTypes.addAll(dbTables);
    dbTypes.addAll(empRecPkgProcedures);
} catch (ParseException e) {
    // handle parse exception
}
 
// instantiate the JPA metadata generator
JPAMetadataGenerator gen = new JPAMetadataGenerator(DEFAULT_PKG, DB_PLATFORM);
 
// generate the XMLEntityMappings instance
XMLEntityMappings mappings = gen.generateXmlEntityMappings(dbTypes);
 
// marshal the metadata to System.out
XMLEntityMappingsWriter writer = new XMLEntityMappingsWriter();
writer.write(mappings, System.out);
 
...

The following JPA metadata would be marshalled:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:named-plsql-stored-function-query name="get_emp_record" function-name="EMP_RECORD_PACKAGE.get_emp_record">
      <orm:parameter direction="IN" name="pId" database-type="NUMERIC_TYPE"/>
      <orm:return-parameter name="RESULT" database-type="EMP_RECORD_PACKAGE.EmpRec"/>
   </orm:named-plsql-stored-function-query>
   <orm:plsql-record name="EMP_RECORD_PACKAGE.EmpRec" compatible-type="EMP_RECORD_PACKAGE_EmpRec" java-type="emp_record_package.Emprec">
      <orm:field name="emp_id" database-type="NUMERIC_TYPE"/>
      <orm:field name="emp_name" database-type="VARCHAR_TYPE"/>
   </orm:plsql-record>
   <orm:entity class="metadatagen.Emptypex" access="VIRTUAL">
      <orm:table name="EMPTYPEX"/>
      <orm:attributes>
         <orm:id name="empno" attribute-type="java.math.BigInteger">
            <orm:column name="EMPNO"/>
         </orm:id>
         <orm:basic name="ename" attribute-type="java.lang.String">
            <orm:column name="ENAME"/>
         </orm:basic>
      </orm:attributes>
   </orm:entity>
   <orm:embeddable class="emp_record_package.Emprec" access="VIRTUAL">
      <orm:struct name="EMP_RECORD_PACKAGE_EmpRec">
         <orm:field>emp_id</orm:field>
         <orm:field>emp_name</orm:field>
      </orm:struct>
      <orm:attributes>
         <orm:basic name="emp_id" attribute-type="java.math.BigInteger">
            <orm:column name="emp_id"/>
         </orm:basic>
         <orm:basic name="emp_name" attribute-type="java.lang.String">
            <orm:column name="emp_name"/>
         </orm:basic>
      </orm:attributes>
   </orm:embeddable>
</orm:entity-mappings>

PL/SQL Procedure with a %ROWTYPE OUT argument

// Table and PL/SQL Package DDL
/*
CREATE TABLE EMPTYPEX (
    EMPNO NUMERIC(4) NOT NULL,
    ENAME VARCHAR(25),
    PRIMARY KEY (EMPNO)
)
 
CREATE OR REPLACE PACKAGE RTYPE_PKG AS
    PROCEDURE testProc(PARAM1 IN INTEGER, PARAM2 OUT EMPTYPEX%ROWTYPE);
END RTYPE_PKG;
 
// JDBC shadow type for the PL/SQL record type generated to handle %ROWTYPE
CREATE OR REPLACE TYPE EMPTYPEX_ROWTYPE AS OBJECT (EMPNO NUMERIC(4), ENAME VARCHAR2(25));
*/
 
static final String DEFAULT_PKG = "metadatagen";
static final String DB_PLATFORM = "org.eclipse.persistence.platform.database.oracle.Oracle11Platform";
 
// use DatabaseTypeBuilder to generate a list of PackageTypes
DatabaseTypeBuilder dbTypeBuilder = new DatabaseTypeBuilder();
List<CompositeDatabaseType> dbTypes;
try {
    // process the table
    List<TableType> dbTables = dbTypeBuilder.buildTables(conn, "SCOTT", "EMPTYPEX");
 
    // process the package
    List<ProcedureType> procedures = new ArrayList<ProcedureType>();
    List<PLSQLPackageType> packages = dbTypeBuilder.buildPackages(conn, "SCOTT", "RTYPE_PACKAGE");
    for (PLSQLPackageType pkgType : packages) {
        // now get the desired procedures/functions from the processed package
        for (ProcedureType procType : pkgType.getProcedures()) {
            if (procType.getProcedureName().equals("testProc")) {
                procedures.add(procType);
            }
        }
    }
 
    // combine tables and procedures to pass to the metadata generator
    dbTypes = new ArrayList();
    dbTypes.addAll(dbTables);
    dbTypes.addAll(procedures);
} catch (ParseException e) {
    // handle parse exception
}
 
// instantiate the JPA metadata generator
JPAMetadataGenerator gen = new JPAMetadataGenerator(DEFAULT_PKG, DB_PLATFORM);
 
// generate the XMLEntityMappings instance
XMLEntityMappings mappings = gen.generateXmlEntityMappings(dbTypes);
 
// marshal the metadata to System.out
XMLEntityMappingsWriter writer = new XMLEntityMappingsWriter();
writer.write(mappings, System.out);
 
...

The following JPA metadata would be marshalled:

<?xml version="1.0" encoding="UTF-8"?>
<orm:entity-mappings xsi:schemaLocation="http://www.eclipse.org/eclipselink/xsds/persistence/orm org/eclipse/persistence/jpa/eclipselink_orm_2_5.xsd" xmlns:orm="http://www.eclipse.org/eclipselink/xsds/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <orm:named-plsql-stored-procedure-query name="testProc" procedure-name="RTYPE_PKG.testProc">
      <orm:parameter direction="IN" name="PARAM1" database-type="INTEGER_TYPE"/>
      <orm:parameter direction="OUT" name="PARAM2" database-type="EMPTYPEX%ROWTYPE"/>
   </orm:named-plsql-stored-procedure-query>
   <orm:plsql-record name="EMPTYPEX%ROWTYPE" compatible-type="EMPTYPEX_ROWTYPE" java-type="Emptypex_rowtype">
      <orm:field name="EMPNO" database-type="NUMERIC_TYPE"/>
      <orm:field name="ENAME" database-type="VARCHAR_TYPE"/>
   </orm:plsql-record>
   <orm:entity class="metadatagen.Emptypex" access="VIRTUAL">
      <orm:table name="EMPTYPEX"/>
      <orm:attributes>
         <orm:id name="empno" attribute-type="java.math.BigInteger">
            <orm:column name="EMPNO"/>
         </orm:id>
         <orm:basic name="ename" attribute-type="java.lang.String">
            <orm:column name="ENAME"/>
         </orm:basic>
      </orm:attributes>
   </orm:entity>
   <orm:embeddable class="Emptypex_rowtype" access="VIRTUAL">
      <orm:struct name="EMPTYPEX_ROWTYPE">
         <orm:field>EMPNO</orm:field>
         <orm:field>ENAME</orm:field>
      </orm:struct>
      <orm:attributes>
         <orm:basic name="empno" attribute-type="java.math.BigInteger">
            <orm:column name="EMPNO"/>
         </orm:basic>
         <orm:basic name="ename" attribute-type="java.lang.String">
            <orm:column name="ENAME"/>
         </orm:basic>
      </orm:attributes>
   </orm:embeddable>
</orm:entity-mappings>

Testing

The package for the metadata generation tests will be org.eclipse.persistence.tools.metadata.generation.test. The following cases should be covered:

  • Stored functions
  • Stored procedures
  • Object types
  • Object table types
  • Varrays
  • PL/SQL stored functions
  • PL/SQL stored procedures
  • PL/SQL records
  • PL/SQL collections
  •  %ROWTYPE
  •  %TYPE
  • Cursors
  • XMLType

JAXB Metadata Generation

JAXB metadata generation could be accomplished in a number of ways. The first option is to generate JAXB metadata (org.eclipse.persistence.jaxb.xmlmodel.XmlBindings) directly from the DDLPraser meta-model. The second option would be to generate the JAXB metadata from an XMLEntityMappings instance, keeping the JAXB and JPA metadata closely coupled. A third option would be to make use of a utility provided and used by JPA-RS; org.eclipse.persistence.jps.rs.util.metadatasources.DynamicXMLMetadataSource. This class will generate an XmlBindings instance based on a session's list of ClassDescriptors.

Open Issues

  1. Query names - what would be the default name for a query generated from a stored procedure, etc.? Perhaps the user could provide a Map of query names > procedure names. Currently, the query name is the stored proc/function name.
  2. For non-Oracle databases, JDBCHelper returns meta-model objects that extend the DDLParser meta-model - should this functionality be taken into consideration here as well?
  3. Automatic CRUD operation generation for tables; by default, DBWS generates CRUD query operations for a given database table - is this something we should include by default? Perhaps it could be configurable. RESOLVED. See Below.
  4. Relationships - there is currently nothing in place to allow the user to define relationships between tables. What information would the user need to provide in order to have entity relationships represented in the generated metadata?
  5. JAXB generation - which of the methods mentioned above is preferred? Are there other options?

Decisions

  1. Automatic CRUD operation generation for tables - a boolean will be provided (default is false, and is set via set method or constructor). If true, NamedNativeQueryMetadata for CRUD operations will be generated for each entity.