else if (evt.getSource() == addNewEntryButton) {
if ( con == null ) {
Print("Not connected.");
return;
}
try {
boolean dataIsValid = true;
java.sql.Date deadlineOfDelivery = null;
if (idField.getText().length() != 0 ) { // if ID field is filled, it has to be filled with an integer.
try { Integer.parseInt(idField.getText()); }
catch (NumberFormatException n) {
result.append("The ID field must contain an integer.\n");
dataIsValid = false;
}
}
if ( dataIsValid ) { //vehicle type must be g/c/d/v/l/a
if ( !vehicleType.getText().equals("g")
&& !vehicleType.getText().equals("c")
&& !vehicleType.getText().equals("d")
&& !vehicleType.getText().equals("v")
&& !vehicleType.getText().equals("l")
&& !vehicleType.getText().equals("a") ) {
result.append("The vehicle type must be either g, c, d, v, l or a.\n");
dataIsValid = false;
}
}
if ( dataIsValid ) { //quantity must be an integer
try { Integer.parseInt(quantity.getText()); }
catch (NumberFormatException n) {
result.append("The quantity field must contain an integer.\n");
dataIsValid = false;
}
}
if ( dataIsValid ) { // date check
dataIsValid = ( deadline.getText().matches("[0-9]{12}") || deadline.getText().length() == 0 );
if (!dataIsValid) result.append("The input string is not using the valid date format.");
}
if ( dataIsValid ) { // if a date was given, its format is checked
if (deadline.getText().length() != 0) {
try {
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmm");
formatter.setLenient(false);
deadlineOfDelivery = new java.sql.Date(formatter.parse(deadline.getText()).getTime());
}
catch (ParseException e) {
result.append("The date format is invalid.\n");
dataIsValid = false;
}
}
}
if ( dataIsValid ) { // description, origin and destination check
dataIsValid = !( origin.getText().length() == 0 || destination.getText().length() == 0 || description.getText().length() == 0 );
if ( !dataIsValid ) result.append("You cannot leave neither the description, the origin nor destination fields empty.\n");
}
if ( dataIsValid ) {
int vehicleID = Integer.parseInt(idField.getText());
String descr = description.getText();
String typeOfVehicle = vehicleType.getText();
int quantityOfOrder = Integer.parseInt(quantity.getText());
String whereItComesFrom = origin.getText();
String whereItGoesTo = destination.getText();
String commentOnDelivery = comments.getText();
// inserts a completely new entry. used when date and ID were given for the generation.
PreparedStatement insertNewEntry = con.prepareStatement("INSERT INTO orders (id, description, vehicle_type, quantity, origin, destination, " +
"deadline, comments) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)");
// updates an old entry. used when date was given.
PreparedStatement updateOldEntry = con.prepareStatement("UPDATE orders SET description = ?, vehicle_type = ?," +
"quantity = ?, origin = ?, destination = ?, deadline = ?, comments = ? WHERE id = ?");
// inserts a completely new entry. used when only date was given.
PreparedStatement insertNewID = con.prepareStatement("INSERT INTO orders (id, description, vehicle_type, quantity, origin, destination, " +
"deadline, comments) VALUES ( sequence_1.nextval, ?, ?, ?, ?, ?, ?, ?)");
// inserts a new entry. used when only ID was given.
PreparedStatement insertDefDate = con.prepareStatement("INSERT INTO orders (id, description, vehicle_type, quantity, origin, destination, " +
"comments) VALUES (?, ?, ?, ?, ?, ?, ?)");
// updates an entry. used when no date was given.
PreparedStatement updateDefDate = con.prepareStatement("UPDATE orders SET description = ?, vehicle_type = ?," +
"quantity = ?, origin = ?, destination = ?, deadline = default, comments = ? WHERE id = ?");
// inserts a new entry. used when neither date nor ID was given.
PreparedStatement insertDefDateID = con.prepareStatement("INSERT INTO orders (id, description, vehicle_type, quantity, origin, destination, " +
"comments) VALUES ( sequence_1.nextval, ?, ?, ?, ?, ?, ?)");
if ( deadline.getText().length() == 0 ) { // date should be default
if ( idField.getText().length() == 0 ) { // id should be default
insertDefDateID.setString(1, descr);
insertDefDateID.setString(2, typeOfVehicle);
insertDefDateID.setInt(3, quantityOfOrder);
insertDefDateID.setString(4, whereItComesFrom);
insertDefDateID.setString(5, whereItGoesTo);
insertDefDateID.setString(6, commentOnDelivery);
insertDefDate.execute();
result.append("The entry has been succesfully added with a newly generated ID and default date settings.\n");
}
else { // an ID was given
Statement searchForID = con.createStatement();
ResultSet rs = searchForID.executeQuery("SELECT * FROM orders WHERE id = " + vehicleID);
if (rs.next()) { // found old entry, updating
updateDefDate.setString(1, descr);
updateDefDate.setString(2, typeOfVehicle);
updateDefDate.setInt(3, quantityOfOrder);
updateDefDate.setString(4, whereItComesFrom);
updateDefDate.setString(5, whereItGoesTo);
updateDefDate.setString(6, commentOnDelivery);
updateDefDate.setInt(7, vehicleID);
updateDefDate.executeUpdate();
result.append("The entry with ID " + vehicleID + " has been successfully updated using default date settings.\n");
}
else { // new entry with ID given and default date
insertDefDate.setInt(1, vehicleID);
insertDefDate.setString(2, descr);
insertDefDate.setString(3, typeOfVehicle);
insertDefDate.setInt(4, quantityOfOrder);
insertDefDate.setString(5, whereItComesFrom);
insertDefDate.setString(6, whereItGoesTo);
insertDefDate.setString(7, commentOnDelivery);
insertDefDate.execute();
result.append("The entry with ID " + vehicleID + " and default date settingshas been successfully added.\n");
}
}
}
else { // a date was given
if ( idField.getText().length() == 0 ) { //there was no id input, so a new id is given and the entry is made
insertNewID.setString(1, descr);
insertNewID.setString(2, typeOfVehicle);
insertNewID.setInt(3, quantityOfOrder);
insertNewID.setString(4, whereItComesFrom);
insertNewID.setString(5, whereItGoesTo);
insertNewID.setDate(6, deadlineOfDelivery);
insertNewID.setString(7, commentOnDelivery);
insertNewID.execute();
result.append("The entry has been succesfully added with a newly generated ID.\n");
}
else { // if an ID was given, it may update or create a row
Statement searchForID = con.createStatement();
ResultSet rs = searchForID.executeQuery("SELECT * FROM orders WHERE id = " + vehicleID);
if (rs.next()) { // there was a line with such id, it is updated with a given date
updateOldEntry.setString(1, descr);
updateOldEntry.setString(2, typeOfVehicle);
updateOldEntry.setInt(3, quantityOfOrder);
updateOldEntry.setString(4, whereItComesFrom);
updateOldEntry.setString(5, whereItGoesTo);
updateOldEntry.setDate(6, deadlineOfDelivery);
updateOldEntry.setString(7, commentOnDelivery);
updateOldEntry.setInt(8, vehicleID);
updateOldEntry.executeUpdate();
result.append("The entry with ID " + vehicleID + " has been successfully updated.\n");
}
else { // new entry with given date and ID
insertNewEntry.setInt(1, vehicleID);
insertNewEntry.setString(2, descr);
insertNewEntry.setString(3, typeOfVehicle);
insertNewEntry.setInt(4, quantityOfOrder);
insertNewEntry.setString(5, whereItComesFrom);
insertNewEntry.setString(6, whereItGoesTo);
insertNewEntry.setDate(7, deadlineOfDelivery);
insertNewEntry.setString(8, commentOnDelivery);
insertNewEntry.execute();
result.append("The entry with ID " + vehicleID + " has been successfully added.\n");
}
}
}
}
}
catch (SQLException e) { result.append(e.toString()); }
}