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:
- First we need a database, I used Java DB. Go to Netbeans ⇒ Services. Right click on Java DB and select Start server.
- Create a database. database name "namesdb" username and password "nbuser".
- Connect to the newly created database
- Execute command
- download the following sql script to create a table and insert 30000 rows. And execute it in sql command window
- Now we have a table with 30000 entries that we want to show in a JTable
- Let's create a project in netbeans. New project ⇒ Java ⇒ Java Application
- Let's create the JPA Entity class for the database. Select the project and click on New ⇒
- In the wizard select the connection and the customers table
- Create a Persistence Unit also and specify TopLink as provider
- Now you have created the entity classes for Customers
- 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
- Now we can create our GUI
- New ⇒ JPanel Form. Type JTablePaginationExample as class name
- Drop a JTable into the form from the Palette
- Now let's add a custom TableModel to this JTable. Switch to the Source view and add the following
- Now you can run it. Select JTablePaginationExample and "Run File"
- You can download the full source code of JTablePaginationExample.java
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;
}
}