Re: [eclipselink-users] The ScrollableCursor's issues

[dmitryerkin <dmitryerkin@xxxxxxxxxxxxx>]

Hi
I found the workaround which is really useful in case you are working with
XMLTYPE's data and using the scrollable cursor.
In this case you need to implement the custom class which extends the
Oracle11Platform class and override the getObjectFromResultSet method in
this way:

    public Object getObjectFromResultSet(ResultSet resultSet, int
columnNumber, int type, AbstractSession session) throws
java.sql.SQLException {

    	if (type == OracleTypes.OPAQUE) {

	    	Object result = resultSet.getObject(columnNumber);
	    	if(!(result instanceof OPAQUE)) {
	    	// Report Queries can cause result to not be an instance of OPAQUE.
	    	return result;
	    	}

	    	XMLType xml;
    		xml = (XMLType) result;
    		String xmlstr = xml.getStringVal();
    		xml.close();
    		return xmlstr;
    	}
    	else{
    		return super.getObjectFromResultSet(resultSet, columnNumber, type,
session);
    	} 	
    }

By doing this you are able to escape from very big memory leaks.

Regards,
Dmitry



dmitryerkin wrote:
> 
> Hi
> I am trying to use the ScrollableCursor for retrieving about 10.000
> records.
> I have to get them divided into small portions which include 100 records
> each.
> All works well except two issues.
> 
> Firstly I see that the size of memory which is used by JVM is being
> increased while the sequence of invocations of the cursor's next(100)
> method is happening although I want memory which was allocated for
> previous portions to be released.
> The total amount of used memory is about 1Gb for 10.000 records.
> 
> Secondly the transaction which is started to scroll through the cursor
> switches to rollback state after 5 minutes although a lot of invocations
> of the cursor's next(100) method are happening.
> 
> My questions are:
> 1. How can I decrease the size of memory which is used by Eclipselink to
> walk through the cursor?
> 2. Can I set the period of transaction's timeout?
> 
> 
> Here is my code:
> 
> 
> 
> The servlet:
> 
> @EJB ejb3.OebsInterface oebs;
> 
> ...
> 
> oebs.findOebsByTime(time);
> 
> int from = 10000;
> int quantity = 100;
> Vector<RootType> rootList = null;
> while((rootList = oebs.getOebsByTime(from, quantity)) != null){
> 	
> 	int i = 0;
> 	HeaderBaseType header;
> 	
> 	while(i < rootList.size()){
> 		header = rootList.elementAt(i).getHeader();
>                       ...
> 		i++;
> 	}
> 		
> 	from = from - quantity;
> }
> 			
> oebs.releaseOebsByTime();
> 
> 
> 
> The session bean:
> 
> @Stateful
> @TransactionManagement(TransactionManagementType.BEAN)
> public class OebsInterfaceImpl implements OebsInterface {
> 	
> 	@PersistenceContext(name="oebsEJB")
> 	EntityManager em;
> 
> 	@Resource javax.transaction.UserTransaction ut;
> ...
> 
> 	protected JpaEntityManager jpaEM = null;
> 	protected UnitOfWork uow = null;
> 	protected ScrollableCursor cursorByTime = null;
> 	
> 
> 	public void findOebsByTime(XMLGregorianCalendar time) throws
> NotSupportedException, SystemException{
> 		
> 		ExpressionBuilder oebs = new ExpressionBuilder();
> 	           Vector arguments = new Vector();
> 	           arguments.addElement("/o:root/o:header[o:timeOfCreation=\"" +
> time.toString() + "000+00:00\"]");
> 	          
> arguments.addElement("xmlns:o=\"http://www.rosbank.ru/oebs\"";);
> 		Expression where =
> oebs.getField("OBJECT_VALUE").getFunction(Oracle10Platform_Customizer.ExistsNodeWithNamespaces,
> arguments).greaterThan(0);
> 		
> 		ReadAllQuery queryByTime = new ReadAllQuery(OebsView.class, where);
> 		queryByTime.useScrollableCursor();
> 		
> 		ut.begin();
> 		
> 		jpaEM = org.eclipse.persistence.jpa.JpaHelper.getEntityManager(em);
> 		uow = jpaEM.getActiveSession().getActiveUnitOfWork();
> 		if(uow == null){
>     		       uow = jpaEM.getActiveSession().acquireUnitOfWork();
> 		}
> 		cursorByTime = (ScrollableCursor) uow.executeQuery(queryByTime);		
> 	}
> 
> 
> 	public Vector<RootType> getOebsByTime(int from, int quantity) throws
> JAXBException{
> 		
> 		Vector<RootType> rootList = null;
> 
> 		if(from <= cursorByTime.size()){
> 
> 			rootList = new Vector<RootType>();
> 
> 			cursorByTime.absolute(from);
> 			
> 			int q;
> 			if((cursorByTime.size() - from) + 1 >= quantity){
> 				q = quantity;
> 			}
> 			else{
> 				q = cursorByTime.size() - from + 1;
> 			}
> 			
> 			Vector<ejb3.OebsView> oebsList = new Vector<ejb3.OebsView>();
> 			oebsList = cursorByTime.next(q);
> 			System.out.println("oebsList.size is " + oebsList.size());    		
> 			
> 			JAXBContext jaxbContext =
> JAXBContext.newInstance("ru.rosbank.oebs:ru.rosbank.oebs.header:ru.rosbank.oebs.content.assignment:ru.rosbank.oebs.content.organization:ru.rosbank.oebs.content.qualifications:com.ibm.xmlns.prod.websphere.j2ca.jdbc.appsxx_assignment_v:com.ibm.xmlns.prod.websphere.j2ca.jdbc.appsxx_organization_v:com.ibm.xmlns.prod.websphere.j2ca.jdbc.appsxx_qualifications_v");
> 			Unmarshaller unmarshaller = jaxbContext.createUnmarshaller();
> 			JAXBElement<RootType> jaxbElement;
> 			RootType root;
> 	    	
> 			int i = 0;
> 			while(i < oebsList.size()){
> 				jaxbElement = (JAXBElement<RootType>) unmarshaller.unmarshal(new
> InputSource(new StringReader(oebsList.elementAt(i).getObjectValue())));
> 				root = (RootType) jaxbElement.getValue();
> 				rootList.add(root);
> 				i++;
> 			}
> 		}
> 		
> 		return rootList;
> 	}
> 
> 	public void releaseOebsByTime() throws SecurityException,
> IllegalStateException, RollbackException, HeuristicMixedException,
> HeuristicRollbackException, SystemException{
> 		uow.release();
> 		
> 		ut.commit();
> 	} 
> 
> This code workes in the Websphere container.
> 
> Regards
> Dmitry
> 

-- 
View this message in context: http://www.nabble.com/The-ScrollableCursor%27s-issues-tp19392081p19755649.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.