Calling MS SQL Server 2005 Stored Procedure from Spring JDBCTemplate
Here are the outcome of the research.
- DB Server : MS SQL Server 2005
- Spring Framework 2.5 (JDBCTemplate features).
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;
}
}
.jpg)
dotnetnuke
asp.net