oracle type to java beans with jaxb

1. create oracle types
2. oracle type to xsd
3. xsd to java beans
4. oracle type to java object with jaxb

1. create oracle types

CREATE OR REPLACE TYPE qwerty_element_t AS OBJECT
(str VARCHAR2(10)
,num number
,str_big clob
,dt date
);
/
CREATE OR REPLACE TYPE qwerty_tt AS TABLE OF qwerty_element_t;
/
CREATE OR REPLACE TYPE qwerty_t AS OBJECT
(id VARCHAR2(1000)
,element qwerty_element_t
,elements qwerty_tt
);
/
create or replace
function getQwertyAsXml(p_id varchar2) return clob as
       v_qwerty        qwerty_t;
       v_xml           XMLTYPE;
begin
       -- init object instance
       v_qwerty := ...;
       --convert object to xml
       v_xml := XMLTYPE (v_qwerty);
       -- xml to clob
       return v_xml.getClobVal();
end;
/

2. oracle type to xsd

OracleType2XSD.groovy

class OracleType2XSD {
 static String clob2String(java.sql.Clob clob) {/*...*/}
 static Sql getSql() {/*...*/}
 static String oracleType2XSD(Sql sql, String oracleType, String rootTag) {
  String res
  sql.call("""{ ${Sql.CLOB} = call dbms_xmlschema.generateSchema(
   user,
   ${Sql.VARCHAR(oracleType)},
   ${Sql.VARCHAR(rootTag)},
   TRUE,FALSE,TRUE
   ).getClobVal() }""") { xsd ->
    res = clob2String(xsd)
  }
  res
 }
 static void main(args) {
  new File('qwerty.xsd').withWriter { 
    it << oracleType2XSD(getSql(), 'QWERTY_T', 'qwerty') 
  }
 }
}

execute

set CLASSPATH=...
java -cp %CLASSPATH% groovy.lang.GroovyShell OracleType2XSD.groovy

product qwerty.xsd

<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb"
  xsi:schemaLocation="http://xmlns.oracle.com/xdb http://xmlns.oracle.com/xdb/XDBSchema.xsd">
  <xsd:element name="qwerty" type="QWERTY_TType" />
  <xsd:complexType name="QWERTY_TType">
    <xsd:sequence>
      <xsd:element name="ID">
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:maxLength value="1000" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:element>
      <xsd:element name="INNER" type="QWERTY_INNER_TType" />
    </xsd:sequence>
  </xsd:complexType>
  <xsd:complexType name="QWERTY_INNER_TType">
    <xsd:sequence>
      <xsd:element name="STR">
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:maxLength value="10" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:element>
      <xsd:element name="NUM" type="xsd:double" />
      <xsd:element name="STR_BIG" type="xsd:string" />
      <xsd:element name="DT" type="xsd:date" />
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

3. xsd to java beans

execute

%JAVA_HOME%/bin/xjc qwerty.xsd -p ru.olimpiks.oracle.model

product java beans:

ru.olimpiks.oracle.model.QWERTYELEMENTTType.java

@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "QWERTY_ELEMENT_TType", propOrder = {
   "str",
   "num",
   "strbig",
   "dt"
})
public class QWERTYELEMENTTType {
   @XmlElement(name = "STR", required = true)
   protected String str;
   @XmlElement(name = "NUM")
   protected double num;
   @XmlElement(name = "STR_BIG", required = true)
   protected String strbig;
   @XmlElement(name = "DT", required = true)
   @XmlSchemaType(name = "date")
   protected XMLGregorianCalendar dt;
       //...setters and getters
}

ru.olimpiks.oracle.model.QWERTYTType.java

@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "QWERTY_TType", propOrder = {
   "id",
   "element",
   "elements"
})
@XmlRootElement(name="qwerty")//!!!to further correct unmarshall object, you must add this line
public class QWERTYTType {
   @XmlElement(name = "ID", required = true)
   protected String id;
   @XmlElement(name = "ELEMENT", required = true)
   protected QWERTYELEMENTTType element;
   @XmlElement(name = "ELEMENTS")
   protected List<QWERTYELEMENTTType> elements;
       //...setters and getters
}

4. oracle type to java object with jaxb (groovy example)

static QWERTYTType getQwerty(Sql sql, String id) {
 String res
 sql.call("""{ ${Sql.CLOB} = call getQwertyAsXml(${Sql.VARCHAR(id)}) }""") { p_res ->
  res = clob2String(p_res)
 }
 JAXBContext context = JAXBContext.newInstance(QWERTYTType.class);
 Unmarshaller u = context.createUnmarshaller();
 return (QWERTYTType)u.unmarshal(new java.io.StringReader(res));        
}