Saturday 12 December 2009

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!.