package dao.implementation.mysql.compteUser;
import modele.commun.Etat;
import modele.compteUser.CompteUser;
import dao.ICompteUserDao;
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> MySqlCompteUserDao est une classe Dao qui implemente les m�thodes de l'interface ICompteUserDao</>
* <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 MySqlCompteUserDao implements ICompteUserDao {
private static final String INSERT_COMPTE_USER = "insert into TCompteUser ( nomUser, prenomUser, mailUser, loginUser, pwd, idEtat) values (?,?,?,?,?,?)";
private static final String UPDATE_COMPTE_USER = "update TCompteUser set nomUser = ? , prenomUser = ? , mailUser = ?, loginUser = ?, pwd = ?, idEtat = ? where idCompte = ?";
private static final String DELETE_COMPTE_USER = "delete from TCompteUser where idCompte = ?";
private static final String GET_ALL_COMPTE_USER = "select idCompte, nomUser, prenomUser, mailUser, loginUser, pwd, TEtat.idEtat,TEtat.descripEtat from TCompteUser, TEtat where TCompteUser.idEtat = TEtat.idEtat";
private static final String GET_ALL_COMPTE_USER_ACTIF = "select idCompte, nomUser, prenomUser, mailUser, loginUser, pwd, TEtat.idEtat,TEtat.descripEtat from TCompteUser, TEtat where TCompteUser.idEtat = TEtat.idEtat AND TEtat.descripEtat != 'inactif' ";
private static final String GET_COMPTE_BY_ID = GET_ALL_COMPTE_USER + " and idCompte = ?";
private static final String CHECK_COMPTE_EXIST = "select count(*) from TCompteUser where loginUser = ? and pwd = ?";
public void delete(final CompteUser compteUser) {
//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_COMPTE_USER);
smt.setInt(1, compteUser.getId());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
}
public CompteUser find(final int id) {
final CompteUser[] result = new CompteUser[1];
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_COMPTE_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
result[0] = new CompteUser(res.getInt(1));
result[0].setNom(res.getString(2));
result[0].setPrenom(res.getString(3));
result[0].setMail(res.getString(4));
result[0].setLogin(res.getString(5));
result[0].setPassWord(res.getString(6));
Etat etat = new Etat(res.getInt(7));
etat.setDescrip(res.getString(8));
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<CompteUser> findAll() {
final ArrayList<CompteUser> listeCompteUser = new ArrayList<CompteUser>();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ALL_COMPTE_USER);
res = smt.executeQuery();
while (res.next()) {
CompteUser compteUser = new CompteUser(res.getInt(1));
compteUser.setNom(res.getString(2));
compteUser.setPrenom(res.getString(3));
compteUser.setMail(res.getString(4));
compteUser.setLogin(res.getString(5));
compteUser.setPassWord(res.getString(6));
Etat etat = new Etat(res.getInt(7));
etat.setDescrip(res.getString(8));
compteUser.setEtat(etat);
listeCompteUser.add(compteUser);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeCompteUser;
}
public CompteUser insert(final CompteUser compteUser) {
final CompteUser[] result = new CompteUser[1];
result[0] = null;
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement psmt = null;
try {
psmt = connection.prepareStatement(INSERT_COMPTE_USER);
psmt.setString(1,compteUser.getNom());
psmt.setString(2,compteUser.getPrenom());
psmt.setString(3,compteUser.getMail());
psmt.setString(4,compteUser.getLogin());
psmt.setString(5,compteUser.getPassWord());
psmt.setInt(6,compteUser.getEtat().getCdeEtat());
psmt.executeUpdate();
} finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("select idCompte, nomUser, prenomUser, mailUser, loginUser, pwd, TEtat.idEtat,TEtat.descripEtat from TCompteUser, TEtat where TCompteUser.idEtat = TEtat.idEtat and idCompte = LAST_INSERT_ID()");
if (res.next()) {
result[0] = new CompteUser(res.getInt(1));
result[0].setNom(res.getString(2));
result[0].setPrenom(res.getString(3));
result[0].setMail(res.getString(4));
result[0].setLogin(res.getString(5));
result[0].setPassWord(res.getString(6));
Etat etat = new Etat(res.getInt(7));
etat.setDescrip(res.getString(8));
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 CompteUser update(final CompteUser compteUser) {
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
try {
smt = connection.prepareStatement(UPDATE_COMPTE_USER);
smt.setString(1, compteUser.getNom());
smt.setString(2, compteUser.getPrenom());
smt.setString(3, compteUser.getMail());
smt.setString(4, compteUser.getLogin());
smt.setString(5, compteUser.getPassWord());
smt.setInt(6, compteUser.getEtat().getCdeEtat());
smt.setInt(7, compteUser.getId());
smt.executeUpdate();
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return compteUser;
}
public boolean existe(final String login, final String motDePasse) {
final boolean[] existe = new boolean[1];
existe[0]= false;
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(CHECK_COMPTE_EXIST);
smt.setString(1, login);
smt.setString(2, motDePasse);
res = smt.executeQuery();
if (res.next()) {
existe[0] = (res.getInt(1) == 1);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return existe[0];
}
@Override
public ArrayList<CompteUser> findAllActif() {
final ArrayList<CompteUser> listeCompteUser = new ArrayList<CompteUser>();
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ALL_COMPTE_USER_ACTIF);
res = smt.executeQuery();
while (res.next()) {
CompteUser compteUser = new CompteUser(res.getInt(1));
compteUser.setNom(res.getString(2));
compteUser.setPrenom(res.getString(3));
compteUser.setMail(res.getString(4));
compteUser.setLogin(res.getString(5));
compteUser.setPassWord(res.getString(6));
Etat etat = new Etat(res.getInt(7));
etat.setDescrip(res.getString(8));
compteUser.setEtat(etat);
listeCompteUser.add(compteUser);
}
} finally {
try {
if (res != null) {
res.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeCompteUser;
}
}