Java API for Object-Relational Modelling (ORM) with rdbCore

1. IRMIS rdbCore Java API Object-Relational Modelling (ORM)

2. IRMIS rdbCore Java API • What is rdbCore? • Database schema definition (ddl) • Static for now, but possible to generate for multiple db • Process variable crawler • Java database access layer (also PHP) • Source code tree overview irmis/ apps/ -> pv viewer, component viewer/editor, etc. rdbCore/ -> rdbCore.jar ddl/ crawler/perl/ db/ php/ java/ gov/anl/aps/irmis/persistence/pv

3. IRMIS rdbCore Java API • Object-Relational Mapping (ORM) • Bridges gap between relational data model and object-oriented data model of applications • This “gap” responsible for a significant amount of development and maintenance hours • ORM allows focus on application and data, not transforming between the two models • Java ORM <==> Hibernate (Gavin King) • LGPL license • Development began in 2001 • Joined jboss.org in 2003, so commercial support and training available • Very popular, mature, with active support forums • Excellent book “Hibernate in Action” (2005 - Bauer, King)

4. IRMIS rdbCore Java API • Data Objects • IOC Record • IOCBoot RecordType • IOCResource Field • URI FieldType • Data Access Objects • IOCBootDAO • IOCBoot findByIocName(String iocName) • List findCurrentLoads() • RecordDAO • setRecordTypeConstraint(List recTypes) • setRecordNameGlobConstraint(String recNamePattern) • List findByConstraints()

5. IRMIS rdbCore Java API • Code example // retrieve set of current ioc boot instances, and all their process variables IOCBootDAO ibDAO = new IOCBootDAO(); List bootList = null; Try { bootList = ibDAO.findCurrentLoads(); } catch (DAOException de) { // handle exception } // iterate over the results Iterator bootIt = bootList.iterator(); while (bootIt.hasNext()) { IOCBoot iocBoot = (IOCBoot)bootIt.next(); String iocName = iocBoot.getIoc().getIocName(); List resources = iocBoot.getIocResources(); // eager fetch, so data already here List records = iocBoot.getRecords(); // lazy, so addtl. query issued here }

6. IRMIS rdbCore Java API • How is this done with Hibernate? • Design the object model and relational schema (what order?) • Depends on whether rdbms schema came first or not • APS designed rdbms schema with application in mind, so our object model entities are almost 1-to-1 with relational entities (hibernate still a big plus). • Hibernate can auto-generate class source code and/or ddl if desired • APS manually created classes and ddl (for now)

7. IRMIS rdbCore Java API • Hibernate revolves around Session class hibernate.cfg.xml Record.hbm.xml Field.hbm.xml Session session = sessionFactory.openSession() IOCBoot.hbm.xml hibernate.cfg.xml <hibernate-configuration> <session-factory> <property name=“connection.*” value=“blah”/> <property name=“dialect” value=“net.sf.hibernate.dialect.MySQLDialect” /> <mapping resource=“gov/anl/aps/irmis/persistence/pv/Record.hbm.xml” /> <mapping resource=“gov/anl/aps.irmis/persistence/pv/Field.hbm.xml” /> <mapping resource=“gov/anl/aps/irmis/persistence/pv/IOCBoot.hbm.xml” /> </session-factory> </hibernate-configuration>

8. IRMIS rdbCore Java API • Partial Record.hbm.xml mapping <hibernate-mapping> <class name=“gov.anl.aps.irmis.persistence.pv.Record” table=“rec” lazy=“true” > <id name=“id” column=“rec_id” type=“long” > <generator class=“native”/> </id> <property name=“recordName” type=“string” column=“rec_nm” /> <set name=“fields” inverse=“true” lazy=“true” > <key column=“rec_id” /> <one-to-many class=“gov.anl.aps.irmis.persistence.pv.Field” /> </set> <many-to-one name=“recordType” column=“rec_type_id” class=“gov.anl.aps.irmis.persistence.pv.RecordType” /> </class> </hibernate-mapping>

9. IRMIS rdbCore Java API • Record class (Record.java) Public class Record implements Serializable { private Long id; private String recordName; private Set fields = new HashSet(); private RecordType recordType; // constructor and accessor methods public Record() {} public Long getId() { return this.id; } public void setId(Long id) { this.id = id; } public Set getFields() { return this.fields; } public void setFields(Set fields) { this.fields = fields; } public void addField(Field field) { field.setRecord(this); fields.add(field); } public boolean equals (Object o) { if (this == other) return true; if (!(other instanceof Record)) return false; final Record castO = (Record)o; return this.getIocBoot().getId()==castO .getIocBoot().getId() && this.getRecordName() ==castO.getRecordName(); } public int hashCode() { int result = HashCodeUtil.SEED; result = HashCodeUtil.hash(result, getIocBoot().getId()); result = HashCodeUtil.hash(result, getRecordName()); return result; } }

10. IRMIS rdbCore Java API • Querying for objects using HQL (Hibernate Query Language) • What is HQL? • minimal OO extension to SQL select • Why? • Query in terms of objects you are interested in • Db independence since Hibernate generates SQL for your db • Optimization - hibernate generally makes optimal SQL List records = session.find(“select from Record r where r.recordName like ‘ABC%’”); List records = session.find(“from Record r join fetch r.fields”); IOCBoot boot = session.find(“from IOCBoot ib where ib.ioc.iocName = ‘iocpar01’”); - OR - if you absolutely have to use native SQL and JDBC Connection con = session.connection(); // any JDBC stuff can be done here

11. IRMIS rdbCore Java API • Saving or updating new objects Record rec = new Record(); rec.setRecordName(“AB:CD:ai”); // set remaining fields except id Transaction tx = session.beginTransaction(); session.saveOrUpdate(rec); tx.commit();

12. IRMIS rdbCore Java API • Hibernate experience • It has a learning curve (2 weeks ?) • Very active support forums where core developers lurk and answer questions within hours • Extremely flexible • Only a small corner of mapping possibilities shown • Will work with schemas using natural keys • Objects can be mapped to one or many tables, either through composition or association • Easy to tweak mappings and watch behavior of SQL (comparing query time using lazy versus eager fetch) • Good log output (uses commons-logging and log4j)