package dao.implementation.mysql.chauffeur;
import modele.chauffeur.Pays;
import modele.chauffeur.Ville;
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 MySqlVilleDao
* @author Kasandra
*
*/
public class MySqlVilleDao implements IDao<Ville> {
private static final String INSERT_VILLE = "insert into TVille (nomVille, idPays) values (?,?)";
private static final String UPDATE_VILLE = "update TVille set nomVille = ?, idPays = ? where idVille = ?";
private static final String DELETE_VILLE = "delete from TVille where idVille = ?";
private static final String GET_ALL_VILLE = "select idVille, nomVille, tpays.idPays, tpays.codePays from TVille, tpays where tpays.idPays = tville.idPays";
private static final String GET_VILLE_BY_ID = GET_ALL_VILLE + " where idVille = ?";
public void delete(final Ville ville) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(DELETE_VILLE);
smt.setInt(1, ville.getId());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
}
public Ville find(final int id) {
final Ville[] result = new Ville[1];
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_VILLE_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
result[0] = new Ville(res.getInt(1));
Pays pays = new Pays(res.getInt(2));
pays.setNom(res.getString(3));
result[0].setPays(pays);
}
} 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<Ville> findAll() {
final ArrayList<Ville> listeVille = new ArrayList<Ville>();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ALL_VILLE);
res = smt.executeQuery();
while (res.next()) {
Ville ville = new Ville(res.getInt(1));
ville.setNomVille(res.getString(2));
Pays pays = new Pays(res.getInt(3));
pays.setCode(res.getString(4));/////////////////////
ville.setPays(pays);
listeVille.add(ville);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeVille;
}
public Ville insert(final Ville ville) {
final Ville[] result = new Ville[1];
result[0] = null;
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement psmt = null;
try {
psmt = connection.prepareStatement(INSERT_VILLE);
psmt.setString(1,ville.getNomVille());
psmt.setInt(2,ville.getPays().getId());
psmt.executeUpdate();
}
finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("select idVille, nomVille, tpays.idPays, tpays.codePays from TVille, tpays where tpays.idPays = tville.idPays and idVille = LAST_INSERT_ID()");
if (res.next()) {
result[0] = new Ville(res.getInt(1));
result[0].setNomVille(res.getString(2));
Pays pays = new Pays(res.getInt(3));
pays.setCode(res.getString(4));
result[0].setPays(pays);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public Ville update(final Ville ville) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(UPDATE_VILLE);
smt.setString(1, ville.getNomVille());
smt.setInt(2, ville.getPays().getId());
smt.setInt(3, ville.getId());
smt.executeUpdate();
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return ville;
}
}