Ruben Laguna's blog

Aug 17, 2009 - 4 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:

  1. First we need a database, I used Java DB. Go to Netbeans ⇒ Services. Right click on Java DB and select Start server.
  2. Create a database. database name "namesdb" username and password "nbuser".
  3. Connect to the newly created database
  4. Execute command
  5. download the following sql script to create a table and insert 30000 rows. And execute it in sql command window
  6. Now we have a table with 30000 entries that we want to show in a JTable
  7. Let's create a project in netbeans. New project ⇒ Java ⇒ Java Application
  8. new project
  9. Let's create the JPA Entity class for the database. Select the project and click on New ⇒
  10. create entity classes for database
  11. In the wizard select the connection and the customers table
  12. entity classes wizard
  13. Create a Persistence Unit also and specify TopLink as provider
  14. create persistence unit
  15. Now you have created the entity classes for Customers
  16. Now we need to add the Java DB / Derby drivers to the project so TopLink is able to find the drive to connect to the database. Right-click on the project Properties ⇒ Libraries ⇒ Add Library and select or create a library for derby
  17. add derby library derby library
  18. Now we can create our GUI
  19. New ⇒ JPanel Form. Type JTablePaginationExample as class name
  20. jpanel form
  21. Drop a JTable into the form from the Palette
  22. Adding JTable
  23. Now let's add a custom TableModel to this JTable. Switch to the Source view and add the following
  24. 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;
      }
    }
  25. Now you can run it. Select JTablePaginationExample and "Run File"
  26. JTablePaginationExample
  27. You can download the full source code of JTablePaginationExample.java
Links -----