/** * Stores a single individual into the database. * * <p><b>Note:</b> This method will discard the individual if it has invalid fields. This includes * empty (or null) first and/or last name and malformed email address. In case of any other * serious SQL error, all changes will be rolled back. * * @param individual The individual to insert. * @return The ID of the new individual, or -1 if insertion was unsuccessful. This may be the case * if an individual with exactly the same data already existed. * @throws SQLException */ public synchronized long storeIndividual(Individual individual) throws SQLException { long individualId = -1; try (PreparedStatement stmt = this.connection.prepareStatement(INSERT_INDIVIDUAL); ) { this.setupLocalFieldParameters(individual); try { this.createShowIfNotExists(individual.getEventDate(), individual.getEventVenue()); this.executeIndividualUpdateStatement(stmt); try (Statement getId = this.connection.createStatement()) { try (ResultSet result = getId.executeQuery(GET_NEW_INDIVIDUAL_ID); ) { if (result.next()) individualId = result.getLong(1); } } } catch (SQLException e) { // Leave individualId as -1. LoggerFactory.getLogger().info(e.getMessage()); } } catch (SQLException e) { this.connection.rollback(); throw e; } this.connection.commit(); return individualId; }
/** * Stores a list of individuals into the database. If the number of individuals is large, this is * the preferred method. Inserting individuals one after the other is less efficient. * * <p><b>Note:</b> This method will discard any individuals with invalid fields. This includes * empty (or null) first and/or last names and malformed email addresses. A count of the number of * individuals inserted will be given. In case of any other serious SQL error, all changes will be * rolled back. * * @param list The list of individuals to store. * @return The number of individuals successfully inserted. * @throws SQLException */ public synchronized int storeIndividual(List<Individual> list) throws SQLException { Iterator<Individual> iterator = list.iterator(); Individual current; int numOfIndividualsInserted = 0; try (PreparedStatement stmt = this.connection.prepareStatement(INSERT_INDIVIDUAL); ) { while (iterator.hasNext()) { current = iterator.next(); this.setupLocalFieldParameters(current); try { this.createShowIfNotExists(current.getEventDate(), current.getEventVenue()); this.executeIndividualUpdateStatement(stmt); numOfIndividualsInserted++; } catch (SQLException e) { // Do not increment numOfIndividualsInserted. LoggerFactory.getLogger().info(e.getMessage()); } } } catch (SQLException e) { this.connection.rollback(); throw e; } this.connection.commit(); return numOfIndividualsInserted; }
private void setupLocalFieldParameters(Individual toStore) { if (toStore.getFirstName() == null) { this.fname = null; } else { this.fname = toStore.getFirstName().trim().equals("") ? null : toStore.getFirstName().trim(); } if (toStore.getLastName() == null) { this.lname = null; } else { this.lname = toStore.getLastName().trim().equals("") ? null : toStore.getLastName().trim(); } if (toStore.getEmail() == null) { this.email = null; } else { this.email = toStore.getEmail().trim().equals("") ? null : toStore.getEmail().trim().toLowerCase(); } if (toStore.getSeat() == null) { this.seat = null; } else { this.seat = toStore.getSeat().trim().equals("") ? null : toStore.getSeat().trim(); } this.date = dateToSQLTimestamp(toStore.getEventDate()); this.venue = toStore.getEventVenue(); }