Tuesday, November 25, 2008

Batch insert/update DB - Use XML as you data...

Today we have many strange requirements because of latest developments in the IT market. We are having tons of data to be inserted in databases or files etc.Mainly for DB operation, we can use batch insert/update, which calls DB for multiple rows of params.

This is not an excellent thing, but i think you should take care of this.Just think that if you are having 100 rows to be inserted in one table, and you make batch update call from you java code.How much time and resources it will take?We just can't answer this question.Yeah, we can't but we have different solution here.

Instead of making batch params data, you make and XML data using XSLT transformation or Castor mapping.We will see what these both things mean later. Today every Database server supports XML parameters as an input to Stored procedure/Function.You can pass your XML to the SP, and then handle the XML traversal logic inside the SP.You are done!

You just need to,

1.Create the XML data for Input param.
2.Pass the XML,execute the SP.
3.SP will handle the XML data and do the processing fetching the data from XML(Using Xpath traversal)
4.SP also can return resultset data as an XML content, which you can again transform map to Object.


XML Transformation: is done by javax.xml.Transform package, which takes one XML as input,Used XSLT file to transform input XML to XML format as defined by the XSLT.


Castor Mapping:is done by third party open source jar, which maps Object to XML and XML to object.So you can directly get XML/Object using this.