Ruben Laguna's blog

Aug 17, 2009 - 3 minute read - abstracttablemodel application bind binding custom derby entitymanager java javadb jpa jtable netbeans pagination persistence query scroll select setfirstresult setmaxresults sql table tablemodel

JTable and JPA Pagination through custom TableModel

I really want to talk about JTable, Beans Binding and JPA pagination but I think I need to write about JTable and JPA pagination first. So I will take the Beans binding stuff in another post.

By the way, choose wisely your JPA Provider/DB Provider combination, as some combinations will not give you any real paginations at all. For example, neither OpenJPA, Hibernate or TopLink/EclipseLink seems to support Apache Derby pagination (OFFSET/FETCH). The example here uses Derby and TopLink which is a bad example because the JPA pagination doesn’t get translated to SQL command for pagination. So if you really want proper pagination you should use other combination like Hibernate JPA/HSQLDB.

To get a JTable showing data from a paginated JPA Query you need to create a Custom TableModel for the JTable like this one:

class JPAPaginationTableModel extends AbstractTableModel {

  private final EntityManager manager;
  private int startPosition;
  private List<Customers> theList;
  private int counter=0;

  JPAPaginationTableModel(EntityManager manager) {
    this.manager = manager;
    this.startPosition = 0;
    this.theList = getItems(startPosition, startPosition+100);
  }

  public int getRowCount() {
    return ((Long) manager.createQuery("SELECT COUNT (c) FROM Customers c").getSingleResult()).intValue();
  }

  public int getColumnCount() {
    return 3;
  }

  public Object getValueAt(int rowIndex, int columnIndex) {

    if((rowIndex >= startPosition) && (rowIndex<(startPosition+100))){

    } else
    {
      this.theList = getItems(rowIndex, rowIndex+100);
      this.startPosition=rowIndex;
    }
    Customers c = theList.get(rowIndex-startPosition);

    Object toReturn = null;
    switch (columnIndex) {
      case 0:
      toReturn = c.getId();
      break;
      case 1:
      toReturn = c.getFirstName();
      break;
      case 2:
      toReturn = c.getLastName();
      break;
      default:
      toReturn = c.getId();
    }
    return toReturn;
  }

  private List<Customers> getItems(int from, int to) {
    System.out.println("number of requests to the database "*counter*+);
    Query query = manager.createQuery("SELECT c FROM Customers c").setMaxResults(to-from).setFirstResult(from);

    //add the cache
    List<Customers> resultList = query.getResultList();
    return resultList;
  }
}

The getValueAt in this custom TableModel make use from a paginated (setMaxResults) JPA query that take 100 rows each time. The database contains 30000 rows but with this TableModel you won’t need to retrieve all of them to display the JTable. Only the rows that need to be shown will be retrieved.

To fully understand this custom TableModel I think it’s better to illustrate a full application so I’m going how to describe how to create an example application with netbeans:

private TableModel getTableModel() {
  EntityManager manager = Persistence.createEntityManagerFactory("JTablePaginationJPAPU").createEntityManager();

  return new JPAPaginationTableModel(manager);
}

public static void main(String args[]) {
  java.awt.EventQueue.invokeLater(new Runnable() {

    public void run() {
      JFrame frame = new JFrame("JTable with custom TableModel and JPA");
      frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      JComponent panel = new JTablePaginationExample();
      frame.add(panel);
      frame.pack();
      frame.setVisible(true);
    }
  });
}

and at the end of the file add a new class

class JPAPaginationTableModel extends AbstractTableModel {

  private final EntityManager manager;
  private int startPosition;
  private List<Customers> theList;
  private int counter = 0;

  JPAPaginationTableModel(EntityManager manager) {
    this.manager = manager;
    this.startPosition = 0;
    this.theList = getItems(startPosition, startPosition + 100);
  }

  public int getRowCount() {
    return ((Long) manager.createQuery("SELECT COUNT (c) FROM Customers c").getSingleResult()).intValue();
  }

  public int getColumnCount() {
    return 3;
  }

  public Object getValueAt(int rowIndex, int columnIndex) {

    if ((rowIndex >= startPosition) && (rowIndex < (startPosition + 100))) {
    } else {
      this.theList = getItems(rowIndex, rowIndex + 100);
      this.startPosition = rowIndex;
    }
    Customers c = theList.get(rowIndex - startPosition);

    Object toReturn = null;
    switch (columnIndex) {
      case 0:
        toReturn = c.getId();
        break;
      case 1:
        toReturn = c.getFirstName();
        break;
      case 2:
        toReturn = c.getLastName();
        break;
      default:
        toReturn = c.getId();
    }
    return toReturn;
  }

  private List<Customers> getItems(int from, int to) {
  System.out.println("numer of requests to the database " + counter**);
  Query query = manager.createQuery("SELECT c FROM Customers c").setMaxResults(to - from).setFirstResult(from);

  //add the cache
  List<Customers> resultList = query.getResultList();
  return resultList;
  }
}