Classroom Management System - Multi-Database Example

A Spring Boot application demonstrating multiple database configurations with PostgreSQL for Teachers and MySQL for Students.

🏗️ Architecture

• PostgreSQL: Manages Teacher data
• MySQL: Manages Student data
• Spring Data JPA: ORM for database operations
• REST API: Full CRUD operations for both entities

📋 Prerequisites

• Java 26+
• Maven 3.8+
• PostgreSQL 12+
• MySQL 8.0+

🚀 Setup Instructions

1. Database Setup

PostgreSQL (Teachers Database)

# Connect to PostgreSQL
psql -U postgres

# Run the setup script
\i setup-postgres.sql

Or manually:

CREATE DATABASE classroom_db;
-- Then run the SQL commands from setup-postgres.sql

MySQL (Students Database)

# Connect to MySQL
mysql -u root -p

# Run the setup script
source setup-mysql.sql;

Or manually:

CREATE DATABASE classroom_student_db;
-- Then run the SQL commands from setup-mysql.sql

2. Update application.properties

Edit src/main/resources/application.properties with your database credentials:

# PostgreSQL
spring.datasource.primary.url=jdbc:postgresql://localhost:5432/classroom_db
spring.datasource.primary.username=postgres
spring.datasource.primary.password=your_password

# MySQL
spring.datasource.secondary.url=jdbc:mysql://localhost:3306/classroom_student_db
spring.datasource.secondary.username=root
spring.datasource.secondary.password=your_password

3. Build and Run

# Build the project
mvn clean install

# Run the application
mvn spring-boot:run

The application will start on http://localhost:8080

📚 API Endpoints

Teacher Management (PostgreSQL)

Create Teacher

POST /api/teachers
Content-Type: application/json

{
  "name": "Dr. John Smith",
  "email": "john.smith@school.com",
  "phone": "+1234567890",
  "specialization": "Mathematics",
  "experience": 10
}

Get All Teachers

GET /api/teachers

Get Teacher by ID

GET /api/teachers/{id}

Update Teacher

PUT /api/teachers/{id}
Content-Type: application/json

{
  "name": "Dr. John Smith",
  "phone": "+1234567890",
  "specialization": "Mathematics",
  "experience": 11
}

Delete Teacher

DELETE /api/teachers/{id}

Student Management (MySQL)

Create Student

POST /api/students
Content-Type: application/json

{
  "name": "Alice Brown",
  "email": "alice.brown@school.com",
  "phone": "+1234567893",
  "studentId": "STU001",
  "className": "Class 10-A",
  "gpa": 3.8
}

Get All Students

GET /api/students

Get Student by ID

GET /api/students/{id}

Update Student

PUT /api/students/{id}
Content-Type: application/json

{
  "name": "Alice Brown",
  "phone": "+1234567893",
  "className": "Class 10-A",
  "gpa": 3.85
}

Delete Student

DELETE /api/students/{id}

📁 Project Structure

src/main/java/com/nanjung/connect/db/example/
├── config/
│   ├── PostgresConfig.java      # PostgreSQL configuration
│   └── MysqlConfig.java         # MySQL configuration
├── entity/
│   ├── teacher/
│   │   └── Teacher.java
│   └── student/
│       └── Student.java
├── repository/
│   ├── teacher/
│   │   └── TeacherRepository.java
│   └── student/
│       └── StudentRepository.java
├── service/
│   ├── teacher/
│   │   └── TeacherService.java
│   └── student/
│       └── StudentService.java
├── controller/
│   ├── teacher/
│   │   └── TeacherController.java
│   └── student/
│       └── StudentController.java
├── dto/
│   ├── teacher/
│   │   └── TeacherDTO.java
│   └── student/
│       └── StudentDTO.java
└── ExampleConnectDbApplication.java

🧪 Testing with cURL

Test Teacher API

# Create teacher
curl -X POST http://localhost:8080/api/teachers \
  -H "Content-Type: application/json" \
  -d '{
    "name": "New Teacher",
    "email": "new.teacher@school.com",
    "phone": "+1234567899",
    "specialization": "Chemistry",
    "experience": 5
  }'

# Get all teachers
curl http://localhost:8080/api/teachers

# Get specific teacher
curl http://localhost:8080/api/teachers/1

# Update teacher
curl -X PUT http://localhost:8080/api/teachers/1 \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Updated Teacher",
    "phone": "+1234567899",
    "specialization": "Chemistry",
    "experience": 6
  }'

# Delete teacher
curl -X DELETE http://localhost:8080/api/teachers/1

Test Student API

# Create student
curl -X POST http://localhost:8080/api/students \
  -H "Content-Type: application/json" \
  -d '{
    "name": "New Student",
    "email": "new.student@school.com",
    "phone": "+1234567898",
    "studentId": "STU999",
    "className": "Class 10-C",
    "gpa": 3.75
  }'

# Get all students
curl http://localhost:8080/api/students

# Get specific student
curl http://localhost:8080/api/students/1

# Update student
curl -X PUT http://localhost:8080/api/students/1 \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Updated Student",
    "phone": "+1234567898",
    "className": "Class 10-C",
    "gpa": 3.8
  }'

# Delete student
curl -X DELETE http://localhost:8080/api/students/1

🔧 Configuration Files

Database Configuration

• PostgresConfig.java: Configures PostgreSQL as primary datasource
• MysqlConfig.java: Configures MySQL as secondary datasource

Entity Manager Setup

• Separate EntityManagerFactory for each database
• Separate TransactionManager for each database
• JpaRepositories scan specific packages

📊 Database Schema

Teachers Table (PostgreSQL)

Column	Type	Constraints
id	BIGSERIAL	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
email	VARCHAR(100)	UNIQUE, NOT NULL
phone	VARCHAR(20)	NOT NULL
specialization	VARCHAR(100)	NOT NULL
experience	INTEGER	NOT NULL
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP	NOT NULL

Students Table (MySQL)

Column	Type	Constraints
id	BIGINT	PRIMARY KEY, AUTO_INCREMENT
name	VARCHAR(100)	NOT NULL
email	VARCHAR(100)	UNIQUE, NOT NULL
phone	VARCHAR(20)	NOT NULL
student_id	VARCHAR(50)	UNIQUE, NOT NULL
class_name	VARCHAR(50)	NOT NULL
gpa	DOUBLE	NOT NULL
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP	NOT NULL

⚠️ Error Handling

The application includes error handling for:

• Duplicate email addresses
• Duplicate student IDs
• Resource not found errors
• Validation errors

📝 Dependencies

• Spring Boot 4.1.0
• Spring Data JPA
• PostgreSQL Driver
• MySQL Connector
• Lombok
• Spring Web

🎯 Key Features

✅ Multiple database support (PostgreSQL + MySQL) ✅ Complete CRUD operations ✅ Separate JPA configurations per database ✅ Transaction management per database ✅ RESTful API endpoints ✅ Data validation and error handling ✅ Automatic timestamp management

🐛 Troubleshooting

Connection Refused

• Ensure PostgreSQL and MySQL are running
• Check database URLs and credentials in application.properties

Table Not Found

• Run the setup SQL scripts
• Check database names match in properties

Dependency Issues

mvn clean install -U

Build Issues

• Ensure Java 26+ is installed
• Run mvn clean before building

📞 Support

For issues or questions, please refer to:

• Spring Boot Documentation: https://spring.io/projects/spring-boot
• Spring Data JPA: https://spring.io/projects/spring-data-jpa

---

Version: 1.0.0
Last Updated: 2026-07-09
Author: