/**
* Inserts a new entry in the `session` table.
*
* @param id I
* @param root
* @return A unique key associated with the user
* @throws InstantiationException
* @throws IllegalAccessException
* @throws ClassNotFoundException
* @throws SQLException
*/
public static String insertSession(int id, boolean root)
throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
/* SESSION_HOUR_DURATIONS starting from now */
Timestamp expires =
new Timestamp(System.currentTimeMillis() + SESSION_HOUR_DURATION * 60 * 60 * 1000);
String key = generateKey();
String sql =
"INSERT INTO `session` " + "(`key`, `user_id`, `expires`, `root`)" + " VALUE(?,?,?,?)";
Connection connection = DataBaseUtils.getMySQLConnection();
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);
ps.setString(1, key);
ps.setInt(2, id);
ps.setTimestamp(3, expires);
ps.setBoolean(4, root);
ps.executeUpdate();
System.out.println("Session inserted for id : " + id);
ps.close();
connection.close();
return key;
}
private void validateAdd(RentalSchema p) {
// TODO: Aqui se anade la data a la tabla de RentMovie
try {
String query =
"INSERT INTO rentmovie (MoviesID, CustomersID, Rented, RentedOn, ReturnedOn)"
+ "VALUES (?, ?, ?, ?, ?)";
////////////
Class.forName("com.mysql.jdbc.Driver"); // MySQL database connection
Connection conn =
(Connection)
DriverManager.getConnection(
"jdbc:mysql://us-cdbr-azure-east-a.cloudapp.net:3306/movierental?"
+ "user=b80812adafee28&password=5b6f9d25");
PreparedStatement pst = (PreparedStatement) conn.prepareStatement(query);
//////////////////////////////
pst.setInt(1, p.getMoviesId());
pst.setInt(2, p.getCustomersId());
pst.setBoolean(3, true);
pst.setString(4, p.getRentedOn());
pst.setString(5, "Not yet Returned!");
JOptionPane.showMessageDialog(null, "Data was saved sccessfully");
pst.executeUpdate();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
JOptionPane.showMessageDialog(
null, "There was some problem with the connection. Please try again!");
e.printStackTrace();
}
}
/**
* Updates the subArea with the fireAlarm or Sensor when enabled or disabled
*
* @param sa
*/
public static void updateSubArea(subArea sa) {
try {
System.out.println("Here !!!");
conn =
DriverManager.getConnection(
"jdbc:mysql://localhost/sosecurity?" + "user=ssuser&password=sspwd");
try {
// String getSensor = "UPDATE sensor SET subAreaID=2, userID=1,
// isArmed = true WHERE sensorID=1";
String updateSubArea = "UPDATE subArea SET hasSensor= ?, hasFire=? WHERE subAreaID=?";
System.out.println(updateSubArea);
PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(updateSubArea);
preparedStmt.setBoolean(1, sa.getHasSensor());
preparedStmt.setBoolean(2, sa.getHasFire());
preparedStmt.setInt(3, sa.getSubAreaID());
// execute the java prepared statement
int affectedRow = preparedStmt.executeUpdate();
System.out.println(affectedRow);
} finally {
// It's important to close the connection when you are done with
// it
try {
conn.close();
} catch (Throwable ignore) {
/*
* Propagate the original exception instead of this one that
* you may want just logged
*/
}
}
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
return;
}
public void lockIds(String marketName, String[] ids, boolean isLocked) {
try {
PreparedStatement pstmt;
pstmt = (PreparedStatement) conn.prepareStatement(LOCK_IDS_QUERY);
for (String id : ids) {
pstmt.setBoolean(1, isLocked);
pstmt.setString(2, marketName);
pstmt.setString(3, id);
pstmt.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* upDate the sensor with new start and end time and also enabled/disabled information in Database
*
* @param sensor
*/
public static void updateSensor(Sensor sensor) {
System.out.println("Update sensor " + sensor.toString());
try {
conn =
DriverManager.getConnection(
"jdbc:mysql://localhost/sosecurity?" + "user=ssuser&password=sspwd");
try {
// String getSensor = "UPDATE sensor SET subAreaID=2, userID=1,
// isArmed = true WHERE sensorID=1";
String updateSubArea =
"UPDATE sensor SET subAreaID= ? , userID= ?, isArmed=?, start_time=?, end_time= ? "
+ "WHERE sensorID=?";
System.out.println(updateSubArea);
PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(updateSubArea);
preparedStmt.setInt(1, sensor.getSubAreaID());
preparedStmt.setLong(2, sensor.getUserID());
preparedStmt.setBoolean(3, sensor.getIsArmed());
preparedStmt.setDate(4, new java.sql.Date(sensor.getFromTime().getTime()));
if (sensor.getToDate() == null) {
preparedStmt.setDate(5, null);
} else {
preparedStmt.setDate(5, new java.sql.Date(sensor.getToDate().getTime()));
}
preparedStmt.setInt(6, sensor.getSensorID());
// execute the java prepared statement
int affectedRow = preparedStmt.executeUpdate();
System.out.println(affectedRow);
} finally {
// It's important to close the connection when you are done with
// it
try {
conn.close();
} catch (Throwable ignore) {
/*
* Propagate the original exception instead of this one that
* you may want just logged
*/
ignore.printStackTrace();
}
}
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
return;
}
