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

From Eclipsepedia

Jump to: navigation, search
(Key DDL to JPA meta-model mappings)
(Key DDL to JPA meta-model mappings)
Line 176: Line 176:
 
This section will provide a high-level outline of what JPA meta-model objects are generated from the key DDL Parser meta-model objects.
 
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 =====
 
===== TableType =====
* Maps to an Entity
+
* Maps to an EntityAccessor
 
===== ProcedureType =====
 
===== ProcedureType =====
 
* Maps to a named-query, i.e. one of:
 
* Maps to a named-query, i.e. one of:
Line 184: Line 184:
 
** NamedPLSQLStoredFunctionQueryMetadata
 
** NamedPLSQLStoredFunctionQueryMetadata
 
===== Composite types  =====
 
===== Composite types  =====
* Map to Embeddables
+
* Map to an EmbeddableAccessor
 
* Examples of composites:  VarrayType, PLSQLRecordType, etc.
 
* 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):
 
* In addition, metadata objects will be generated for each record, table, object and array (each references its associated Embeddable):
Line 192: Line 192:
 
** OracleArrayTypeMetadata
 
** OracleArrayTypeMetadata
 
===== Nested composites =====
 
===== Nested composites =====
* map to Embeddeds
+
* Map to an EmbeddedAccessor
 
* An example of a nested composite would be a PL/SQL record with a PL/SQL record field
 
* An example of a nested composite would be a PL/SQL record with a PL/SQL record field
 
===== %ROWTYPE =====
 
===== %ROWTYPE =====

Revision as of 14:12, 22 July 2013

Contents

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.

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

// 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.dbws.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

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>

JAXB Metadata Generation

  • TBD

Open Issues

  1. What should the default package name for generated Entities (or anything not in a PL/SQL package) be? Ideally the user would be permitted to set this on the generator.
  2. 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.
  3. 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?
  4. 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.
  5. Relationships - there is currently nothing in place to allow the user to define relationships between tables.
  6. Embeddable generation for PL/SQL and Advanced JDBC types - DBWS will generate an Embeddable for PL/SQL Records and Collections, Varrays, Objects, etc. so there are EclipseLink descriptors for these at runtime. There would typically be no Entity referencing the Embeddable, so would this generation make sense outside of DBWS?