iBATIS Framework

1. iBATIS Framework Presented by Joe Borosky, Chris DeCelles, Yvonne Krashkevich 11-16-05

2. iBATIS Background (1) • iBATIS project started by Clinton Begin in 2001. • In early 2002 Microsoft published a paper claiming that .Net was 10 times faster and 4 times more productive than J2EE. • Realizing that this was simply not the case, the iBATIS project quickly responded and on July 1, 2002, JPetStore 1.0 was released.

3. iBATIS Background (2) • Based on the same Pet Store requirements, JPetStore demonstrated that Java could not only be more productive than .Net, but could also do so while achieving a better architecture than was used in the Microsoft implementation. • JPetStore made use of an interesting persistence layer that quickly captured the attention of the open source community. • Shortly after releasing JPetStore, questions and requests for the SQL Maps and DAO frameworks spawned the project that would become known as iBATIS Database Layer.

4. iBATIS Background (3) • The iBATIS Database Layer includes two frameworks that simply happen to be packaged together: SQL Maps and DAO. • Today the iBATIS project is heavily focused on the persistence layer frameworks known as SQL Maps and Data Access Objects (DAO). • JPetStore lives on as the official example of typical usage of these frameworks.

5. iBATIS Introduction • The iBATIS Data Mapper Framework makes it easier to use a database with Java and .Net applications. • iBATIS couples objects with stored procedures or SQL statements using a XML descriptor. • Simplicity is the biggest advantage of the iBATIS Data Mapper over object relational mapping tools.

6. iBATIS Introduction • To use the iBATIS Data Mapper you rely on your own objects, XML, and SQL. • There is little to learn that you do not already know. • With the iBATIS Data Mapper you have full power of both SQL and stored procedures at your fingertips.

7. iBATIS Frameworks Overview • iBATIS encompasses two completely independent frameworks: • DataMapper framework • DAO Framework

8. iBATIS Data Mapper Framework • Significantly reduces the amount of Java and .NET code that is normally needed to access a relational database. • Maps classes to SQL statements using a very simple XML descriptor. • To use iBATIS you need only be familiar with your own application domain objects (basic JavaBeans or .NET classes), XML, and SQL. • Can map nearly any database to any object model and is very tolerant of legacy designs, or even bad designs, without using special database tables, peer objects or code generation.

9. iBATIS DAO Framework • iBATIS Data Access Objects is an abstraction layer that hides the details of your persistence solution and provides a common API to the rest of your application. • DAOs allow you to create simple components that provide access to your data without revealing the specifics of the implementation to the rest of your application. • Using DAOs you can allow your application to be dynamically configured to use different persistence mechanisms.

10. iBATIS and SQL Maps • The SQL Maps framework is very tolerant of bad database models and even bad object models • However it is recommended to use best practices when designing your database and object model. • By using best practices you will get good performance and a Clean Design.

11. iBATIS and SQL Maps • The easiest place to start is to analyze what you’re working with • What are your business objects? • What are your database tables? • How do they relate to each other?

12. First Example: Person • Person.Java • package examples.domain; //imports implied…. public class Person { private int id; private String firstName; private String lastName; private Date birthDate; private double weightInKilograms; private double heightInMeters; public int getId () { return id; } public void setId (int id) { this.id = id; } //…let’s assume we have the other getters and setters to save space… }

13. First Example: Person • Person.sql • CREATE TABLE PERSON( PER_ID NUMBER (5, 0) NOT NULL, PER_FIRST_NAME VARCHAR (40) NOT NULL, PER_LAST_NAME VARCHAR (40) NOT NULL, PER_BIRTH_DATE DATETIME , PER_WEIGHT_KG NUMBER (4, 2) NOT NULL, PER_HEIGHT_M NUMBER (4, 2) NOT NULL, PRIMARY KEY (PER_ID) )

14. iBATIS and SQL Map Configuration File • Once we are comfortable with the classes and tables we are working with the best place to start is the SQL Map configuration file. • This file acts as the root configuration for our SQL Map implementation. • The configuration file is an XML file. • Within the file we will configure properties, JDBC DataSources, and SQL Maps.

15. iBATIS and SQL Map Configuration File • The configuration file is a convenient location to centrally configure your DataSource which can be any number of different implementations. • The framework can handle a number of DataSource implementations including • iBATIS Simple DataSource, Jakarta DBCP (Commons), and any DataSource that can be looked up via a JNDI context (Example from within an application server).

16. Sample Configuration File • SqlMapConfigExample.xml • Will be discussed later • SqlMapConfigExample.properties # This is just a simple properties file that simplifies automated # configuration of the SQL Maps configuration file (e.g. by Ant # builds or continuous integration tools for different # environments… etc.) These values can be used in any # property value in the file above (e.g. “${driver}”) # Using a properties file such as this is completely optional. # Driver, url, username, and password change accordingly to fit # your needs. driver=oracle.jdbc.driver.OracleDriver url=jdbc:oracle:thin:@localhost:1521:oracle1 username=jsmith password=test

17. SQL Map File(s) • 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"> <select id="getPerson" resultClass="examples.domain.Person"> SELECT PER_ID as id, PER_FIRST_NAME as firstName, PER_LAST_NAME as lastName, PER_BIRTH_DATE as birthDate, PER_WEIGHT_KG as weightInKilograms, PER_HEIGHT_M as heightInMeters FROM PERSON WHERE PER_ID = #value# </select> </sqlMap>

18. SQL Map File(s) • It shows the simplest form of SQL map. • It uses a feature of the SQL Maps framework that automatically maps the columns of a ResultSet to JavaBeans properties (or Map keys, etc.) based on name matching. • The #value# token is an input parameter • More specifically the use of “value” implies that we are using a simple primitive wrapper type.

19. SQL Map File(s) • Although very simple there are limitations of using the auto result mapping approach. • There is no way to specify the types of the output columns or • to automatically load related data, and • there is a slight performance implication in that this approach requires accessing the ResultSetMetaData. • We can overcome these limitations by using a resultMap

20. SQL Map File(s) • Person.xml (Insert, Update, Delete parts) <!-- Use Person object (JavaBean) properties as parameters. Each of the parameters in the #hash# symbols is a JavaBeans property. --> <insert id="insertPerson" parameterClass="examples.domain.Person"> INSERT INTO PERSON (PER_ID, PER_FIRST_NAME, PER_LAST_NAME, PER_BIRTH_DATE, PER_WEIGHT_KG, PER_HEIGHT_M) VALUES (#id#, #firstName#, #lastName#, #birthDate#, #weightInKilograms#, #heightInMeters#) </insert> <update id="updatePerson" parameterClass="examples.domain.Person"> UPDATE PERSON SET PER_FIRST_NAME = #firstName#, PER_LAST_NAME = #lastName#, PER_BIRTH_DATE = #birthDate#, PER_WEIGHT_KG = #weightInKilograms#, PER_HEIGHT_M = #heightInMeters# WHERE PER_ID = #id# </update> <delete id="deletePerson" parameterClass="examples.domain.Person"> DELETE PERSON WHERE PER_ID = #id# </delete>

21. Programming with the SQL Map Framework • Now that we are configured and mapped we need to code in our Java application. • The first step is to configure the SQL Map (this is simply a matter of loading the SQL Map configuration XML file that we created before) • To simplify loading the XML file we can make use of the Resources class included with the framework.

22. Programming with the SQL Map Framework • The SqlMapClient object is a long lived thread safe service object. • For a given run of an application you only need to instantiate/configure it once. • This makes it a good candidate for a static member of a base class, or if you prefer to have it more centrally configured and globally available you could wrap it up in a convenience class of your own.

23. Reading Objects from the Database • Now that the SqlMap instance is initialized and easily accessible we can make use of it. • To get a Person object from the database we simply need the SqlMap instance, the name of the mapped statement and a PersonID. SqlMapClient sqlMap = MyAppSqlMapConfig.getSqlMapInstance(); Integer personPk = new Integer(5); Person person = (Person) sqlMap.queryForObject (“getPerson”, personPk);

24. Writing Objects to the Database • Once we have the database we can modify the data using Insert, Update and Delete • Update person.setHeightInMeters(1.83); sqlMap.update(“updatePerson”, person); • Delete sqlMap.delete (“deletePerson”, person); • Insert Person newPerson = new Person(); newPerson.setId(11); newPerson.setFirstName(“Clinton”); … sqlMap.insert (“insertPerson”, newPerson);

25. Data Mapper Framework • One definition of a Mapper is an "object that sets up communication between two independent objects.“ • A Data Mapper is a "layer of mappers that moves data between objects and a database while keeping them independent of each other and the mapper itself. • You provide the database and the objects; iBATIS provides the mapping layer that goes between the two.

26. Problems solved by iBATIS Data Mapper • Separating SQL code from programming code • Passing input parameters to the library classes and extracting the output • Separating data access classes from business logic classes • Caching often-used data until it changes • Managing transactions and threading

27. iBATIS Data Mapper Workflow • Provide a parameter, either as an object or a native type. The parameter can used to set runtime values in your SQL statement or stored procedure. If a runtime value is not needed, the parameter can be omitted. • Execute the mapping by passing the parameter and the name you gave the statement or procedure in your XML descriptor. This step is where the magic happens. The framework will prepare the SQL statement or stored procedure, set any runtime values using your parameter, execute the procedure or statement, and return the result. • In the case of an update, the number of rows affected is returned. In the case of a query, a single object, or a collection of objects is returned. Like the parameter, the result object, or collection of objects, can be a plain-old object or a native type. The result can also be given as XML.

28. iBATIS Data Mapper Workflow

29. Generic iBATIS Workflow

30. The SQL Map XML Configuration File • The <properties> Element • The <settings> Element • The <typeAlias> Element • The <transactionManager> element • The <dataSource> Element • The <sqlMap> Element

31. Config: <properties> • The SQL Map can have a single <properties> element that allows a standard Java properties file (name=value) to be associated with the SQL Map XML configuration document. • By doing so, each named value in the properties file can become a variable that can be referred to in the SQL Map configuration file and all Data Mapper referenced within. • E.g. • <properties resource="properties/database.properties"/>

32. Config: <settings> • Note: All settings are optional. • maxRequests: • The maximum number of threads that can execute an SQL statement at a time. • Should usually be at least 10 times maxTransactions and should be greater than maxSessions and maxTransactions. • Reducing the maximum number of concurrent requests can increase performance. • Example: maxRequests=”256” • Default: 512

33. Config: <settings> • maxSessions • Number of sessions (or clients) that can be active at a time. • Includes both explicit sessions, requested programmatically, or whenever a thread makes use of an SqlMapClient instance (e.g. executes a statement etc.). • Should always be greater than or equal to maxTransactions and less than maxRequests. • Reducing the maximum number of concurrent sessions can reduce the overall memory footprint. • Example: maxSessions=”64” • Default: 128

34. Config: <settings> • maxTransactions • Maximum number of threads that can enter SqlMapClient.startTransaction() at a time. • Should always be less than or equal to maxSessions and always much less than maxRequests. • Often reducing the maximum number of concurrent transactions can increase performance. • Example: maxTransactions=”16” • Default: 32

35. Config: <settings> • cacheModelsEnabled • Globally enables or disables all cache models for a SqlMapClient. • Example: cacheModelsEnabled=”true” • Default: true (enabled) • lazyLoadingEnabled • Globally enables or disables all lazy loading for a SqlMapClient. • Example: lazyLoadingEnabled=”true” • Default: true (enabled)

36. Config: <settings> • enhancementEnabled • Enables runtime bytecode enhancement to facilitate optimized JavaBean property access as well as enhanced lazy loading. • Example: enhancementEnabled=”true” • Default: false (disabled) • useStatementNamespaces • With this setting enabled, you must always refer to mapped statements by their fully qualified name, which is the combination of the sqlMap name and the statement name. • For example: • queryForObject(“sqlMapName.statementName”); • Example: useStatementNamespaces=”false” • Default: false (disabled)

37. Config: <typeAlias> • Allows you to specify a shorter name to refer to what is usually a long, fully qualified classname. • For example: • <typeAlias alias="shortname" type="com.long.class.path.Class"/>

38. Config: <transactionManager> • Allows configuration of transaction management services for an SQL Map. • JDBC - Allows JDBC to control the transaction via the usual Connection commit() and rollback() methods. • JTA - Uses a JTA global transaction such that the SQL Map activities can be included as part of a wider scope transaction that possibly involves other databases or transactional resources. Requires a UserTransaction property set to locate the user transaction from a JNDI resource. • EXTERNAL – This allows you to manage transactions on your own. Some part of your application external to Data Mapper must manage the transactions.

39. Config: <dataSource> • Included as part of the transaction manager configuration is a dataSource element and a set of properties to configure a DataSource for use with your SQL Map. • There are currently three datasource factories provided with the framework, but you can also write your own. • SimpleDataSourceFactory • DbcpDataSourceFactory • JndiDataSourceFactory

40. Config: <sqlMap> • Used to explicitly include an SQL Map or another SQL Map Configuration file. • Each SQL Map XML file that is going to be used by this SqlMapClient instance must be declared. • The SQL Map XML files will be loaded as a stream resource from the classpath or from a URL. • You must specify any and all Data Mappers (as many as there are).

41. Simple SQL Map XML File <sqlMap id=“Product”> <select id=“getProduct” parameterClass=“ com.ibatis.example.Product” resultClass=“com.ibatis.example.Product”> select PRD_ID as id, PRD_DESCRIPTION as description from PRODUCT where PRD_ID = #id# </select> </sqlMap>

42. Alternative SQL Map XML File <sqlMap id=“Product”> <cacheModel id=“productCache” type=”LRU”> <flushInterval hours=“24”/> <property name=“size” value=”1000” /> </cacheModel> <typeAlias alias=“product” type=“com.ibatis.example.Product” /> <parameterMap id=“productParam” class=“product”> <parameter property=“id”/> </parameterMap> <resultMap id=“productResult” class=“product”> <result property=“id” column=“PRD_ID”/> <result property=“description” column=“PRD_DESCRIPTION”/> </resultMap> <select id=“getProduct” parameterMap=“productParam” resultMap=“productResult” cacheModel=“product-cache”> select * from PRODUCT where PRD_ID = ? </select> </sqlMap>

43. SQL Maps • The Data Mapper concept is centered around mapped statements. • Mapped statements can be any SQL statement and can have parameter maps (input) and result maps (output). • If the case is simple, the mapped statement can be configured directly to a class for parameters and results. • The mapped statement can also be configured to use a cache model to cache popular results in memory.

44. Statement Types • <statement> • <insert> • <update> • <delete> • <select> • <procedure>

45. The SQL • You can use any SQL that is valid for your database and JDBC driver. • Note: Potential for conflicting SQL/XML special characters, such as the greater-than and less-than symbols. • By using a standard XML CDATA section, none of the special characters will be parsed and the problem is solved. • For example: <statement id="getPersonsByAge" parameterClass=”int” resultClass="examples.domain.Person"> <![CDATA[ SELECT * FROM PERSON WHERE AGE > #value# ]]> </statement>

46. parameterClass • The value of the parameterClass attribute is the fully qualified name of a Java class (including package). • Used to limit parameters passed to the statement, as well as to optimize the performance of the framework. • Example parameterClass with inline parameters: <statement id=“statementName” parameterClass=” examples.domain.Product”> insert into PRODUCT values (#id#, #description#, #price#) </statement>

47. External parameterMap <parameterMap id=“insert-product-param” class=“com.domain.Product”> <parameter property=“id”/> <parameter property=“description”/> </parameterMap> <statement id=“insertProduct” parameterMap=“insert-product-param”> insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (?,?); </statement>

48. Primitive Type Parameters • It is not always necessary or convenient to write a JavaBean just to use as a parameter. • In these cases you are perfectly welcome to use a primitive type wrapper object (String, Integer, Date etc.) as the parameter directly. • For example: <statement id=“insertProduct” parameter=“java.lang.Integer”> select * from PRODUCT where PRD_ID = #value# </statement>

49. resultClass • The value of the resultClass attribute is the fully qualified name of a Java class (i.e. including package). • The resultClass attribute allows us to specify a class that will be auto-mapped to our JDBC ResultSet based on the ResultSetMetaData. • Wherever a property on the JavaBean and a column of the ResultSet match, the property will be populated with the column value.

50. resultClass Example <statement id="getPerson" parameterClass=“int” resultClass="examples.domain.Person"> SELECT PER_ID as id, PER_FIRST_NAME as firstName, PER_LAST_NAME as lastName, PER_BIRTH_DATE as birthDate, PER_WEIGHT_KG as weightInKilograms, PER_HEIGHT_M as heightInMeters FROM PERSON WHERE PER_ID = #value# </statement>