Spring + ibatis: calling a sql procedure

This post is a sample of a procedure call from ibatis, when we're under the spring influence!

This is the ibator generator xml. Used with the eclipse plugin.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE ibatorConfiguration PUBLIC "-//Apache Software Foundation//DTD Apache iBATIS Ibator Configuration 1.0//EN" "http://ibatis.apache.org/dtd/ibator-config_1_0.dtd" >
<ibatorConfiguration >
 <classPathEntry location="C:\eclipse\extra\BDD\SQLServer\sqljdbc_2.0\esn\sqljdbc4.jar" />
 <ibatorContext id="test" >
     <jdbcConnection driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver" connectionURL="jdbc:sqlserver://...:1433;databaseName=...." userId="..." password="..."/>
     <javaModelGenerator targetPackage="es.test.model.WebProd" targetProject="test.negocio" />
     <sqlMapGenerator targetPackage="es.test.dao.WebProd.sqlMap" targetProject="test.negocio" />
 <daoGenerator targetPackage="es.test.dao.WebProd" targetProject="test.negocio" type="SPRING" />
 <table schema="dbo" catalog="WebProd" tableName="aliasCuentas">
   <property name="useActualColumnNames" value="true"/>
 </table>    
  </ibatorContext>
</ibatorConfiguration>

To the generated code of ibator, just add the next items:

In WebProd_dbo_aliasCuentas_SqlMap.xml:

<parameterMap id="getAccountsCall" class="map">
 <parameter property="titular" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
 <parameter property="permiso" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
</parameterMap>
<resultMap id="cuentaAlias" class="es.test.model.WebProd.Aliascuentas">
 <result property="aliasCuenta" column="aliasCuenta" />
 <result property="idCuenta" column="idCuenta" />
</resultMap>
<procedure id="getAccounts" resultMap="cuentaAlias" parameterMap="getAccountsCall">
 {call WebProd.dbo.sp_web_getCuenstasUsuarioAlias(?, ?)}
</procedure>

In AliascuentasDAO:

List<es.test.model.WebProd.Aliascuentas> selectCuentas(Map example);

In AliascuentasDAOImpl:

public List<es.test.model.WebProd.Aliascuentas> selectCuentas(Map example) {
 List<es.test.model.WebProd.Aliascuentas> list = getSqlMapClientTemplate().queryForList("WebProd_dbo_aliasCuentas.getAccounts", example);
 return list;
}

Now, the configurations files of spring and the java source of the call.

In applicationContext*.xml:

<bean id="aliascuentasDAO" class="es.test.dao.WebProd.AliascuentasDAOImpl">
 <property name="sqlMapClient">
  <ref bean="sqlMapWebProd"/>
 </property>
</bean>
<bean id="aliasServicio" class="es.test.miGestion.administracionDatos.service.AliasCuentasServiceImpl">
 <property name="aliascuentasDAO">
  <ref bean="aliascuentasDAO"/>
 </property>
</bean>

And finally the AliasCuentasServiceImpl.java:

public List<Aliascuentas> getCuentas(String usuario) {
 log.debug("getCuentas: inicio. usuario:" + usuario);
 List<Aliascuentas> cuentasList;

 Map<String, String> map = new HashMap<String, String>();
 map.put("titular", usuario);
 map.put("permiso", "Consultas");
 cuentasList = aliascuentasDAO.selectCuentas(map);
 log.debug("getCuentas: fin");
 return cuentasList;
}

That's all!.

Developing Portlets using Eclipse. Part II

Developing Portlets using Eclipse. Part II

As I told you, the architecture that I use for these post, has iBATIS for it's persistence layer.

The main reason for use this framework was that iBatis give you the flexibility of write your own optimized sql code, and an easy way to call directly to a sql procedure. Not to mention, the existence of iBator, the code generator for iBatis :)

Spring will be a pivotal piece for the application, the reason for use of this framework it's as simple as I want to sleep as a baby. Some of the things that I want accomplish with spring is to have transparency over my data-source connection, jms..,, to make uncouple implementations from each other...

Well, the first interaction between ibatis and spring it's done in the configuration files, the next example show you how to make a configuration that also makes the application container independent.

Extract from applicationContext.xml:

<!-- obtain datasource  -->   
<jee:jndi-lookup id="dataSourceWebProd" jndi-name="jdbc/webProd" cache="true" resource-ref="true" lookup-on-startup="false" proxy-interface="javax.sql.DataSource"/>

<!-- map iBatis to datasource  -->   
<bean id="sqlMapBaseWeb" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
 <property name="configLocation"><value>classpath:/config/sqlmap-configWebProd.xml</value></property>
 <property name="dataSource"><ref bean="dataSourceWebProd" /></property>
</bean> 

<bean id="aliascuentasDAO" class="es.struts2PortletTemplate.dao.WebProd.AliascuentasDAOImpl">
 <property name="sqlMapClient">
  <ref bean="sqlMapWebProd"/>
 </property>
</bean>
<bean id="informesServicio" class="es.struts2PortletTemplate.miGestion.informes.service.InformesServiceImpl">
 <property name="aliascuentasDAO">
  <ref bean="aliascuentasDAO"/>
 </property>
</bean>

sqlmap-configWebProd.xml:

<settings cachemodelsenabled="true" enhancementenabled="true" usestatementnamespaces="true">
    <sqlmap resource="es/struts2PortletTemplate/dao/WebProd/sqlMap/WebProd_dbo_aliasCuentas_SqlMap.xml">
</sqlmap></settings>

And of course, you may well decide to use the Spring Framework's declarative transactions offer...

<bean id="txManagerWebProd" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
 <property name="dataSource" ref="dataSourceWebProd"/>
</bean>
<tx:advice id="txAdviceWebProd" transaction-manager="txManagerWebProd">
 <tx:attributes>
  <tx:method name="get*" read-only="true"/>
  <tx:method name="find*" read-only="true"/>
  <tx:method name="view*" read-only="true"/>
  <tx:method name="save*" propagation="REQUIRED"/>
  <tx:method name="*" propagation="REQUIRED"/>
 </tx:attributes>
</tx:advice>