/**
*
*/
package dao.implementation.mysql.chauffeur;
import modele.chauffeur.Pays;
import dao.IPaysDao;
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;
/**
* <p> MySqlPaysDao est une classe Dao qui implemente les m�thodes de l'interface IPaysDao</>
* <p> Elle �tablit la connexion avec la base de donn�es et la synchronise</p>
* <p> Les m�thodes de cette classe permettent soit ajouter, modifier, supprimer ou chercher le(s) enregistrement(s).</p>
*
* @author Kasandra
*
*/
public class MySqlPaysDao implements IPaysDao {
private static final String INSERT_PAYS = "insert into TPays (codePays, nomPays) values (?,?)";
private static final String UPDATE_PAYS = "update TPays set codePays = ? , nomPays = ? where idPays = ?";
private static final String DELETE_PAYS = "delete from TPays where idPays = ?";
private static final String GET_ALL_PAYS = "select idPays, codePays, nomPays from TPays";
private static final String GET_PAYS_BY_ID = GET_ALL_PAYS + " where idPays = ?";
private static final String GET_PAYS_BY_CODE = GET_ALL_PAYS + " where codePays = ?";
public void delete(final Pays pays) {
//Connexion BD, synchronise acc�s � la base de donn�es
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(DELETE_PAYS);
smt.setInt(1, pays.getId());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
}
public Pays find(final int id) {
final Pays[] result = new Pays[1];
//Connexion BD, synchronise acc�s � la base de donn�es
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_PAYS_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
result[0] = new Pays(res.getInt(1));
result[0].setCode(res.getString(2));
result[0].setNom(res.getString(3));
}
} 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<Pays> findAll() {
final ArrayList<Pays> listePays = new ArrayList<Pays>();
//Connexion BD, synchronise acc�s � la base de donn�es
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ALL_PAYS);
res = smt.executeQuery();
while (res.next()) {
Pays pays = new Pays(res.getInt(1));
pays.setCode(res.getString(2));
pays.setNom(res.getString(3));
listePays.add(pays);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listePays;
}
public Pays insert(final Pays pays) {
final Pays[] result = new Pays[1];
result[0] = null;
//Connexion BD, synchronise acc�s � la base de donn�es
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement psmt = null;
try {
psmt = connection.prepareStatement(INSERT_PAYS);
psmt.setString(1,pays.getCode());
psmt.setString(2,pays.getNom());
psmt.executeUpdate();
} finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("SELECT idPays, codePays, nomPays from TPays where idPays = LAST_INSERT_ID()");
if (res.next()) {
result[0] = new Pays(res.getInt(1));
result[0].setCode(res.getString(2));
result[0].setNom(res.getString(3));
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public Pays update(final Pays pays) {
//Connexion BD, synchronise acc�s � la base de donn�es
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(UPDATE_PAYS);
smt.setString(1, pays.getCode());
smt.setString(2, pays.getNom());
smt.setInt(3, pays.getId());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return pays;
}
public Pays findByCode(final String codePays) {
final Pays[] result = new Pays[1];
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_PAYS_BY_CODE);
smt.setString(1, codePays);
res = smt.executeQuery();
if (res.next()) {
result[0] = new Pays(res.getInt(1));
result[0].setCode(res.getString(2));
result[0].setNom(res.getString(3));
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
}