package dao.implementation.mysql.chauffeur;
import modele.chauffeur.Contrat;
import modele.chauffeur.TypeContrat;
import modele.commun.Etat;
import dao.IDao;
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;
/**
* Class MySqlContratDao
* @author Kasandra
*/
public class MySqlContratDao implements IDao<Contrat>{
private static final String INSERT_CONTRAT = "insert into tcontrat ( statutCont, idTypeCont, idEtat) values (?,?,?)";
private static final String UPDATE_CONTRAT = "update tcontrat set statutCont = ? ,idTypecont, idEtat = ? where idCont = ?";
private static final String DELETE_CONTRAT = "delete from tcontrat where idCont = ?";
private static final String GET_ALL_CONTRAT = "select idCont, statutCont, ttypecontrat.idTypeCont, ttypecontrat.descripTypeCont, TEtat.idEtat,TEtat.descripEtat from tcontrat, ttypecontrat, TEtat where ttypecontrat.idTypeCont = tcontrat.idTypeCont and tcontrat.idEtat = TEtat.idEtat";
private static final String GET_CONTRAT_BY_ID = GET_ALL_CONTRAT + " and idcont = ?";
public void delete(final Contrat contrat) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(DELETE_CONTRAT);
smt.setInt(1, contrat.getId());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
}
public Contrat find(final int id) {
final Contrat[] result = new Contrat[1];
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_CONTRAT_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
result[0] = new Contrat(res.getInt(1));
result[0].setStatut(res.getString(2));
TypeContrat typeContrat = new TypeContrat(res.getInt(3));
typeContrat.setLibelle(res.getString(4));
result[0].setTypeContrat(typeContrat);
Etat etat = new Etat(res.getInt(5));
etat.setDescrip(res.getString(6));
result[0].setEtat(etat);
}
} 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<Contrat> findAll() {
final ArrayList<Contrat> listeContrat = new ArrayList<Contrat>();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ALL_CONTRAT);
res = smt.executeQuery();
while (res.next()) {
Contrat contrat = new Contrat(res.getInt(1));
contrat.setStatut(res.getString(2));
TypeContrat typeContrat = new TypeContrat(res.getInt(3));
typeContrat.setLibelle(res.getString(4));
contrat.setTypeContrat(typeContrat);
Etat etat = new Etat(res.getInt(5));
etat.setDescrip(res.getString(6));
contrat.setEtat(etat);
listeContrat.add(contrat);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeContrat;
}
public Contrat insert(final Contrat contrat) {
final Contrat[] result = new Contrat[1];
result[0] = null;
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement psmt = null;
try {
psmt = connection.prepareStatement(INSERT_CONTRAT);
psmt.setString(1,contrat.getStatut());
psmt.setInt(2, contrat.getTypeContrat().getId());
psmt.setInt(3,contrat.getEtat().getCdeEtat());
psmt.executeUpdate();
} finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("select idCont, statutCont,ttypecontrat.idTypeCont, ttypecontrat.descripTypeCont, TEtat.idEtat,TEtat.descripEtat from tcontrat, ttypecontrat, TEtat where tcontrat.idTypeCont = ttypecontrat.idTypeCont and tcontrat.idEtat = TEtat.idEtat and idCont = LAST_INSERT_ID()");
if (res.next()) {
result[0] = new Contrat(res.getInt(1));
result[0].setStatut(res.getString(2));
TypeContrat typeContrat = new TypeContrat(res.getInt(3));
typeContrat.setLibelle(res.getString(4));
result[0].setTypeContrat(typeContrat);
Etat etat = new Etat(res.getInt(5));
etat.setDescrip(res.getString(6));
result[0].setEtat(etat);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public Contrat update(final Contrat contrat) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(UPDATE_CONTRAT);
smt.setString(1, contrat.getStatut());
smt.setInt(2, contrat.getTypeContrat().getId());
smt.setInt(3, contrat.getEtat().getCdeEtat());
smt.setInt(4, contrat.getId());
smt.executeUpdate();
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return contrat;
}
}