A simple Spring Boot REST API that performs basic CRUD operations backed by a MySQL database, plus logic endpoints to test Java problem-solving and Spring Boot understanding.
- Java 17+
- Maven
- MySQL Server 8.0+ (with a taskdb database created)
- Postman or cURL (for testing endpoints)
Option A: Create the database manually
CREATE DATABASE taskdb;Option B: Use provided schema.sql
Place the schema.sql file in src/main/resources/. Spring Boot will execute it automatically on startup.
File: src/main/resources/schema.sql
CREATE TABLE IF NOT EXISTS tasks (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
description TEXT,
priority INT NOT NULL CHECK (priority BETWEEN 1 AND 5),
completed BOOLEAN DEFAULT FALSE
);Optional Sample Data
Include a data.sql file in src/main/resources/ for sample data.
File: src/main/resources/data.sql
INSERT INTO tasks (title, description, priority, completed) VALUES
('Setup project', 'Initialize folders', 3, false),
('Implement CRUD', 'Develop API routes', 2, false);Create a .env file in the project root (do not commit this to GitHub):
DB_HOST=localhost
DB_PORT=3306
DB_NAME=taskdb
DB_USER=your_mysql_username
DB_PASS=your_mysql_password
Update src/main/resources/application.properties:
spring.datasource.url=jdbc:mysql://${DB_HOST}:${DB_PORT}/${DB_NAME}?useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASS}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.sql.init.mode=always
server.port=8080
spring.main.banner-mode=off
Open a terminal in the project directory and run:
mvn spring-boot:runThe application will start on: 👉 http://localhost:8080
See src/main/resources/schema.sql above for the tasks table structure.
CRUD Operations
- Create:
INSERT INTO tasks (title, description, priority, completed) VALUES (?, ?, ?, ?) - Read (All):
SELECT * FROM tasks - Read (By ID):
SELECT * FROM tasks WHERE id = ? - Update (Complete):
UPDATE tasks SET completed = TRUE WHERE id = ? - Delete:
DELETE FROM tasks WHERE id = ?
Special Endpoints
- Highest Priority:
SELECT * FROM tasks WHERE priority = (SELECT MIN(priority) FROM tasks) LIMIT 1 - Stats:
SELECT COUNT() FROM tasks; SELECT COUNT() FROM tasks WHERE completed = TRUE
Method: POST
Endpoint: /tasks
Request:
{
"title": "Finish documentation",
"description": "Add README.md",
"priority": 1,
"completed": false
}Response (201 Created):
{
"id": 3,
"title": "Finish documentation",
"description": "Add README.md",
"priority": 1,
"completed": false
}Method: GET
Endpoint: /tasks
Response (200 OK):
[
{
"id": 1,
"title": "Setup project",
"description": "Initialize folders",
"priority": 3,
"completed": false
},
{
"id": 2,
"title": "Implement CRUD",
"description": "Develop API routes",
"priority": 2,
"completed": false
}
]Method: GET
Endpoint: /tasks/{id} (e.g., /tasks/2)
Response (200 OK):
{
"id": 2,
"title": "Implement CRUD",
"description": "Develop API routes",
"priority": 2,
"completed": false
}Method: PUT
Endpoint: /tasks/{id}/complete (e.g., /tasks/2/complete)
Response (200 OK): No content
Method: DELETE
Endpoint: /tasks/{id} (e.g., /tasks/2)
Response (204 No Content): No content
Method: GET
Endpoint: /tasks/highest-priority
Response (200 OK):
{
"id": 3,
"title": "Finish documentation",
"description": "Add README.md",
"priority": 1,
"completed": false
}Method: GET
Endpoint: /tasks/stats
Response (200 OK):
"Total: 3, Completed: 0, Pending: 3"Assumptions:
- Priority ranges from 1 to 5, with 1 being the highest priority.
- id is auto-incremented by the database.
Edge Cases Handled:
- Invalid priority (outside 1-5) throws an error.
- Non-existent task ID returns 404.
- Empty task list for
/tasks/highest-priorityor/tasks/statshandles appropriately (e.g., no tasks or zero counts).
Name: Zaid Rahman
Project: ShadhinTask
Tech Stack: Spring Boot, MySQL, Maven