Java Buzz Forum - iBATIS - Where have you been all my life!

iBATIS SQL Maps is an open-source JDBC framework that provides a very simple and flexible means of moving data between your Java objects and a relational database. The SQL Maps framework helps reduce the amount of Java/JDBC code that is needed to access a relational database. The framework allows you to map JavaBeans to SQL statements using a very simple XML descriptor that allows you to create complex queries, and inner/outer joins. The beauty of it all is that this is achieved without any special database tables, bytecode manipulation, or code generation.

iBATIS is not meant to replace Object-Relational Mapping (ORM) tools such as Hibernate, OJB, Entity beans or TopLink to name a few. Rather it is a low-level framework that allows you to directly hand-code your SQL statements and map them to Java object. Once you map your persistence layer to your object model, you are all set. You don't need to lookup DataSource, get connections, create prepared statements, parse ResultSet or even cache the results – iBATIS does it all for you. Under the covers, iBATIS creates a PreparedStatement, sets the parameters (if any), executes the statements and builds a Map or JavaBean object from the ResultSet. If the SQL statement was an insert or an update, the numbers of rows affected by the SQL are returned. Here's a little sample to illustrate the features of iBATIS: We'll start with a simple database that ships with the JpetStore application from iBATIS.

First you configure SQL Maps by creating a XML configuration file, which provides configuration details for DataSources, SQL Maps and other options like thread management. Here is a simple example of the SQL Map configuration file:

<?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> 
 
    <settings cacheModelsEnabled="false" enhancementEnabled="true" lazyLoadingEnabled="true" 
        maxRequests="32" maxSessions="100" maxTransactions="100" useStatementNamespaces="false"/> 
 
    <transactionManager type="JDBC"> 
        <dataSource type="JNDI"> 
            <property name="context.java.naming.factory.initial" value="weblogic.jndi.WLInitialContextFactory"/> 
            <property name="context.java.naming.provider.url" value="t3://localhost:7001"/> 
            <property name="DataSource" value="jdbc/jpetstoreDS"/> 
        </dataSource> 
    </transactionManager> 
 
    <sqlMap resource="com/j2eegeek/ibatis/dao/maps/Account.xml"/> 
 
</sqlMapConfig>

In my case, I am deploying this code in WebLogic and I have already created a connection pool and a datasource on the WebLogic side. I am just referring to the name of the datasource by using jdbc/jpetstoreDS. You can also create your own datasource via. the SQL Map configuration file:

<transactionManager type="JDBC"> 
    <dataSource type="SIMPLE"> 
        <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/> 
        <property name="JDBC.ConnectionURL" value="jdbc:mysql:///localhost"/> 
        <property name="JDBC.Username" value="user"/> 
        <property name="JDBC.Password" value="password"/>  
         
        <!-- optional elements include --> 
        <property name="Pool.MaximumActiveConnections" value="10"/> 
        <property name="Pool.MaximumIdleConnections" value="5"/> 
        <property name="Pool.MaximumCheckoutTime" value="120000"/> 
        <property name="Pool.TimeToWait" value="10000"/> 
        <property name="Pool.PingQuery" value="select * from dbname.tablename"/> 
        <property name="Pool.PingEnabled" value="false"/> 
        <property name="Pool.PingConnectionsOlderThan" value="0"/> 
        <property name="Pool.PingConnectionsNotUsedFor" value="0"/> 
    </dataSource> 
</transactionManager>

The SQL Map configuration file includes a reference to another SQL Map file that contains your SQL statements for insert, update, delete as well as results and parameter mapping. Here is a simple example of the file Account.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="Account"> 

     <typeAlias alias="account" type="com.j2eegeek.ibatis.domain.Account"/> 
 
    <resultMap id="accounts" class="account"> 
        <result property="userID" column="userid"/>
         <result property="email" column="email"/> 
        <result property="firstName" column="firstname"/> 
        <result property="lastName" column="lastname"/> 
    </resultMap> 
 
    <select id="getAccount" resultClass="accounts" parameterClass="java.lang.Integer"> 
         SELECT          
         userid, email, firstname, lastname          
         FROM jpetstore.account          
         where          
         userid = #value#      
    </select> 
 
</sqlMap>

The Account.xml SQL Map file is pretty self-explanatory -- In the file, we are describing a select statement that takes an Integer as it's argument and returns an instance of com.j2eegeek.ibatis.domain.Account. Insert, updates, deletes work the same way along with stored procedures and dynamic queries. The programming API for SQL Maps is really straightforward and provides the developer with the ability to: configure an SQL Map, execute an SQL update (including insert and delete), execute a query for a single object, and execute a query for a list of objects.

 
        SqlMapClient sqlMapClient = null; 
 
        try { 
            String sqlMapConfigFile = "sql-map-config.xml"; 
            Reader sqlMapConfigReader = Resources.getResourceAsReader(sqlMapConfigFile); 
            sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(sqlMapConfigReader); 
 
        } catch (IOException e) { 
            log.error(e); 
        } 
 
        try { 
            Account myAccount = (Account) sqlMapClient.queryForObject("getAccount", new Integer(1234)); 
            log.debug("myAccount.getFirstName() = " + myAccount.getFirstName()); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
            log.error(e.toString()); 
 
        }

A pretty simple example but it should illustrate the simplicity of iBATIS SQL Maps and show you the potential of this framework. SQL Maps takes away all of the work required to create Statements, validate and parse inputs, create and parse ResultSets and all of the nitty-gritty details of working with SQL by hand. Instead, you are working at the object level and not really worrying about how your data is stored or retrieved. I've found that this also enables a good separation of work where your 'data-guy' can create up the appropriate SQL statements for you and you just plug them in, assuming you have a 'data-guy'. I've found that SQL Maps really helps me in my development process (In most cases, I am working with existing databases) as I spend time looking at the data-model as part of my overall design process and will typically mock-up the SQL statements I am going to use to manipulate the data at hand. Now I can just take my SQL statements and plug them into the SQL Map XML files and half my app is already built.

iBATIS SQL Maps is really powerful and you can take this one step further by using Spring's DAO framework in conjunction with iBATIS. Using Spring's DAO framework and the iBATIS template classes provided by Spring, you will write even less code that you would have normally written using iBATIS by itself.


	Web Artima.com