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
| public void storeVisitorTime(int[] data) throws SQLException {
modifySequenceTable("visitortime");
//We select the last id of the simulation
String query_select_sim = "SELECT id FROM simulation";
ResultSet res_sim = state.executeQuery(query_select_sim);
if(res_sim.last()) {
int id_fk = res_sim.getInt("id"), id_exhibit, id_visitor, id_exhibit_unique;
String query_select_ex, query_insert, query_select_vis;
ResultSet res_ex, res_vis;
PreparedStatement ps;
//Select the exhibit id
query_select_ex = "SELECT e.* FROM exhibitentity e, roomentity r, simulation s ";
query_select_ex += "WHERE e.room_id=r.id AND r.simulation_id=s.id ";
query_select_ex += "AND s.id="+id_fk;
res_ex = state.executeQuery(query_select_ex);
if(res_ex.first()) {
do {
id_exhibit = res_ex.getInt("id");
id_exhibit_unique = res_ex.getInt("exid"); //Select the exhibit id necessary to recory the correct data
query_select_vis = "SELECT v.id FROM visitorentity v, simulation s ";
query_select_vis += "WHERE v.simulation_id=s.id ";
query_select_vis += "AND s.id="+id_fk;
res_vis = state.executeQuery(query_select_vis);
if (res_vis.first()){
do {
//Select the visitor id
id_visitor = res_vis.getInt("id");
query_insert = "INSERT INTO visitortime (visitor_id, exhibit_id, time_enter) ";
query_insert += "VALUES (?,?,?)";
ps = conn.prepareStatement(query_insert);
ps.setInt(1, id_visitor);
ps.setInt(2, id_exhibit);
ps.setInt(3, data[id_exhibit_unique]); //Recovery the correct data
ps.executeUpdate();
} while (res_vis.next()) ;
}
}while (res_ex.next()) ;
}
}else{
throw new SQLException("The table simulation haven't row");
}
} |
Partager