Effectively retrieving BLOB XML from database using Java

Question

I am trying to read XML file details from Sun Identity Manager database using below snippet

            String xmlTxt="";
            ArrayList<String> xmlList = new ArrayList<String>();
            BLOB blob=null;
            OracleConnection conn=null;
            OraclePreparedStatement stmt = null;
            OracleResultSet rs = null;
            GZIPInputStream gStream = null;
            log.debug("Initializing DB connection...");

                try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                conn=(oracle.jdbc.OracleConnection)DriverManager.getConnection(dbURL, dbUserName, dbPassword);           
                stmt = (OraclePreparedStatement) conn.prepareCall("SELECT XML FROM TASK WHERE TYPE='WorkItem'");
                rs = (OracleResultSet) stmt.executeQuery();

                    while(rs.next()){
                        blob = rs.getBLOB(columnLabel);
                    if(!blob.isEmptyLob())
                        gStream = new GZIPInputStream(blob.getBinaryStream());
                    if(gStream.available()>0)
                        xmlTxt = IOUtils.toString(gStream,columnEncoding);                          
                        xmlList.add(xmlTxt);
                    }
                }
                catch(ClassNotFoundException cnf){
                    //Handle errors for Class.forName
                    log.error("ClassNotFoundException " + cnf.getMessage());
                    log.error("ClassNotFoundException " + cnf);     
                }
                catch (SQLException se) {
                //Handle errors for JDBC
                log.error("SQLException " +se.getMessage());
                log.error("SQLException " + se);
                }
                catch (IOException ie) {
                //Handle errors for I/O
                log.error("IOException " +ie.getMessage());
                log.error("IOLException " + ie);
                }
                catch (OutOfMemoryError E){
                    log.error("OutOfMemoryError Encountered :"+ Runtime.getRuntime().totalMemory());
                }
                finally{
                    try {
                        if(rs!=null)
                        rs.close();
                        if(stmt!=null)
                        stmt.close();
                        if(blob!=null)
                        blob.free();
                        if(conn!=null)
                        conn.close();
                        } 
                    catch (SQLException se) {
                        //Handle errors for JDBC
                        log.error("SQLException during close " +se.getMessage());
                        log.error("SQLException during close" + se);
                        }
                }
                log.debug("End of DB Operation");               
            return xmlList;

Currently I am testing it in our Dev environment where the records are in 100's and average size of each blob data is 2 MB. Also I could see consumption of heap size is huge when there is steady increase in total records. My current Java heap size is Xmx512m -Xms64m and in prod we have nearly 1.5 million records to process so wondering how much heap size would be required.

I usually get out of memory error for below line

xmlTxt = IOUtils.toString(gStream,columnEncoding);

https://commons.apache.org/proper/commons-io/apidocs/org/apache/commons/io/IOUtils.html

Please advise if there are any other better way we can optimize the code to improve performance.

You're streaming the entire contents of your database table into memory. So, one way to optimize your code would be to, um, not do that. — Robby Cornelissen, Feb 12 '16 at 05:29
To answer your question about how much heap space you'll need in prod: I'm estimating it's gonna be somewhere around the 3000GB mark. — Robby Cornelissen, Feb 12 '16 at 05:34
Hmm.. But I have all my required data in the BLOB column only so have to retrieve that anyhow and process it with our business logic... — karthikeyan selvarajan, Feb 12 '16 at 08:13
It's not about the one BLOB. It's about storing potentially one and a half million of them in a list (`xmlList`). Can't you implement your logic in a streaming fashion? — Robby Cornelissen, Feb 12 '16 at 08:14
Thanks Robby. I am able to change my logic using streaming option for entire project and that has reduced the heap size to greater extent :-) — karthikeyan selvarajan, Feb 12 '16 at 11:51

score 0 · Answer 1 · answered Feb 12 '16 at 11:53

0

Parse the data from table in streaming fashion rather than copying it to memory as string/array list.

answered Feb 12 '16 at 11:53

karthikeyan selvarajan

11
3

Effectively retrieving BLOB XML from database using Java

1 Answers1