public DataSourceProvider getDataSourceProvider(String name) {
DataSourceProvider ret = null;
for (DataSourceProvider ds : dataSourceProviders) {
if (StringUtils.isNotEmpty(name) && name.equals(ds.getName())) {
return ds;
}
}
return null;
}
@Override
protected ArrayList<Provider> doInBackground(Void... params) {
DataSourceProvider ids = new DataSourceProvider(getApplicationContext());
ids.open();
itemList = ids.getAllProviders();
ids.close();
return itemList;
}
public int CompterCommentaire(Integer idArticle) {
int compt = 0;
try {
/** ** Creation de la connexion *** */
Connection connection = DataSourceProvider.getDataSource().getConnection();
/** ** Utilisation de la connexion *** */
PreparedStatement stmt =
connection.prepareStatement(
"" + "SELECT count(*) FROM t_comment WHERE T_ARTICLE_ART_ID=?");
stmt.setInt(1, idArticle);
ResultSet results = stmt.executeQuery();
while (results.next()) {
compt = results.getInt("count(*)");
}
/** ** Fermer la connexion *** */
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return compt;
}
public List<Commentaire> listerCommentaire(Integer idArticle) {
List<Commentaire> listeCommentaires = new ArrayList<Commentaire>();
try {
/** ** Creation de la connexion *** */
Connection connection = DataSourceProvider.getDataSource().getConnection();
/** ** Utilisation de la connexion *** */
PreparedStatement stmt =
connection.prepareStatement("" + "SELECT * FROM t_comment WHERE T_ARTICLE_ART_ID=?");
stmt.setInt(1, idArticle);
ResultSet results = stmt.executeQuery();
while (results.next()) {
Commentaire commentaire =
new Commentaire(
results.getInt("COM_ID"),
results.getString("COM_TEXT"),
results.getString("COM_NAME"),
results.getString("COM_EMAIL"),
results.getInt("T_ARTICLE_ART_ID"));
listeCommentaires.add(commentaire);
}
/** ** Fermer la connexion *** */
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return listeCommentaires;
}
public List<Paiement> listerPaiementsAEncaisser(java.util.Date date) {
List<Paiement> listePaiements = new ArrayList<Paiement>();
try {
// Créer une nouvelle connexion à la BDD
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"SELECT * FROM `paiement` WHERE YEAR(`echeance`)=? AND MONTH(`echeance`)=?");
stmt.setInt(1, date.getYear() + 1900);
stmt.setInt(2, date.getMonth() + 1);
ResultSet results = stmt.executeQuery();
while (results.next()) {
Paiement paiement =
new Paiement(
results.getInt("idPaiement"),
results.getBoolean("payer"),
results.getString("banque"),
results.getString("numCheque"),
results.getDate("echeance"),
results.getFloat("montant"));
listePaiements.add(paiement);
}
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return listePaiements;
}
@Test
public void testModifierSeance() throws Exception {
Seance seance =
new Seance(
1L,
"monTitre",
"maDescription",
new GregorianCalendar(2014, Calendar.SEPTEMBER, 6).getTime(),
true,
new GregorianCalendar(2014, Calendar.SEPTEMBER, 6, 18, 5).getTime(),
TypeSeance.TP);
seanceDao.modifierSeance(seance);
Connection connection = DataSourceProvider.getInstance().getDataSource().getConnection();
Statement stmt = connection.createStatement();
ResultSet results = stmt.executeQuery("SELECT * FROM seance WHERE id=1");
if (results.next()) {
Assert.assertEquals(1L, results.getLong("id"));
Assert.assertEquals("monTitre", results.getString("titre"));
Assert.assertEquals("maDescription", results.getString("description"));
Assert.assertEquals(
new GregorianCalendar(2014, Calendar.SEPTEMBER, 6, 0, 0).getTime(),
results.getTimestamp("date"));
Assert.assertTrue(results.getBoolean("isnote"));
Assert.assertEquals(
new GregorianCalendar(2014, Calendar.SEPTEMBER, 6, 18, 5).getTime(),
results.getTimestamp("datelimiterendu"));
Assert.assertEquals(TypeSeance.TP, TypeSeance.valueOf(results.getString("type")));
} else {
Assert.fail();
}
stmt.close();
connection.close();
}
@Override
public List<Paiement> listerPaiements(Adherent adherent) {
List<Paiement> listePaiements = new ArrayList<Paiement>();
try {
// Créer une nouvelle connexion à la BDD
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement("SELECT * FROM `paiement` WHERE `numLicence`=?");
stmt.setString(1, adherent.getLicence());
ResultSet results = stmt.executeQuery();
while (results.next()) {
Paiement paiement =
new Paiement(
results.getInt("idPaiement"),
results.getBoolean("payer"),
results.getString("banque"),
results.getString("numCheque"),
results.getDate("echeance"),
results.getFloat("montant"));
listePaiements.add(paiement);
}
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return listePaiements;
}
public List<Participer> listerParticipationforEtudiant(String identifiant) {
List<Participer> listeParticipationforEtudiant = new ArrayList<Participer>();
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"SELECT participer.*, challenge.*, etudiant.*, adresse.* FROM adresse INNER JOIN (challenge INNER JOIN (participer INNER JOIN etudiant ON participer.id_etudiant = etudiant.id_etudiant)ON participer.id_challenge = challenge.id_challenge) ON adresse.id_adr = challenge.id_adresse WHERE participer.id_etudiant = ? ORDER BY challenge.date_challenge DESC");
stmt.setString(1, identifiant);
ResultSet results = stmt.executeQuery();
while (results.next()) {
Participer participer =
new Participer(
results.getString("participer.id_etudiant"),
results.getString("participer.id_challenge"),
results.getString("participer.presence"));
participer.setEtudiant(
new Etudiant(
results.getString("etudiant.id_etudiant"),
results.getString("etudiant.nom_etudiant"),
results.getString("etudiant.prenom_etudiant"),
results.getString("etudiant.classe_etudiant"),
results.getString("etudiant.tel_etudiant"),
results.getString("etudiant.mail_etudiant"),
results.getString("etudiant.photo_etudiant"),
results.getBoolean("etudiant.cotisation_etudiant"),
results.getBoolean("etudiant.certificat_etudiant"),
results.getString("licence_etudiant")));
participer.setChallenge(
new Challenge(
results.getString("challenge.id_challenge"),
results.getString("challenge.nom_challenge"),
results.getDate("challenge.date_challenge"),
results.getTime("challenge.heure_challenge"),
results.getString("challenge.description_challenge"),
results.getString("challenge.id_adresse")));
participer
.getChallenge()
.setAdresse(
new Adresse(
results.getString("adresse.id_adr"),
results.getString("adresse.site_adr"),
results.getString("adresse.num_adr"),
results.getString("adresse.rue_adr"),
results.getString("adresse.cp_adr"),
results.getString("adresse.ville_adr"),
results.getString("adresse.pays_adr")));
listeParticipationforEtudiant.add(participer);
}
// Fermer la connexion
results.close();
stmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return listeParticipationforEtudiant;
}
public List<Participer> listerParticipationByChallenge(String id_challenge) {
List<Participer> listeParticipationByChallenge = new ArrayList<Participer>();
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"SELECT participer.*, challenge.*, adresse.* FROM participer INNER JOIN (challenge INNER JOIN adresse ON challenge.id_adresse = adresse.id_adr) ON participer.id_challenge = challenge.id_challenge WHERE participer.id_challenge = ?");
stmt.setString(1, id_challenge);
ResultSet results = stmt.executeQuery();
while (results.next()) {
Participer participer =
new Participer(
results.getString("participer.id_etudiant"),
results.getString("participer.id_challenge"),
results.getString("participer.presence"));
participer.setChallenge(
new Challenge(
results.getString("challenge.id_challenge"),
results.getString("challenge.nom_challenge"),
results.getDate("challenge.date_challenge"),
results.getTime("challenge.heure_challenge"),
results.getString("challenge.description_challenge"),
results.getString("challenge.id_adresse")));
participer
.getChallenge()
.setAdresse(
new Adresse(
results.getString("adresse.id_adr"),
results.getString("adresse.site_adr"),
results.getString("adresse.num_adr"),
results.getString("adresse.rue_adr"),
results.getString("adresse.cp_adr"),
results.getString("adresse.ville_adr"),
results.getString("adresse.pays_adr")));
listeParticipationByChallenge.add(participer);
}
// Fermer la connexion
results.close();
stmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return listeParticipationByChallenge;
}
public void afficherNotification() {
// TODO Auto-generated method stub
try {
// Créer une nouvelle connexion à la BDD
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt = connection.prepareStatement("UPDATE flag SET notification = ?");
stmt.setBoolean(1, true);
stmt.executeUpdate();
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void nePasEncaisserPaiement(Integer idPaiement) {
// TODO Auto-generated method stub
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement("UPDATE `paiement` SET `payer`='0' WHERE `idPaiement`=?");
stmt.setInt(1, idPaiement);
stmt.executeUpdate();
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Before
public void init() throws Exception {
Connection connection = DataSourceProvider.getInstance().getDataSource().getConnection();
Statement stmt = connection.createStatement();
stmt.executeUpdate("DELETE FROM ressource");
stmt.executeUpdate("DELETE FROM travailutilisateur");
stmt.executeUpdate("DELETE FROM travail");
stmt.executeUpdate("DELETE FROM seance");
stmt.executeUpdate(
"INSERT INTO `seance`(`id`,`titre`,`description`,`date`,`type`) VALUES(1,'cours1','cours de debuggage','2014-07-26','COURS')");
stmt.executeUpdate(
"INSERT INTO `seance`(`id`,`titre`,`description`,`date`,`type`) VALUES(2,'cours2','cours de correction','2014-08-26','COURS')");
stmt.executeUpdate(
"INSERT INTO `seance`(`id`,`titre`,`description`,`date`,`type`,`datelimiterendu`,`isnote`) VALUES(3,'tp1','tp de debuggage','2014-07-29','TP','2014-07-29 18:00:00',true)");
stmt.executeUpdate(
"INSERT INTO `seance`(`id`,`titre`,`description`,`date`,`type`,`datelimiterendu`,`isnote`) VALUES(4,'tp2','tp de correction','2014-08-29','TP','2014-08-29 18:00:00',true)");
stmt.close();
connection.close();
}
public void ModifierPresence(String id_etudiant, String id_challenge, String presence) {
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"UPDATE participer SET presence = ? WHERE id_etudiant = ? AND id_challenge = ?");
stmt.setString(1, presence);
stmt.setString(2, id_etudiant);
stmt.setString(3, id_challenge);
stmt.executeUpdate();
// Fermer la connexion
stmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void AjouterParticipationToChallenge(Participer participer) {
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"INSERT INTO participer (id_etudiant, id_challenge, presence) VALUES (?, ?, ?)");
stmt.setString(1, participer.getId_etudiant());
stmt.setString(2, participer.getId_challenge());
stmt.setString(3, participer.getPresence());
stmt.executeUpdate();
// Fermer la connexion
stmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public boolean etatNotification() {
// TODO Auto-generated method stub
boolean res = false;
try {
// Créer une nouvelle connexion à la BDD
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
Statement stmt = connection.createStatement();
ResultSet results = stmt.executeQuery("SELECT * FROM `flag`");
results.next();
res = results.getBoolean("notification");
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
@Test
public void testAjouterSeanceComplete() throws Exception {
Seance seance =
new Seance(
null,
"monTitre",
"maDescription",
new GregorianCalendar(2014, Calendar.SEPTEMBER, 6).getTime(),
true,
new GregorianCalendar(2014, Calendar.SEPTEMBER, 6, 18, 5).getTime(),
TypeSeance.TP);
Seance seanceCreee = seanceDao.ajouterSeance(seance);
Assert.assertNotNull(seanceCreee.getId());
Connection connection = DataSourceProvider.getInstance().getDataSource().getConnection();
PreparedStatement stmt = connection.prepareStatement("SELECT * FROM seance WHERE id=?");
stmt.setLong(1, seanceCreee.getId());
ResultSet results = stmt.executeQuery();
if (results.next()) {
Assert.assertEquals(seanceCreee.getId().longValue(), results.getLong("id"));
Assert.assertEquals("monTitre", results.getString("titre"));
Assert.assertEquals("maDescription", results.getString("description"));
Assert.assertEquals(
new GregorianCalendar(2014, Calendar.SEPTEMBER, 6, 0, 0).getTime(),
results.getTimestamp("date"));
Assert.assertTrue(results.getBoolean("isnote"));
Assert.assertEquals(
new GregorianCalendar(2014, Calendar.SEPTEMBER, 6, 18, 5).getTime(),
results.getTimestamp("datelimiterendu"));
Assert.assertEquals(TypeSeance.TP, TypeSeance.valueOf(results.getString("type")));
} else {
Assert.fail();
}
stmt.close();
connection.close();
}
@Override
public void ajouterPaiement(Adherent adherent, Paiement paiement) {
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
// Utiliser la connexion
PreparedStatement stmt =
connection.prepareStatement(
"INSERT INTO `paiement`(`payer`, `banque`, `numCheque`, `echeance`, `montant`,`numLicence`) VALUES(?,?,?,?,?,?)");
stmt.setBoolean(1, paiement.isPayer());
stmt.setString(2, paiement.getBanque());
stmt.setString(3, paiement.getNumCheque());
stmt.setDate(4, new Date(paiement.getEcheance().getTime()));
stmt.setFloat(5, paiement.getMontant());
stmt.setString(6, adherent.getLicence());
stmt.executeUpdate();
// Fermer la connexion
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void ajouterCommentaire(Commentaire commentaire) {
/** ** Creation de la connexion *** */
try {
Connection connection = DataSourceProvider.getDataSource().getConnection();
/** ** Utilisation de la connexion *** */
PreparedStatement stmt =
connection.prepareStatement(
""
+ "INSERT INTO t_comment(COM_TEXT,COM_NAME,COM_EMAIL,T_ARTICLE_ART_ID) VALUES (?,?,?,?)");
stmt.setString(1, commentaire.getText());
stmt.setString(2, commentaire.getName());
stmt.setString(3, commentaire.getEmail());
stmt.setInt(4, commentaire.getIdArticle());
stmt.executeUpdate();
/** ** Fermer la connexion *** */
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public DataSource getDataSource(String name) {
DataSourceProvider prov = getDataSourceProvider(name);
if (prov != null) return prov.getDataSource();
return null;
}
public void bind(DataSourceProvider dataSourceProvider) {
log.info("Binding dataSource - " + dataSourceProvider.getName());
dataSourceProviders.add(dataSourceProvider);
}
public void unbind(DataSourceProvider dataSourceProvider) {
log.info("Unbinding dataSource - " + dataSourceProvider.getName());
dataSourceProviders.remove(dataSourceProvider);
}
public <R> R withSql(String dataSourceName, CallableWithArgs<R> callable) {
return provider.withSql(dataSourceName, callable);
}
public <R> R withSql(String dataSourceName, Closure<R> closure) {
return provider.withSql(dataSourceName, closure);
}
protected ReportParameterValue[] getParamValuesFromDataSource(
ReportParameter param, Map<String, Object> parameters) throws ProviderException {
Connection conn = null;
PreparedStatement pStmt = null;
ResultSet rs = null;
try {
ReportDataSource dataSource = param.getDataSource();
conn = dataSourceProvider.getConnection(dataSource.getId());
if (parameters == null || parameters.isEmpty()) {
pStmt = conn.prepareStatement(param.getData());
} else {
}
rs = pStmt.executeQuery();
ResultSetMetaData rsMetaData = rs.getMetaData();
boolean multipleColumns = false;
if (rsMetaData.getColumnCount() > 1) multipleColumns = true;
ArrayList<ReportParameterValue> v = new ArrayList<ReportParameterValue>();
while (rs.next()) {
ReportParameterValue value = new ReportParameterValue();
if (param.getClassName().equals("java.lang.String")) {
value.setId(rs.getString(1));
} else if (param.getClassName().equals("java.lang.Double")) {
value.setId(new Double(rs.getDouble(1)));
} else if (param.getClassName().equals("java.lang.Integer")) {
value.setId(new Integer(rs.getInt(1)));
} else if (param.getClassName().equals("java.lang.Long")) {
value.setId(new Long(rs.getLong(1)));
} else if (param.getClassName().equals("java.math.BigDecimal")) {
value.setId(rs.getBigDecimal(1));
} else if (param.getClassName().equals("java.util.Date")) {
value.setId(rs.getDate(1));
} else if (param.getClassName().equals("java.sql.Date")) {
value.setId(rs.getDate(1));
} else if (param.getClassName().equals("java.sql.Timestamp")) {
value.setId(rs.getTimestamp(1));
}
if (multipleColumns) {
value.setDescription(rs.getString(2));
}
v.add(value);
}
rs.close();
ReportParameterValue[] values = new ReportParameterValue[v.size()];
v.toArray(values);
return values;
} catch (Exception e) {
throw new ProviderException("Error retreiving param values from database: " + e.getMessage());
} finally {
try {
if (pStmt != null) pStmt.close();
if (conn != null) conn.close();
} catch (Exception c) {
log.error("Error closing");
}
}
}
