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 { MapreturnMap = 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.
The idea in this article is quite different and innovative. Thanks for this.
ReplyDeleteSAS Training in Chennai
sas training fees
sas course fees
SAS Training in Tnagar
SAS Training in Anna Nagar
clinical sas training in chennai
Placement Training in Chennai
soft skills training institutes in chennai
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.
DeleteSpring 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
I really admired with your post, do share more updates.
ReplyDeleteTally Training in Chennai
Tally course
Tally institute in Chennai
AngularJS course in Chennai
ccna Training in Chennai
PHP course in Chennai
Salesforce course in Chennai
Web Designing Training in Chennai
Tally Course in Velachery
ReplyDeleteYou 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
ReplyDeleteThis content of information has
helped me a lot. It is very well explained and easy to understand.
seo training classes
seo training course
seo training institute in chennai
seo training institutes
seo courses in chennai
seo institutes in chennai
seo classes in chennai
seo training center in chennai
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.
ReplyDeleteDigital Marketing Course In Kolkata
Web Design Course In Kolkata