CS 157A Final Project

Group D4: Group D4: Kathryna Malixi, Emily Lu, Tyler Moquin, Mohammed Nassar

Project Overview

This Java program connects to a MySQL database, which is ticketMaster, using JDBC. The application provides a text-based menu for CRUD operations on certain tables, for transaction execution, and for displaying data.

Key Features:

• JDBC connection using DBConnection.java and app.properties
• PreparedStatements for all SQL operations
• Input validation and exception handling
• Implementaion of transactional workflow using COMMIT and ROLLBACK
• MySQL VIEW, stored procedure/function, and a constraint
• Tables: Customer, Venue, EventCoordinator, Event, Ticket, Order, OrderItem, Waitlist, Review

Database Setup

Instructions for Building DB

1. Install MySQL

Install MySQL Server 8.0.x and MySQL Workbench 8.0

2. Create database

Execute the following MySQL statements:

-- Drop existing database if necessary
DROP DATABASE IF EXISTS ticketMaster;

-- Create database
CREATE DATABASE ticketMaster;

3. Run SQL Script

1. Open MySQL Workbench and connect to your MySQL server
2. Open and execute the create_and_populate.sql file
3. This will create all tables, constraints, view, stored procedure, and populate sample data
4. Verify: SHOW TABLES;

MYSQL statement: -- Create user and grant privileges CREATE USER 'ticketUser'@'localhost' IDENTIFIED BY 'Password123'; GRANT ALL PRIVILEGES ON ticketMaster.* TO 'ticketUser'@'localhost'; FLUSH PRIVILEGES;

Java Program Setup

1. Requirements

• Java JDK 17+
• MySQL Server 8.0.x
• MySQL Connector/J 9.5.0 (mysql-connector-j-9.5.0.jar) - included in lib/ folder

2. Import Project into IntelliJ

1. Open IntelliJ IDEA
2. Select "Open" and navigate to the CS157A-TicketMaster folder
3. Click "OK" to open the project

3. Add MySQL Connector Library

1. Right-click on project → Open Module Settings (or File → Project Structure)
2. Go to Dependencies tab
3. Click "+" → Select "JARs or directories"
4. Navigate to lib/mysql-connector-j-9.5.0.jar and select it
5. Click "OK" to add the library

4. Configure Database Connection

1. Navigate to src/resources/app.properties
2. Update the following values:

db.url=jdbc:mysql://localhost:3306/ticketmaster
db.user=root
db.password=YOUR_PASSWORD_HERE

Note: Replace YOUR_PASSWORD_HERE with your MySQL password. If using ticketUser, set db.user=ticketUser and db.password=Password123.

2. Import Project into IntelliJ and Establish Connection

1. Open IntelliJ and open project
2. Setup app.properties file so DBConnection.java can load it correctly
3. Add MySQL Connector JAR as a library/dependency
4. Once dependency is added. DBConnection.java can successfully connect to MySQL.

3. Build Console Menu with Scanner I/O

• Simple text-based menu

4. Implement PreparedStatements

• Implement CRUD operations for tables using PreparedStatements (SELECT, INSERT, UPDATE, DELETE)

5. Implement Transactional Workflow

• COMMIT and ROLLBACK

6. Add Input Validation and Error Handling

• Input Validation
• SQLException Error Handling and Error Messages when necessary

7. Add View, Stored Procedure/Function, Constraint

• MySQL VIEW that shows available tickets
• ReserveTicket Stored Procedure
• Constraint

Application Development Process

1. Database Schema Design: Created ER diagram, designed tables with constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK)
2. JDBC Connection Setup: Implemented DBConnection.java with app.properties configuration, tested connection and driver loading
3. DAO Layer: Created DAO classes (CustomerDAO, EventDAO, TicketDAO, etc.) using PreparedStatement for all SQL operations
4. Service Layer: Implemented business logic and input validation (email, phone, date, name validation) in CustomerService
5. Transaction Management: Created TransactionService with COMMIT/ROLLBACK for multi-table ticket purchase operations
6. Advanced Features:
   • VIEW: AvailableTicketsView (joins Ticket, Event, Venue tables)
   • Stored Procedure: ReserveTicket (enforces business rule: only available tickets can be reserved)
   • Constraint: CHECK constraint on Review.Stars (BETWEEN 1 AND 5)
7. User Interface: Built console menu in Main.java with Scanner I/O, integrated all operations
8. Testing: Created TestRunner.java, tested all CRUD operations, transactions, VIEW, and stored procedure

Instructions for Running Java Program

1. Run Main.java in IntelliJ
2. Enter a number from the menu (choosing from customer operations, event operations, ticket operations, transaction, or exit)
3. Enter information if needed
4. Use the console menu to engage with the database

Test Rollback: Try purchasing an unavailable ticket to see transaction rollback.