package dao.implementation.mysql.chauffeur;
import gui.MainWindow;
import gui.util.TaxiGuiUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import modele.chauffeur.Chauffeur;
import modele.chauffeur.Localite;
import modele.chauffeur.Ville;
import dao.IDao;
import dao.implementation.mysql.AccessMySql;
/**
* Class MySqlChauffeurDao
* @author Kasandra
*
*/
public class MySqlChauffeurDao implements IDao<Chauffeur>{
private static final String INSERT_CHAUFFEUR = "insert into tchauffeurs ( nomChauf, prenomChauf, rueChauf, numRueChauf, cp, commune, ville, dateNaisChauf, numNatChauf, sexe, etCiv, nbrPersAChrgChauf, telChauf, gsmChauf, mailChauf, numCpteChauf, typePermisChauf, datePermisChauf, dateEcheancePermisChauf, etatPermisChauf, dateVisiteMedicChauf, certifBVMChauf) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
private static final String UPDATE_CHAUFFEUR = "update tchauffeurs set nomChauf = ? , prenomChauf = ? , rueChauf = ?, numRueChauf = ?, cp = ?, commune = ?, ville = ?, dateNaisChauf = ?, numNatChauf = ?, sexe = ?, etCiv = ?, nbrPersAChrgChauf = ?, telChauf = ?, gsmChauf = ?, mailChauf = ?, numCpteChauf = ?, typePermisChauf = ?, datePermisChauf = ?, dateEcheancePermisChauf = ?, etatPermisChauf = ?, dateVisiteMedicChauf = ?, certifBVMChauf = ? where idChauf = ?";
private static final String DELETE_CHAUFFEUR = "delete from tchauffeurs where idChauf = ?";
private static final String GET_ALL_CHAUFFEUR = "select idChauf, nomChauf, prenomChauf, rueChauf, numRueChauf, tlocalite.cp, tlocalite.commune, tville.idVille, tville.nomVille, dateNaisChauf, numNatChauf, sexe, etCiv, nbrPersAChrgChauf, telChauf, gsmChauf, mailChauf, numCpteChauf, typePermisChauf, datePermisChauf, dateEcheancePermisChauf, etatPermisChauf, dateVisiteMedicChauf, certifBVMChauf from tchauffeurs,tlocalite, tville where tchauffeurs.cp = tlocalite.cp and tchauffeurs.commune = tlocalite.commune and tlocalite.idVille = tville.idVille" ;
private static final String GET_CHAUFFEUR_BY_ID = GET_ALL_CHAUFFEUR + " and idChauf = ?";
public void delete(final Chauffeur chauffeur) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(DELETE_CHAUFFEUR);
smt.setInt(1, chauffeur.getId());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
//throw new RuntimeException(e);
e.printStackTrace();
TaxiGuiUtil.MessageBox(MainWindow.desktop(), "Probl�me de suppression d'un enregistrement : " + e.getMessage(), "Erreur syst�me");
}
}
});
}
public Chauffeur find(final int id) {
final Chauffeur[] result = new Chauffeur[1];
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_CHAUFFEUR_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
result[0] = new Chauffeur(res.getInt(1));
result[0].setNom(res.getString(2));
result[0].setPrenom(res.getString(3));
result[0].setRue(res.getString(4));
result[0].setNumRue(res.getString(5));
Localite localite = new Localite(res.getInt(6));
localite.setCp(res.getInt(8));
result[0].setLocalite(localite);
localite.setCommune(res.getString(9));
result[0].setLocalite(localite);
Ville ville = new Ville(res.getInt(10));
ville.setNomVille(res.getString(11));
result[0].setVille(ville);
Date dateNais = new Date(res.getDate(12).getTime());
result[0].setDtNais(dateNais);
result[0].setNumNat(res.getString(13));
result[0].setSexe(res.getString(14));
result[0].setEtCiv(res.getString(15));
result[0].setNbPers(res.getString(16));
result[0].setTel(res.getString(17));
result[0].setGsm(res.getString(18));
result[0].setMail(res.getString(19));
result[0].setNumCpte(res.getString(20));
result[0].setTypePermis(res.getString(21));
Date datePermis = new Date(res.getDate(22).getTime());
result[0].setDatePermis(datePermis);
Date dateEcheance = new Date(res.getDate(23).getTime());
result[0].setDateEcheaPermis(dateEcheance);
/*
Etat etat = new Etat(res.getInt(24));
etat.setDescrip(res.getString(25));
result[0].setEtat(etat);
*/
result[0].setEtat(res.getString(24));
Date dateVisitMed = new Date(res.getDate(25).getTime());
result[0].setDtVisitMed(dateVisitMed);
result[0].setCertifBVM(res.getString(27));
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public ArrayList<Chauffeur> findAll() {
final ArrayList<Chauffeur> listeChauffeur = new ArrayList<Chauffeur>();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ALL_CHAUFFEUR);
res = smt.executeQuery();
while (res.next()) {
Chauffeur chauffeur = new Chauffeur(res.getInt(1));
chauffeur.setNom(res.getString(2));
chauffeur.setPrenom(res.getString(3));
chauffeur.setRue(res.getString(4));
chauffeur.setNumRue(res.getString(5));
Localite localite = new Localite(res.getInt(6));
//localite.setCp(res.getInt(7));
chauffeur.setLocalite(localite);
localite.setCommune(res.getString(7));
chauffeur.setLocalite(localite);
Ville ville = new Ville(res.getInt(8));
ville.setNomVille(res.getString(9));
chauffeur.setVille(ville);
Date dateNais = new Date(res.getDate(10).getTime());
chauffeur.setDtNais(dateNais);
chauffeur.setNumNat(res.getString(11));
chauffeur.setSexe(res.getString(12));
chauffeur.setEtCiv(res.getString(13));
chauffeur.setNbPers(res.getString(14));
chauffeur.setTel(res.getString(15));
chauffeur.setGsm(res.getString(16));
chauffeur.setMail(res.getString(17));
chauffeur.setNumCpte(res.getString(18));
chauffeur.setTypePermis(res.getString(19));
Date datePermis = new Date(res.getDate(20).getTime());
chauffeur.setDatePermis(datePermis);
Date dateEchea = new Date(res.getDate(21).getTime());
chauffeur.setDateEcheaPermis(dateEchea);
/*
Etat etat = new Etat(res.getInt(22));
etat.setDescrip(res.getString(23));
chauffeur.setEtat(etat);
*/
chauffeur.setEtat(res.getString(22));
Date dateVisitMed = new Date(res.getDate(23).getTime());
chauffeur.setDtVisitMed(dateVisitMed);
chauffeur.setCertifBVM(res.getString(24));
listeChauffeur.add(chauffeur);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeChauffeur;
}
public Chauffeur insert(final Chauffeur chauffeur) {
final Chauffeur[] result = new Chauffeur[1];
result[0] = null;
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement psmt = null;
try {
psmt = connection.prepareStatement(INSERT_CHAUFFEUR);
psmt.setString(1,chauffeur.getNom());
psmt.setString(2,chauffeur.getPrenom());
psmt.setString(3,chauffeur.getRue());
psmt.setString(4,chauffeur.getNumRue());
psmt.setInt(5,chauffeur.getLocalite().getCp());
psmt.setString(6,chauffeur.getLocalite().getCommune());
psmt.setString(7,chauffeur.getVille().getNomVille());
psmt.setDate(8,new java.sql.Date(chauffeur.getDtNais().getTime()));
psmt.setString(9,chauffeur.getNumNat());
psmt.setString(10,chauffeur.getSexe());
psmt.setString(11,chauffeur.getEtCiv());
psmt.setString(12,chauffeur.getNbPers());
psmt.setString(13,chauffeur.getTel());
psmt.setString(14,chauffeur.getGsm());
psmt.setString(15,chauffeur.getMail());
psmt.setString(16,chauffeur.getNumCpte());
psmt.setString(17,chauffeur.getTypePermis());
psmt.setDate(18,new java.sql.Date(chauffeur.getDatePermis().getTime()));
psmt.setDate(19,new java.sql.Date(chauffeur.getDateEcheaPermis().getTime()));
psmt.setString(20,chauffeur.getEtat());
psmt.setDate(21,new java.sql.Date(chauffeur.getDtVisitMed().getTime()));
psmt.setString(22,chauffeur.getCertifBVM());
psmt.executeUpdate();
} finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("select idChauf, nomChauf, prenomChauf, rueChauf, numRueChauf,tlocalite.cp, tlocalite.commune, tville.idVille, tville.nomVille, dateNaisChauf, numNatChauf, sexe, etCiv, nbrPersAChrgChauf, telChauf, gsmChauf, mailChauf, numCpteChauf, typePermisChauf, datePermisChauf, dateEcheancePermisChauf, etatPermisChauf, dateVisiteMedicChauf, certifBVMChauf from tchauffeurs,tlocalite, tville where tchauffeurs.cp = tlocalite.cp and tlocalite.idVille = tville.idVille and idChauf = LAST_INSERT_ID()");
if (res.next()) {
result[0] = new Chauffeur(res.getInt(1));
result[0].setNom(res.getString(2));
result[0].setPrenom(res.getString(3));
result[0].setRue(res.getString(4));
result[0].setNumRue(res.getString(5));
Localite localite = new Localite(res.getInt(6));
//localite.setCp(res.getInt(7));
chauffeur.setLocalite(localite);
localite.setCommune(res.getString(7));
Ville ville = new Ville(res.getInt(8));
ville.setNomVille(res.getString(9));
chauffeur.setVille(ville);
Date dateNais = new Date(res.getDate(10).getTime());
chauffeur.setDtNais(dateNais);
chauffeur.setNumNat(res.getString(11));
chauffeur.setSexe(res.getString(12));
chauffeur.setEtCiv(res.getString(13));
chauffeur.setNbPers(res.getString(14));
chauffeur.setTel(res.getString(15));
chauffeur.setGsm(res.getString(16));
chauffeur.setMail(res.getString(17));
chauffeur.setNumCpte(res.getString(18));
chauffeur.setTypePermis(res.getString(19));
Date datePermis = new Date(res.getDate(20).getTime());
chauffeur.setDatePermis(datePermis);
Date dateEchea = new Date(res.getDate(21).getTime());
chauffeur.setDateEcheaPermis(dateEchea);
/*
Etat etat = new Etat(res.getInt(23));
etat.setDescrip(res.getString(24));
chauffeur.setEtat(etat);
*/
chauffeur.setEtat(res.getString(22));
Date dateVisitMed = new Date(res.getDate(23).getTime());
chauffeur.setDtVisitMed(dateVisitMed);
chauffeur.setCertifBVM(res.getString(24));
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public Chauffeur update(final Chauffeur chauffeur) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(UPDATE_CHAUFFEUR);
smt.setString(1, chauffeur.getNom());
smt.setString(2, chauffeur.getPrenom());
smt.setString(3,chauffeur.getRue());
smt.setString(4,chauffeur.getNumRue());
smt.setInt(5,chauffeur.getLocalite().getCp());
smt.setString(6,chauffeur.getLocalite().getCommune());
smt.setInt(7,chauffeur.getVille().getId());
smt.setDate(8, new java.sql.Date(chauffeur.getDtNais().getTime()));
smt.setString(9,chauffeur.getNumNat());
smt.setString(10,chauffeur.getSexe());
smt.setString(11,chauffeur.getEtCiv());
smt.setString(12,chauffeur.getNbPers());
smt.setString(13,chauffeur.getTel());
smt.setString(14,chauffeur.getGsm());
smt.setString(15,chauffeur.getMail());
smt.setString(16,chauffeur.getNumCpte());
smt.setString(17,chauffeur.getTypePermis());
smt.setDate(18, new java.sql.Date(chauffeur.getDatePermis().getTime()));
smt.setDate(19, new java.sql.Date(chauffeur.getDateEcheaPermis().getTime()));
//smt.setInt(20,chauffeur.getEtat().getCdeEtat());
smt.setString(20,chauffeur.getEtat());
smt.setDate(21, new java.sql.Date(chauffeur.getDtVisitMed().getTime()));
smt.setString(22,chauffeur.getCertifBVM());
smt.setInt(23, chauffeur.getId());
smt.executeUpdate();
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return chauffeur;
}
}