By kswaughs | Monday, October 10, 2016

Spring Stored Procedure CLOB OUT Example

This example shows how to read CLOB data returned from Stored Procedure using Spring.

Step 1: Oracle Stored Procedure Setup

Stored Procedure
create or replace PROCEDURE GETARTICLE 
(
  IN_ARTICLE_ID IN NUMBER,  
  OUT_ARTICLE_NAME OUT VARCHAR2,  
  OUT_ARTICLE_CONTENT OUT CLOB  
) AS 
BEGIN
  SELECT ARTICLE_NAME , ARTICLE_CONTENT
  INTO OUT_ARTICLE_NAME, OUT_ARTICLE_CONTENT
  from  ARTICLES WHERE ARTICLE_ID = IN_ARTICLE_ID;
END GETARTICLE;

Step 2: Implement SqlReturnType

Create a new class that implements org.springframework.jdbc.core.SqlReturnType to read CLOB data and convert it into String object.

CLOBToStringConverter.java
package com.kswaughs.util;

import java.io.IOException;
import java.io.Reader;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.SQLException;

import org.springframework.jdbc.core.SqlReturnType;

public class CLOBToStringConverter implements SqlReturnType {

    @Override
    public Object getTypeValue(CallableStatement cs,
            int paramIndex, int sqlType, String typeName) throws SQLException {
        
         Clob aClob = cs.getClob(paramIndex);

         final Reader clobReader = aClob.getCharacterStream();
         
         int length = (int) aClob.length();
         char[] inputBuffer = new char[1024];
         final StringBuilder outputBuffer = new StringBuilder();
         try {
            while ((length = clobReader.read(inputBuffer)) != -1)
             {
             outputBuffer.append(inputBuffer, 0, length);
             }
        } catch (IOException e) {
            throw new SQLException(e.getMessage());
        }
         return outputBuffer.toString();
    }
}

Step 3: DAO Implementation

Write a DAO class that extends org.springframework.jdbc.object.StoredProcedure. While declaring the OUT parameters in constructor, pass the above CLOBToStringConverter as parameter to SqlOutParameter.

ArticleDAO.java
package com.kswaughs.dao;

import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

import com.kswaughs.util.CLOBToStringConverter;

public class ArticleDAO  extends StoredProcedure {
    
    public ArticleDAO(final JdbcTemplate jdbcTemplate) {

        super(jdbcTemplate, "GETARTICLE");
        
        // Input Parameters
        declareParameter(new SqlParameter("IN_ARTICLE_ID", Types.NUMERIC));
        
        // Output Parameters
        declareParameter(new SqlOutParameter("OUT_ARTICLE_NAME", Types.VARCHAR));
        declareParameter(new SqlOutParameter("OUT_ARTICLE_CONTENT", Types.CLOB, 
             null, new CLOBToStringConverter()));
    }
    
    
    public String getArticleContent(final String articleID) throws Exception {
        
        Map returnMap = null;
        
        Map inParams = new HashMap();
        inParams.put("IN_ARTICLE_ID", articleID);
    
        Map returnMap = super.execute(inParams);
        
        if(returnMap== null || returnMap.isEmpty()){
            
            throw new Exception("Article Not found") ;
        }
        
        String articleContent = String.valueOf(returnMap.get("OUT_ARTICLE_CONTENT"));
        return articleContent;
    }    
    
}

Common Mistakes

If we pass typeName as Empty String to SqlOutParameter as below,

 declareParameter(new SqlOutParameter("OUT_ARTICLE_CONTENT", Types.CLOB, 
        "", new CLOBToStringConverter()));

we will get below SQLException

Error occured while executing the Stored procedure. CallableStatementCallback; uncategorized SQLException for SQL [{call GETARTICLE(?, ?, ?)}]; SQL state [99999]; error code [17060]; Fail to construct descriptor: empty Object name; nested exception is java.sql.SQLException: Fail to construct descriptor: empty Object name at com.kswaughs.dao.ArticleDAO.getArticleContent.

Recommend this on


6 comments:

  1. Replies
    1. The effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Project Centers in Chennai for CSE.

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
      Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

      Delete

  2. You write this post very carefully I think, which is easily understand to me. Not only this, other post is also good. As a newbie this info is really helpful for me. Thanks to you.
    Tally Training in Chennai
    Tally ERP 9 Training
    tally classes
    Tally Training institute in Chennai
    Tally course in Chennai

    ReplyDelete
  3. Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
    Digital Marketing Course In Kolkata
    Web Design Course In Kolkata

    ReplyDelete