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()); } }