1	iBATIS SQL Maps Maurice Naftalin Morningside Light Ltd JPMorgan, Glasgow 18^th November 2005
2	iBATIS SQL Maps What is iBATIS? iBATIS “Hello World” Parameter and Result Maps Handling Aggregations Other topics Configuration, Inserts, Stored Procedures, Dynamic mapped statements, Logging Conclusions
3	What is iBATIS? Data Persistence Framework Like Hibernate, JDO, TopLink, EJB CMP Lightweight, good for persisting POJOs (Plain Old Java Objects) Used freestanding, or as part of application design frameworks like Spring
4	How is iBATIS different? Different from Hibernate/JDO/CMP Uses existing SQL, stored procedures Handles overhead of coding connections, caching, transactions Simple XML JavaBean-DB mapping iBATIS DAO “shares the brand”
5	Why “iBATIS”? ab·a·tis NOUN: pl. ab·a·tis or ab·a·tis·es A defensive obstacle made by laying felled trees on top of each other with branches, sometimes sharpened, facing the enemy. iBATIS is an Internet abatis!
6	How does it work?
7	How does it work? (cont’d)
8	iBATIS SQL Maps What is iBATIS? iBATIS “Hello World” Parameter and Result Maps Handling Aggregations Other topics Configuration, Inserts, Stored Procedures, Dynamic mapped statements, Logging Conclusions
9	Person.java package uk.co.morninglight.ibatistest; public class Person { private String catchPhrase; private Long id; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getCatchPhrase () { return catchPhrase; } public void setCatchPhrase(String catchPhrase) { this.catchPhrase = catchPhrase; } }
10	sqlMapConfig.xml <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <typeAlias alias="mlPerson" type="uk.co.morninglight.ibatistest.Person"/> <typeAlias alias="mlFamily" type="uk.co.morninglight.ibatistest.Family"/> <transactionManager type="JDBC" > <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/> <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost/test"/> <property name="JDBC.Username" value=“maurice"/> <property name="JDBC.Password" value=“mypasswd"/> </dataSource> </transactionManager> <sqlMap resource=“uk/co/morninglight/ibatistest/Person.xml" /> </sqlMapConfig>
11	The SQL Map file (Person.xml) <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="Person"> <!– type aliases, result and parameter maps, queries --> </sqlMap>
12	Person.xml (cont’d) <?xml …> <!DOCTYPE …> <sqlMap namespace="Person"> <select id="getPerson" resultClass=“mlPerson"> SELECT ID as id, CATCH_PHRASE as catchPhrase FROM PERSON WHERE ID = #value# </select> </sqlMap>
13	A simple DAO package uk.co.morninglight.ibatistest; import com.ibatis.sqlmap.client.*; import com.ibatis.common.resources.Resources; public class PersonDAO { private static SqlMapClient sqlMap; private static PersonDAO ourInstance = new PersonDAO(); public static PersonDAO getInstance() { return ourInstance; } private PersonDAO() { try { String resource = "uk/co/morninglight/ibatistest/SQLMapConfig.xml"; Reader reader = Resources.getResourceAsReader (resource); sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader); } catch (IOException e) { e.printStackTrace(); } } // DAO methods … }
14	A simple DAO (cont’d) public class PersonDAO { // see previous slide public Person getPerson(int i) throws SQLException { return (Person) sqlMap.queryForObject("getPerson", i); } // more DAO methods }
15	iBATIS SQL Maps What is iBATIS? iBATIS “Hello World” Parameter and Result Maps Handling Aggregations Other topics Configuration, Inserts, Stored Procedures, Dynamic mapped statements, Logging Conclusions
16	Auto-mapping (of results) <?xml …> <!DOCTYPE …> <sqlMap namespace="Person"> <select id="getPerson" resultClass=“mlPerson"> SELECT ID as id, CATCH_PHRASE as catchPhrase FROM PERSON WHERE ID = #value# </select> </sqlMap>
17	Explicit mapping For parameters and results Explicit mapping not so simple, but Can specify output column types Can load null value and “complex” properties Doesn’t require reflection into ResultSetMetaData
18	Parameter maps Explicit parameter maps (verbose) Inline parameter maps – coded into SQL a single wrapper object JavaBean property names: <sqlMap namespace="Person"> <select>…</select> <update id="updatePerson" parameterClass="mlPerson"> UPDATE PERSON SET CATCH_PHRASE = #catchPhrase# WHERE ID = #id# </update> </sqlMap>
19	Using JavaBean mapped properties public class PersonDAO { public Person getPerson(int i) throws SQLException {…} public void setPersonCP(int i,String cp) throws SQLException{ Person person = (Person)sqlMap.queryForObject("getPerson", i); person.setCatchPhrase(cp); sqlMap.update("updatePerson",person); } }
20	Parameter maps (cont’d) You can also use Map type parameters <sqlMap namespace="Person"> <select>…</select> <update>…</update> <insert id="insertPerson" parameterClass="map"> INSERT INTO PERSON (ID, CATCH_PHRASE) VALUES (#id#, #catchPhrase#) </insert> </sqlMap>
21	Using properties from java.util.Map public class PersonDAO { public Person getPerson(int i) throws SQLException {…} public void setPersonCP(int i,String cp) throws SQLException{ … } public void insertPerson(Map map) throws SQLException { sqlMap.insert("insertPerson", map); } }
22	SqlMapClient Interface in com.ibatis.sqlmap.client
23	SqlMapExecutor Interface in com.ibatis.sqlmap.client update(), insert(), delete() startBatch(), executeBatch() queryForObject(String, Object) queryForObject(String, Object, Object) queryForList(String, Object) - with variant for a subrange queryForMap(String, Object, String) with variant for a particular property queryForPaginatedList(String, Object, int) queryWithRowHandler(String, Object, RowHandler)
24	Result Maps Can populate a simple Java class <sqlMap> <resultMap id="CPResultMap" class="java.lang.String"> <result property="val" column="CATCH_PHRASE"/> </resultMap> <select id="getCPs" resultMap="CPResultMap"> SELECT CATCH_PHRASE FROM PERSON </select> <sqlMap> public List getCatchPhrases() throws SQLException { return sqlMap.queryForList("getCPs",null); }
25	Result Maps (cont’d) Can also populate Map instances <resultMap id="getPersonMap" class="java.util.HashMap"> <result property="id" column="ID"/> <result property="firstName" column="FIRST_NAME"/> <result property="lastName" column="LAST_NAME"/> </resultMap> <select id="getPersonProperties" resultMap="getPersonMap" > SELECT ID, FIRST_NAME, LAST_NAME FROM PERSON WHERE CATCH_PHRASE LIKE CONCAT(#value#,'%‘) </select>
26	iBATIS SQL Maps What is iBATIS? iBATIS “Hello World” Parameter and Result Maps Handling Aggregation Other topics Configuration, Inserts, Stored Procedures, Dynamic mapped statements, Logging Conclusions
27	Complex properties (aggregation) Aggregation types Can be populated by means of a join SELECT * FROM PERSON, FAMILY WHERE FAMILY.ID=PERSON.FAMILY Or automatically via the resultMap…
28	Complex properties (cont’d) Using the resultMap: <resultMap id="getPersonResult" class="mlPerson"> <result property="id" column="ID"/> <result property="family" column="FAMILY" select="getFamily"/> </resultMap> <select id="getPersonWithFamily" resultMap="getPersonResult"> SELECT * FROM PERSON WHERE ID=#value# </select> <select id="getFamily" resultClass=“mlFamily"> SELECT * FROM FAMILY WHERE ID = #value# </select> Easy but can be inefficient (“1+N problem”); joins often perform better.
29	iBATIS SQL Maps What is iBATIS? iBATIS “Hello World” Parameter and Result Maps Handling Aggregations Other topics Configuration, Inserts, Stored Procedures, Dynamic mapped statements, Logging Conclusions
30	Configuring SQL Maps You can specify (non-exhaustive list) The maximum number of transactions, sessions, requests Whether caching is enabled Whether lazy loading is enabled How transactions should be managed – JDBC, JTA, or user-managed Which DataSource to use – simple, DBCP, or JNDI
31	Inserting data Problem – how to generate or discover the new primary key? Databases differ SQL Server will return the auto-generated key Oracle provides pre-generated keys from a sequence <!—Oracle SEQUENCE Example --> <insert id="insertProduct-ORACLE" parameterClass=“product"> <selectKey resultClass="int" > SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL </selectKey> INSERT INTO PRODUCT (PRD_ID,PRD_DESCRIPTION) VALUES (#id#,#description#) </insert>
32	Stored Procedures Supported by <procedure> element <sqlMap> <parameterMap id="swapParameters" class="map" > <parameter property="email1" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/> <parameter property="email2" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/> </parameterMap> <procedure id="swapAddresses" parameterMap="swapParameters" > {call swap_email_address (?, ?)} </procedure> </sqlMap>
33	Dynamic Mapped Statements Use built-in statement generator Avoids writing conditional SQL concatenations <statement id="someName“ resultMap="account-result" > SELECT * FROM ACCOUNT <dynamic prepend=“WHERE"> <isGreaterThan prepend=“AND" property="id" compareValue="0"> ACC_ID = #id# </isGreaterThan> <isNotNull prepend=” AND " property="lastName"> ACC_LAST_NAME = #lastName# </isNotNull> </dynamic> ORDER BY ACC_LAST_NAME </statement>
34	Logging Uses Jakarta Commons Logging framework http://jakarta.apache.org/commons/logging/index.html only dependency in iBATIS! “ultra-thin bridge between different logging libraries” Can plug in standard logging API: Log4J http://jakarta.apache.org/log4j/docs/index.html JDK Logging API http://java.sun.com/j2se/1.4.1/docs/guide/util/logging/
35	Conclusions Lightweight framework Shallow learning curve Uses knowledge (and code?) that you already have SQL, XML WYSIWYG But still saves a lot of tedious coding Going to become very popular!