package dao.implementation.mysql.compteUser;
import modele.compteUser.RoleUser;
import dao.IRoleUserDao;
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;
import java.util.Collection;
/**
* class MySqlRoleUserDao
* implemente les m�thode:
* insert(final RoleUser roleUser),
* update(final RoleUser roleUser),
* find(final int id),
* findAll(),
* delete(finalRoleUser roleUser)
* findByLoginName(final String loginName)
*
* @author Kasandra
*
*/
public class MySqlRoleUserDao implements IRoleUserDao {
private static final String INSERT_ROLE_USER = "insert into troleuser (descripRole) values (?)";
private static final String UPDATE_ROLE_USER = "update troleuser SET descripRole = ? where idRole = ?";
private static final String DELETE_ROLE_USER = "delete from troleuser where idRole = ?";
private static final String GET_ALL_ROLE_USER = "select idRole, descripRole from troleuser";
private static final String GET_ROLE_USER_BY_ID = GET_ALL_ROLE_USER + " where idRole = ?";
private static final String GET_BY_LOGIN_NAME ="select r.idRole, r.descripRole from troleuser r , tattributionrolecompteuser a, tcompteuser u where r.idRole = a.idRole and a.idCompte=u.idCompte and u.loginUser=?";
public void delete(final RoleUser roleUser) {
//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_ROLE_USER);
smt.setInt(1, roleUser.getId());
smt.executeUpdate();
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
}
public RoleUser find(final int id) {
final RoleUser[] result = new RoleUser[1];
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement smt = null;
ResultSet res = null;
try {
smt = connection.prepareStatement(GET_ROLE_USER_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
result[0] = new RoleUser(res.getInt(1));
result[0].setLibelle(res.getString(2));
}
}
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<RoleUser> findAll() {
final ArrayList<RoleUser> listeRoleUser = new ArrayList<RoleUser>();
//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_ROLE_USER);
res = smt.executeQuery();
while (res.next()) {
RoleUser roleUser = new RoleUser(res.getInt(1));
roleUser.setLibelle(res.getString(2));
listeRoleUser.add(roleUser);
}
}
finally {
try {
if (res != null) {
res.close();
}
}
finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeRoleUser;
}
public RoleUser insert(final RoleUser roleUser) {
final RoleUser[] result = new RoleUser[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_ROLE_USER);
psmt.setString(1,roleUser.getLibelle());
psmt.executeUpdate();
}
finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("SELECT idRole, descripRole from troleuser where idRole = LAST_INSERT_ID()");
if (res.next()) {
result[0] = new RoleUser(res.getInt(1));
result[0].setLibelle(res.getString(2));
}
}
finally {
try {
if (res != null) {
res.close();
}
}
finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return result[0];
}
public RoleUser update( final RoleUser roleUser) {
//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_ROLE_USER);
smt.setString(1, roleUser.getLibelle());
smt.setInt(2, roleUser.getId());
smt.executeUpdate();
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return roleUser;
}
public Collection<RoleUser> findByLoginName(final String loginName) {
final ArrayList<RoleUser> listeRoleUser = new ArrayList<RoleUser>();
//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_BY_LOGIN_NAME);
smt.setString(1, loginName);
res = smt.executeQuery();
while (res.next()) {
RoleUser roleUser = new RoleUser(res.getInt(1));
roleUser.setLibelle(res.getString(2));
listeRoleUser.add(roleUser);
}
}
finally {
try {
if (res != null) {
res.close();
}
}
finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return listeRoleUser;
}
}