package dao.implementation.mysql.commun;
import modele.commun.Etat;
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 MySqlEtatDao
* impl�mente les m�thodes DAO
* insert(final Etat etat)
* update(final Etat etat)
* find(final int id)
* findAll()
* delete( final Etat etat) *
* Cette classe permet l'acc�s avec la base de donn�es
*
* @author Kasandra
*
*/
public class MySqlEtatDao implements IDao<Etat>{
private static final String INSERT_ETAT = "insert into tetat( descripEtat, typeEtat) values (?, ?)";
private static final String UPDATE_ETAT = "update tetat set descripEtat = ?, typeEtat = ? where idEtat = ?";
private static final String DELETE_ETAT = "delete from tetat where idEtat = ?";
private static final String GET_ALL_ETAT = "select idEtat, descripEtat, typeEtat from tetat";
private static final String GET_ETAT_BY_ID = GET_ALL_ETAT + " and idEtat = ?";
public void delete( final Etat etat) {
//�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_ETAT);
smt.setInt(1, etat.getCdeEtat());
smt.executeUpdate();
} finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
}
public Etat find(final int id) {
final Etat[] result = new Etat[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_ETAT_BY_ID);
smt.setInt(1, id);
res = smt.executeQuery();
if (res.next()) {
result[0] = new Etat(res.getInt(1));
result[0].setDescrip(res.getString(2));
result[0].setTypeEtat(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<Etat> findAll() {
final ArrayList<Etat> etats = new ArrayList<Etat>();
//�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 resultSet = null;
try {
smt = connection.prepareStatement("SELECT idEtat, descripEtat, typeEtat from TETAT");
resultSet = smt.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt("idEtat");
Etat etat = new Etat(id);
etat.setDescrip(resultSet.getString("descripEtat"));
etat.setTypeEtat(resultSet.getString("typeEtat"));
etats.add(etat);
}
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
} finally {
if (smt != null) {
smt.close();
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return etats;
}
public Etat insert(final Etat etat) {
final Etat[] result = new Etat[1];
result[0] = null;
//�tablit la connexion avec la base de donn�es et le synchronise
AccessMySql.withConnection(new AccessMySql.RunnableWithConnection() {
public void runWithconnection(Connection connection) {
try {
PreparedStatement psmt = null;
try {
psmt = connection.prepareStatement(INSERT_ETAT);
psmt.setString(1,etat.getDescrip());
psmt.setString(2,etat.getTypeEtat());
psmt.executeUpdate();
} finally {
if (psmt != null) {
psmt.close();
}
}
Statement smt = null;
ResultSet res = null;
try {
smt = connection.createStatement();
res = smt.executeQuery("select idEtat, descripEtat, typeEtat from tetat where idEtat = LAST_INSERT_ID()");
if (res.next()) {
result[0] = new Etat(res.getInt(1));
result[0].setDescrip(res.getString(2));
result[0].setTypeEtat(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 Etat update(final Etat etat) {
//�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_ETAT);
smt.setString(1, etat.getDescrip());
smt.setString(2, etat.getTypeEtat());
smt.setInt(3 , etat.getCdeEtat());//////////////////
smt.executeUpdate();
}
finally {
if (smt != null) {
smt.close();
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
return etat;
}
}