Ibatis - Basics

Data Mapper:- Data Mapper provides a very simple framework for using XML descriptors to map JavaBeans, Map implementations, primitive wrapper types (String, Integer…) and even XML documents to an SQL statement. The following is a high level description of the lifecycle: 1. Provide an object as a parameter (a JavaBean, Map or primitive wrapper). The parameter object will be used to set input values in an update statement, or where clause values in a query... 2. Execute the mapped statement. This step is where the magic happens. The Data Mapper framework will create a Prepared Statement instance, set any parameters using the provided parameter object, execute the statement and build a result object from the Result Set. 3. 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 parameters, result objects can be a JavaBean, a Map, a primitive type wrapper or XML.   The SQL Map XML Configuration File (http://ibatis.apache.org/dtd/sql-map-config-2.dtd) Data Mapper is configured using a central XML configuration file, which provides configuration details for Data Sources, Data Mapper and other options like thread management... The following is an example of the SQL Map configuration file:  SqlMapConfig.xml <!—Always ensure to use the correct XML header as below! --> <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <!-- The properties (name=value) in the file specified here can be used placeholders in this config file (e.g. “${driver}”. The file is relative to the classpath and is completely optional. --> <properties resource=" examples/sqlmap/maps/SqlMapConfigExample.properties " /> <!-- These settings control SqlMapClient configuration details, primarily to do with transaction management. They are all optional (more detail later in this document). --> <settings cacheModelsEnabled="true" enhancementEnabled="true"  lazyLoadingEnabled="true" maxRequests="128" maxSessions="10" maxTransactions="5" useStatementNamespaces="false" defaultStatementTimeout="5" statementCachingEnabled="true" classInfoCacheEnabled="true" /> <!-- This element declares a factory class that iBATIS will use for creating result objects. This element is optional (more detail later in this document). --> <resultObjectFactory type="com.mydomain.MyResultObjectFactory" > <property name="someProperty" value="someValue"/> </resultObjectFactory> <!-- Type aliases allow you to use a shorter name for long fully qualified class names. --> <typeAlias alias="order" type="testdomain.Order"/> <!-- Configure a datasource to use with this SQL Map using SimpleDataSource. Notice the use of the properties from the above resource --> <transactionManager type="JDBC" > <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="${driver}"/> <property name="JDBC.ConnectionURL" value="${url}"/> <property name="JDBC.Username" value="${username}"/> <property name="JDBC.Password" value="${password}"/> <property name="JDBC.DefaultAutoCommit" value="true" /> <property name="Pool.MaximumActiveConnections" value="10"/> <property name="Pool.MaximumIdleConnections" value="5"/> <property name="Pool.MaximumCheckoutTime" value="120000"/> <property name="Pool.TimeToWait" value="500"/> <property name="Pool.PingQuery" value="select 1 from ACCOUNT"/> <property name="Pool.PingEnabled" value="false"/> <property name="Pool.PingConnectionsOlderThan" value="1"/> <property name="Pool.PingConnectionsNotUsedFor" value="1"/> </dataSource> </transactionManager> <!-- Identify all SQL Map XML files to be loaded by this SQL map. Notice the paths are relative to the classpath. For now, we only have one… --> <sqlMap resource="examples/sqlmap/maps/Person.xml" /> </sqlMapConfig> ·         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. ·         For example, if the properties file contains the following: driver=oracle.jdbc.driver.OracleDriver ·         Then the SQL Map configuration file or each SQL Map referenced by the configuration document can use the placeholder ${driver} as a value that will be replaced by oracle.jdbc.driver.OracleDriver. For example: <property name="JDBC.Driver" value="${driver}"/> ·         The <settings> element allows you to configure various options and optimizations for the SqlMapClient instance that will be built using this XML file. The settings element and all of its attributes are completely optional. o   maxRequests - maximum number of threads that can execute an SQL statement at a time. (Default = 512) o   maxSessions - the number of sessions (or clients) that can be active at a given time. (Default = 128) o   maxTransactions - the maximum number of threads that can enter SqlMapClient.startTransaction() at a time. (Default = 32) o   cacheModelsEnabled - This setting globally enables or disables all cache models for a SqlMapClient. (Default = true) o   lazyLoadingEnabled - This setting globally enables or disables all lazy loading for a SqlMapClient. (Default = true) ·         The <resultObjectFactory> Element - The resultObjectFactory element allows you to specify a factory class for creating objects resulting from the execution of SQL statements. This element is optional – if you don't specify the element, iBATIS will use internal mechanisms to create result objects (class.newInstance())...If you choose to implement a factory, your factory class must implement the interface com.ibatis.sqlmap.engine.mapping.result.ResultObjectFactory, and your class must have a public default constructor. ·         The <typeAlias> Element - The typeAlias element simply 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"/> ·         The <transactionManager> Element - The <transactionManager> element allows you to configure the transaction management services for an SQL Map. The type attribute indicates which transaction manager to use. The value can either be a class name or a type alias. The three transaction managers included with the framework are: JDBC, JTA and EXTERNAL. o   JDBC - This allows JDBC to control the transaction via the usual Connection commit() and rollback() methods. o   JTA - This transaction manager 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. This configuration requires a  UserTransaction property set to locate the user transaction from a JNDI resource. (Ex: java:comp/UserTransaction) o   EXTERNAL – This allows you to manage transactions on your own. You can still configure a data source, but transactions will not be committed or rolled back as part of the framework lifecycle. This means that some part of your application external to Data Mapper must manage the transactions. This setting is also useful for non-transactional databases (e.g. Read-only). ·         The <transactionManager> element also allows an optional attribute commitRequired that can be true or false. Normally iBATIS will not commit transactions unless an insert, update, or delete operation has been performed. This is true even if you explicitly call the commitTransaction() method. This behavior creates problems in some cases. If you want iBATIS to always commit transactions, even if no insert, update, or delete operation as been performed, then set the value of the commitRequired attribute to true. Examples of where this attribute is useful include: o   If you call a stored procedures that updates data as well as returning rows. In that case you would call the procedure with the queryForList() operation – so iBATIS would not normally commit the transaction. But then the updates would be rolled back. o   In a WebSphere environment when you are using connection pooling and you use the JNDI <dataSource> and the JDBC or JTA transaction manager. WebSphere requires all transactions on pooled connections to be committed or the connection will not be returned to the pool. o    Note that the commitRequired attribute has no effect when using the EXTERNAL transaction manager. ·         The <dataSource> Element - 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. o   SimpleDataSourceFactory - The SimpleDataSource factory provides a basic implementation of a pooling DataSource that is ideal for providing connections in cases where there is no container provided DataSource. It is based on the iBATIS SimpleDataSource connection pool implementation. Ex: <transactionManager type="JDBC" > <dataSource type="SIMPLE"> o   DbcpDataSourceFactory - This implementation uses Jakarta DBCP (Database Connection Pool) to provide connection pooling services via the DataSource API. This DataSource is ideal where the application/web container cannot provide a DataSource implementation, or you’re running a standalone application. Ex: <transactionManager type="JDBC" > <dataSource type="DBCP"> o   JndiDataSourceFactory - This implementation will retrieve a DataSource implementation from a JNDI context from within an application container. This is typically used when an application server is in use and a container managed connection pool and associated DataSource implementations are provided. Ex: <transactionManager type="JDBC" > <dataSource type="JNDI"> ·         The <sqlMap> Element - The sqlMap element is 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 Mapper o   <!-- CLASSPATH RESOURCES --> §  <sqlMap resource="com/ibatis/examples/sql/Customer.xml" /> o   <!-- URL RESOURCES --> §  <sqlMap url="file:///c:/config/Customer.xml " /> ·         The SQL Map XML File ( http://ibatis.apache.org/dtd/sql-map-config-2.dtd) <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> <sqlMap namespace=”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> (or) <sqlMap namespace=”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> ·         Mapped Statements - The Data Mapper concept is centered on 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. <statement id=”statementName” [parameterClass=”some.class.Name”] [resultClass=”some.class.Name”] [parameterMap=”nameOfParameterMap”] [resultMap=”nameOfResultMap”] [cacheModel=”nameOfCache”] [timeout=“5”]> select * from PRODUCT where PRD_ID = [? | #propertyName#] order by [$simpleDynamic$] </statement> Where statement can be any of insert, update, delete, select, procedure, or statement. In the above statement, the [bracketed] parts are optional and in some cases only certain combinations are allowed. So it is perfectly legal to have a Mapped Statement with as simple as this:                 <insert id=”insertTestProduct” > insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (1, “Shih Tzu”) </insert> ·         Statement Types - The <statement> element is a general “catch all” statement that can be used for any type of SQL statement. o   <statement> o   <insert> o   <update> o   <delete> o   <select> o   <procedure> ·         The SQL - The SQL is obviously the most important part of the map. It can be any SQL that is valid for your database and JDBC driver. The most common obviously is the greater-than and less-than symbols (<>). These are commonly required in SQL and are reserved symbols in XML. There is a simple solution to deal with these and any other special XML characters you might need to put in your SQL. By using a standard XML CDATA section, none of the special characters will be parsed. ·                        <select id="getPersonsByAge" parameterClass=”int” resultClass="examples.domain.Person"> SELECT * FROM PERSON WHERE AGE <![CDATA[ > ]]> #value# </select> ·         Reusing SQL Fragments - When writing SqlMaps, you often encounter duplicate fragments of SQL, for example a FROM-clause or constraint-statement. iBATIS offers a simple yet powerful tag to reuse them. To eliminate this duplication, we use the tags <sql> and <include>. The <sql> tag contains the fragment to reuse, the <include> tag includes such a fragment in a statement. For example: <sql id="selectItemfragment"> FROM items WHERE parentid = 6 </sql> <select id="selectItemCount" resultClass="int"> SELECT COUNT(*) AS total <include refid="selectItemfragment"/> </select> <select id="selectItems" resultClass="Item"> SELECT id, name <include refid="selectItemfragment"/> </select> ·         Stored Procedures - Stored procedures are supported via the <procedure> statement element. The following example shows how a stored procedure would be used with output parameters. <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="swapEmailAddresses" parameterMap="swapParameters" >                 {call swap_email_address (?, ?)} </procedure> ·         parameterClass - The value of the parameterClass attribute is the fully qualified name of a Java class (i.e. including package). The parameterClass attribute is optional, but highly recommended. It is used to limit parameters passed to the statement, as well as to optimize the performance of the framework. If you’re using a parameterMap, there is no need to use the parameterClass attribute. For example, if you only wanted to allow objects of type (i.e. instanceof) “examples.domain.Product” to be passed in as a parameter, you could do something like this: <insert id=”statementName” parameterClass=” examples.domain.Product”>                 insert into PRODUCT values (#id#, #description#, #price#) </insert> IMPORTANT: Although optional for backward compatibility, it is highly recommended to always provide a parameter class (unless of course there are no required parameters). You will achieve better performance by providing the class, because the framework is capable of optimizing itself if it knows the type in advance. ·         parameterMap - The parameterMap attribute is rarely used in favor of the parameterClass attribute. The value of the parameterMap attribute is the name of a defined parameterMap element and inline parameters. However, this is a good approach if XML purity and consistency is your concern, or you need a more descriptive parameterMap <parameterMap id=”insert-product-param” class=”com.domain.Product”>                 <parameter property=”id”/>                 <parameter property=”description”/> </parameterMap> <insert id=”insertProduct” parameterMap=”insert-product-param”>                 insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (?,?) </insert> In the example above, the parameter map describes two parameters that will match, in order, the value tokens (“?”) in the SQL statement. So the first “?” will be replaced by the value of the “id” property and the second with the “description” property. Parameter maps and their options are described in more detail later in this document. ·         Inline Parameters - Inline parameters can be used inside of a mapped statement. For example: <insert id=”insertProduct” >                 insert into PRODUCT (PRD_ID, PRD_DESCRIPTION)                 values (#id#, #description#) </insert> In the above example the inline parameters are #id# and #description#. Each represents a JavaBeans property that will be used to populate the statement parameter in-place. ·         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 Result Set based on the ResultSetMetaData. Wherever a property on the JavaBean and a column of the Result Set match, the property will be populated with the column value. ·         resultMap - The resultMap property is one of the more commonly used. The value of the resultMap attribute is the name of a defined resultMap element. Using the resultMap attribute allows you to control how data is extracted from a result set and which properties to map to which columns. Unlike the auto-mapping approach using the resultClass attribute, the resultMap allows you to describe the column type, a null value replacement and complex property mappings (including other JavaBeans, Collections and primitive type wrappers). ·         cacheModel - The cacheModel attribute value is the name of a defined cacheModel element. A cacheModel is used to describe a cache for use with a query mapped statement. Each query mapped statement can use a different cacheModel, or the same one. The following example will demonstrate how it looks related to a statement. <cacheModel id="product-cache" type="LRU">                 <flushInterval hours="24"/>                 <flushOnExecute statement="insertProduct"/>                 <flushOnExecute statement="updateProduct"/>                 <flushOnExecute statement="deleteProduct"/>                 <property name=”size” value=”1000” /> </cacheModel> <select id=”getProductList” parameterClass=”int” cacheModel=”product-cache”>                 select * from PRODUCT where PRD_CAT_ID = #value# </select> ·         xmlResultName - When mapping results directly to an XML document, the value of the xmlResultName will be the name of the root element of the XML document. For example:                 <select id="getPerson" parameterClass=”int” resultClass="xml" xmlResultName=”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> The above select statement would produce a result XML object of the following structure: <person>                 <id>1</id>                 <firstName>Clinton</firstName>                 <lastName>Begin</lastName>                 <birthDate>1900-01-01</birthDate>                 <weightInKilograms>89</weightInKilograms>                 <heightInMeters>1.77</heightInMeters> </person> ·         remapResults - The remapResults attribute is available on <statement>, <select>, and <procedure> mapped statements. It's an optional attribute and the default is false. The remapResults attribute should be set to true when a query has a variable set of return columns. For example consider the following queries:                 SELECT $fieldList$ FROM table In the former example the list of columns are dynamic, even though the table is always the same.                 SELECT * FROM $sometable$ In the former example the table could be different. Because of the usage of * in the select clause, the resulting column names could be different as well. Dynamic elements could also cause the column list to change from one query execution to the next one. Since the overhead to introspect/determine the resultset metadata is not trivial, iBATIS will remember what was returned the last time the query was run. This creates problems in situations similar to the examples above, hence the possibility to do metadata introspection with every query execution. So if the return columns can change set remapResults to true, else set remapResults to false to avoid the overhead of metadata introspection. ·         resultsetType  - To specify the resultSetType of the SQL statement. It can either be: FORWARD_ONLY: cursor may move only forward SCROLL_INSENSITIVE: cursor is scrollable but generally not sensitive to changes made by others SCROLL_SENSITIVE: cursor is scrollable and generally sensitive to changes made by others Note that resultSetType is generally not required and that different JDBC drivers may behave differently using the same resultSetType setting (e.g. Oracle does not support SCROLL_SENSITIVE). fetchSize - Sets the fetchSize on the SQL statement that will be executed. It gives the JDBC driver a hint to do pre fetching in order to minimize round-trips to the database server. timeout - Sets the JDBC query timeout for this statement. Any value specified here will override the value specified in the “defaultStatementTimeout” setting in the SQLMapConfig.xml file. If you specify a default timeout and decide that you don't want a timeout for a particular statement, set the timeout value to 0. The specified value is the number of seconds the driver will wait for a statement to finish. Note that not all drivers support this setting. ·         Learning’s o   When running functions should not specify integer (0) values to Nullable fields. o   When running functions should not set nullValue=true option. o   When running functions if need to pass null value we should definitely have the property nullValue = true. o   When we use resultSet as java.util.HashMap, it caches the results, which creates a mess of results for multiple calls with different parameters where different result is expected. To avoid this set remapResultSet = true to not cache the results. Ibatis XML files will not parse the sql comments (--) , they gives unrelated errors if your query in ibatis xml file contains these comments. Happy Learning!