Difference between revisions of "IBatis Presentation"
From GMOD
m (→Acknowledgements) |
m (→Support) |
||
Line 352: | Line 352: | ||
* In GMOD used by | * In GMOD used by | ||
− | ** Xenbase, Artemis at Sanger | + | ** Xenbase, [[Artemis]] at Sanger |
* Many other users | * Many other users | ||
** e.g. MySpace.com | ** e.g. MySpace.com | ||
Line 358: | Line 358: | ||
** www.ibatis.apache.org | ** www.ibatis.apache.org | ||
* Active community | * Active community | ||
− | |||
=====What iBatis Does Well===== | =====What iBatis Does Well===== |
Revision as of 19:21, 28 September 2009
Jeff Bowes, Xenbase, University of Calgary. This Wiki section is an edited version of Jeff's presentation.
Contents
- 1 ibatis
- 2 Abator
- 3 Abator Example
- 4 Abator Example
- 5 Abator Example
- 6 Abator
- 7 DAO Methods
- 8 Insert
- 9 Insert
- 10 Insert
- 11 Insert
- 12 Insert
- 13 Problem 1 - Insert
- 14 Problem 1 - Insert
- 15 Transactions
- 16 Retrieval
- 17 Problem 2 - Master Detail Reports
- 18 Problem 2 - Master Detail Report
- 19 Master Detail Report
- 20 Dynamic Queries
- 21 Dynamic Queries
- 22 Dynamic Queries
- 23 Miscellaneous Features
- 24 Support
- 25 What iBatis Does Well
- 26 Acknowledgements
ibatis
- iBatis
- Light-weight framework
- Still based on SQL but eliminates the repetitive drudgery of JDBC
- You can tune a query by re-writing the SQL in XML & the API does not change.
- iBatis does not create your database in memory as objects
- Shallow learning curve
- Manually create a Java class and SQL map to describe higher-level objects
- Example: Gene
- Support for inheritance
- Inheritance in result maps, allows fair amount of re-use.
- Supports different transaction schemes
- For example, JDBC, Java Transaction API
Abator
- Generates ibatis CRUD objects by introspecting database tables
- Abator creates SQL in XML files (SQL Map files) and Java classes
- Within these files is a Result Map section.
- Abator config files are simple, set connection parameters, tell where the files are.
- In the SQL Map files you can specify how to find parent ids, such asfeature_id.
Abator Example
<xml>
<abatorConfiguration> <abatorContext> <jdbcConnection driverClass="COM.ibm.db2.jdbc.app.DB2Driver" connectionURL="jdbc:db2:XBDV05" userId="db2inst1" password=“*******"> <classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.zip" /> </jdbcConnection>
<javaModelGenerator targetPackage="org.gmod.architecture.framwork.bakeoff.abator.model" targetProject="gene" /> <sqlMapGenerator targetPackage="org.gmod.architecture.framwork.bakeoff.abator.sql" targetProject="gene" /> <daoGenerator type="IBATIS" targetPackage="org.gmod.architecture.framwork.bakeoff.abator.dao" targetProject="gene" /> <abatorConfiguration>
</xml>
Abator Example
<xml>
<abatorConfiguration> <abatorContext> <jdbcConnection driverClass="COM.ibm.db2.jdbc.app.DB2Driver" connectionURL="jdbc:db2:XBDV05" userId="db2inst1" password=“*******"> <classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.zip" /> </jdbcConnection>
<javaModelGenerator targetPackage="org.gmod.architecture.framwork.bakeoff.abator.model" targetProject="gene" /> <sqlMapGenerator targetPackage="org.gmod.architecture.framwork.bakeoff.abator.sql" targetProject="gene" /> <daoGenerator type="IBATIS" targetPackage="org.gmod.architecture.framwork.bakeoff.abator.dao" targetProject="gene" /> <abatorConfiguration>
</xml>
Abator Example
<table schema="db2inst1" tableName="synonym">
<generatedKey column="synonym_id" sqlStatement="VALUES PREVVAL FOR synonym_seq" identity="true" /> <columnOverride column="CREATED_BY" jdbcType="INTEGER" /> <columnOverride column="MODIFIED_BY" jdbcType="INTEGER" />
</table>
Abator
Works as:
- Eclipse plug-in
- ANT
- Standalone
DAO Methods
- Insert (Feature)
- Update (Feature)
- DeletebyKey (FeatureKey)
- SelectbyKey (FeatureKey)
- SelectbyExample (FeatureExample)
- DeletebyExample (FeatureExample)
Insert
<xml>
<insert id="abatorgenerated_insert" parameterClass= "org.gmod.architecture.framwork.bakeoff.abator.model.FeatureWithBLOBs"> insert into db2inst1.feature (DBXREF_ID, ORGANISM_ID, NAME, UNIQUENAME, RESIDUES, SEQLEN, MD5CHECKSUM, TYPE_ID, IS_ANALYSIS, IS_OBSOLETE, CREATED_BY) values (#dbxrefId:INTEGER#, #organismId:INTEGER#, #name:VARCHAR#, #uniquename:VARCHAR#, #residues:CLOB#, #seqlen:INTEGER#, #md5checksum:CHAR#, #typeId:INTEGER#, #isAnalysis:SMALLINT#, #isObsolete:SMALLINT#, #createdBy:INTEGER#)
<selectKey resultClass="java.lang.Integer" keyProperty="featureId"> VALUES PREVVAL FOR feature_seq </selectKey> </insert>
</xml>
Insert
<xml>
<insert id="abatorgenerated_insert" parameterClass= "org.gmod.architecture.framwork.bakeoff.abator.model.FeatureWithBLOBs"> insert into db2inst1.feature (DBXREF_ID, ORGANISM_ID, NAME, UNIQUENAME, RESIDUES, SEQLEN, MD5CHECKSUM, TYPE_ID, IS_ANALYSIS, IS_OBSOLETE, CREATED_BY) values (#dbxrefId:INTEGER#, #organismId:INTEGER#, #name:VARCHAR#, #uniquename:VARCHAR#, #residues:CLOB#, #seqlen:INTEGER#, #md5checksum:CHAR#, #typeId:INTEGER#, #isAnalysis:SMALLINT#, #isObsolete:SMALLINT#, #createdBy:INTEGER#)
<selectKey resultClass="java.lang.Integer" keyProperty="featureId"> VALUES PREVVAL FOR feature_seq </selectKey> </insert>
</xml>
Insert
<xml>
<insert id="abatorgenerated_insert" parameterClass= "org.gmod.architecture.framwork.bakeoff.abator.model.FeatureWithBLOBs"> insert into db2inst1.feature (DBXREF_ID, ORGANISM_ID, NAME, UNIQUENAME, RESIDUES, SEQLEN, MD5CHECKSUM, TYPE_ID, IS_ANALYSIS, IS_OBSOLETE, CREATED_BY) values (#dbxrefId:INTEGER#, #organismId:INTEGER#, #name:VARCHAR#, #uniquename:VARCHAR#, #residues:CLOB#, #seqlen:INTEGER#, #md5checksum:CHAR#, #typeId:INTEGER#, #isAnalysis:SMALLINT#, #isObsolete:SMALLINT#, #createdBy:INTEGER#)
<selectKey resultClass="java.lang.Integer" keyProperty="featureId"> VALUES PREVVAL FOR feature_seq </selectKey> </insert>
</xml>
Insert
<xml>
<selectKey resultClass="java.lang.Integer" keyProperty="featureId"> VALUES PREVVAL FOR feature_seq </selectKey>
</xml>
Insert
<xml>
<selectKey resultClass="java.lang.Integer" keyProperty="featureId"> VALUES PREVVAL FOR feature_seq </selectKey>
</xml>
Problem 1 - Insert
<java>
try { sqlMap.startTransaction(); pGene.id =featureDAO.insert(pGene.getFeatureWithBLOBs()); featurepropDAO.insert(pGene.getPropertyDescription()); pGene.featurelocId = featurelocDAO.insert(pGene .getFeaturelocWithBLOBS()); pGene = insertExons(pGene); insertSynonyms(pGene); sqlMap.commitTransaction(); } catch (Exception e) { System.out.println(e); throw (e); } finally { sqlMap.endTransaction(); }
</java>
Problem 1 - Insert
<java>
try { sqlMap.startTransaction(); pGene.id =featureDAO.insert(pGene.getFeatureWithBLOBs()); featurepropDAO.insert(pGene.getPropertyDescription()); pGene.featurelocId = featurelocDAO.insert(pGene .getFeaturelocWithBLOBS()); pGene = insertExons(pGene); insertSynonyms(pGene); sqlMap.commitTransaction(); } catch (Exception e) { System.out.println(e); throw (e); } finally { sqlMap.endTransaction(); }
</java>
Transactions
- SQLMap
- JDBC
- JTA - Java Transaction API
- 2-Phase commit
- Hibernate
- External (Customized)
Retrieval
symbol: xfile description: A test gene for GMOD meeting mRNA Feature exon_1: start: 13691 end: 13767 strand: 1 srcFeature_id: Id of genomic sample exon_2: start: 14687 end: 14720 strand: 1 srcFeature_id: Id of genomic sample
Problem 2 - Master Detail Reports
Account for cycles or recursion in Master Detail Report.
<xml>
<resultMap id="SelectGeneResults" class="org.gmod.architecture.framwork.bakeoff.Gene" groupBy="id"> <result column="FEATURE_ID" property="id" jdbcType="INTEGER"/> <result column="GENE_NAME" property="name" jdbcType="VARCHAR" /> <result column="DESCRIPTION" property="description“ jdbcType="VARCHAR" /> <result column="TYPE_ID" property="typeId" jdbcType="INTEGER" /> <result property="exons" resultMap = "gene.SelectExonResults"/> </resultMap>
<resultMap id="SelectExonResults" class="org.gmod.architecture.framwork.bakeoff.Exon"> <result column="EXON_ID" property="id" jdbcType="INTEGER"/> <result column="EXON_NAME" property="name" jdbcType="VARCHAR" /> <result column="EXON_RESIDUES" property="residues" jdbcType="CLOB" /> <result column="STRAND" property="strand" jdbcType="INTEGER" /> <result column="FMIN" property="fmin" jdbcType="INTEGER" /> <result column="FMAX" property="fmax" jdbcType="INTEGER" /> <result column="SRCFEATURE_ID" property="sourceFeatureId" jdbcType="INTEGER" /> </resultMap>
</xml>
Problem 2 - Master Detail Report
<xml>
<resultMap id="SelectGeneResults" class="org.gmod.architecture.framwork.bakeoff.Gene" groupBy="id"> <result column="FEATURE_ID" property="id" jdbcType="INTEGER"/> <result column="GENE_NAME" property="name" jdbcType="VARCHAR" /> <result column="DESCRIPTION" property="description“ jdbcType="VARCHAR" /> <result column="TYPE_ID" property="typeId" jdbcType="INTEGER" /> <result property="exons" resultMap = "gene.SelectExonResults"/> </resultMap>
<resultMap id="SelectExonResults" class="org.gmod.architecture.framwork.bakeoff.Exon"> <result column="EXON_ID" property="id" jdbcType="INTEGER"/> <result column="EXON_NAME" property="name" jdbcType="VARCHAR" /> <result column="EXON_RESIDUES" property="residues" jdbcType="CLOB" /> <result column="STRAND" property="strand" jdbcType="INTEGER" /> <result column="FMIN" property="fmin" jdbcType="INTEGER" /> <result column="FMAX" property="fmax" jdbcType="INTEGER" /> <result column="SRCFEATURE_ID" property="sourceFeatureId" jdbcType="INTEGER" /> </resultMap>
</xml>
Master Detail Report
gene_id Symbol Type Fmin Fmax 6129482 x-files gene 13691 13767 6129482 x-files gene 14687 14720
Becomes:
gene_id Symbol Type Fmin Fmax 6129482 x-files gene 13691 13767 14687 14720
Dynamic Queries
- Gene Name (Description)
- Feature, Featureprop
- Symbol
- Feature
- Feature Synonyms
- Feature, Feature_Synonym, Synonym
- Ortholog Synonyms
- Feature, Feature_relationship, Feature, Feature Synonyms
Dynamic Queries
FROM CAT_X_GENE_V gc <isEqual prepend=",property="searchSymbol" compareValue="true"> GENE_SYMBOLS s </isEqual>
<isEqual prepend="," property="searchNcbi" compareValue="true"> NCBI_GI n </isEqual>
Dynamic Queries
<dynamic prepend="WHERE"> <isEqual prepend="AND" property="searchNameOnly“ compareValue="true"> <iterate property="searchTokens" conjunction="AND" open=" (" close=") "> LOWER(VARCHAR(gc.longname)) LIKE LOWER(CAST(#searchTokens[]:VARCHAR# AS VARCHAR(512))) </iterate> </isEqual>
Iterate very useful for multiple search terms
Miscellaneous Features
- Supports various data sources
- Simple JDBC
- DBCP – Apache Connection Pooling
- JNDI – Java Naming Directory Interface
- Very flexible
- Local caching of results
- Lazy loading
Support
- In GMOD used by
- Xenbase, Artemis at Sanger
- Many other users
- e.g. MySpace.com
- Top level Apache Project
- www.ibatis.apache.org
- Active community
What iBatis Does Well
- Does not hide SQL
- No new query language to learn
- Separates and groups SQL
- Simple!!
- Light wrapper - No real tweaks
- Does the job well
- Excellent support for Master-Detail
- Dynamically generated queries
- You can structure conditions around clauses in SQL
- One XML statement can represent many variations on a query
Acknowledgements
GMOD
- Eric Just
- Everyone else
Ibatis Developers
- Kevin Snyder,
- Chris Jarabek,
- Ross Gibb
PI
- Peter Vize
Financial Support
- Alberta Heritage Foundation for Medical Research
- Alberta Network for Proteomics Innovation
- University of Calgary, Faculty of Science
- University of Calgary Dept. of Computer Science
- NICHD