I have a JavaFX TableView that I populate using a JPQL Query with EclipseLink that gets data from Client table in a MySQL database. the more data is stored in Client table the longer it takes to populate the table, which create that feeling of lagging for the user. and it became really annoying giving that it takes 4 seconds to load 1600 records, I created a Thread to load the GUI before fetching records but it makes no difference. do you guys have any ideas to optimize my code ? here it is :
Here is
initialize()that starts the thread and then the fetching :
@Override
public void initialize(URL url, ResourceBundle rb) {
Thread refresh = new Thread(new Runnable() {
@Override
public void run() {
Platform.runLater(new Runnable() {
@Override
public void run() {
//Optilight.echo("showLoadingWindow()");
//FactureTemplateController.showLoadingWindow();
mouseRightClick();
refreshData();
//Optilight.echo("hideLoadingWindow()");
//FactureTemplateController.hideLoadingWindow();
return;
}
});
}
});
refresh.setDaemon(true);
refresh.start();
}
Fetching data :
public void refreshData(){
em = Optilight.emf.createEntityManager();
List<Client> lst = em.createQuery("SELECT c FROM Client c JOIN FETCH c.personne p ORDER BY c.codePersonne DESC").getResultList();
data = FXCollections.observableArrayList();
for (Client client : lst) {
data.add(new ClientFX(
client.getCodePersonne(), client.getPersonne().getNom(),
client.getPersonne().getPrenom(), client.getPersonne().getAdresse(),
client.getPersonne().getLocalite(), client.getPersonne().getCodePostale(),
client.getPersonne().getPays(), client.getPersonne().getGsm(),
client.getPersonne().getFax(), client.getPersonne().getFixe(),
client.getPersonne().getEmail(), client.getPersonne().getFirstConatct(),
client.getPersonne().getModified(), client.getRegimeMutuelle(), client.getNoMututelle(),
client.getConvention(), client.getTelConvention(), client.getTitre(),
client.getOrganismeSociale(), client.getCaisseSociale()
));
}
em.close();
idClient.setStyle(" -fx-alignment: CENTER;");
firstContactClient.setStyle("-fx-alignment: CENTER;");
gsmClient.setStyle("-fx-alignment: CENTER;");
ajoutePar.setStyle("-fx-alignment: CENTER;");
tableClient.setItems(data);
tableClient.setColumnResizePolicy(TableView.UNCONSTRAINED_RESIZE_POLICY);
idClient.setCellValueFactory(cell -> cell.getValue().getCodePersonneProperty());
nomClient.setCellValueFactory(cell -> cell.getValue().getPersonneNom());
prenomClient.setCellValueFactory(cell -> cell.getValue().getPersonnePrenom());
adresseClient.setCellValueFactory(cell -> cell.getValue().getPersonneAdresse());
gsmClient.setCellValueFactory(cell -> cell.getValue().getPersonneGsm());
emailClient.setCellValueFactory(cell -> cell.getValue().getPersonneEmail());
firstContactClient.setCellValueFactory(cell -> cell.getValue().getPersonneFirstContact());
ajoutePar.setCellValueFactory(cell -> cell.getValue().getPersonneModified());
}
ClientFX.java :
public class ClientFX {
private SimpleIntegerProperty codePersonne;
private SimpleStringProperty regimeMutuelle;
private SimpleStringProperty noMututelle;
private SimpleStringProperty convention;
private SimpleStringProperty telConvention;
private SimpleStringProperty titre;
private SimpleStringProperty organismeSociale;
private SimpleStringProperty caisseSociale;
private Personne personne;
private List<Dossiermonture> dossiermontureList;
public ClientFX() {
}
public ClientFX(Integer codePersonne) {
this.codePersonne = new SimpleIntegerProperty(codePersonne);
}
public ClientFX(Integer codePersonne, String nom, String prenom, String adresse, String localite,
String codePostale, String pays, String gsm, String fax, String fixe, String email,
String firstConatct,String modified,String regimeMutuelle, String noMututelle, String convention,
String telConvention, String titre, String organismeSociale, String caisseSociale) {
this.personne=new Personne(codePersonne, nom, prenom,adresse,
localite,codePostale,pays,gsm,fax,
fixe,email,firstConatct,modified);
this.codePersonne = new SimpleIntegerProperty(codePersonne);
this.regimeMutuelle = new SimpleStringProperty(regimeMutuelle);
this.noMututelle=new SimpleStringProperty(noMututelle);
this.convention=new SimpleStringProperty(convention);
this.telConvention=new SimpleStringProperty(telConvention);
this.titre=new SimpleStringProperty(titre);
this.organismeSociale=new SimpleStringProperty(organismeSociale);
this.caisseSociale=new SimpleStringProperty(caisseSociale);
}
public Integer getCodePersonne() {
return codePersonne.get();
}
public SimpleIntegerProperty getCodePersonneProperty() {
return codePersonne;
}
public void setCodePersonne(Integer codePersonne) {
this.codePersonne = new SimpleIntegerProperty(codePersonne);
}
public String getRegimeMutuelle() {
return regimeMutuelle.get();
}
public SimpleStringProperty getRegimeMutuelleProperty() {
return regimeMutuelle;
}
public void setRegimeMutuelle(String regimeMutuelle) {
this.regimeMutuelle = new SimpleStringProperty(regimeMutuelle);
}
public String getNoMututelle() {
return noMututelle.get();
}
public SimpleStringProperty getNoMututelleProperty() {
return noMututelle;
}
public void setNoMututelle(String noMututelle) {
this.noMututelle = new SimpleStringProperty(noMututelle);
}
public String getConvention() {
return convention.get();
}
public SimpleStringProperty getConventionProperty() {
return convention;
}
public void setConvention(String convention) {
this.convention = new SimpleStringProperty(convention);
}
public String getTelConvention() {
return telConvention.get();
}
public SimpleStringProperty getTelConventionProperty() {
return telConvention;
}
public void setTelConvention(String telConvention) {
this.telConvention = new SimpleStringProperty(telConvention);
}
public String getTitre() {
return titre.get();
}
public SimpleStringProperty getTitreProperty() {
return titre;
}
public void setTitre(String titre) {
this.titre = new SimpleStringProperty(titre);
}
public String getOrganismeSociale() {
return organismeSociale.get();
}
public SimpleStringProperty getOrganismeSocialeProperty() {
return organismeSociale;
}
public void setOrganismeSociale(String organismeSociale) {
this.organismeSociale = new SimpleStringProperty(organismeSociale);
}
public String getCaisseSociale() {
return caisseSociale.get();
}
public SimpleStringProperty getCaisseSocialeProperty() {
return caisseSociale;
}
public void setCaisseSociale(String caisseSociale) {
this.caisseSociale = new SimpleStringProperty(caisseSociale);
}
@XmlTransient
public List<Dossiermonture> getDossiermontureList() {
return dossiermontureList;
}
public void setDossiermontureList(List<Dossiermonture> dossiermontureList) {
this.dossiermontureList = dossiermontureList;
}
public Personne getPersonne() {
return personne;
}
public void setPersonne(Personne personne) {
this.personne = personne;
}
public SimpleStringProperty getPersonneNom() {
return new SimpleStringProperty(personne.getNom());
}
public SimpleStringProperty getPersonnePrenom() {
return new SimpleStringProperty(personne.getPrenom());
}
public SimpleStringProperty getPersonneAdresse() {
return new SimpleStringProperty(personne.getAdresse());
}
public SimpleStringProperty getPersonneLocalite() {
return new SimpleStringProperty(personne.getLocalite());
}
public SimpleStringProperty getPersonneCodePostale() {
return new SimpleStringProperty(personne.getCodePostale());
}
public SimpleStringProperty getPersonnePays() {
return new SimpleStringProperty(personne.getPays());
}
public SimpleStringProperty getPersonneGsm() {
return new SimpleStringProperty(personne.getGsm());
}
public SimpleStringProperty getPersonneFax() {
return new SimpleStringProperty(personne.getFax());
}
public SimpleStringProperty getPersonneFixe() {
return new SimpleStringProperty(personne.getFixe());
}
public SimpleStringProperty getPersonneEmail() {
return new SimpleStringProperty(personne.getEmail());
}
public SimpleStringProperty getPersonneFirstContact() {
return new SimpleStringProperty(personne.getFirstConatct());
}
public SimpleStringProperty getPersonneModified() {
return new SimpleStringProperty(personne.getModified());
}
Personne.Java
@Entity
@Table(name = "personne")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Personne.findAll", query = "SELECT p FROM Personne p"),
@NamedQuery(name = "Personne.findByCodePersonne", query = "SELECT p FROM Personne p WHERE p.codePersonne = :codePersonne"),
@NamedQuery(name = "Personne.findByNom", query = "SELECT p FROM Personne p WHERE p.nom = :nom"),
@NamedQuery(name = "Personne.findByPrenom", query = "SELECT p FROM Personne p WHERE p.prenom = :prenom"),
@NamedQuery(name = "Personne.findByAdresse", query = "SELECT p FROM Personne p WHERE p.adresse = :adresse"),
@NamedQuery(name = "Personne.findByLocalite", query = "SELECT p FROM Personne p WHERE p.localite = :localite"),
@NamedQuery(name = "Personne.findByCodePostale", query = "SELECT p FROM Personne p WHERE p.codePostale = :codePostale"),
@NamedQuery(name = "Personne.findByPays", query = "SELECT p FROM Personne p WHERE p.pays = :pays"),
@NamedQuery(name = "Personne.findByGsm", query = "SELECT p FROM Personne p WHERE p.gsm = :gsm"),
@NamedQuery(name = "Personne.findByFax", query = "SELECT p FROM Personne p WHERE p.fax = :fax"),
@NamedQuery(name = "Personne.findByFixe", query = "SELECT p FROM Personne p WHERE p.fixe = :fixe"),
@NamedQuery(name = "Personne.findByEmail", query = "SELECT p FROM Personne p WHERE p.email = :email"),
@NamedQuery(name = "Personne.findByFirstConatct", query = "SELECT p FROM Personne p WHERE p.firstConatct = :firstConatct")})
public class Personne implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.TABLE)
@Basic(optional = false)
@Column(name = "codePersonne", nullable = false)
private Integer codePersonne;
@Column(name = "nom", length = 254)
private String nom;
@Column(name = "prenom", length = 254)
private String prenom;
@Column(name = "adresse", length = 254)
private String adresse;
@Column(name = "localite", length = 254)
private String localite;
@Column(name = "codePostale", length = 254)
private String codePostale;
@Column(name = "pays", length = 254)
private String pays;
@Column(name = "gsm", length = 254)
private String gsm;
@Column(name = "fax", length = 254)
private String fax;
@Column(name = "fixe", length = 254)
private String fixe;
@Column(name = "email", length = 254)
private String email;
@Column(name = "firstConatct", length = 254)
private String firstConatct;
@Column(name = "modified", length = 254)
private String modified;
@OneToOne(cascade = CascadeType.ALL, mappedBy = "personne")
private Fournisseur fournisseur;
@OneToOne(cascade = CascadeType.ALL, mappedBy = "personne")
private Client client;
@OneToOne(cascade = CascadeType.ALL, mappedBy = "personne")
private Docteur docteur;
public Personne() {
}
public Personne(Integer codePersonne) {
this.codePersonne = codePersonne;
}
Personne(Integer codePersonne, String nom,
String prenom, String adresse,
String localite, String codePostale,
String pays, String gsm, String fax,
String fixe, String email,
String firstConatct,String modified) {
this.codePersonne = codePersonne;
this.nom = AES.encrypt(nom);
this.prenom = AES.encrypt(prenom);
this.adresse = AES.encrypt(adresse);
this.localite = AES.encrypt(localite);
this.codePostale = AES.encrypt(codePostale);
this.pays = AES.encrypt(pays);
this.gsm = AES.encrypt(gsm);
this.fax = AES.encrypt(fax);
this.fixe = AES.encrypt(fixe);
this.email = AES.encrypt(email);
this.firstConatct = AES.encrypt(firstConatct);
this.modified = AES.encrypt(modified);
}
public Integer getCodePersonne() {
return codePersonne;
}
public void setCodePersonne(Integer codePersonne) {
this.codePersonne = codePersonne;
}
public String getNom() {
return AES.decrypt(nom);
}
public void setNom(String nom) {
this.nom = AES.encrypt(nom);
}
public String getPrenom() {
return AES.decrypt(prenom);
}
public void setPrenom(String prenom) {
this.prenom = AES.encrypt(prenom);
}
public String getAdresse() {
return AES.decrypt(adresse);
}
public void setAdresse(String adresse) {
this.adresse = AES.encrypt(adresse);
}
public String getLocalite() {
return AES.decrypt(localite);
}
public void setLocalite(String localite) {
this.localite = AES.encrypt(localite);
}
public String getCodePostale() {
return AES.decrypt(codePostale);
}
public void setCodePostale(String codePostale) {
this.codePostale = AES.encrypt(codePostale);
}
public String getPays() {
return AES.decrypt(pays);
}
public void setPays(String pays) {
this.pays = AES.encrypt(pays);
}
public String getGsm() {
return AES.decrypt(gsm);
}
public void setGsm(String gsm) {
this.gsm = AES.encrypt(gsm);
}
public String getFax() {
return AES.decrypt(fax);
}
public void setFax(String fax) {
this.fax = AES.encrypt(fax);
}
public String getFixe() {
return AES.decrypt(fixe);
}
public void setFixe(String fixe) {
this.fixe = AES.encrypt(fixe);
}
public String getEmail() {
return AES.decrypt(email);
}
public void setEmail(String email) {
this.email = AES.encrypt(email);
}
public String getFirstConatct() {
return AES.decrypt(firstConatct);
}
public void setFirstConatct(String firstConatct) {
this.firstConatct = AES.encrypt(firstConatct);
}
public String getModified() {
return AES.decrypt(modified);
}
public void setModified(String modified) {
this.modified = AES.encrypt(modified);
}
public Fournisseur getFournisseur() {
return fournisseur;
}
public void setFournisseur(Fournisseur fournisseur) {
this.fournisseur = fournisseur;
}
public Client getClient() {
return client;
}
public void setClient(Client client) {
this.client = client;
}
public String getFullName() {
return AES.decrypt(this.nom)+" "+AES.decrypt(this.prenom);
}
public SimpleStringProperty getFullNameProperty() {
return new SimpleStringProperty(AES.decrypt(this.nom)+" "+AES.decrypt(this.prenom));
}
public boolean DateSet(){
if (this.firstConatct!="")
return true;
else
return false;
}
public boolean DateNull(){
if (this.firstConatct!=null)
return true;
else
return false;
}
@Override
public int hashCode() {
int hash = 0;
hash += (codePersonne != null ? codePersonne.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Personne)) {
return false;
}
Personne other = (Personne) object;
if ((this.codePersonne == null && other.codePersonne != null) || (this.codePersonne != null && !this.codePersonne.equals(other.codePersonne))) {
return false;
}
return true;
}
@Override
public String toString() {
return "samplefx.model.Personne[ codePersonne=" + codePersonne + " ]";
}
public SimpleStringProperty getPrenomProperty() {
return new SimpleStringProperty(this.getNom());
}
public Docteur getDocteur() {
return docteur;
}
public void setDocteur(Docteur docteur) {
this.docteur = docteur;
}
}
SHOW CREATE TABLEforClienttable :
client | CREATE TABLE `client` (
`codePersonne` int(11) NOT NULL,
`regimeMutuelle` varchar(254) DEFAULT NULL,
`noMututelle` varchar(254) DEFAULT NULL,
`convention` varchar(254) DEFAULT NULL,
`telConvention` varchar(254) DEFAULT NULL,
`titre` varchar(254) DEFAULT NULL,
`organismeSociale` varchar(254) DEFAULT NULL,
`caisseSociale` varchar(254) DEFAULT NULL,
PRIMARY KEY (`codePersonne`),
CONSTRAINT `FK_Generalization_4` FOREIGN KEY (`codePersonne`) REFERENCES `personne` (`codePersonne`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
As you can see there is a generalization foreign key to Personne Table.
SHOW CREATE TABLEforPersonnetable :
personne | CREATE TABLE `personne` (
`codePersonne` int(11) NOT NULL AUTO_INCREMENT,
`nom` varchar(254) DEFAULT NULL,
`prenom` varchar(254) DEFAULT NULL,
`adresse` varchar(254) DEFAULT NULL,
`localite` varchar(254) DEFAULT NULL,
`codePostale` varchar(254) DEFAULT NULL,
`pays` varchar(254) DEFAULT NULL,
`gsm` varchar(254) DEFAULT NULL,
`fax` varchar(254) DEFAULT NULL,
`fixe` varchar(254) DEFAULT NULL,
`email` varchar(254) DEFAULT NULL,
`firstConatct` varchar(254) DEFAULT NULL,
`modified` varchar(254) NOT NULL,
PRIMARY KEY (`codePersonne`)
) ENGINE=InnoDB AUTO_INCREMENT=1588 DEFAULT CHARSET=latin1
SHOW VARIABLES LIKE '%buffer%';:
bulk_insert_buffer_size | 8388608
innodb_buffer_pool_chunk_size | 134217728
innodb_buffer_pool_dump_at_shutdown | ON
innodb_buffer_pool_dump_now | OFF
innodb_buffer_pool_dump_pct | 25
innodb_buffer_pool_filename | ib_buffer_pool
innodb_buffer_pool_instances | 1
innodb_buffer_pool_load_abort | OFF
innodb_buffer_pool_load_at_startup | ON
innodb_buffer_pool_load_now | OFF
innodb_buffer_pool_size | 134217728
innodb_change_buffer_max_size | 25
innodb_change_buffering | all
innodb_log_buffer_size | 16777216
innodb_sort_buffer_size | 1048576
join_buffer_size | 262144
key_buffer_size | 8388608
myisam_sort_buffer_size | 41943040
net_buffer_length | 16384
preload_buffer_size | 32768
read_buffer_size | 65536
read_rnd_buffer_size | 262144
sort_buffer_size | 262144
sql_buffer_result | OFF
