Sunrise Dental Clinic

Problem

The clinic in this project (a case study from the assignment brief, not a real customer) ran on paper. Reception staff needed one place to book appointments, find them again by number, keep each patient’s treatment history and print bills. Managers needed reports, and only admins should be able to see them.

My role

This was individual coursework for the Advanced Programming module. I built the whole system from scratch, alone:

• the requirements and UML design
• the database, back end, web UI and REST API
• the security, the tests, the CI/CD pipeline and the release
• the written report

How I built it

It is a three-tier application: a web UI plus REST web services, a business logic layer, and a MySQL database.

• Spring Boot 3 and Java 17. 41 endpoints across the REST API and the web screens, with input validation and one standard error format.
• MySQL 8. 10 tables, stored procedures, functions and 10 triggers. One procedure creates the unique appointment numbers. Normal queries go through Spring Data JPA, and the stored procedures through JdbcTemplate.
• Thymeleaf screens for booking, searching, schedules, patients, bills, reminders and reports, with a help section for new staff.
• Security. BCrypt password hashing, two roles (admin and receptionist), sessions, CSRF protection and a login audit.
• Design patterns used for real problems. Strategy, Template Method and Factory for three billing types (standard, surgical and consultation only), Observer for appointment reminders by email and log, and Singleton for the clinic settings.
• Test-driven development. For each layer I wrote failing tests first, then the code that made them pass.
• GitHub Actions. Three jobs on every push (tests on H2, tests on real MySQL 8, and packaging the jar), plus a release pipeline that publishes a GitHub release from a version tag.

Challenges

The app would not start on real MySQL

All the tests passed, but the app failed at startup against real MySQL. The H2 test database builds its tables from the Java entities, so it always agreed with them and hid the problem. I mapped the password column as a fixed 60-character field, the length of a BCrypt hash.

Booking failed on a standard MySQL 8 install

Text comparisons between tables, procedures and triggers failed because of mismatched collation settings. I pinned the collation in the schema, so booking works on a normal MySQL 8 setup without manual changes.

Forms broke after login

After logging in, the CSRF token cookie was not issued again, so every form submission was rejected. I added a filter that keeps issuing the token cookie, and wrote tests for the full log-in-then-submit flow over a real HTTP connection.

Proving the stored procedures really work

H2 can never test real MySQL procedures and triggers. I wrote 6 integration tests that run against real MySQL 8, locally and in a separate CI job.

Result

• 268 automated tests with 0 failures, and 78.8% instruction coverage (62.2% branch).
• Database bugs are now caught automatically on every push, because one CI job runs against real MySQL 8.
• A 111-case test plan, test data from boundary-value analysis, and a requirement-to-test traceability matrix.
• Three tagged releases on GitHub. Version 1.0.0 is the one I submitted.