Introduction:

We recently had a requirement for loading large chunks on data from Oracle table into a background process using Java and ADF framework.  We were not allowed to use “straight” SQL and had to stick to ADF framework!  It took a little bit of time to understand how range paging worked – you see, it was simple to learn how to set the Access mode on a View Object but then it took a little longer to figure out how to use this feature in code. This document is an unpolished look at the lessons learned.

Jumping in:

Querying a lot of rows in a table using ADF View Objects requires a little understanding of how the “Access Mode” works.  There are some great articles online to get you some in depth knowledge on this topic [ See Avrom’s Java EE/Oracle ADF post on this topic and Steve Munech’s blog titled “What is the ADF View Object Range Paging Feature?”] … jump in and read those articles to get your head around what it means.

Lets assume you know what the Access modes are and have select “Range Paging” access mode.  Lets now assume that you have ‘n’ rows (say n=200,000) with a range size of 10,000 rows (I had to read 2 million rows into memory). You will be in essence jumping forward/iterating through ‘p’ pages of 10K rows, where p=20 (200K/10K).

You can see how you can play with the range size number to get to a sweet spot between the number of rows fetched vs number of pages to iterate over.

The rest is simple … you need to ask the framework for the number of pages and then for each page get the rows in range, map this to your data object and viola! Right? Well not quite. When I started using the “getEstimatedRangePageCount()” method, I enthusiastically put this into my loop conditional check – bad idea! Duh!

Lesson Learned: Do getEstimatedRangePageCount() only once.  This method goes through ALL the rows in and tries to figure out the number of pages. Depending on the size of your table … this method can take a while.

Once you begin iterating through the pages, you need to “scroll” to the right page.  I don’t know about you, but my loops always start with ‘0’ and goto “size-1” integer value. So instinctively I said “scrollToPage(p)” where “p=0 to pages-1”. Well not a good idea. … you see page numbers 0 and 1 were the same (check by printing the row values of first and last elements). I don’t know why but scroll to page (p+1) where p went from 0 to pages-1 worked.

Lesson Learned: Always “scroll to page (p+1)” where p goes from “0 to pages-1”

Now there was one other thing that I used to use when using other access mode with Read Only View Objects, I had learned to use the rovo.executeQuery() after binding my variables etc. Naturally, I thought that in the Range Paging mode – I had to do the same, i.e. iterate through the page, do rovo.executeQuery(), ask ADF to explicitly run the query and fetch the data. Actually it turns out,  I was wrong again! We simply needed to “scroll” to the right page and say getRowsInRange for an array of size matching the RangeSize we have specified.

Lesson Learned: No need to do rovo.executeQuery() – scroll to Page and getRowsInRange is enough to fetch the data

Putting it all together

Now lets put it all together. First build your Read only View Object (put in your query, bind what you need to etc) and then goto the “General” tab on the ROVO query and select the right Access Mode and set the Range Size. Here is an example:

Once you have the framework settings done you can code up the fetch in your application module as show below. I use an example that does not need to bind anything (fetches ALL rows) but you can simple bind before you do a getEstimatedPageRowCount.

Code Snippet To Load All Rows in a table into memory:

 9732    public List<MyRowObjectDTO> getAllRowsFromTable()
 9733    {
 9734      QueryAllReadOnlyImpl rovo = getQueryAllRowsReadOnlyRO1();
 9735      List<MyRowObjectDTO> myRowObjDTOList = new ArrayList<MyRowObjectDTO>();
 9736      long st = System.currentTimeMillis();
 9737      int totalPages = rovo.getEstimatedRangePageCount(); //how many pages?

 9739      for (int p = 0; p < totalPages; p++)
 9740      {
 9741        rovo.scrollToRangePage(p + 1);
 9742        Row[] rows = rovo.getAllRowsInRange();

 9744        for (int i = 0; i < rows.length; i++)
 9745        {
 9746          QueryAllReadOnlyRowImpl curRow = (QueryAllReadOnlyRowImpl) rows[i];
 9747          myRowObjDTOList.add(converEntityObjectToMyDTO(curRow));
 9748        }
 9749      }


 9752      return myRowObjDTOList;
 9753    }