You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
143 lines
4.3 KiB
Java
143 lines
4.3 KiB
Java
import java.sql.Connection;
|
|
import java.sql.DriverManager;
|
|
import java.sql.ResultSet;
|
|
import java.sql.SQLException;
|
|
import java.sql.Statement;
|
|
import java.util.Date;
|
|
|
|
|
|
public class BanqueJDBC {
|
|
|
|
Connection conn;
|
|
|
|
public BanqueJDBC(String nomBD) {
|
|
try {
|
|
Class.forName("org.h2.Driver");
|
|
conn = DriverManager.getConnection("jdbc:h2:"+nomBD+";IGNORECASE=TRUE", "sa", "");
|
|
// on cree un objet Statement qui va permettre l'execution des requetes
|
|
Statement s = conn.createStatement();
|
|
|
|
// On regarde si la table existe deja
|
|
String query = "select id from BANQUE limit 1";
|
|
try {
|
|
s.executeQuery(query);
|
|
} catch(Exception e) {
|
|
// sinon on l'a cree
|
|
s.execute("create table BANQUE ( " +
|
|
" id VARCHAR( 256 ) NOT NULL PRIMARY KEY, " +
|
|
" solde REAL , " +
|
|
" dateDerniereOperation TIMESTAMP)");
|
|
// on ajoute des entrees de test
|
|
s.executeUpdate("insert into BANQUE values ('Toto', 1000.5, CURRENT_TIMESTAMP())");
|
|
s.executeUpdate("insert into BANQUE values ('Titi', 500.5, CURRENT_TIMESTAMP())");
|
|
s.executeUpdate("insert into BANQUE values ('Tata', 20.0, CURRENT_TIMESTAMP())");
|
|
s.executeUpdate("insert into BANQUE values ('Tutu', 2000.0, CURRENT_TIMESTAMP())");
|
|
}
|
|
} catch(Exception e) {
|
|
// il y a eu une erreur
|
|
e.printStackTrace();
|
|
}
|
|
}
|
|
|
|
public boolean creerCompte(String id, double somme) {
|
|
try {
|
|
Statement s = conn.createStatement();
|
|
ResultSet rs = s.executeQuery("select solde from BANQUE where id = '"+id+"'");
|
|
if (rs.next()) {
|
|
// un compte existe deja avec cet id
|
|
return false;
|
|
} else {
|
|
s.executeUpdate("insert into BANQUE values ('"+id+"', "+somme+", CURRENT_TIMESTAMP())");
|
|
return true;
|
|
}
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public boolean ajouter(String id, double somme) {
|
|
try {
|
|
Statement s = conn.createStatement();
|
|
ResultSet rs = s.executeQuery("select solde from BANQUE where id = '"+id+"'");
|
|
if (rs.next()) {
|
|
double solde = rs.getDouble("solde");
|
|
solde+=somme;
|
|
if (s.executeUpdate("update BANQUE set solde="+solde+",dateDerniereOperation=CURRENT_TIMESTAMP() where id = '"+id+"'")==1)
|
|
return true;
|
|
else
|
|
return false;
|
|
} else {
|
|
return false;
|
|
}
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public boolean retirer(String id, double somme) {
|
|
try {
|
|
Statement s = conn.createStatement();
|
|
ResultSet rs = s.executeQuery("select solde from BANQUE where id = '"+id+"'");
|
|
if (rs.next()) {
|
|
double solde = rs.getDouble("solde");
|
|
solde-=somme;
|
|
if (s.executeUpdate("update BANQUE set solde="+solde+",dateDerniereOperation=CURRENT_TIMESTAMP() where id = '"+id+"'")==1)
|
|
return true;
|
|
else
|
|
return false;
|
|
} else {
|
|
return false;
|
|
}
|
|
} catch (SQLException e) {
|
|
e.printStackTrace();
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public Position position(String id) {
|
|
try {
|
|
Statement s = conn.createStatement();
|
|
ResultSet rs = s.executeQuery("select solde,dateDerniereOperation from BANQUE where id = '"+id+"'");
|
|
if (rs.next()) {
|
|
double solde = rs.getDouble("solde");
|
|
Date date = rs.getTimestamp("dateDerniereOperation");
|
|
Position p = new Position(solde);
|
|
p.setDerniereOperation(date);
|
|
return p;
|
|
} else {
|
|
return null;
|
|
}
|
|
} catch(Exception ex) {
|
|
// il y a eu une erreur
|
|
ex.printStackTrace();
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public void fermer() throws Exception {
|
|
try {
|
|
conn.close();
|
|
} catch(Exception ex) {
|
|
// il y a eu une erreur
|
|
ex.printStackTrace();
|
|
}
|
|
}
|
|
|
|
public static void main(String[] args) throws Exception {
|
|
BanqueJDBC banque = new BanqueJDBC("banque");
|
|
|
|
banque.creerCompte("Bobby", 1000);
|
|
System.out.println(" Compte Bobby : "+banque.position("Bobby"));
|
|
banque.creerCompte("Bob", 1000);
|
|
System.out.println(" Compte Bob : "+banque.position("Bob"));
|
|
banque.ajouter("Bobby", 100);
|
|
banque.retirer("Bobby", 150);
|
|
System.out.println(" Compte Bobby : "+banque.position("Bobby"));
|
|
|
|
banque.fermer();
|
|
}
|
|
|
|
}
|