Breaking up of array

[agarwal_srushti]

Hi,
I have naerly 80,000 records in my database.
When user clicks on search it fetches the corresponding data in an array from database.
When it tries display the records on page it becomes really very slow.
I just display 20 records per page.
I want that when it gets data from database, it should get the first 20 records and then when user clicks on next page it should get the next 20 records.
Can anyone tell me how can this be implemented.
Need it urgently.

Thanks & Regards,
Srushti

[Supamagier]

Just use something like:

for (int i = START; i < START+20; ++i)
    list.add(record[i]);

[travishein]

I think the better solution is to only query what is needed to show on the given page for the given request. Consider the long term situation where there could be 1.0E10 records in the database, which to query all of them, but then only selectively filter out the 20 needed for the current page, and magnify that with a web application environment where each active user session could have its own local copy of the entire table contents, only to show the few records on one page, this could quickly exceed the amount of memory on the application server right.

for this, because it is reading from a database, would your dabase support a 'limit and offset construct' (postgresql, mysql, H2, hypesonic databases do)

and depending on the sql to java mapping utility (ibatis, hibernate, jdbc), you should be able to specify these constructs on where you do your 'getList()' .

For example,

mysql:

select * from a_table where something = true order by foo [B]limit 0, 20[/B]

select * from a_table where something = true order by foo [B]limit 20, 20[/B]

postgresql

select * from a_table where something = true [B]limit 20 offset 0[/B]

select * from a_table where something = true [B]limit 20 offset 20[/B]

It bothers me how the database specific sql comes into play, but this is workaroundable with
- using hibernate and specifying the database dialect. the hibernate query API has setLimit() setOffset() kinds of high-level constructs.
- using ibatis, loading different mapping files, such as with a property set in the spring framework application.properties used in the applicationContext.xml
- in your jdbc code where you query the jdbc metadata and have the switch() in your code.

the latter is the most unideal

the trick then is in your API where you query the database, change the getList() to something like getList(int startOffset, int itemsPerPage)

where you could even have the second parameter a system default, not needing to pass it in every time.

in my API I usually evolve a kind of standard ListQueryParam bean. (and all the getList() type of functions have one of these as their parameters. Which contains these start offset and items to get, but also fields for sorting the results and by what direction. Because the next thing is being able to have the list sorted. This works well, because many databases do not return consistent results from a limit..offset constructs unless there is an order by clause.

for example,

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

public class ListQueryParam implements Serializable {

  private static final long serialVersionUID = 1L;

  private int               limit;
  private int               offset;
  List<OrderCriteria>       orderCriteria;

  public int getLimit() {
    return limit;
  }

  public void setLimit(int limit) {
    this.limit = limit;
  }

  public int getOffset() {
    return offset;
  }

  public void setOffset(int offset) {
    this.offset = offset;
  }

  public List<OrderCriteria> getOrderCriteria() {
    return orderCriteria;
  }

  public void setOrderCriteria(List<OrderCriteria> orderCriteria) {
    this.orderCriteria = orderCriteria;
  }

  public void addOrderCriteria(String column, Direction dir) {
    if (this.orderCriteria == null) {
      this.orderCriteria = new ArrayList<OrderCriteria>();
    }
    this.orderCriteria.add(new OrderCriteria(column, dir));
  }
}

enum Direction {
  ASC,

  DESC,

  ;

  public static Direction parse(String in) {
    for (Direction value : Direction.values()) {
      if (value.name().equalsIgnoreCase(in)) {
        return value;
      }
    }
    throw new IllegalArgumentException("unsupported direction: `" + in + "`");
  }
}

class OrderCriteria implements Serializable {
  private static final long serialVersionUID = 1L;

  String                    column;
  Direction                 direction;

  public OrderCriteria() {

  }

  public OrderCriteria(String column, Direction direction) {
    this.column = column;
    this.direction = direction;
  }

  public String getColumn() {
    return column;
  }

  public void setColumn(String column) {
    this.column = column;
  }

  public Direction getDirection() {
    return direction;
  }

  public void setDirection(Direction direction) {
    this.direction = direction;
  }

}

(note: possible several files, i just jammed these into 1 listing here for the posting).

.. and then if your API builds those, like from the list view page and passes them to the query DAO, the server side (and specific to what ever database mapping utility you're using) can be fed these values from the bean.

Hope that's helpful.

[agarwal_srushti]

hi,
thanks for your help.
I will try using that part.
Thanks a lot.