package dao.implementation.mysql.chauffeur;
import modele.chauffeur.Localite;
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 MySqlLocaliteDao
* impl�mente les m�thodes DAO
* insert(final Localite localite)
* update(final Localite localite)
* find(final int id)
* findAll()
* delete( final Localite localite) *
* Cette classe permet l'acc�s avec la base de donn�es
* @author Kasandra
*
*/
public class MySqlLocaliteDao implements IDao<Localite>{
private static final String INSERT_LOCALITE = "insert into tlocalite ( cp, commune, idVille) values (?,?,?)";
private static final String UPDATE_LOCALITE = "update tlocalite set cp = ?, commune = ?, idVille = ? where cp = ?";
private static final String DELETE_LOCALITE = "delete from tlocalite where cp = ?";
private static final String GET_ALL_LOCALITE = "select cp, commune, tville.idVille, tville.nomVille from tlocalite, tville where tlocalite.idVille = tville.idVille ";
private static final String GET_LOCALITE_BY_ID = GET_ALL_LOCALITE + " where cp = ?";
public void delete(final Localite localite) {
//�tablit la connexion avec la base de donn�es et la synchronise
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(DELETE_LOCALITE);
smt.setInt(1, localite.getCp());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
}
public Localite find(final int id) {
final Localite[] result = new Localite[1];
//�tablit la connexion avec la base de donn�es et la synchronise
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_LOCALITE_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
result[0] = new Localite(res.getInt(1));
//result[0].setCp(Integer.parseInt(res.getString(1)));
result[0].setCommune(res.getString(2));
Ville ville = new Ville(res.getInt(3));
ville.setNomVille(res.getString(4));
result[0].setVille(ville);
}
} 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<Localite> findAll() {
final ArrayList<Localite> listeLocalite = new ArrayList<Localite>();
//�tablit la connexion avec la base de donn�es et la synchronise
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ALL_LOCALITE);
res = smt.executeQuery();
while (res.next()) {
Localite localite = new Localite(res.getInt(1));
//localite.setCp(Integer.parseInt(res.getString(2)));
localite.setCommune(res.getString(2));
Ville ville = new Ville(res.getInt(3));
ville.setNomVille(res.getString(4));/////////////////////
localite.setVille(ville);
listeLocalite.add(localite);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeLocalite;
}
public Localite insert(final Localite localite) {
final Localite[] result = new Localite[1];
result[0] = null;
//�tablit la connexion avec la base de donn�es et la synchronise
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement psmt = null;
try {
psmt = connection.prepareStatement(INSERT_LOCALITE);
psmt.setInt(1,localite.getCp());
psmt.setString(2,localite.getCommune());
psmt.setInt(3,localite.getVille().getId());
psmt.executeUpdate();
}
finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("select cp, commune, tville.idVille,tville.nomVille from tlocalite, tville where tlocalite.idVille = tville.idVille ");//and idLoca = LAST_INSERT_ID()");
if (res.next()) {
result[0] = new Localite(res.getInt(1));
result[0].setCommune(res.getString(2));
Ville ville = new Ville(res.getInt(3));
ville.setNomVille(res.getString(4));
result[0].setVille(ville);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public Localite update(final Localite localite) {
//�tablit la connexion avec la base de donn�es et la synchronise
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(UPDATE_LOCALITE);
smt.setInt(1, localite.getCp());
smt.setString(2, localite.getCommune());
smt.setInt(3, localite.getVille().getId());
smt.executeUpdate();
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return localite;
}
}