Archive for the “Spring Framework” Category

Calling MS SQL Server 2005 Stored Procedure from Spring JDBCTemplate

 

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.

  •     DB Server : MS SQL Server 2005
  •     Spring Framework 2.5 (JDBCTemplate features).
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;
     }
}

 

Sep 6, 2009 Posted Under: Java Enterprise Edition, Spring Framework   Read More

Role based implementation in RichFaces with Spring Security

 

This article will explain how to implement role based system in the user control panel page. The login process is required to enter the page. The authentication process will be handled by the Spring Framework. I assume the reader understand how the Spring Security authentication works.
 
In the RichFaces page:
Page contain list of menu by implementing <rich:toolBar/> RichFace tag. The menu will be rendered based on user credential. The rendering process will be controled by bean class via rendered parameter in <rich:toolBar/> tag.
 
<html xmlns="http://www.w3.org/1999/xhtml"
    xmlns:ui="http://java.sun.com/jsf/facelets"
    xmlns:h="http://java.sun.com/jsf/html"
    xmlns:f="http://java.sun.com/jsf/core"
    xmlns:rich="http://richfaces.org/rich">

…

<rich:toolBar itemSeparator="line">
     <rich:toolBarGroup rendered="#{managerControlBean.menuSubscriber}">
          <rich:dropDownMenu value="Subscriber">
               <rich:menuGroup value="Manage Subscription" direction="right">
                    <rich:menuItem submitMode="ajax" value="Current Subscription">
                    </rich:menuItem>
                            …
                </rich:dropDownMenu>
            </rich:toolBarGroup>
…

In the bean class:

Declare the property of menuSubscriber in the class as a boolean type.

private boolean menuSubscriber = false;

Declare a final string type for the ROLE type. User can declare more than 1 role type, please ensure the role type in the bean class is the same as the role type declared in database.

private final String role_subscriber = "ROLE_SUBSCRIBER";

In the <rich:toolBar/> the rendered parameter will get the value of menuSubcriber property via isMenuSubcriber() method

public boolean isMenuSubscriber() {
        this.menuSubscriber = validateAuthorization(role_subscriber);
        return menuSubscriber;
}

Method isMenuSubcriber() will call method validateAuthorization(String) to validate the user credential. Spring Framework method is used to validate the user credential.

private boolean validateAuthorization(String authorizationString){
        boolean authStatus = false;
       
        SecurityContext securityContext = SecurityContextHolder.getContext();
        if(securityContext != null){
            Authentication authentication = securityContext.getAuthentication();
           
            GrantedAuthority[] authorities = authentication.getAuthorities();
           
            for(GrantedAuthority authStr : authorities){
                if((authStr.toString()).compareToIgnoreCase(authorizationString) == 0){
                    authStatus = true;
                }
            }
        }
        return authStatus;
    }

 

Aug 30, 2009 Posted Under: JavaServer Faces, Spring Framework   Read More