SoftONELogic Alliance Network

The idea came from DotNetNuke design pattern, based on experience on DNN based project, SoftONE Logic’s team try to find the easier way to use Stored Procedure on the Spring JDBCTemplate . SoftONE Logic’s team has used iBatis to call stored procedure. Due to the ‘complexity’ of XML, the team start to do research to find other way to implement it.

Here are the outcome of the research.

Get Object from DB

Create Stored Procedure named hr_GetRequisitionForm, has one input parameter.

USE [hr]
    GO
SET ANSI_NULLS ON
    GO
SET QUOTED_IDENTIFIER ON
    GO
CREATE procedure [dbo].[hr_GetRequisitionForm]
    @id int
as
    select
*
    from hr_vw_RequisitionForm
where
   id = @id

Create Method to call above stored procedure by using Spring JDBC Template.

public RequisitionFormVO getRequisitionDataByRequisitionId(int id){
     String queryString = "{call hr_GetRequisitionForm(?)}";
     Object args []= new Object[] {id};
     return (RequisitionFormVO)jdbcTemplate.queryForObject(queryString, args, new RequisitionFormMapper());
}

I assume reader has already familiar with Spring JDBC Tempalte. The hr_GetRequisitionForm stored procedure has one input parameter (id). Pass the value of id to the stored procedure with hr_GetRequisitionForm(?) statement, and Object args []= new Object[] {id}; . Cast the returned object from jdbcTemplate and use the method queryForObject as shown below:

(RequisitionFormVO)jdbcTemplate.queryForObject(queryString, args, new RequisitionFormMapper());

Get List from DB

Create Stored Procedure named hr_GetApprovedRequisitionForm

USE [hr]
   GO
SET ANSI_NULLS ON
   GO
SET QUOTED_IDENTIFIER ON
   GO
CREATE procedure [dbo].[hr_GetApprovedRequisitionForm]
   as
SELECT
   *
FROM
   hr_vw_RequisitionForm
WHERE
   HRManagerVerifiedStatus = 'verified' AND
   SGManagerApprovedStatus = 'approved' AND
   ManagingDirectorApprovedStatus = 'approved'

Create Method to call above stored procedure by using Spring JDBC Template.

public List getApprovedRequisitionData(){
     String queryString = "{call hr_GetApprovedRequisitionForm}";
     return jdbcTemplate.query(queryString, new RequisitionFormMapper());
}

The MapperClass is to map the Database column with ValueObject class.

package com.softonelogic.project.smoe.hr.utils.persistence.sqlmapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.softonelogic.project.smoe.hr.utils.vo.RequisitionFormVO;
public class RequisitionFormMapper implements RowMapper {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
     RequisitionFormVO obj = new RequisitionFormVO();
     obj.setId(rs.getInt("ID"));
     obj.setPositionVacant(rs.getString("PositionVacant"));
     ...
     return obj;
     }
}