/**
* Insert all service in services table
*
* @param noms
* @throws SQLException
*/
public static void insertStations(List<String[]> pdvs) throws SQLException {
int idPdv;
String nom = "";
String adresse = "";
String ville = "";
String cp = "";
String type_route;
String[] ouvert = new String[3];
String[] fermer = new String[3];
Time hor_Ouv = null;
hor_Ouv.valueOf("00:00:00");
Time hor_Ferm = null;
hor_Ferm.valueOf("00:00:00");
double latitudePdv;
double longitudePdv;
String path = "src\\DAL\\StationsNOM.csv";
List<String> nomStations = LectureFichier.readTXT(path);
Random rand = new Random();
int nbrand = 2;
String requete =
"INSERT INTO stations "
+ "(id_Station,Nom,Adresse,Ville,CP,Type_route,Horaire_ouverture,Horaire_fermeture,latitude,longitude) "
+ "VALUES(?,?,?,?,?,?,?,?,?,?)";
// int[] count= new int[0];
Connection connection = ConnexionManager.GetInstance().GetConnection();
for (String[] pdv : pdvs) {
if (!pdv[0].equals("id")) {
idPdv = Integer.parseInt(pdv[0]);
// integration des noms g�n�r� al�atoirement
nbrand = rand.nextInt(nomStations.size() - 2) + 1;
nom = nomStations.get(nbrand);
adresse = pdv[1];
ville = pdv[2];
cp = pdv[3];
type_route = pdv[4];
ouvert = pdv[5].split(":");
fermer = pdv[6].split(":");
// fermer = pdv[6].replace(':', '-');
hor_Ouv =
new Time(
Integer.parseInt(ouvert[0]),
Integer.parseInt(ouvert[1]),
Integer.parseInt(ouvert[2]));
hor_Ferm =
new Time(
Integer.parseInt(fermer[0]),
Integer.parseInt(fermer[1]),
Integer.parseInt(fermer[2]));
latitudePdv = Double.parseDouble(pdv[7]);
longitudePdv = Double.parseDouble(pdv[8]);
try {
PreparedStatement requeteSql = connection.prepareStatement(requete);
requeteSql.setInt(1, idPdv);
requeteSql.setString(2, nom);
requeteSql.setString(3, adresse);
requeteSql.setString(4, ville);
requeteSql.setString(5, cp);
requeteSql.setString(6, type_route);
requeteSql.setTime(7, hor_Ouv);
requeteSql.setTime(8, hor_Ferm);
requeteSql.setDouble(9, latitudePdv);
requeteSql.setDouble(10, longitudePdv);
requeteSql.addBatch();
int[] count = requeteSql.executeBatch();
// requeteSql.executeBatch();
// System.out.println(count.length);
} catch (SQLException sqle) {
// TODO Auto-generated catch block
ConnexionManager.GetInstance().GetConnection().rollback();
sqle.printStackTrace();
}
}
}
}
public void testSetTime() throws Exception {
if (!min74) {
// We can't do timezones properly for time/timetz values before 7.4
System.err.println("Skipping TimezoneTest.testSetTime on a pre-7.4 server");
return;
}
for (int i = 0; i < PREPARE_THRESHOLD; i++) {
con.createStatement().execute("delete from testtimezone");
PreparedStatement insertTimestamp =
con.prepareStatement("INSERT INTO testtimezone(seq,t,tz) VALUES (?,?,?)");
int seq = 1;
Time tJVM, tUTC, tGMT03, tGMT05, tGMT13;
// +0100 (JVM default)
tJVM = new Time(50400000L); // 1970-01-01 15:00:00 +0100
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTime(2, tJVM); // 15:00:00
insertTimestamp.setTime(3, tJVM); // 15:00:00+03
insertTimestamp.executeUpdate();
// UTC
tUTC = new Time(54000000L); // 1970-01-01 15:00:00 +0000
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTime(2, tUTC, cUTC); // 15:00:00
insertTimestamp.setTime(3, tUTC, cUTC); // 15:00:00+00
insertTimestamp.executeUpdate();
// +0300
tGMT03 = new Time(43200000L); // 1970-01-01 15:00:00 +0300
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTime(2, tGMT03, cGMT03); // 15:00:00
insertTimestamp.setTime(3, tGMT03, cGMT03); // 15:00:00+03
insertTimestamp.executeUpdate();
// -0500
tGMT05 = new Time(72000000L); // 1970-01-01 15:00:00 -0500
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTime(2, tGMT05, cGMT05); // 15:00:00
insertTimestamp.setTime(3, tGMT05, cGMT05); // 15:00:00-05
insertTimestamp.executeUpdate();
// +1300
tGMT13 = new Time(7200000L); // 1970-01-01 15:00:00 +1300
insertTimestamp.setInt(1, seq++);
insertTimestamp.setTime(2, tGMT13, cGMT13); // 15:00:00
insertTimestamp.setTime(3, tGMT13, cGMT13); // 15:00:00+13
insertTimestamp.executeUpdate();
insertTimestamp.close();
// check that insert went correctly by parsing the raw contents in UTC
checkDatabaseContents(
"SELECT seq::text,t::text,tz::text from testtimezone ORDER BY seq",
new String[][] {
new String[] {
"1", "15:00:00", "15:00:00+01",
},
new String[] {
"2", "15:00:00", "15:00:00+00",
},
new String[] {
"3", "15:00:00", "15:00:00+03",
},
new String[] {
"4", "15:00:00", "15:00:00-05",
},
new String[] {
"5", "15:00:00", "15:00:00+13",
}
});
//
// check results
//
seq = 1;
PreparedStatement ps = con.prepareStatement("SELECT seq,t,tz FROM testtimezone ORDER BY seq");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(tJVM, rs.getTime(2));
assertEquals(tJVM, rs.getTime(3));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(tUTC, rs.getTime(2, cUTC));
assertEquals(tUTC, rs.getTime(2, cUTC));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(tGMT03, rs.getTime(2, cGMT03));
assertEquals(tGMT03, rs.getTime(2, cGMT03));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(tGMT05, rs.getTime(2, cGMT05));
assertEquals(tGMT05, rs.getTime(2, cGMT05));
assertTrue(rs.next());
assertEquals(seq++, rs.getInt(1));
assertEquals(tGMT13, rs.getTime(2, cGMT13));
assertEquals(tGMT13, rs.getTime(2, cGMT13));
assertTrue(!rs.next());
ps.close();
}
}
