Difference between revisions of "IBatis Presentation"
From GMOD
m (→Support) |
m |
||
(2 intermediate revisions by the same user not shown) | |||
Line 22: | Line 22: | ||
** Within these files is a Result Map section. | ** Within these files is a Result Map section. | ||
* Abator config files are simple, set connection parameters, tell where the files are. | * 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 | + | * In the SQL Map files you can specify how to find parent ids, such as feature_id. |
=====Abator Example===== | =====Abator Example===== | ||
− | <xml> | + | |
+ | <syntaxhighlight lang="xml"> | ||
<abatorConfiguration> | <abatorConfiguration> | ||
<abatorContext> <!-- TODO: Add Database Connection Information --> | <abatorContext> <!-- TODO: Add Database Connection Information --> | ||
Line 34: | Line 35: | ||
<classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.zip" /> | <classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.zip" /> | ||
</jdbcConnection> | </jdbcConnection> | ||
− | + | ||
<javaModelGenerator | <javaModelGenerator | ||
targetPackage="org.gmod.architecture.framwork.bakeoff.abator.model" | targetPackage="org.gmod.architecture.framwork.bakeoff.abator.model" | ||
Line 45: | Line 46: | ||
targetProject="gene" /> | targetProject="gene" /> | ||
<abatorConfiguration> | <abatorConfiguration> | ||
− | </ | + | </syntaxhighlight> |
=====Abator Example===== | =====Abator Example===== | ||
− | <xml> | + | |
+ | <syntaxhighlight lang="xml"> | ||
<abatorConfiguration> | <abatorConfiguration> | ||
<abatorContext> <!-- TODO: Add Database Connection Information --> | <abatorContext> <!-- TODO: Add Database Connection Information --> | ||
Line 57: | Line 59: | ||
<classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.zip" /> | <classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.zip" /> | ||
</jdbcConnection> | </jdbcConnection> | ||
− | + | ||
<javaModelGenerator | <javaModelGenerator | ||
targetPackage="org.gmod.architecture.framwork.bakeoff.abator.model" | targetPackage="org.gmod.architecture.framwork.bakeoff.abator.model" | ||
Line 68: | Line 70: | ||
targetProject="gene" /> | targetProject="gene" /> | ||
<abatorConfiguration> | <abatorConfiguration> | ||
− | </ | + | </syntaxhighlight> |
=====Abator Example===== | =====Abator Example===== | ||
− | < | + | |
− | <table schema="db2inst1" tableName="synonym"></nowiki> | + | <syntaxhighlight lang="xml"> |
+ | <table schema="db2inst1" tableName="synonym"></nowiki> | ||
<generatedKey column="synonym_id" sqlStatement="VALUES PREVVAL FOR | <generatedKey column="synonym_id" sqlStatement="VALUES PREVVAL FOR | ||
synonym_seq" identity="true" /> | synonym_seq" identity="true" /> | ||
<columnOverride column="CREATED_BY" jdbcType="INTEGER" /> | <columnOverride column="CREATED_BY" jdbcType="INTEGER" /> | ||
<columnOverride column="MODIFIED_BY" jdbcType="INTEGER" /> | <columnOverride column="MODIFIED_BY" jdbcType="INTEGER" /> | ||
− | + | </table> | |
+ | </syntaxhighlight> | ||
+ | |||
=====Abator===== | =====Abator===== | ||
Line 97: | Line 102: | ||
=====Insert===== | =====Insert===== | ||
− | <xml> | + | |
+ | <syntaxhighlight lang="xml"> | ||
<insert id="abatorgenerated_insert" parameterClass= | <insert id="abatorgenerated_insert" parameterClass= | ||
"org.gmod.architecture.framwork.bakeoff.abator.model.FeatureWithBLOBs"> | "org.gmod.architecture.framwork.bakeoff.abator.model.FeatureWithBLOBs"> | ||
Line 114: | Line 120: | ||
</selectKey> | </selectKey> | ||
</insert> | </insert> | ||
− | </ | + | </syntaxhighlight> |
=====Insert===== | =====Insert===== | ||
− | <xml> | + | <syntaxhighlight lang="xml"> |
<insert id="abatorgenerated_insert" parameterClass= | <insert id="abatorgenerated_insert" parameterClass= | ||
"org.gmod.architecture.framwork.bakeoff.abator.model.FeatureWithBLOBs"> | "org.gmod.architecture.framwork.bakeoff.abator.model.FeatureWithBLOBs"> | ||
Line 129: | Line 135: | ||
#isAnalysis:SMALLINT#, #isObsolete:SMALLINT#, | #isAnalysis:SMALLINT#, #isObsolete:SMALLINT#, | ||
#createdBy:INTEGER#) | #createdBy:INTEGER#) | ||
− | + | ||
<selectKey resultClass="java.lang.Integer" keyProperty="featureId"> | <selectKey resultClass="java.lang.Integer" keyProperty="featureId"> | ||
VALUES PREVVAL FOR feature_seq | VALUES PREVVAL FOR feature_seq | ||
</selectKey> | </selectKey> | ||
</insert> | </insert> | ||
− | </ | + | </syntaxhighlight> |
=====Insert===== | =====Insert===== | ||
− | <xml> | + | <syntaxhighlight lang="xml"> |
<insert id="abatorgenerated_insert" parameterClass= | <insert id="abatorgenerated_insert" parameterClass= | ||
"org.gmod.architecture.framwork.bakeoff.abator.model.FeatureWithBLOBs"> | "org.gmod.architecture.framwork.bakeoff.abator.model.FeatureWithBLOBs"> | ||
Line 154: | Line 160: | ||
</selectKey> | </selectKey> | ||
</insert> | </insert> | ||
− | </ | + | </syntaxhighlight> |
=====Insert===== | =====Insert===== | ||
− | <xml> | + | <syntaxhighlight lang="xml"> |
<selectKey resultClass="java.lang.Integer" | <selectKey resultClass="java.lang.Integer" | ||
keyProperty="featureId"> | keyProperty="featureId"> | ||
VALUES PREVVAL FOR feature_seq | VALUES PREVVAL FOR feature_seq | ||
</selectKey> | </selectKey> | ||
− | </ | + | </syntaxhighlight> |
=====Insert===== | =====Insert===== | ||
− | <xml> | + | <syntaxhighlight lang="xml"> |
<selectKey resultClass="java.lang.Integer" | <selectKey resultClass="java.lang.Integer" | ||
keyProperty="featureId"> | keyProperty="featureId"> | ||
VALUES PREVVAL FOR feature_seq | VALUES PREVVAL FOR feature_seq | ||
</selectKey> | </selectKey> | ||
− | </ | + | </syntaxhighlight> |
=====Problem 1 - Insert===== | =====Problem 1 - Insert===== | ||
− | <java> | + | <syntaxhighlight lang="java"> |
try { | try { | ||
sqlMap.startTransaction(); | sqlMap.startTransaction(); | ||
Line 190: | Line 196: | ||
sqlMap.endTransaction(); | sqlMap.endTransaction(); | ||
} | } | ||
− | </ | + | </syntaxhighlight> |
=====Problem 1 - Insert===== | =====Problem 1 - Insert===== | ||
− | <java> | + | <syntaxhighlight lang="java"> |
try { | try { | ||
sqlMap.startTransaction(); | sqlMap.startTransaction(); | ||
Line 210: | Line 216: | ||
sqlMap.endTransaction(); | sqlMap.endTransaction(); | ||
} | } | ||
− | </ | + | </syntaxhighlight> |
=====Transactions===== | =====Transactions===== | ||
Line 237: | Line 243: | ||
Account for cycles or recursion in Master Detail Report. | Account for cycles or recursion in Master Detail Report. | ||
− | <xml> | + | <syntaxhighlight lang="xml"> |
<resultMap id="SelectGeneResults" | <resultMap id="SelectGeneResults" | ||
class="org.gmod.architecture.framwork.bakeoff.Gene" groupBy="id"> | class="org.gmod.architecture.framwork.bakeoff.Gene" groupBy="id"> | ||
Line 247: | Line 253: | ||
<result property="exons" resultMap = "gene.SelectExonResults"/> | <result property="exons" resultMap = "gene.SelectExonResults"/> | ||
</resultMap> | </resultMap> | ||
− | + | ||
<resultMap id="SelectExonResults" | <resultMap id="SelectExonResults" | ||
class="org.gmod.architecture.framwork.bakeoff.Exon"> | class="org.gmod.architecture.framwork.bakeoff.Exon"> | ||
Line 259: | Line 265: | ||
jdbcType="INTEGER" /> | jdbcType="INTEGER" /> | ||
</resultMap> | </resultMap> | ||
− | </ | + | </syntaxhighlight> |
=====Problem 2 - Master Detail Report===== | =====Problem 2 - Master Detail Report===== | ||
− | <xml> | + | <syntaxhighlight lang="xml"> |
<resultMap id="SelectGeneResults" | <resultMap id="SelectGeneResults" | ||
class="org.gmod.architecture.framwork.bakeoff.Gene" groupBy="id"> | class="org.gmod.architecture.framwork.bakeoff.Gene" groupBy="id"> | ||
Line 272: | Line 278: | ||
<result property="exons" resultMap = "gene.SelectExonResults"/> | <result property="exons" resultMap = "gene.SelectExonResults"/> | ||
</resultMap> | </resultMap> | ||
− | + | ||
<resultMap id="SelectExonResults" | <resultMap id="SelectExonResults" | ||
class="org.gmod.architecture.framwork.bakeoff.Exon"> | class="org.gmod.architecture.framwork.bakeoff.Exon"> | ||
Line 284: | Line 290: | ||
jdbcType="INTEGER" /> | jdbcType="INTEGER" /> | ||
</resultMap> | </resultMap> | ||
− | </ | + | </syntaxhighlight> |
=====Master Detail Report===== | =====Master Detail Report===== | ||
Line 318: | Line 324: | ||
GENE_SYMBOLS s | GENE_SYMBOLS s | ||
</isEqual> | </isEqual> | ||
− | + | ||
<isEqual prepend="," | <isEqual prepend="," | ||
property="searchNcbi" | property="searchNcbi" |
Latest revision as of 21:10, 9 October 2012
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 as feature_id.
Abator Example
<abatorConfiguration> <abatorContext> <!-- TODO: Add Database Connection Information --> <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>
Abator Example
<abatorConfiguration> <abatorContext> <!-- TODO: Add Database Connection Information --> <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>
Abator Example
<table schema="db2inst1" tableName="synonym"></nowiki> <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
<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>
Insert
<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>
Insert
<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>
Insert
<selectKey resultClass="java.lang.Integer" keyProperty="featureId"> VALUES PREVVAL FOR feature_seq </selectKey>
Insert
<selectKey resultClass="java.lang.Integer" keyProperty="featureId"> VALUES PREVVAL FOR feature_seq </selectKey>
Problem 1 - Insert
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(); }
Problem 1 - Insert
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(); }
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.
<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>
Problem 2 - Master Detail Report
<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>
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