Notes

...package database;

import java.sql.*;
import java.util.*;

import dataClasses.*;
import dataClasses.Driver;

public class DatabaseMethods {
private Connection conn;

public DatabaseMethods(Connection conn) {
this.conn = conn;
}

/*
* Accepts: Nothing
* Behaviour: Retrieves information about all accounts
* Returns: List of account objects
*/
public ArrayList<Account> getAllAccounts() throws SQLException {
ArrayList<Account> accounts = new ArrayList<Account>();

// TODO: Implement
String selectAllAccountSql = "SELECT accounts.ID as ID,FIRST_NAME,LAST_NAME,BIRTHDATE,STREET,CITY,PROVINCE,POSTAL_CODE,PHONE_NUMBER,EMAIL FROM accounts INNER JOIN addresses ON accounts.ADDRESS_ID=addresses.ID";
PreparedStatement selectAllAccountStmt = conn.prepareStatement(selectAllAccountSql);
ResultSet selectAllAccountResultSet = selectAllAccountStmt.executeQuery();

while (selectAllAccountResultSet.next()) {
int accountId = selectAllAccountResultSet.getInt("ID");
String firstName = selectAllAccountResultSet.getString("FIRST_NAME");
String lastName = selectAllAccountResultSet.getString("LAST_NAME");
String birthdate = selectAllAccountResultSet.getString("BIRTHDATE");
String street = selectAllAccountResultSet.getString("STREET");
String city = selectAllAccountResultSet.getString("CITY");
String province = selectAllAccountResultSet.getString("PROVINCE");
String postalCode = selectAllAccountResultSet.getString("POSTAL_CODE");
String phoneNumber = selectAllAccountResultSet.getString("PHONE_NUMBER");
String email = selectAllAccountResultSet.getString("EMAIL");
boolean isPassenger = false, isDriver = false;

String isPassengerSql = "SELECT * FROM passengers WHERE id=?";
PreparedStatement isPassengerStmt = conn.prepareStatement(isPassengerSql);
isPassengerStmt.setInt(1, accountId);
ResultSet isPassengerResultSet = isPassengerStmt.executeQuery();
if (isPassengerResultSet.isBeforeFirst()) {
isPassenger = true;
}

String isDriverSql = "SELECT * FROM drivers WHERE id=?";
PreparedStatement isDriverStmt = conn.prepareStatement(isDriverSql);
isDriverStmt.setInt(1, accountId);
ResultSet isDriverResultSet = isDriverStmt.executeQuery();
if (isDriverResultSet.isBeforeFirst()) {
isDriver = true;
}
Account account = new Account(firstName, lastName, street, city, province, postalCode, phoneNumber, email,
birthdate, isPassenger, isDriver);
accounts.add(account);
}
return accounts;
}

/*
* Accepts: Email address of driver
* Behaviour: Calculates the average rating over all rides performed by the
* driver specified by the email address
* Returns: The average rating value
*/
public double getAverageRatingForDriver(String driverEmail) throws SQLException {
double averageRating = 0.0;

// TODO: Implement
String findDriverIdSql = "SELECT ID FROM accounts WHERE EMAIL = ?";
PreparedStatement findDriverIdStmt = conn.prepareStatement(findDriverIdSql);
findDriverIdStmt.setString(1, driverEmail);
ResultSet driverIdResultSet = findDriverIdStmt.executeQuery();

int driverId = -1;
if (driverIdResultSet.next()) {
driverId = driverIdResultSet.getInt("ID");
} else {
// Driver with the specified email not found
return -1; // Or handle accordingly
}

String retrieveRidesSql = "SELECT RATING_FROM_PASSENGER FROM rides WHERE DRIVER_ID = ?";
PreparedStatement retrieveRidesStmt = conn.prepareStatement(retrieveRidesSql);
retrieveRidesStmt.setInt(1, driverId);
ResultSet ridesResultSet = retrieveRidesStmt.executeQuery();
int totalRatings = 0;
int numberOfRides = 0;
while (ridesResultSet.next()) {
int rating = ridesResultSet.getInt("RATING_FROM_PASSENGER");
if (rating != 0) {
totalRatings += rating;
numberOfRides++;
}
}
if (numberOfRides != 0) {
averageRating = (double) totalRatings / numberOfRides;
}

return averageRating;
}

/*
* Accepts: Account details, and passenger and driver specific details.
* Passenger or driver details could be
* null if account is only intended for one type of use.
* Behaviour:
* - Insert new account using information provided in Account object
* - For non-null passenger/driver details, insert the associated data into the
* relevant tables
* Returns: Nothing
*/
public void createAccount(Account account, Passenger passenger, Driver driver) throws SQLException {
// TODO: Implement
// Hint: Use the available insertAccount, insertPassenger, and insertDriver
// methods
int accId = insertAccount(account);
if (passenger != null) {
insertPassenger(passenger, accId);
}
if (driver != null) {
insertDriver(driver, accId);
}
}

/*
* Accepts: Account details (which includes address information)
* Behaviour: Inserts the new account, as well as the account's address if it
* doesn't already exist. The new/existing address should
* be linked to the account
* Returns: Id of the new account
*/
public int insertAccount(Account account) throws SQLException {
int accountId = -1;
int addressId = -1;

// Check if the address already exists
String checkAddressSql = "SELECT ID FROM addresses WHERE STREET = ? AND CITY = ? AND PROVINCE = ? AND POSTAL_CODE = ?";
PreparedStatement checkAddressStmt = conn.prepareStatement(checkAddressSql);
checkAddressStmt.setString(1, account.getStreet());
checkAddressStmt.setString(2, account.getCity());
checkAddressStmt.setString(3, account.getProvince());
checkAddressStmt.setString(4, account.getPostalCode());
ResultSet addressResultSet = checkAddressStmt.executeQuery();

if (addressResultSet.next()) {
// Address already exists, retrieve its ID
addressId = addressResultSet.getInt("ID");
} else {
// Address doesn't exist, insert the new address
addressId = insertAddressIfNotExists(
new Address(account.getStreet(), account.getCity(), account.getProvince(), account.getPostalCode()));
}

// Step 2: Insert the new account
String insertAccountSql = "INSERT INTO accounts (FIRST_NAME, LAST_NAME, BIRTHDATE, ADDRESS_ID, PHONE_NUMBER, EMAIL) VALUES (?, ?, ?, ?, ?, ?)";
PreparedStatement insertAccountStmt = conn.prepareStatement(insertAccountSql, Statement.RETURN_GENERATED_KEYS);
insertAccountStmt.setString(1, account.getFirstName());
insertAccountStmt.setString(2, account.getLastName());
insertAccountStmt.setString(3, account.getBirthdate());
insertAccountStmt.setInt(4, addressId);
insertAccountStmt.setString(5, account.getPhoneNumber());
insertAccountStmt.setString(6, account.getEmail());
insertAccountStmt.executeUpdate();

// Retrieve the generated account ID
ResultSet generatedAccountKeys = insertAccountStmt.getGeneratedKeys();
if (generatedAccountKeys.next()) {
accountId = generatedAccountKeys.getInt(1);
} else {
// Failed to retrieve generated account ID
// Handle error
}

// Step 3: Return the ID of the newly inserted account
return accountId;
}

/*
* Accepts: Passenger details (should not be null), and account id for the
* passenger
* Behaviour: Inserts the new passenger record, correctly linked to the account
* id
* Returns: Id of the new passenger
*/
public int insertPassenger(Passenger passenger, int accountId) throws SQLException {
// TODO: Implement
// int passengerId = -1;

// Step 1: Insert the new passenger record
String insertPassengerSql = "INSERT INTO passengers (ID, CREDIT_CARD_NUMBER) VALUES (?, ?)";
PreparedStatement insertPassengerStmt = conn.prepareStatement(insertPassengerSql, Statement.RETURN_GENERATED_KEYS);
insertPassengerStmt.setInt(1, accountId); // Assuming passenger ID is same as account ID
insertPassengerStmt.setString(2, passenger.getCreditCardNumber());
insertPassengerStmt.executeUpdate();

// Retrieve the generated passenger ID
ResultSet generatedPassengerKeys = insertPassengerStmt.getGeneratedKeys();
if (generatedPassengerKeys.next()) {
accountId = generatedPassengerKeys.getInt(1);
} else {
// Failed to retrieve generated passenger ID
// Handle error
}

// Step 2: Return the ID of the newly inserted passenger
// return passengerId;
return accountId;
}

/*
* Accepts: Driver details (should not be null), and account id for the driver
* Behaviour: Inserts the new driver and driver's license record, correctly
* linked to the account id
* Returns: Id of the new driver
*/
public int insertDriver(Driver driver, int accountId) throws SQLException {
// TODO: Implement
// Hint: Use the insertLicense method

int driverId = -1;
int licenseId = insertLicense(driver.getLicenseNumber(), driver.getLicenseExpiryDate());

// Step 1: Insert the driver details into the 'drivers' table
String insertDriverSql = "INSERT INTO drivers (ID, LICENSE_ID) VALUES (?, ?)";
PreparedStatement insertDriverStmt = conn.prepareStatement(insertDriverSql, Statement.RETURN_GENERATED_KEYS);
insertDriverStmt.setInt(1, accountId);
insertDriverStmt.setInt(2, licenseId);
insertDriverStmt.executeUpdate();

// Get the generated driver ID
ResultSet generatedKeys = insertDriverStmt.getGeneratedKeys();
if (generatedKeys.next()) {
driverId = generatedKeys.getInt(1);
} else {
// Handle error: Unable to retrieve generated driver ID
}
return driverId;
}

/*
* Accepts: Driver's license number and license expiry
* Behaviour: Inserts the new driver's license record
* Returns: Id of the new driver's license
*/
public int insertLicense(String licenseNumber, String licenseExpiry) throws SQLException {
int licenseId = -1;

// Step 1: Insert the new driver's license record
String insertLicenseSql = "INSERT INTO licenses (NUMBER, EXPIRY_DATE) VALUES (?, ?)";
PreparedStatement insertLicenseStmt = conn.prepareStatement(insertLicenseSql, Statement.RETURN_GENERATED_KEYS);
insertLicenseStmt.setString(1, licenseNumber);
insertLicenseStmt.setString(2, licenseExpiry);
insertLicenseStmt.executeUpdate();

// Retrieve the generated license ID
ResultSet generatedLicenseKeys = insertLicenseStmt.getGeneratedKeys();
if (generatedLicenseKeys.next()) {
licenseId = generatedLicenseKeys.getInt(1);
} else {
// Failed to retrieve generated license ID
// Handle error
}

// Step 2: Return the ID of the newly inserted driver's license
return licenseId;
}

/*
* Accepts: Address details
* Behaviour:
* - Checks if an address with these properties already exists.
* - If it does, gets the id of the existing address.
* - If it does not exist, creates the address in the database, and gets the id
* of the new address
* Returns: Id of the address
*/
public int insertAddressIfNotExists(Address address) throws SQLException {
int addressId = -1;

// Step 1: Check if an address with the provided details already exists
String checkAddressSql = "SELECT ID FROM addresses WHERE STREET = ? AND CITY = ? AND PROVINCE = ? AND POSTAL_CODE = ?";
PreparedStatement checkAddressStmt = conn.prepareStatement(checkAddressSql);
checkAddressStmt.setString(1, address.getStreet());
checkAddressStmt.setString(2, address.getCity());
checkAddressStmt.setString(3, address.getProvince());
checkAddressStmt.setString(4, address.getPostalCode());
ResultSet addressResultSet = checkAddressStmt.executeQuery();

if (addressResultSet.next()) {
// Address already exists, retrieve its ID
addressId = addressResultSet.getInt("ID");
} else {
// Step 2: Create the address in the database
String insertAddressSql = "INSERT INTO addresses (STREET, CITY, PROVINCE, POSTAL_CODE) VALUES (?, ?, ?, ?)";
PreparedStatement insertAddressStmt = conn.prepareStatement(insertAddressSql, Statement.RETURN_GENERATED_KEYS);
insertAddressStmt.setString(1, address.getStreet());
insertAddressStmt.setString(2, address.getCity());
insertAddressStmt.setString(3, address.getProvince());
insertAddressStmt.setString(4, address.getPostalCode());
insertAddressStmt.executeUpdate();

// Retrieve the generated address ID
ResultSet generatedKeys = insertAddressStmt.getGeneratedKeys();
if (generatedKeys.next()) {
addressId = generatedKeys.getInt(1);
} else {
// Failed to retrieve generated address ID
// Handle error
}
}

// Step 3: Return the ID of the address
return addressId;
}

/*
* Accepts: Name of new favourite destination, email address of the passenger,
* and the id of the address being favourited
* Behaviour: Finds the id of the passenger with the email address, then inserts
* the new favourite destination record
* Returns: Nothing
*/
public void insertFavouriteDestination(String favouriteName, String passengerEmail, int addressId)
throws SQLException {
int passengerId = getPassengerIdFromEmail(passengerEmail);
String insertFavDestSql = "INSERT INTO favourite_locations (PASSENGER_ID,LOCATION_ID,NAME) VALUES(?,?,?)";
PreparedStatement insertFavDestStmt = conn.prepareStatement(insertFavDestSql);
insertFavDestStmt.setInt(1, passengerId);
insertFavDestStmt.setInt(2, addressId);
insertFavDestStmt.setString(3, favouriteName);
insertFavDestStmt.executeUpdate();
}

/*
* Accepts: Email address
* Behaviour: Determines if a driver exists with the provided email address
* Returns: True if exists, false if not
*/
public boolean checkDriverExists(String email) throws SQLException {
boolean exists = false;

// Step 1: Query the database to check if a driver exists with the provided
// email address
String checkDriverSql = "SELECT COUNT(*) AS driverCount FROM drivers INNER JOIN accounts ON drivers.ID = accounts.ID WHERE EMAIL = ?";
PreparedStatement checkDriverStmt = conn.prepareStatement(checkDriverSql);
checkDriverStmt.setString(1, email);
ResultSet driverCountResultSet = checkDriverStmt.executeQuery();

if (driverCountResultSet.next()) {
int driverCount = driverCountResultSet.getInt("driverCount");
exists = (driverCount > 0); // Set to true if driver exists, false otherwise
}

// Step 2: Return the result
return exists;
}

/*
* Accepts: Email address
* Behaviour: Determines if a passenger exists with the provided email address
* Returns: True if exists, false if not
*/
public boolean checkPassengerExists(String email) throws SQLException {
boolean exists = false;

// Step 1: Query the database to check if a passenger exists with the provided
// email address
String checkPassengerSql = "SELECT COUNT(*) AS passengerCount FROM passengers INNER JOIN accounts ON passengers.ID = accounts.ID WHERE EMAIL = ?";
PreparedStatement checkPassengerStmt = conn.prepareStatement(checkPassengerSql);
checkPassengerStmt.setString(1, email);
ResultSet passengerCountResultSet = checkPassengerStmt.executeQuery();

if (passengerCountResultSet.next()) {
int passengerCount = passengerCountResultSet.getInt("passengerCount");
exists = (passengerCount > 0); // Set to true if passenger exists, false otherwise
}

// Step 2: Return the result
return exists;
}

/*
* Accepts: Email address of passenger making request, id of dropoff address,
* requested date/time of ride, and number of passengers
* Behaviour: Inserts a new ride request, using the provided properties
* Returns: Nothing
*/
public void insertRideRequest(String passengerEmail, int dropoffLocationId, String date, String time,
int numberOfPassengers) throws SQLException {
int passengerId = getPassengerIdFromEmail(passengerEmail);
int pickupAddressId = getAccountAddressIdFromEmail(passengerEmail);
String insertRideRequestSql = "INSERT INTO ride_requests (PASSENGER_ID,PICKUP_LOCATION_ID,PICKUP_DATE,PICKUP_TIME,NUMBER_OF_RIDERS,DROPOFF_LOCATION_ID) VALUES(?,?,?,?,?,?)";
PreparedStatement insertRideRequestStmt = conn.prepareStatement(insertRideRequestSql);
insertRideRequestStmt.setInt(1, passengerId);
insertRideRequestStmt.setInt(2, pickupAddressId);
insertRideRequestStmt.setString(3, date);
insertRideRequestStmt.setString(4, time);
insertRideRequestStmt.setInt(5, numberOfPassengers);
insertRideRequestStmt.setInt(6, dropoffLocationId);
insertRideRequestStmt.executeUpdate();
}

/*
* Accepts: Email address
* Behaviour: Gets id of passenger with specified email (assumes passenger
* exists)
* Returns: Id
*/
public int getPassengerIdFromEmail(String passengerEmail) throws SQLException {
int passengerId = -1;

// Query the database to get the ID of the passenger with the specified email
String getPassengerIdSql = "SELECT passengers.ID FROM passengers INNER JOIN accounts ON passengers.ID = accounts.ID WHERE EMAIL = ?";
PreparedStatement getPassengerIdStmt = conn.prepareStatement(getPassengerIdSql);
getPassengerIdStmt.setString(1, passengerEmail);
ResultSet passengerIdResultSet = getPassengerIdStmt.executeQuery();

// Extract the passenger ID from the result set
if (passengerIdResultSet.next()) {
passengerId = passengerIdResultSet.getInt("ID");
}

// Return the passenger ID
return passengerId;
}

/*
* Accepts: Email address
* Behaviour: Gets id of driver with specified email (assumes driver exists)
* Returns: Id
*/
public int getDriverIdFromEmail(String driverEmail) throws SQLException {
int driverId = -1;

// Query the database to get the ID of the driver with the specified email
String getDriverIdSql = "SELECT drivers.ID FROM drivers INNER JOIN accounts ON drivers.ID = accounts.ID WHERE EMAIL = ?";
PreparedStatement getDriverIdStmt = conn.prepareStatement(getDriverIdSql);
getDriverIdStmt.setString(1, driverEmail);
ResultSet driverIdResultSet = getDriverIdStmt.executeQuery();

// Extract the driver ID from the result set
if (driverIdResultSet.next()) {
driverId = driverIdResultSet.getInt("ID");
}

// Return the driver ID
return driverId;
}

/*
* Accepts: Email address
* Behaviour: Gets the id of the address tied to the account with the provided
* email address
* Returns: Address id
*/
public int getAccountAddressIdFromEmail(String email) throws SQLException {
int addressId = -1;

// Query the database to get the ID of the address tied to the account with the
// provided email address
String getAddressIdSql = "SELECT ADDRESS_ID FROM accounts WHERE EMAIL = ?";
PreparedStatement getAddressIdStmt = conn.prepareStatement(getAddressIdSql);
getAddressIdStmt.setString(1, email);
ResultSet addressIdResultSet = getAddressIdStmt.executeQuery();

// Extract the address ID from the result set
if (addressIdResultSet.next()) {
addressId = addressIdResultSet.getInt("ADDRESS_ID");
}

// Return the address ID
return addressId;
}

/*
* Accepts: Email address of passenger
* Behaviour: Gets a list of all the specified passenger's favourite
* destinations
* Returns: List of favourite destinations
*/
public ArrayList<FavouriteDestination> getFavouriteDestinationsForPassenger(String passengerEmail)
throws SQLException {
ArrayList<FavouriteDestination> favouriteDestinations = new ArrayList<FavouriteDestination>();

// Query the database to retrieve all favorite destinations for the specified
// passenger
String getFavouriteDestinationsSql = "SELECT fl.NAME,ad.ID,ad.STREET,ad.CITY,ad.PROVINCE,ad.POSTAL_CODE FROM favourite_locations fl INNER JOIN addresses ad ON fl.LOCATION_ID=ad.ID INNER JOIN passengers p ON fl.PASSENGER_ID=p.ID INNER JOIN accounts ac ON p.ID=ac.ID WHERE ac.EMAIL=?";
PreparedStatement getFavouriteDestinationsStmt = conn.prepareStatement(getFavouriteDestinationsSql);
getFavouriteDestinationsStmt.setString(1, passengerEmail);
ResultSet favouriteDestinationsResultSet = getFavouriteDestinationsStmt.executeQuery();

// Iterate through the result set and populate the list of favorite destinations
while (favouriteDestinationsResultSet.next()) {
int addressId = favouriteDestinationsResultSet.getInt("ID");
String name = favouriteDestinationsResultSet.getString("NAME");
String street = favouriteDestinationsResultSet.getString("STREET");
String city = favouriteDestinationsResultSet.getString("CITY");
String province = favouriteDestinationsResultSet.getString("PROVINCE");
String postalCode = favouriteDestinationsResultSet.getString("POSTAL_CODE");

// Create a new FavouriteDestination object and add it to the list
FavouriteDestination favouriteDestination = new FavouriteDestination(name, addressId, street, city, province,
postalCode);
favouriteDestinations.add(favouriteDestination);
}

return favouriteDestinations;
}

/*
* Accepts: Nothing
* Behaviour: Gets a list of all uncompleted ride requests (i.e. requests
* without an associated ride record)
* Returns: List of all uncompleted rides
*/
public ArrayList<RideRequest> getUncompletedRideRequests() throws SQLException {
ArrayList<RideRequest> uncompletedRideRequests = new ArrayList<RideRequest>();

// Query the database to retrieve all uncompleted ride requests
String getUncompletedRideRequestsSql = "SELECT rr.ID, a1.FIRST_NAME AS PASSENGER_FIRST_NAME, a1.LAST_NAME AS PASSENGER_LAST_NAME, "
+
"a2.STREET AS PICKUP_STREET, a2.CITY AS PICKUP_CITY, a3.STREET AS DROPOFF_STREET, a3.CITY AS DROPOFF_CITY, " +
"rr.PICKUP_DATE, rr.PICKUP_TIME " +
"FROM ride_requests rr " +
"INNER JOIN passengers p ON rr.PASSENGER_ID = p.ID " +
"INNER JOIN accounts a1 ON p.ID = a1.ID " +
"INNER JOIN addresses a2 ON rr.PICKUP_LOCATION_ID = a2.ID " +
"LEFT JOIN addresses a3 ON rr.DROPOFF_LOCATION_ID = a3.ID " +
"LEFT JOIN rides r ON rr.ID = r.REQUEST_ID " +
"WHERE r.REQUEST_ID IS NULL";

PreparedStatement getUncompletedRideRequestsStmt = conn.prepareStatement(getUncompletedRideRequestsSql);
ResultSet uncompletedRideRequestsResultSet = getUncompletedRideRequestsStmt.executeQuery();

while (uncompletedRideRequestsResultSet.next()) {
int id = uncompletedRideRequestsResultSet.getInt("ID");
String passengerFirstName = uncompletedRideRequestsResultSet.getString("PASSENGER_FIRST_NAME");
String passengerLastName = uncompletedRideRequestsResultSet.getString("PASSENGER_LAST_NAME");
String pickupStreet = uncompletedRideRequestsResultSet.getString("PICKUP_STREET");
String pickupCity = uncompletedRideRequestsResultSet.getString("PICKUP_CITY");
String dropoffStreet = uncompletedRideRequestsResultSet.getString("DROPOFF_STREET");
String dropOffCity = uncompletedRideRequestsResultSet.getString("DROPOFF_CITY");
;
String desiredPickupDate = uncompletedRideRequestsResultSet.getString("PICKUP_DATE");
;
String desiredPickupTime = uncompletedRideRequestsResultSet.getString("PICKUP_TIME");
;

RideRequest rideRequest = new RideRequest(id, passengerFirstName, passengerLastName, pickupStreet, pickupCity,
dropoffStreet, dropOffCity, desiredPickupDate, desiredPickupTime);

uncompletedRideRequests.add(rideRequest);
}

return uncompletedRideRequests;
}

/*
* Accepts: Ride details
* Behaviour: Inserts a new ride record
* Returns: Nothing
*/
public void insertRide(Ride ride) throws SQLException {
// TODO: Implement
// Hint: Use getDriverIdFromEmail
int driverId = getDriverIdFromEmail(ride.getDriverEmail());
String insertRideSql = "INSERT INTO rides (DRIVER_ID,REQUEST_ID,ACTUAL_START_DATE,ACTUAL_START_TIME,ACTUAL_END_DATE,ACTUAL_END_TIME,RATING_FROM_DRIVER,RATING_FROM_PASSENGER,DISTANCE,CHARGE) VALUES(?,?,?,?,?,?,?,?,?,?)";
PreparedStatement insertRideStmt = conn.prepareStatement(insertRideSql);
insertRideStmt.setInt(1, driverId);
insertRideStmt.setInt(2, ride.getRideRequestId());
insertRideStmt.setString(3, ride.getStartDate());
insertRideStmt.setString(4, ride.getStartTime());
insertRideStmt.setString(5, ride.getEndDate());
insertRideStmt.setString(6, ride.getEndTime());
insertRideStmt.setInt(7, ride.getRatingFromDriver());
insertRideStmt.setInt(8, ride.getRatingFromPassenger());
insertRideStmt.setDouble(9, ride.getDistance());
insertRideStmt.setDouble(10, ride.getCharge());
insertRideStmt.executeUpdate();
}

}