Skip to content

Latest commit

 

History

History
153 lines (123 loc) · 3.5 KB

DATABASE_SETUP.md

File metadata and controls

153 lines (123 loc) · 3.5 KB

Database Configuration Guide

Current Setup

Your College Q&A Platform supports multiple database configurations:

1. H2 In-Memory Database (Default - Currently Active)

Configuration: application.properties

  • Type: In-memory (data lost on restart)
  • URL: jdbc:h2:mem:testdb
  • Username: sa
  • Password: (empty)

Access H2 Console:

  1. Start application: mvn spring-boot:run
  2. Open browser: http://localhost:8081/h2-console
  3. Connection settings:
    • JDBC URL: jdbc:h2:mem:testdb
    • User Name: sa
    • Password: (leave empty)
  4. Click "Connect"

2. H2 File Database (Persistent)

Configuration: application-h2-file.properties

  • Type: File-based (data persists)
  • URL: jdbc:h2:file:./data/college_qa_db
  • Username: sa
  • Password: admin123

To Use:

cd backend
mvn spring-boot:run -Dspring.profiles.active=h2-file

Access H2 Console:

  • URL: http://localhost:8081/h2-console
  • JDBC URL: jdbc:h2:file:./data/college_qa_db
  • User Name: sa
  • Password: admin123

3. PostgreSQL Database

Configuration: application-postgresql.properties

  • URL: jdbc:postgresql://localhost:5432/college_qa_db
  • Username: postgres
  • Password: password

Setup Steps:

  1. Install PostgreSQL
  2. Create database: 
    CREATE DATABASE college_qa_db;
  3. Run application: 
    mvn spring-boot:run -Dspring.profiles.active=postgresql

4. MySQL Database

Configuration: application-mysql.properties

  • URL: jdbc:mysql://localhost:3306/college_qa_db
  • Username: root
  • Password: password

Setup Steps:

  1. Install MySQL
  2. Create database: 
    CREATE DATABASE college_qa_db;
  3. Run application: 
    mvn spring-boot:run -Dspring.profiles.active=mysql

Database Tables

The application creates these tables automatically:

Questions Table

  • id (Primary Key)
  • title (VARCHAR 200)
  • content (VARCHAR 2000)
  • student_name (VARCHAR 100)
  • student_email (VARCHAR 100)
  • category (VARCHAR 50)
  • priority (ENUM: LOW, MEDIUM, HIGH, URGENT)
  • status (ENUM: PENDING, IN_PROGRESS, ANSWERED, CLOSED)
  • created_at (TIMESTAMP)
  • updated_at (TIMESTAMP)

Answers Table

  • id (Primary Key)
  • content (VARCHAR 3000)
  • expert_name (VARCHAR 100)
  • expert_email (VARCHAR 100)
  • expert_title (VARCHAR 100)
  • is_verified (BOOLEAN)
  • helpful_count (INTEGER)
  • question_id (Foreign Key)
  • created_at (TIMESTAMP)
  • updated_at (TIMESTAMP)

Quick Start Commands

Start with Default H2 (In-Memory)

cd backend
mvn spring-boot:run

Start with Persistent H2

cd backend
mvn spring-boot:run -Dspring.profiles.active=h2-file

Start with PostgreSQL

cd backend
mvn spring-boot:run -Dspring.profiles.active=postgresql

Start with MySQL

cd backend
mvn spring-boot:run -Dspring.profiles.active=mysql

Database Access URLs

  • H2 Console: http://localhost:8081/h2-console
  • API Base URL: http://localhost:8081/api
  • Frontend: http://localhost:3000

Sample Data

You can insert sample data through:

  1. H2 Console (SQL commands)
  2. API endpoints (POST requests)
  3. Frontend application

Troubleshooting

Common Issues:

  1. Port 8081 in use: Change server.port in properties file
  2. Database connection failed: Check database server is running
  3. Tables not created: Check spring.jpa.hibernate.ddl-auto setting
  4. CORS errors: Verify frontend URL in CORS configuration