Package com.nnamo.services

Class DatabaseService

java.lang.Object
com.nnamo.services.DatabaseService
public class DatabaseService extends Object
Service class for managing database operations related to GTFS data, users, favorites, routes, stops, and metrics.
  • Initializes DAOs and tables
  • Imports and caches GTFS data
  • Provides query methods for models
  • Handles favorite stops and routes
  • Supports fuzzy search for stops and routes
  • Manages realtime metrics and trip updates
Author:
Samuele Lombardi, Davide Galilei

  • Constructor Details

    • DatabaseService

      public DatabaseService(com.j256.ormlite.jdbc.JdbcConnectionSource connection) throws SQLException
      Creates the DatabaseService by creating a connection to the SQLite database and by initializing tables and DAOs
      Throws:
      SQLException

    • DatabaseService

      public DatabaseService(String connectionUrl) throws SQLException
      Throws:
      SQLException

    • DatabaseService

      public DatabaseService() throws SQLException
      Throws:
      SQLException

  • Method Details

    • initTables

      public void initTables() throws SQLException
      Throws:
      SQLException

    • wipeTables

      public void wipeTables() throws SQLException
      Throws:
      SQLException

    • preloadGtfsData

      public void preloadGtfsData(StaticGtfsService gtfs) throws SQLException, IOException, URISyntaxException
      Loads static GTFS data in the database
      Parameters:
      gtfs - StaticGtfsService the GTFS service
      Throws:
      SQLException - if query to the connection goes wrong
      IOException - if static gtfs data can be loaded
      URISyntaxException - if static gtfs data can be loaded

    • needsCaching

      public boolean needsCaching() throws SQLException
      Checks if database needs caching
      Throws:
      SQLException - if query to the connection goes wrong

    • getDao

      public <T, ID> com.j256.ormlite.dao.Dao<T,ID> getDao(Class<T> modelClass)
      Returns a Model's DAO (Database Access Object) by providing the Class of the model
      Parameters:
      modelClass - the Class of the model to return
      Returns:
      Dao if the model's dao exists, null otherwise

    • getAllStops

      public List<StopModel> getAllStops() throws SQLException
      Returns every stop instance from the database
      Returns:
      stop models (empty list if no stop found)
      Throws:
      SQLException

    • getStopById

      public StopModel getStopById(String id) throws SQLException
      Returns a stop by its ID
      Parameters:
      id - String
      Returns:
      StopModel
      Throws:
      SQLException

    • getStopsByName

      public List<StopModel> getStopsByName(String searchTerm) throws SQLException
      Fuzzy search stops by its name. Fuzzy finding means finding strings that match a pattern approximately
      Parameters:
      searchTerm - String
      Returns:
      the list of models with the approximate match
      Throws:
      SQLException - if query fails

    • getRoutesByName

      public List<RouteDirection> getRoutesByName(String searchTerm) throws SQLException
      Fuzzy search routes by its name. Fuzzy finding means finding strings that match a pattern approximately
      Parameters:
      searchTerm - The search term for the route
      Returns:
      the list of routes with the approximate match
      Throws:
      SQLException - if query fails

    • getDirectionedRoutes

      public List<RouteDirection> getDirectionedRoutes(List<RouteModel> routes) throws SQLException
      For each route in the provided list, this method creates a directioned route for each available direction. Which means that the method returns all the provided routes but for each directions. It is pretty useful in order to display routes' trips based on the direction (OUTGOING, INGOING)
      Parameters:
      routes - The routes which we want for each direction
      Returns:
      the list of directioned routes
      Throws:
      SQLException - if query fails

    • getStopRoutes

      public List<RouteModel> getStopRoutes(String stopId) throws SQLException
      Gets the unique routes that go through a specific stop
      Parameters:
      stopId - The ID of the stop
      Returns:
      the list of routes that go through that stop
      Throws:
      SQLException - if query fails

    • getStopTimes

      public List<StopTimeModel> getStopTimes(String stopId) throws SQLException
      Gets the stop times for a specific stop
      Parameters:
      stopId - The ID of the stop
      Returns:
      the list of ALL stoptimes for that stop
      Throws:
      SQLException - if query fails

    • getNextStopTimes

      public List<StopTimeModel> getNextStopTimes(String stopId, LocalTime time) throws SQLException
      Gets the next 6 hours stop times for a specific stop based on the time provided
      Parameters:
      stopId - The ID of the stop
      time - the time from when we want the stop times to start
      Returns:
      the list of next 6 hours (based on the time provided) stoptimes for that stop
      Throws:
      SQLException - if query fails

    • getNextStopTimes

      public List<StopTimeModel> getNextStopTimes(String stopId, LocalTime time, int hours) throws SQLException
      Gets the next X hours stop times for a specific stop based on the time provided
      Parameters:
      stopId - The ID of the stop
      time - the time from when we want the stop times to start
      hours - the hours range from the time provided
      Returns:
      the list of next X hours (based on the time provided) stoptimes for that stop
      Throws:
      SQLException - if query fails

    • getNextStopTimes

      public List<StopTimeModel> getNextStopTimes(String stopId, LocalTime time, Date date, int hours) throws SQLException
      Gets the next X hours stop times for a specific stop based on the time provided and the date provided. This returns only the stop times for trips that run on the provided day.
      Parameters:
      stopId - The ID of the stop
      time - the time from when we want the stop times to start
      date - the date of the returned stop times
      hours - the hours range from the time provided
      Returns:
      the list of next X hours (based on the time and date provided) stoptimes for that stop
      Throws:
      SQLException - if query fails

    • getNextStopTimes

      public List<StopTimeModel> getNextStopTimes(String stopId, LocalTime time, int hours, Date date, List<RealtimeStopUpdate> tripUpdates) throws SQLException
      Gets the next X hours stop times for a specific stop based on the time provided and the date provided. This returns only the stop times for trips that run on the provided day. It also provides the static stop times of realtime trips
      Parameters:
      stopId - The ID of the stop
      time - the time from when we want the stop times to start
      hours - the hours range from the time provided
      date - the date of the returned stop times
      tripUpdates - the trip updates for that stop
      Returns:
      the list of next X hours (based on the time and date provided + the realtime trips) stoptimes for that stop
      Throws:
      SQLException - if query fails

    • getServicesById

      public List<ServiceModel> getServicesById(String serviceId) throws SQLException
      Get services by ID
      Parameters:
      serviceId - The id of the services to get
      Returns:
      services with that ID
      Throws:
      SQLException - if query fails

    • getTripServices

      public List<ServiceModel> getTripServices(TripModel trip) throws SQLException
      Get services of a specific trip by model
      Parameters:
      trip - The model trip with the services
      Returns:
      trip's services
      Throws:
      SQLException - if query fails

    • getTripServices

      public List<ServiceModel> getTripServices(String tripId) throws SQLException
      Get services of a specific trip by ID
      Parameters:
      tripId - The ID of the trip with the services
      Returns:
      trip's services
      Throws:
      SQLException - if query fails

    • addUser

      public void addUser(UserModel user) throws SQLException
      Creates user from model
      Parameters:
      user - User model
      Throws:
      SQLException - if query fails

    • addUser

      public void addUser(String username, String passwordHash) throws SQLException
      Creates user from username and password
      Parameters:
      username - User's username
      passwordHash - User's password
      Throws:
      SQLException - if query fails

    • getUserByName

      public UserModel getUserByName(String username) throws SQLException
      Get user by the username
      Parameters:
      username - User's username
      Returns:
      the user with the provided name, or null if doesn't exist
      Throws:
      SQLException - if query fails

    • getUserById

      public UserModel getUserById(int id) throws SQLException
      Get user by ID
      Parameters:
      id - User's id
      Returns:
      the user with the provided ID, or null if doesn't exist
      Throws:
      SQLException - if query fails

    • addFavStop

      public void addFavStop(int userId, String stopId) throws SQLException
      Add favorite stop to a user's favorite stops list
      Parameters:
      userId - The id of the user adding the favorite stop
      stopId - The id of the favorite stop
      Throws:
      SQLException - if query fails

    • addFavStop

      public void addFavStop(UserModel user, StopModel stop) throws SQLException
      Add favorite stop to a user's favorite stops list
      Parameters:
      user - User model
      stop - Stop model
      Throws:
      SQLException - if query fails

    • removeFavStop

      public void removeFavStop(int userId, String stopId) throws SQLException
      Remove favorite stop from a user's favorite stops list
      Parameters:
      userId - The id of the user adding the favorite stop
      stopId - The id of the favorite stop
      Throws:
      SQLException - if query fails

    • getFavoriteStops

      public List<StopModel> getFavoriteStops(int userId) throws SQLException
      Get user's favorite stops
      Parameters:
      userId - The id of the user who has the favorite stops
      Returns:
      list of user's favorite stops
      Throws:
      SQLException - if query fails

    • getFavoriteStops

      public List<StopModel> getFavoriteStops(UserModel user) throws SQLException
      Get user's favorite stops
      Parameters:
      user - The model of the user who has the favorite stops
      Returns:
      list of user's favorite stops
      Throws:
      SQLException - if query fails

    • getFavoriteStopsByName

      public List<StopModel> getFavoriteStopsByName(int userId, String searchTerm) throws SQLException
      Get user's favorite stops. Fuzzy search
      Parameters:
      userId - The id of the user adding the favorite stop
      searchTerm - Fuzzy pattern to search the stops
      Throws:
      SQLException - if query fails

    • getFavoriteRoutesByName

      public List<RouteDirection> getFavoriteRoutesByName(int userId, String searchTerm) throws SQLException
      Get user's favorite routes. Fuzzy search
      Parameters:
      userId - The id of the user adding the favorite stop
      searchTerm - Fuzzy pattern to search the stops tram, etc.)
      Throws:
      SQLException - if query fails

    • getRouteById

      public RouteModel getRouteById(String id) throws SQLException
      Get route by ID
      Parameters:
      id - route ID
      Returns:
      RouteModel with the specified ID, or null if not found
      Throws:
      SQLException - if query fails

    • getDirectionTrip

      public TripModel getDirectionTrip(String routeId, Direction direction) throws SQLException
      Get a trip for a specific route and direction
      Parameters:
      routeId - String route ID
      direction - Direction enum (INBOUND/OUTBOUND)
      Returns:
      TripModel for the route in the specified direction, or null if not found
      Throws:
      SQLException - if query fails

    • getStaticPosition

      public StaticVehiclePosition getStaticPosition(String routeId, Direction direction, LocalTime fromTime) throws SQLException
      Parameters:
      routeId - String route ID
      direction - Direction enum (INBOUND/OUTBOUND)
      Returns:
      TripModel for the route in the specified direction, or null if not found
      Throws:
      SQLException - if query fails

    • getOrderedStopsForRoute

      public List<StopModel> getOrderedStopsForRoute(String routeId, Direction direction) throws SQLException
      Get ordered stops for a route in a specific direction
      Parameters:
      routeId - String route ID
      direction - Direction enum (INBOUND/OUTBOUND)
      Returns:
      List of stops ordered by arrival time for the route
      Throws:
      SQLException - if query fails

    • getAverageDelayForRoute

      public int getAverageDelayForRoute(String routeId) throws SQLException
      Calculate average delay for a route based on trip updates
      Parameters:
      routeId - String route ID
      Returns:
      average delay in seconds, or 0 if no delays found
      Throws:
      SQLException - if query fails

    • getRouteTripUpdates

      public List<TripUpdateModel> getRouteTripUpdates(String routeId) throws SQLException
      Get trip updates for a specific route
      Parameters:
      routeId - String route ID
      Returns:
      List of trip updates for the route
      Throws:
      SQLException - if query fails

    • createTripUpdateDelays

      public void createTripUpdateDelays(List<com.google.transit.realtime.GtfsRealtime.FeedEntity> tripEntities) throws SQLException
      Create trip update delays from GTFS realtime feed entities
      Parameters:
      tripEntities - List of GTFS realtime feed entities
      Throws:
      SQLException - if query fails

    • saveMetric

      public void saveMetric(RealtimeMetricType type, int value) throws SQLException
      Save a realtime metric to the database
      Parameters:
      type - RealtimeMetricType the type of metric
      value - int the metric value
      Throws:
      SQLException - if query fails

    • getMetrics

      public List<RealtimeMetricModel> getMetrics(RealtimeMetricType type) throws SQLException
      Get metrics of a specific type
      Parameters:
      type - RealtimeMetricType the type of metric to retrieve
      Returns:
      List of metrics ordered by creation date (newest first)
      Throws:
      SQLException - if query fails

    • batchCreateOrUpdate

      public <ID, MODEL> void batchCreateOrUpdate(Class<MODEL> modelClass, List<MODEL> data)
      Batch create or update multiple models
      Type Parameters:
      ID - ID type
      MODEL - Model type
      Parameters:
      modelClass - Class of the model
      data - List of models to create or update

    • batchCreate

      public <ID, MODEL> void batchCreate(Class<MODEL> modelClass, List<MODEL> data)
      Batch create or update multiple models
      Type Parameters:
      ID - ID type
      MODEL - Model type
      Parameters:
      modelClass - Class of the model
      data - List of models to create or update

    • isFavoriteStop

      public boolean isFavoriteStop(int userId, String stopId) throws SQLException
      Check if a stop is in user's favorites
      Parameters:
      userId - int user ID
      stopId - String stop ID
      Returns:
      true if stop is favorite, false otherwise
      Throws:
      SQLException - if query fails

    • addFavRoute

      public void addFavRoute(int userId, String routeId) throws SQLException
      Add route to user's favorite routes
      Parameters:
      userId - int user ID
      routeId - String route ID
      Throws:
      SQLException - if query fails

    • removeFavRoute

      public void removeFavRoute(int userId, String routeId) throws SQLException
      Remove route from user's favorite routes
      Parameters:
      userId - int user ID
      routeId - String route ID
      Throws:
      SQLException - if query fails

    • getFavoriteRoutes

      public List<RouteModel> getFavoriteRoutes(int userId) throws SQLException
      Get user's favorite routes
      Parameters:
      userId - int user ID
      Returns:
      List of user's favorite routes
      Throws:
      SQLException - if query fails

    • getFavoriteDirectionRoutes

      public List<RouteDirection> getFavoriteDirectionRoutes(int userId) throws SQLException
      Get user's favorite routes with direction information
      Parameters:
      userId - int user ID
      Returns:
      List of directioned routes from user's favorites
      Throws:
      SQLException - if query fails

    • isFavouriteRoute

      public boolean isFavouriteRoute(int userId, String routeId) throws SQLException
      Check if a route is in user's favorites
      Parameters:
      userId - int user ID
      routeId - String route ID
      Returns:
      true if route is favorite, false otherwise
      Throws:
      SQLException - if query fails

    • getAllMetrics

      public Map<RealtimeMetricType,List<RealtimeMetricModel>> getAllMetrics() throws SQLException
      Get all realtime metrics grouped by type
      Returns:
      Map of RealtimeMetricType to list of RealtimeMetricModel
      Throws:
      SQLException - if query fails