elstar IT

Fullstack | Java | Tech Speaker | Tech Coach | Frank van der Linden

  • About me
  • Blog license
  • My Open source projects

Java Tip: post Clob data to stored procedure in Oracle with Spring Framework

11-02-2014 no responses development

Currently I am very busy with a new Java webapp project. One of the specifiactions was to collect XML data and store it via an Stored Procedure in a Oracle table. The XML data can be very large so we should use CLOB (Character Large Object ). For details, see http://en.wikipedia.org/wiki/CLOB.

The customer is using for all there Java web applications JBoss as the Java Server. As a framework, they have chosen for Spring Framework. It is not bad as a framework, you will get lots of usefull scaffolding, but as almost every framework there are also some annoying parts.

One of the  annoying parts is the Oracle Connections, Spring is using lots of wrapper classes around the native Oracle Connections. 

After some trial and error I found a fairly simple solution.

In the ApplicationContext.xml, the place where the beans for the webapp are specified, I specified 2 new beans. One of the beans points to the nativeJdbcExtractor. In the oracleLOBHandler I make reference to the other bean.

<!– LOB Handler for handling CLOB/BLOB data –>
<bean id=”oracleLOBHandler” class=”org.springframework.jdbc.support.lob.OracleLobHandler”>
 <property name=”nativeJdbcExtractor” ref=”jbossNativeJdbcExtractor” />
</bean>
<!– Native JDBC Extractor for LOB Handler. This is used to get the innermost connection object. –>
<bean id=”jbossNativeJdbcExtractor” />

In the end my DAO class looks a little bit like below ( I removed customer related code and the imports  )

public class UpdateRecordStoredProcedure extends StoredProcedure
private final String storedProcName = “pck_object.verwerk_clob”;
     @Autowired
     private JdbcTemplate jdbcTemplate;
    
     public UpdateCarsStoredProcedure(final DataSource dataSource) {
         declareParameter(new SqlParameter(“p_clob_bericht”, Types.CLOB));
         declareParameter(new SqlOutParameter(“p_err_msg”, Types.VARCHAR));
         setFunction(false);
         setSql(storedProcName);
         setDataSource(dataSource);
        }
        
public String updateRecord(String xml){
String error = “no error”;
OracleLobHandler lh = new OracleLobHandler();
       lh.setNativeJdbcExtractor(new SimpleNativeJdbcExtractor());
Map<String, Object> params = new HashMap<String, Object>();
       params.put(“p_clob_bericht”, new SqlLobValue(xml, lh));
       
       Map<String, Object> outparams = super.execute(params);
       if (outparams.get(“p_err_msg”) != null) {
        error = outparams.get(“p_err_msg”);
       }
       
       return error;
        }
}

The bold parts in the code above do the trick, to get great chuncks of XML data in an Oracle Table

Tags: development, java, Oracle, spring

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Honored to be speaking at Engage »

Contact me

My name is Frank van der Linden and I am an independent software developer based in the Netherlands. The last 2 years I was awarded as IBM Champion. Also I am on the board of OpenNTF. My specialisations are Java, Web development and Domino.


If you want to hire me, please fill in the Contact form


IBM Champion web badge
Apache Logo

All the code on this blog are under the Apache License 2.0. For more details, see Apache License 2.0

Most recent posts

  • NL Portal, a open source project
  • 10th anniversary as a freelancer and more…
  • Engage 2020: Hello are you listening, There is stream for everything
  • Spring Cloud Function on Azure run locally
  • Deploy Spring Cloud Function to IBM Cloud

Latest reactions

  • Spring Cloud Function on Azure run locally - elstar IT on Deploy Spring Cloud Function to IBM Cloud
  • flinden68 on Quick XPages tip: add Fullcalendar plugin to your application
  • Rajesh samal on Quick tip: Swagger support for Spring Webflux
  • dsieyx on Quick XPages tip: add Fullcalendar plugin to your application
  • John on Named as IBM Champion 2019

Archive

  • November 2024
  • April 2024
  • March 2020
  • February 2020
  • January 2020
  • October 2019
  • September 2019
  • June 2019
  • May 2019
  • April 2019
  • March 2019
  • January 2019
  • December 2018
  • October 2018
  • September 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • October 2017
  • July 2017
  • June 2017
  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015
  • December 2014
  • October 2014
  • September 2014
  • August 2014
  • July 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014

Category

  • bluemix
  • business
  • cloudant
  • community
  • development
  • hrassistant
  • openntf
  • running
  • salesforce
  • Springboot
  • Tesla
  • trailrunning
  • Uncategorized
  • watson
  • OpenNTF
  • Collaboration Today
  • XSnippets
  • Stackoverflow
  • IBM Collaboration Solutions
  • Social Business Toolkit
  • About me
  • Dutch curriculum vitae
  • English curriculum vitae
  • Google+
  • LinkedIn profile
  • Twitter
  • Slideshare
  • Blog license
  • My open source projects