Binding JTable with mySQL data in Java NetBeans
Problem: In a Java swing desktop application I try to bind a remote mySQL database table to a JTable. NetBeans takes care of all the GUI and persistence part. I built a JFrame form that has a JTable bound to a mySQL table. The query is dynamic and depends on the user’s selection, that actually becomes the WHERE clause. I spent a day trying to include a WHERE clause in the query. After a heavy internet search I figured it out, but when I set the frame to visible the new data is not shown. The JTable repaint() method does exactly nothing. I tried messing up with the fire action listeners, but I couldn’t get that to work either. The JTable has no data refresh method. Can anyone of you tell me the way to have my JTable displayed after an sql query is executed in runtime? I am attaching my code.
Solution: By using MySQL Connector/J, your Java program can easily access MySQL databases. Good starting points are NetBeans.org and mySQL web sites. Visit NetBeans.org and how to use MySQL with Java from here. With the Java persistence API you don’t have to create complex data access objects, and the API helps you manage transactions and you can write standards-based code that interacts with any relational database. I assume that you have already added the Entity Manager, the Query, and the Query Result components to your NetBeans desktop project.
In the source code you missed several points as many of us do a lot of times. You executed the Query but did nothing with the result set (Query Result). You did not refreshed the JTable by the retrieved data as you have not set to the new content of your own Query Result list and did not bind it to the JTable. This is a typical error.
A JTable doesn’t know when its data model has changed. JTable is a presentation control only. Repainting the table simply repaints the JTable. That’s not enough. It has to be notified about data changes.
Avoid binding your JTable directly to the the query.getResultList(). Create a temporary List, unbind JTable, and then bind the JTable with that new List instead. Example code is here:
Open Source Java persistence objects
Java developer’s forum at Oracle (aka Sun)
JPA interpretation of SQL for selecting objects from a datastore
import org.jdesktop.beansbinding.*;
import javax.persistence.*;
@SuppressWarnings("unchecked")
public void AddressQueryBasedOnID( Integer id )
{
// We save it to keep track, defined as Integer. Pay attention to definations otherwise will get exception
// later at the composition of query
this.ID = id;
// Get the Entity Manager, see generated code for it
// EM takes care of mySQL connection, database open and close actions
EntityManager em = this.MyManagerPUEntityManager;
try{
// It's an HQL, and not SQL in this case
// Set query like "SELECT a FROM Address a WHERE a.id = :id"; // a.id => Integer
Query addressQuery = em.createNamedQuery("Address.findById").setParameter("id", this.ID );
// Execute, get the temporary Query Result List
java.util.List<MyApp.Address> data = addressQuery.getResultList();
// Find binding if you do not know its name
// addressList is our java.util.List<MyApp.Address> variable with class focus
// jTable1 is the JTable we use in the form
Binding b = findBinding(this.addressList, this.jTable1);
// Unbind previous
b.unbind();
// Clear our own Query Result List
this.addressList.clear();
// Add data to this Query Result List
this.addressList.addAll(data);
// Bind it
b.bind();
// Repaint our JTable
this.jTable1.repaint();
} catch (NoResultException e) {
System.out.println(e.getMessage());
}catch(Exception e){
System.out.println(e.getMessage());
}
}
// FIND BINDING
// It is a sample method how to find a binding
// Binding group is generated by NetBeans: see this.bindingGroup in the source file
public Binding findBinding(Object source, Object target)
{
for ( Iterator<Binding> i = this.bindingGroup.getBindings().iterator(); i.hasNext(); ){
Binding b = i.next();
boolean found = (source ==null || b.getSourceObject() == source) &&
(target == null || b.getTargetObject() == target );
if (found ){
return b;
}
}
return null;
}