Class Database

java.lang.Object
Database
public class Database extends Object
Database helper utilities used by the application for H2 persistence.

This class centralizes connection handling, simple CRUD helpers and transaction execution helpers. It is intentionally minimal and synchronous to match the small desktop application's usage pattern.

H2 database details

  • JDBC URL: jdbc:h2:./app_home/print_jobs — relative to the repository root; the actual data file is app_home/print_jobs.mv.db.
  • Default credentials: user sa with an empty password (used intentionally for local desktop deployments; secure or change in production).
  • Backups: The project includes DatabaseBackup which copies the MV store file into app_home/backups/. Prefer creating a backup before performing operations that modify the database.
  • Recovery: Use DatabaseBackup.restoreBackup(String,String) to restore an archived file into app_home/print_jobs.mv.db. Also see recoverDatabase() which attempts to restore the most recent backup found in the backups folder.
  • Concurrency: This application uses embedded H2 file mode. Avoid opening the same database file from multiple JVMs concurrently — prefer the single-process desktop usage pattern. When necessary, configure a networked H2 server or use a separate DB server.
  • Lock timeout: connectWithTimeout() sets a 5s lock timeout to reduce UI hangs when a lock is held by another process.
Since:
1.0.0

  • Field Details

  • Constructor Details

    • Database

      private Database()
      Private constructor to prevent instantiation of this utility class.

  • Method Details

    • connect

      public static Connection connect() throws SQLException
      Open a JDBC connection using the configured URL and default credentials.
      Returns:
      a new Connection
      Throws:
      SQLException - if a connection cannot be established

    • connectWithTimeout

      public static Connection connectWithTimeout() throws SQLException
      Open a JDBC connection and configure a shorter lock timeout to reduce UI hangs when another process holds database locks.
      Returns:
      a configured Connection
      Throws:
      SQLException - if a connection cannot be established

    • insertProject

      public static int insertProject(String name, String projectType, String filePath, String description) throws SQLException
      Insert a new project record and return the generated primary key.
      Parameters:
      name - the project name
      projectType - the project type string
      filePath - absolute filesystem path to project files
      description - free-text description
      Returns:
      generated id for the new project
      Throws:
      SQLException - if insertion fails Example: 
      int id = Database.insertProject("Box", "Prototype", "/tmp/box", "Student project");

    • addLastPrintedDates

      public static void addLastPrintedDates(int projectId, List<String> dates) throws SQLException
      Add a collection of last-printed dates for a project in a single transaction. Dates must be provided as strings parsable to a timestamp using the pattern yyyy-MM-dd (time portion is set to midnight).
      Parameters:
      projectId - the project primary key
      dates - list of date strings in yyyy-MM-dd format
      Throws:
      SQLException - on DB errors

    • executeTransaction

      public static void executeTransaction(Database.DatabaseOperation operations) throws SQLException
      Execute code inside a transaction. The operations lambda receives a Connection which it must use for all statements to ensure they participate in the same transaction.
      Parameters:
      operations - lambda executed within a transaction
      Throws:
      SQLException - if the execution or commit fails

    • executeTransactionWithRollback

      public static void executeTransactionWithRollback(Database.DatabaseOperation operations) throws SQLException
      Similar to executeTransaction(DatabaseOperation) but logs the error before rethrowing so callers may avoid duplicating logging logic.
      Parameters:
      operations - transactional operations
      Throws:
      SQLException - on failure

    • verifyDatabaseIntegrity

      public static void verifyDatabaseIntegrity()
      Basic integrity check ensuring required tables exist. Any missing table will be logged via ErrorHandler.

    • isDatabaseCorrupted

      public static boolean isDatabaseCorrupted()
      Quick corruption test: attempt a simple query and report failure.
      Returns:
      true when corruption is detected, false otherwise

    • recoverDatabase

      public static void recoverDatabase()
      Recover from the most recent backup found in `app_home/backups/`. This method attempts to locate, verify and restore the latest backup file.

    • updateSchema

      public static void updateSchema()
      Apply simple schema updates to ensure optional columns exist. This is idempotent and safe to call at startup.

    • loadProjectById

      public static ResultSet loadProjectById(int projectId) throws SQLException
      Load project record by id. The returned ResultSet is connected to an open Connection which the caller is responsible for closing. Callers should ensure they close both the ResultSet and the underlying connection.
      Parameters:
      projectId - project primary key
      Returns:
      ResultSet positioned before first row
      Throws:
      SQLException - on failure

    • updateProject

      public static void updateProject(int projectId, String name, String projectType, String recipient, String tags, String description) throws SQLException
      Update a project's metadata in the database.
      Parameters:
      projectId - id of the project to update
      name - updated project name
      projectType - updated type
      recipient - updated recipient
      tags - comma-separated tags string
      description - updated description
      Throws:
      SQLException - on failure