O/R Mapping, Data Access Objects and XML Persistence

1
Persistence

• O/R Mapping, Data Access Objects and XML
  Persistence

2
Topics

• O/R Mapping
• Data Access Objects
• XML Persistence

3
Mapping Java Objects to Relational Databases

• SQL Types and Java Types are different
• Databases also support SQL types differently
• Tend to define their own internal data types e.g.
  Oracles NUMBER type
• Types must be mapped between Java and
  SQL/Database.

4
Normalization

• Attempt to organize tables in analysis phase to
  eliminate data anomalies
• 1NF Disallows repeating groups in a single
  table columns are scalar
• 2NF no partial key dependencies. The Whole
  Key
• 3NF Nothing but the key
• Further refined forms exist Boyce-Codd, 4NF,
  5NF, DKNF

5
Object To Relational Mapping

• Guidelines for storing the state of Java objects
  in relational tables
• classes map to a table
• primitive attributes map to a column
• Date,Time,Timestamp,BigDecimal, and byte arrays
  are treated as primitive - they can be stored
  natively by the database

6
Converting between SQL Types and Java Types

• JDBC (Generic SQL) Types are defined in
  java.sql.Types
• java types are very rich SQL types are more
  restrictive

7
Mappings to Cover

• Simple Types
• Streaming Data Types BLOBs and CLOBs
• Relationships (1-1, 1-Many, Many-Many)
• Classes
• Weakly-Typed Data Types
• Strongly-Typed Data Types
• Inheritance

8
StandardJaws.xml

• lttype-mapping-definitiongt
• ltnamegtHypersonic SQLlt/namegt
• ltmappinggt
• ltjava-typegtjava.lang.Bytelt/java-typegt
• ltjdbc-typegtSMALLINTlt/jdbc-typegt
• ltsql-typegtSMALLINTlt/sql-typegt
• lt/mappinggt
• ltmappinggt
• ltjava-typegtjava.util.Datelt/java-typegt
• ltjdbc-typegtDATElt/jdbc-typegt
• ltsql-typegtDATElt/sql-typegt
• lt/mappinggt
• ltmappinggt
• ltjava-typegtjava.lang.Booleanlt/java-typegt
• ltjdbc-typegtBITlt/jdbc-typegt
• ltsql-typegtBITlt/sql-typegt
• lt/mappinggt
• ltmappinggt
• ltjava-typegtjava.lang.Integerlt/java-typegt

9
ltmappinggt ltjava-typegtjava.lang.Stringlt/java-
typegt ltjdbc-typegtVARCHARlt/jdbc-typegt
ltsql-typegtVARCHAR(256)lt/sql-typegt lt/mappinggt
ltmappinggt ltjava-typegtjava.sql.Timestamplt/ja
va-typegt ltjdbc-typegtTIMESTAMPlt/jdbc-typegt
ltsql-typegtTIMESTAMPlt/sql-typegt lt/mappinggt
ltmappinggt ltjava-typegtjava.lang.Floatlt/java
-typegt ltjdbc-typegtREALlt/jdbc-typegt
ltsql-typegtREALlt/sql-typegt lt/mappinggt
ltmappinggt ltjava-typegtjava.lang.Longlt/java-ty
pegt ltjdbc-typegtBIGINTlt/jdbc-typegt
ltsql-typegtBIGINTlt/sql-typegt lt/mappinggt
ltmappinggt ltjava-typegtjava.lang.Doublelt/java-
typegt ltjdbc-typegtDOUBLElt/jdbc-typegt
ltsql-typegtDOUBLElt/sql-typegt lt/mappinggt lt/type-m
apping-definitiongt
10
Java Mapping to Pointbase
From the Pointbase developers manual
http//www.pointbase.com/support/docs/pbdeveloper.
pdf
11
From the Pointbase developers manual
http//www.pointbase.com/support/docs/pbdeveloper.
pdf
12
Database Column Types
13
Traditional Class Mapping

• A class with simple types can have each attribute
  mapped to a discrete column in a database table

14
Principal Value Object Model
Note Principal has a role within a
group
15
Person Table Definition
CREATE TABLE Person ( id
VARCHAR(32) NOT NULL, first_name
VARCHAR(128), last_name
VARCHAR(128) ) ALTER TABLE Person ADD
CONSTRAINT PersonPK PRIMARY KEY(id) OR CONSTRAINT
PersonPK PRIMARY KEY(id) -- Hypersonic NOTE
Simple Example ignoring other relationships in
class diagram. Located in persistLib.
16
Save Person
public void savePerson(Connection c, Person
person) try PreparedStatement
psc.prepareStatement( "INSERT INTO
Person (id, first_name, last_name) VALUES
(?,?,?)") ps.setString(1,
person.getId()) ps.setString(2,
person.getFirstName()) ps.setString(3,
person.getLastName()) int
countps.executeUpdate()
System.out.println("Updated Records " count)
ps.close() catch(Exception
e) e.printStackTrace()
17
Restore Person
public Person getPerson(Connection c,String
personId) Person personnull try
PreparedStatement psc.prepareStatement("SELE
CT from Person WHERE id?")
ps.setString(1,personId) ResultSet
rsps.executeQuery() if(rs.next())
personnew Person()
person.setId(rs.getString("id"))
person.setLastName(rs.getString("first_name"))
person.setFirstName(rs.getString("last_name
")) rs.close()
ps.close() catch(Exception e)
e.printStackTrace() return
person
18
Classes

• Can be mapped to a user-defined type
• java.sql.Struct
• getAttributes methods
• Can define custom mapping with java.sql.SQLData
• User Defined Types (UDTs) are a SQL and JDBC
  standard. Support is still uneven among database
  vendors
• Can drastically simplify O/R mapping

19
Mapping to Database UDTs
CREATE TYPE PERSON_TY AS OBJECT ( id
VARCHAR2(32), last_name VARCHAR2(128),
first_name VARCHAR2(128) ) CREATE TABLE
PersonTable ( id VARCHAR2(32),
person PERSON_TY ) NOTE Simple Example
ignoring other relationships in class diagram
20
Code Example
public void savePerson(Connection c, Person2
person) try PreparedStatement
psc.prepareStatement( "INSERT INTO
PersonTable (id, person) VALUES (?,?)")
ps.setString(1, person.getId())
ps.setObject(2, person) int
countps.executeUpdate()
System.out.println("Updated Records " count)
ps.close() catch(Exception
e) e.printStackTrace()
21
public Person2 getPerson(Connection c,String
personId) Person2 personnull try
PreparedStatement ps
c.prepareStatement("SELECT person from
PersonTable WHERE id?")
ps.setString(1,personId) ResultSet
rsps.executeQuery() if(rs.next())
person(Person2) rs.getObject(1)
rs.close() ps.close()
catch(Exception e) e.printStackTrace()
return person
22
protected void setTypeMap(Connection c)
Map personMapnew HashMap()
personMap.put("COREUSER.PERSON_TY",
"corej2ee.examples.principal.da.Person2")
c.setTypeMap(personMap)
public static void main(String args) throws
Exception Connection cConnect.getConnecti
on() BasicPersonOracleDA danew
BasicPersonOracleDA() da.setTypeMap(c)
Person2 p1da.getPerson(c, "id1")
System.out.println("Got Person " p1)
Person2 p2new Person2("id2", "Joe", "Smith")
da.savePerson(c, p2) Person2
p3da.getPerson(c, "id2")
System.out.println("Got Person " p3)
c.close()
23
Person 2 (SQLData Implementation)
package corej2ee.examples.persist.dao import
java.sql.SQLInput import java.sql.SQLOutput impo
rt java.sql.SQLException public class Person2
implements java.sql.SQLData public Person2()
public Person2(String id, String
firstName, String lastName) id_
id firstName_ firstName lastName_
lastName
24
public String getSQLTypeName() return
"COREUSER.PERSON_TY" public void
readSQL(SQLInput inStream, String typeName)
throws SQLException setId(inStream.readS
tring()) setFirstName(inStream.readString()
) setLastName(inStream.readString())
public void writeSQL(SQLOutput outStream)
throws SQLException
outStream.writeString(getId())
outStream.writeString(getFirstName())
outStream.writeString(getLastName())
25
1-Many Relationship

• A Principal can have many named contacts

26
1-M Database Schema(Relate to 1 with Foreign
Key)
CREATE TABLE Contact ( principalId
VARCHAR(32), contactName VARCHAR(16),
street_address1 VARCHAR(128), street_address2
VARCHAR(128), city
VARCHAR(96), zip
CHAR(10), phone VARCHAR(20),
email VARCHAR(64) ) ALTER
TABLE Contact ADD CONSTRAINT FOREIGN
KEY(principalId) REFERENCES
Principal(id) ALTER TABLE Contact ADD PRIMARY
KEY(principalId, contactName)
27
Many-Many Relationship

• Consider a Person that can be in multiple Groups
  and Groups that can have multiple Persons
• Traditional technique requires creation of a
  junction table (PrincipalGroup).
• Principal to PrincipalGroup is 1-Many
• Group to PrincipalGroup is 1-Many

28
M-M relationship (Cont)
Principal
Group
Principal- Group
M
M
1
1
29
Junction Table for M-M Relationship
CREATE TABLE PrincipalGroup ( principalId
VARCHAR(32), groupId
VARCHAR(32) ) ALTER TABLE PrincipalGroup ADD
CONSTRAINT FOREIGN KEY(principalId)
REFERENCES Principal(id) ALTER TABLE
PrincipalGroup ADD CONSTRAINT FOREIGN
KEY(groupId) REFERENCES Principal(id)
30
Mapping Issues

• As the number of relationships between classes
  increases, the number of database joins required
  to retrieve an object's state becomes large - a
  potential performance problem
• O/R mapping tools like Cocobase can automate much
  of this mapping
• Can cache perform lazy instantiation of
  referenced objects
• Java-aware databases
• Enhanced getObject() to access databases that
  support direct storage of Java Objects

31
Inheritance

• Many options available
• Technique chosen should be based upon systems
  use cases
• Options
• BLOB (punt)
• Vertical Partitioning
• Horizontal Partitioning
• Unification

32
Object/RDBMS

• How do we map the following Class Model to an
  RDBMS

33
Streaming data BLOBs and CLOBs

• java.sql.CLOB/BLOB
• setObject and setBinaryStream should be usable
  according to JDBC

34
Storing the Objects as Blobs

• void save() throws SQLException, Exception
• PreparedStatement pstatement null
• try
• pstatement
• connection.prepareStatement("insert into
  accounts(id, data) values (?, ?)")
• for(int i0 iltaccounts_.length i)
• pstatement.setString(1,accounts_i.getId())
  
• try
• ByteArrayOutputStream bout new
  ByteArrayOutputStream()
• ObjectOutputStream ostream new
  ObjectOutputStream(bout)
• ostream.writeObject(accounts_i)
• ostream.close()
• ByteArrayInputStream istream new
  ByteArrayInputStream(bout.toByteArray())
• pstatement.setBinaryStream(2, istream,
  bout.size())
• //pstatement.setObject(2,accounts_i)
  - for databases supporting setObject
• pstatement.execute()
• pstatement.clearParameters()
• catch (...

35
Restoring Objects from Blobs

• void restore() throws SQLException, Exception
• Statement statement null
• ResultSet rs null
• try
• statement connection_.createStatement()
• rs statement.executeQuery("select id, data
  from accounts")
• Vector accounts new Vector()
• while (rs.next())
• String accountNo rs.getString(1)
• ObjectInputStream istream new
  ObjectInputStream(rs.getBinaryStream(2))
• Account account (Account)
  istream.readObject()
• accounts.add(account)
• accounts_ new Accountaccounts.size()
  accounts.toArray(accounts_)
• finally
• if (rs ! null) rs.close() if
  (statement ! null) statement.close()

36
Using Blobs

• Pros
• Good encapsulation of object properties
• Cons
• Example still allows for accidental object
  duplication
• Slows database performance
• can segment object into multiple tables and make
  use of lazy instantiation
• Serialization brittle in the face of software
  changes/extended time
• better use as a cache
• possible use of XML or other stable marshalling
  forms

37
Horizontal Partitioning

• Each concrete class is mapped to a table

38
Vertical Partitioning

• Each class is mapped to a table

39
Unification

• Each sub-class is mapped to the same table

40
UDT Alternative

• CREATE TYPE ACCOUNT_TY AS OBJECT (
• id NUMBER,
• ownerName VARCHAR2(128),
• balance NUMBER
• ) NOT FINAL
• CREATE TYPE INTEREST_ACCOUNT_TY UNDER ACCOUNT_TY
  (
• rate NUMBER,
• term_days NUMBER
• )
• CREATE TYPE CHECKING_ACCOUNT_TY UNDER ACCOUNT_TY
  (
• check_fee NUMBER
• )
• CREATE TABLE Account OF ACCOUNT_TY
• JDBC setObject() and getObject() can be used to
  store and retrieve any derived account type

41
RDBMS Mapping

• Horizontal Partitioning
• entire object within one table
• only one table required to activate object
• no unnecessary fields in the table
• must search over multiple tables for common
  properties
• Vertical Partitioning
• object spread across different tables
• must join several tables to activate object

• Vertical Partitioning (cont.)
• no unnecessary fields in each table
• only need to search over parent tables for common
  properties
• Unification
• entire object within one table
• only one table required to activate object
• unnecessary fields in the table
• all sub-types will be located in a search of the
  common table

42
Object-Oriented Databases

• Object databases remove the need for mapping code
  and can be more efficient when there are many
  class relationships
• Vendors
• eXcelon
• Versant
• POET

43
Object Database Management Group (ODMG)

• www.odmg.org
• Consortium of companies defining standards for
  object databases
• 2.0 widely distributed but 3.0 coming out any day
  now
• Charter being extended to define storage of
  object state in any database
• Object Databases dont have strong acceptance yet
  - if ever

44
When to use an object database ?

• When classes have many relationships and the
  object-relational mapping is too cumbersome
• When the number of joins required to retrieve
  object state becomes a performance bottleneck
  (can be minimized by de-normalizing tables)
• When reporting tools are not a focus

45
Example Code

• Database db database.open("Images",
  Database.openReadWrite)
• Transaction t transaction.begin()
• try
• Image image new Image("file.gif")
• db.bind( image, "Image" )
• image.setWidth( 1728 )
• catch(ODMGException e )
• t.commit()
• db.close()

46
The Future ?

• ODMG standard support among vendors is spotty
• Some will support full API and omit Object Query
  Language (OQL). Some only support subsets of the
  API
• Probably a niche market
• Useful for middle-tier state caching. See Javlin
  from eXcelon. www.exceloncorp.com
• Relational (with object extensions) will dominate

47
Data Access Objects(DAO)

• Isolating Persistence Code

48
Inserting Data Access Objects
Application Object
Data Access Object
Value Object
PrincipalMgr
PrincipalDAO
Principal
ContactDAO
Contact
Dependency Direction
49
Roles

• Application Objects
• Encapsulate the business rules
• Obtain connections
• Demarcate transactions
• Not Serializable
• Value Objects
• Simply carry values
• Serializable
• Data Access Objects
• Encapsulate interaction with information source
  (database)
• Designed to work with different Application
  Objects (e.g., no threads)
• Not Serializable

50
Value Object

• package corej2ee.examples.principal
• public class Person extends PrincipalBase
  implements Principal, Serializable
• private String id_, String firstName_,
  lastName_
• public Person()
• public Person(String id) id_ id
• public Person(String id, String firstName,
  String lastName)
• id_ id
• firstName_ firstName
• lastName_ lastName
• public String getId()
  return id_
• public void setId(String id)
  id_id
• public String getFirstName()
  return firstName_
• public void setFirstName(String firstName)
  firstName_ firstName
• public String getLastName()
  return lastName_
• public void setLastName(String lastName)
  lastName_ lastName
• public String getName()
  return firstName_ " " lastName_
• public String toString() //...

51
Data Access Object

• package corej2ee.examples.principal.dao
• public class PrincipalDAO
• public void insert(Connection connection,
  Principal principal)
• throws SQLException
• if (principal instanceof Person)
• insertPerson(connection,
  (Person)principal)
• else if (principal instanceof Group)
• insertGroup(connection,
  (Group)principal)
• else //...
• for(Iterator itrprincipal.getContactNames().it
  erator() itr.hasNext())
• String contactName (String)itr.next()
• Contact contact principal.getContact(co
  ntactName)
• contactDAO_.insert(connection,
• principal.getId(),
  contactName, contact)

52
Data Access Object (cont.)

• public void insertPerson(Connection connection,
  Person person)
• throws SQLException
• PreparedStatement pStatement null
• try
• String sql "INSERT INTO " tableName_
  " (id,principalType,firstName,lastName) "
• " values (?, 'P', ?, ?)"
• pStatement connection.prepareStatement(sql
  )
• pStatement.setString(1,person.getId())
• pStatement.setString(2,person.getFirstName()
  )
• pStatement.setString(3,person.getLastName())
  
• pStatement.execute()
• finally
• JDBCUtil.close(null, pStatement, null)
• pStatement null

53
Application Object

• package corej2ee.mythical.examples.principal.dao
• /
• This class represents the business logic for
  Principals.
• /
• public class PrincipalMgr
• private static PrincipalDAO principalDAO_ new
  PrincipalDAO()
• public PrincipalMgr()
• private Connection getConnection()
  
• private void closeConnection(Connection
  connection)

54
Application Object

• public void create(Principal principal) throws
  SQLException
• Connection connection null
• try
• connection getConnection()
• principalDAO_.insert(connection,
  principal)
• finally
• closeConnection(connection)

55
Application Object (cont.)

• public void remove(String id) throws
  SQLException
• Connection connection null
• try
• connection getConnection()
• principalDAO_.remove(connection, id)
• finally
• closeConnection(connection)

56
Principal Loader/DAO Example

• Start the database server
• coredev tools dbstarthsql
• Initialize the Unit
• coredev principal init
• Run the Loader
• coredev principal ingest
• Optional Clear tables to re-run
• coredev principal clean
• Optional Remove tables from database
• coredev principal realclean

57
Principal ingest Target

• From principal.xml
• lttarget name"ingest"
• description"loads people into db from
  -Dfile XML file"gt
• ltproperty name"file" value"PROJECT_HOME/
  config/people.xml"/gt
• ltjava classname"corej2ee.examples.principal.
  xml.PersonXMLLoaderMain"
• fork"true"gt
• ltclasspathgt
• ltpathelement path"JDBC_CLASSES
  "/gt
• ltpathelement path"XML_CLASSES"
  /gt
• ltpathelement path"CLIENTLIB_HOM
  E/principal.jar"/gt
• ltpathelement path"COREDEV_HOME
  /lib/coredev.jar"/gt
• ltpathelement path"COREJ2EE_HOME
  /lib/corej2ee.jar"/gt
• lt/classpathgt
• ltsysproperty key"JDBC_DRIVER"
  value"JDBC_DRIVER"/gt
• ltsysproperty key"JDBC_URL"
  value"JDBC_URL"/gt
• ltsysproperty key"JDBC_USER"
  value"JDBC_USER"/gt
• ltsysproperty key"JDBC_PASSWORD"
  value"JDBC_PASSWORD"/gt
• ltarg value"file"/gt

58
Example DAO Client PersonXMLLoader

• package corej2ee.examples.principal.xml
• import corej2ee.examples.principal.dao.PrincipalDA
  O
• import corej2ee.examples.principal.Person
• import corej2ee.examples.principal.Contact
• import corej2ee.examples.principal.Principal
• import corej2ee.xml.XMLLoaderBase
• import org.xml.sax.SAXException
• import java.sql.SQLException
• public class PersonXMLLoader extends
  XMLLoaderBase
• PrincipalDAO dao_
• Person person_
• Contact contact_
• public PersonXMLLoader() this(false)
• public PersonXMLLoader(boolean validate)
  dao_ new PrincipalDAO()

59
Example DAO Client XMLLoaderBase

• package corej2ee.xml
• import java.io.InputStream
• import java.sql.Connection
• import javax.xml.parsers.SAXParser
• import javax.xml.parsers.SAXParserFactory
• import org.xml.sax.Attributes
• import org.xml.sax.Locator
• import org.xml.sax.SAXException
• import org.xml.sax.helpers.DefaultHandler
• public abstract class XMLLoaderBase extends
  DefaultHandler
• protected SAXParserFactory spf_
• protected Connection connection_
• protected StringBuffer text_
• protected XMLLoaderBase() this(false)
• protected XMLLoaderBase(boolean validate)

60
Example DAO Client XMLLoaderBase

• public void execute(Connection connection,
  InputStream is) throws Exceptio
• connection_ connection
• SAXParser parser spf_.newSAXParser()
• try
• // connection_.setAutoCommit(fal
  se)
• parser.parse(is, this)
• // connection_.commit()
• catch (Exception ex)
• ex.printStackTrace()
• // connection_.rollback()
• throw ex

61
Example DAO Client XMLLoaderBase

• public void setDocumentLocator(Locator loc)
• public void startDocument() throws SAXException
  
• public void endDocument() throws SAXException
• public void startPrefixMapping(String prefix,
  String uri) throws SAXException
• public void endPrefixMapping(String prefix)
  throws SAXException
• public void startElement(String uri, String
  localName, String qName,
• Attributes attrs) throws SAXException
• text_ new StringBuffer()
• public abstract void endElement(String uri,
  String localName, String qName throws
  SAXException
• public void characters(char ch, int start, int
  length) throws SAXException
• text_.append(ch, start, length)

62
Example DAO Client PersonXMLLoader

• public void endElement(String uri, String
  localName, String qName)
• throws SAXException
• //base class has placed text in text_
  StringBuffer
• String text text_.toString()
• log("uri" uri ", localName" localName
  ", qName" qName ", text" text)
• if (qName.equals("id"))
• person_ new Person(text) contact_ new
  Contact()
• else if (qName.equals("firstName"))
  person_.setFirstName(text)
• else if (qName.equals("e-mail"))
  contact_.setEmail(text)
• else if (qName.equals("person"))
• try
• person_.addContact(Principal.HOME_CONTACT
  ,contact_)
• log("inserting" person_)
• dao_.insert(connection_, person_)
• catch (SQLException ex)

63
Example DAO Client Main PersonXMLLoaderMain

• package corej2ee.examples.principal.xml
• import corej2ee.xml.XMLLoaderBase
• import corej2ee.xml.XMLLoaderMainBase
• public class PersonXMLLoaderMain extends
  XMLLoaderMainBase
• / This is a factory method for the base
  class /
• protected XMLLoaderBase createLoader() throws
  Exception
• return new PersonXMLLoader()
• public static void main(String args)
• XMLLoaderMainBase app new
  PersonXMLLoaderMain()
• try
• app.execute(args)
• catch (Exception ex)
• System.err.println(ex)
• System.exit(-1)

64
Example DAO Client Main XMLLoaderMainBase

• package corej2ee.xml
• import coredev.jdbc.JDBCUtil
• import java.sql.Connection
• import java.io.FileInputStream
• import java.io.File
• public abstract class XMLLoaderMainBase
• protected abstract XMLLoaderBase
  createLoader() throws Exception
• public void execute(String args) throws
  Exception
• if (args.length ! 1) throw new
  Exception("usage getClass().getName() "ltxml
  filegt")
• File file new File(args0)
• if (!file.exists()) throw new
  Exception(file " does not exist")
• Connection connection null
• try
• FileInputStream fin new
  FileInputStream(file)
• XMLLoaderBase loader
  createLoader()
• JDBCUtil jdbcUtil new JDBCUtil()
• connection jdbcUtil.getConnection()
  
• System.out.println("connection"
  connection)

65
Java Data Objects(JDO)

• Optional Topic

66
JDO Summary

• Latest option for object persistence
• Version 1.0 Released
• Reference Implementation for Solaris
• Compliant Commercial offerings available
• Prism Technologies, Tech Trader, Forte, etc.
• Commercial and Free look-alikes also available
• TopLink, CocoBase, Castor
• Provides wrapper for
• custom/mobile storage devices
• regular SQL/JDBC data stores
• flat files
• basically storage mechanism removed form the code

67
JDO Key (javax.jdo) Interfaces/Classes

• JDO Helper
• used to load PersistenceManagerFactory
  implementation class
• PersistanceManagerFactory
• used to manage PersistenceManager objects
• PersistenceManager
• singlethreaded entry point into the persistent
  store
• PersistenceCapable
• objects managed by JDO framework must be
  PersistenceCapable
• usually done through bytecode or inheritance
  Enhancement
• (Enhancer) - vendor utility to make user classes
  PersistenceCapable
• Transaction - scope of action
• Extent - scope of query
• Query - database neutral query mechanism

68
Obtain PersistenceManagerFactory

• InputStream is Thread.currentThread().
• getContextClassLoader().
• getResourceAsStream("jdo.p
  roperties")
• Properties props new Properties()
• props.load(is)
• //get the factory
• pmf_ JDOHelper.getPersistenceManagerFactory(prop
  s)

69
Insert Element

• PersistenceManager pm pmf_.getPersistenceManager
  ()
• Transaction tx pm.currentTransaction()
• try
• tx.begin()
• pm.makePersistent(element_)
• objectId_ pm.getObjectId(element_)
• tx.commit()
• objectId_ pm.getObjectById(objectId_,
  true))
• catch (Exception ex) tx.rollback() throw ex
  
• finally pm.close()

70
Getting an Element

• PersistenceManager pm pmf_.getPersistenceManager
  ()
• Transaction tx pm.currentTransaction()
• try
• tx.begin()
• SearchElement element
• (SearchElement)pm.getObjectById(objectId_,
  true)
• tx.commit()
• pm.makeTransient(element)
• catch (Exception ex) tx.rollback() throw
  ex
• finally pm.close()

71
Update Element

• PersistenceManager pm pmf_.getPersistenceManager
  ()
• Transaction tx pm.currentTransaction()
• try
• tx.begin()
• PCSearchElement element
• (PCSearchElement)pm.getObjectById(objectId_,
  true)
• element.setTitle("stuff")
• tx.commit()
• catch (Exception ex) tx.rollback() throw
  ex
• finally pm.close()

72
JDO and J2EE

• Server-side persistence
• Use in stand-alone applications
• Use in Web tier
• Use in Session Beans
• Use in BMP Entity Beans
• Integrated with Connector Architecture in
  Enterprise Environment
• PersistenceManagerFactory located in JNDI Tree

73
XML Persistence

• Optional Topic

74
Topics

• XML Persistence Issues
• XML Storage Options
• Text Storage of XML
• XML to Relational Databases
• XML to Object-Relational
• XML-Enabled Databases
• Database knows it is XML

75
XML Difficulties

• An XML document represents an entire entity and
  its relationships
• Purchase Order with all line items
• Purchaser information
• Etc.
• Relational Databases normalize relationships to
  multiple tables
• Impedance mismatch must be kept in mind when
  selecting an XML persistence approach

76
Sample XML Document
ltPURCHASEgt ltITEM name"bike" quantity"1"
unitCost"120.00" /gt ltITEM name"helmet"
quantity"2" unitCost"20.00" /gt ltPURCHASERgt
ltnamegtDanlt/namegt ltaddressgtPasadena,
MDlt/addressgt lt/PURCHASERgt lt/PURCHASEgt
77
Storing XML as a CLOB

• Treat XML document as relatively opaque
• See program CLOB in persistLib
• Works well when you just need a place to put the
  document and retrieve it later

78
CLOB Tradeoffs
Complexity Performance Functionality
Query Simple Good Very Limited
Insert Simple Good Good
Update Moderate Poor Have to replace entire document to update node
Document Re-Assembly Simple Good Just return CLOB field
79
Storing XML as Text

• XML is (normally) character data
• Can be stored easily in text databases
• Inverted indexes built for tokenized words
• Free Text Queries

80
CLOB with Text Index Tradeoffs
Complexity Performance Functionality
Query Simple Good Moderate. CONTAINS
Insert Moderate. Index updates, fragmentation Good Good
Update Moderate Poor Have to replace entire document to update node
Document Re-Assembly Simple Good Just return CLOB field
81
Storing XML as Sectioned Text

• Text Indexing Engine can identify Sections and
  Ranges in the document
• Allows us to query more precisely
• CONTAINS(xml, Dan WITHIN Name)
• See CLOB with XML Sectioning in persistLib

82
Sectioning Tradeoffs
Complexity Performance Functionality
Query Simple Good Good. CONTAINS, WITHIN
Insert Moderate. Index updates, fragmentation Good Good
Update Moderate Poor Have to replace entire document to update node
Document Re-Assembly Simple Good Just return CLOB field
83
Hybrid Approach

• Store both text and relational data
• Keep document in indexed CLOB
• Pull key fields from document and store in
  dedicated columns
• SQL Queries and CONTAINS/WITHIN
• SQL Queries usually faster
• Often a very good compromise
• See Hybrid Approach in persistLib

84
Hybrid Tradeoffs
Complexity Performance Functionality
Query Simple Very Good Very Good. SQL and Text Queries
Insert Moderate. Index updates, fragmentation. Shredding Still Good. Can degrade if lots of fields are extracted Good
Update Moderate-Difficult Poor Have to replace entire document to update node
Document Re-Assembly Simple Good Just return CLOB field
85
Storing XML in Normalized Relational Databases

• Hierarchical nature of XML causes difficulties
• Have to Shred the document to split it out to
  multiple relational tables
• Document reconstruction
• Retrieval most important
• Keep original document around if updates dont
  occur (so no joins required to return entire
  document)
• Insertion most important
• Form the document when query takes place. (Slow
  because of joins)
• SQL-enabled programs work well. Data Mining,
  Reporting Tools, etc.

86
ER Diagram
87
Normalized Table Tradeoffs
Complexity Performance Functionality
Query Simple Excellent Very Good. SQL and Text Queries
Insert Complex Can be poor N/A
Update Moderate Excellent Individual nodes can be updated
Document Re-Assembly Simple if document is maintained in a CLOB. Difficult otherwise Can be poor-performing if document re-assembly required on the fly May have to join
88
Assembling Document On The Fly

• ResultSetMetadata has enough information to
  create basic document automatically
• Commonly subjected to XSLT transform
• Oracle XSU libraries
• See code snapshot next page
• If performance is important
• Keep flag regarding dirty status of XML
  document in CLOB field
• Mark dirty if relational tables are modified

89
XSU
OracleXMLQuery qrynew OracleXMLQuery(connection,
resultSet) qry.setRowTag(ORDER) qry.setRowsetT
ag(ORDERS) String xmlqry.getXMLString()
90
XML-Enabled Databases

• Database knows the text being stored is XML
• Stores and retrieves documents
• Does the mapping for you
• Hierarchical XML Databases
• XML in Object Oriented Databases
• XML Document in Text Databases

91
Hierarchical XML Storage

• Tamino
• http//www.softwareag.com/tamino/
• Potential for improved performance
• Nodes stored close together on disk
• Fewer disk operations required
• Queried with XQL

92
Object Oriented Database

• Stores XML Document Nodes as Objects in an OO
  Database
• eXcelon Corp
• eXtensible Information Server (XIS)
• http//www.exln.com/products/index.shtml

93
XML in Text Database

• XML Layer over text database
• Oracles XMLType
• Functionality layer over CLOB
• Native Database Type

94
Creating columns of XMLType

• CREATE TABLE docs(
• doc_id NUMBER(3),
• document SYS.XMLTYPE,
• author VARCHAR2(35),
• size NUMBER(4))

95
Inserting into XMLType Column

• INSERT into docs (
• doc_id, document, author,size) VALUES (
• 1001, sys.XMLType.createXML(ltDOCgtltSOMETAGgtte
  xtlt/SOMETAGgtlt/DOCgt), Dan, 256))
• Node-wise update of xml document is not currently
  supported

96
Query (Larger Example)

• select e.poDoc.extract('/PO/PNAME/text()').getStri
  ngVal() PNAME from po_xml_table
• where e.poDoc.existsNode('/PO/SHIPADDR') 1 and
  e.poDoc.extract('//PONO/text()').getNumberVal()
  300 and e.poDoc.extract('//_at_CUSTNAME').getStringVa
  l() like 'John'

97
Summary

• There are many options for persisting XML
• Method selected should be based upon
• System Use Cases
• Staff Experience
• Integration Requirements with other Software
  packages
• Data Mining
• Report Tools

98
References

• http//www.rpbourret.com/xml/XMLDatabaseProds.htm
• http//www.rpbourret.com/xml/XMLAndDatabases.htm