package dao.implementation.mysql.chauffeur;
import modele.chauffeur.Avertissement;
import modele.chauffeur.ReferenceChauffeur;
import modele.chauffeur.TypeAvertissement;
import dao.IDao;
import dao.IReadOnlyDao;
import dao.implementation.mysql.AccessMySql;
import gui.MainWindow;
import gui.util.TaxiGuiUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
/**
* Class MySqlAvertissementDao
* @author Kasandra
*
*/
public class MySqlAvertissementDao implements IDao<Avertissement>{
private static final String INSERT_AVERTISSEMENT = "insert into tavertissement ( descripAverti, dateDebAverti, dateFinAverti, idChauf, idTypeAverti) values (?,?,?,?,?)";
private static final String UPDATE_AVERTISSEMENT = "update tavertissement set descripAverti = ? , dateDebAverti = ? , dateFinAverti = ?, idChauf = ?,idTypeAverti = ? where idAverti = ?";
private static final String DELETE_AVERTISSEMENT = "delete from tavertissement where idAverti = ?";
private static final String GET_ALL_AVERTISSEMENT = "select idAverti, descripAverti, dateDebAverti, dateFinAverti, idChauf, ttypeavertissement.idTypeAverti, ttypeavertissement.descripTypeAverti from tavertissement, ttypeavertissement where tavertissement.idTypeAverti = ttypeavertissement.idTypeAverti";
private static final String GET_AVERTISSEMENT_BY_ID = GET_ALL_AVERTISSEMENT + " and idAverti = ?";
public void delete(final Avertissement avertissement) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(DELETE_AVERTISSEMENT);
smt.setInt(1, avertissement.getId());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
}
public Avertissement find(final int id) {
final Avertissement[] result = new Avertissement[1];
final IReadOnlyDao<ReferenceChauffeur> referenceChauffeurDao = getReferenceChauffeurDao();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_AVERTISSEMENT_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
result[0] = new Avertissement(res.getInt(1));
result[0].setDescription(res.getString(2));
Date dateDeb = new Date(res.getDate(3).getTime());
result[0].setDateDebut(dateDeb);
Date dateFin = new Date(res.getDate(4).getTime());
result[0].setDateFin(dateFin);
result[0].setChauffeur(referenceChauffeurDao.find(res.getInt(5)));
TypeAvertissement typeAvertissement = new TypeAvertissement(res.getInt(6));
typeAvertissement.setLibelle(res.getString(7));
result[0].setTypeAvertissement(typeAvertissement);
}
} 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<Avertissement> findAll() {
final ArrayList<Avertissement> listeAvertissement = new ArrayList<Avertissement>();
final IReadOnlyDao<ReferenceChauffeur> referenceChauffeurDao = getReferenceChauffeurDao();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ALL_AVERTISSEMENT);
res = smt.executeQuery();
while (res.next()) {
Avertissement avertissement = new Avertissement(res.getInt(1));
avertissement.setDescription(res.getString(2));
Date dateDeb = new Date(res.getDate(3).getTime());
avertissement.setDateDebut(dateDeb);
Date dateFin = new Date(res.getDate(4).getTime());
avertissement.setDateFin(dateFin);
avertissement.setChauffeur(referenceChauffeurDao.find(res.getInt(5)));
TypeAvertissement typeAvertissement = new TypeAvertissement(res.getInt(6));
typeAvertissement.setLibelle(res.getString(7));/////////////////////
avertissement.setTypeAvertissement(typeAvertissement);
listeAvertissement.add(avertissement);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeAvertissement;
}
public Avertissement insert(final Avertissement avertissement) {
final Avertissement[] result = new Avertissement[1];
result[0] = null;
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement psmt = null;
try {
psmt = connection.prepareStatement(INSERT_AVERTISSEMENT);
psmt.setString(1,avertissement.getDescription());
psmt.setDate(2,new java.sql.Date(avertissement.getDateDebut().getTime()));
psmt.setDate(3,new java.sql.Date(avertissement.getDateFin().getTime()));
psmt.setInt(4,avertissement.getChauffeur().getId());
psmt.setInt(5,avertissement.getTypeAvertissement().getId());
psmt.executeUpdate();
}
finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("select LAST_INSERT_ID() from tavertissement;");
if (res.next()) {
result[0] = find(res.getInt(1));
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public Avertissement update(final Avertissement avertissement) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(UPDATE_AVERTISSEMENT);
smt.setString(1, avertissement.getDescription());
smt.setDate(2, new java.sql.Date(avertissement.getDateDebut().getTime()));
smt.setDate(3, new java.sql.Date(avertissement.getDateFin().getTime()));
smt.setInt(4, avertissement.getChauffeur().getId());
smt.setInt(5, avertissement.getTypeAvertissement().getId());
smt.setInt(6, avertissement.getId());
smt.executeUpdate();
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return avertissement;
}
private IReadOnlyDao<ReferenceChauffeur> getReferenceChauffeurDao() {
return new MySqlReferenceChauffeurDao();
}
}