iBATIS - The Beauty of Simplicity

When I was involved in a project for a Strength of Materials Certification Laboratory for my University, the solution included open source frameworks as Struts as presentation tier. but I had a doubt of connection tier.

The good point is Data model, tables, data, exist already, SQL queries I was going to be help for a student of last year, even more I could use some and I needed a simple solution, so EJB is discard immediately, Hibernate sound good (I reckon is the Object related tool on web today, but I need something simple and clean) then I remember I heard iBATIS as a easy-fast productivity SQL Mapper framework.

iBATIS gives all what I need, centralized management, easy of implement writing down all the SQL queries I need, linking them in a ResultSet (and JavaBean) and another doing the JSP interface (actually the html:form tags).



Thus the code could be reduce a lot, not doing those JDBC statement (mention above), besides I made a simple Java code to help writing down SQL maps and JavaBean (well, later on I knew there is Perl tool that do this better with DDL from the database: http://alxeg.narod.ru/ibatis/index.html . However my tool worked parsing , so it can read the name of the fields and make the JavaBean and XML; (this reduce even more the coding).

Another parameter to consider was iBATIS born mature :) because after sun shows Jpetstore with better performance than Microsoft and release the interesting framework layer including SQL Maps and DAO.

Finally, the iBATIS web scene was mature enough to get support for any issue on it. Plus the development team is still working hard enough to get more update version, solving some unexpected issues (you never know and it’s better to have some backup).
iBATIS mapped framework according to ibatis.apache.org, it has two important features, SQL maps and DAO -the last one we are going only mention because mapping is our main issue-

SQL Maps makes development much easier, and the maintenance too.

The Architecture of iBATIS is:

Centralized SQL Mappings inside XML files, this way is easy to maintenance, flexible , scability, fix problems. Every java programmer knows how tedious is to code many times JDBC connection, making the code larger, more complex to read and worse to maintain and scability.



figure 1, taken from http://ibatis.apache.org/

architecture of iBATIS










<-- figure 2 --> data model of the example: entities (client, application_form, application_form_detail).

Here we configure the database properties (too redundant? :P)database.properties:
driver=org.firebirdsql.jdbc.FBDriver
url=jdbc:firebirdsql:localhost/3050:C:/development/database/SAMPLEDBS.GDB
username=administrator
password=development2006

This file contains all the XML with SQL Maps
sql-map-config.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>

<properties resource="database.properties" />

<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}"/>
</dataSource>
</transactionManager>

<sqlMap resource="TmpIndicesSQL.xml" />
<sqlMap resource="DetalleSolicitudSQL.xml" />

</sqlMapConfig>

An example of SQL Map, showing how to make ResultSet, INSERT, SELECT and UPDATE statement, with dynamic concatenation of condition in the statement (AND/OR in the WHERE).
DetalleSolicitudSQL.xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="DetalleSolicitudSQL">

<cacheModel id="detallesolicitud_cache" type="MEMORY" >
<flushInterval hours="24"/>
<flushOnExecute statement= "insertDetalleSolicitud" />
<property name="reference-type" value="WEAK" />
</cacheModel>

<resultMap id="detallesolicitud_result" class="cl.unab.dicemat.utils.facade.DetalleSolicitud">
<result property="detsol_descripcion" column="x_detsol_descripcion" />
<result property="detsol_cantidad" column="x_detsol_cantidad" />
<result property="ens_cod_tipo" column="x_ens_cod_tipo" />
<result property="solcer_id" column="x_solcer_id" />
</resultMap>

<statement id="insertDetalleSolicitud">
INSERT INTO detalle_solicitud ( detsol_descripcion, detsol_cantidad, ens_cod_tipo, solcer_id )
VALUES ( #detsol_descripcion#, #detsol_cantidad#, #ens_cod_tipo#, #solcer_id# )
</statement>

<statement id="viewDetalleSolicitud" resultMap="detallesolicitud_result" cacheModel="detallesolicitud_cache">
SELECT detsol_descripcion AS x_detsol_descripcion,
detsol_cantidad AS x_detsol_cantidad,
ens_cod_tipo AS x_ens_cod_tipo,
solcer_id AS x_solcer_id
FROM
detalle_solicitud
</statement>

</sqlMap>
with a select to get a autoincrement key for Firebird database (also there is commented examples for PostgreSQL, in (2) you can get more code to different engines). This features that iBATIS has implemented for most DB engines, but still aren't 100% solve in the current time, however I could solved doing another query, leaving atomized and thread-safe. (I can show the example with Firebird). The good thing was the flexibility iBATIS has to go through this problem.

IndiceSQL.xml:

<resultMap id="tmpindices_result" class="cl.unab.dicemat.utils.facade.TmpIndices">
<result property="tabla" column="x_tabla" />
<result property="obra_id" column="x_obra_id" />
</resultMap>

<!--obra_id y tabla hacen solo de nombres genericos -->

<statement id="viewTmpIndices" resultMap="tmpindices_result" cacheModel="tmpindices_cache">
SELECT gen_id(GEN_SOLICITUDCERTIFICADO, 1) as x_obra_id,
rdb$$relation_id as x_tabla
FROM
rdb$$database
</statement>
SolicitudCertificadoAction.java snippet
The simple method dao.insertSolicitudCertificado(solicitudCertificadoDTO); insert the data in the javabean. Using beanutils commons.

SolicitudCertificadoForm.java, dynaform can be used, however I prefer to use normal JavaBean thus I can maintain the kind of data (int, String, Date).


Conclusion:

We finally are in the last stages of the project doing QA, the SQL Mapping was a success, I saved a lot of time though this framework. Getting a simpler and cleaner code, the performance of the application.


Bibliography:

iBATIS Home- ibatis.apache.org
iBATIS Wiki- http://opensource.atlassian.com/confluence/oss/display/IBATIS/Home
iBATIS mailing-list