1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117
|
/* Classe Main */
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
public class Main {
/**
* @param args
* @throws SQLException
* @throws ClassNotFoundException
* @throws ParseException
*/
public static void main(String[] args) throws ClassNotFoundException, SQLException, ParseException {
// TODO Auto-generated method stub
SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Connection c;
ConnexionBDD conn = new ConnexionBDD();
c = conn.connect();
Statement state = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
Fonctions fonc = new Fonctions();
fonc.NettoyerTables(c, simple);
}
}
/* Classe Fonctions */
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
public class Fonctions {
@SuppressWarnings({ "unchecked", "deprecation" })
public void NettoyerTables(Connection c, SimpleDateFormat simple) throws SQLException, ParseException{
Timestamp date_present = new Timestamp (new java.util.Date().getTime());
int i = 0;
Statement state = c.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
try{
ResultSet r2 = state.executeQuery("SELECT * FROM table_donnees WHERE (TO_DAYS('" + date_present + "') - TO_DAYS(date) > 14) GROUP BY idcpt ORDER BY date DESC");
while (r2.next() ) {
Timestamp date_ancienne = new Timestamp (simple.parse(r2.getString("date")).getTime());
String idcpt = r2.getString("idcpt");
System.out.println("date ancienne : " + date_ancienne + " //\\ idcpt concerne : " + idcpt);
if(r2.next() && !(r2.isClosed())){
Timestamp date_ancienne2 = new Timestamp (simple.parse(r2.getString("date")).getTime());
if(date_ancienne.getDate() == date_ancienne2.getDate() && date_ancienne.getMonth() == date_ancienne2.getMonth() && date_ancienne.getYear() == date_ancienne2.getYear()){
r2.deleteRow();
}
else{
r2.previous();
}
}
}
}
catch (Exception e){
e.printStackTrace();
}
}
Fonctions() {}
}
/* Classe ConnexionBDD */
import java.sql.*;
import java.util.Properties;
public class ConnexionBDD
{
// The JDBC Connector Class.
private static final String dbClassName = "com.mysql.jdbc.Driver";
private static final String CONNECTION =
"jdbc:mysql://127.0.0.1/dbvalorisation";
public Connection connect() throws
ClassNotFoundException,SQLException
{
// System.out.println("JDBC Connector class : "+dbClassName);
Class.forName(dbClassName);
// Properties for user and password. Here the user and password are both 'gazeo'
Properties p = new Properties();
p.put("user","gazeo");
p.put("password","gazeo");
// Now try to connect
Connection c = DriverManager.getConnection(CONNECTION,p);
// A UTILISER DANS MAIN
/*Statement state = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet result = state.executeQuery("SELECT conso FROM table_donnees_eau WHERE ID = 1");
result.absolute(1);
state.executeUpdate("UPDATE table_donnees_eau SET conso = 12 WHERE ID = 1");
state.close();
c.close();*/
return c;
}
ConnexionBDD() {
}
} |
Partager