Dashboard Service

A small Spring Boot service that stores SQL queries and executes them against a Titanic passengers dataset (H2 in-memory database).

• Java 17
• Spring Boot 3.4
• No external database required

1. Getting Started

# clone repository, then run:
./mvnw spring-boot:run

The application starts on http://localhost:8080 and during startup it

• creates the schema defined in schema.sql,
• loads titanic.csv (891 rows) into the passengers table.

Restarting the app recreates a fresh database every time.

2. REST API

Method & Path	Description	Example
POST /api/queries	Store a SQL query	curl -X POST localhost:8080/api/queries -H "Content-Type:text/plain" -d "SELECT * FROM passengers;" → {"id":1}
GET /api/queries	List all stored queries	curl localhost:8080/api/queries → [ {"id":1,"queryText":"SELECT * FROM passengers;"} ]
GET /api/execute?query={ID}	Run the query identified by ID	curl localhost:8080/api/execute?query=1 → [[1,0,3,"Braund, Mr Owen Harris",...], ...]

Responses are plain JSON arrays.

3. Safety and Performance Guards

Concern	Mitigation
Accidental (or not) data modification	A lightweight validator rejects SQL that contains INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, or TRUNCATE.
Very long queries	jdbcTemplate.setQueryTimeout(120) cancels any statement still running after 120 seconds.
Very large result sets	jdbcTemplate.setMaxRows(1000) caps each query at 1000 rows.
Repeated expensive queries	Spring’s built-in cache stores results; identical queries return from memory.

Both time-out value and row cap can be changed in QueryService if needed.

4. Running Tests

./mvnw test

Unit tests cover validation, caching, and core service logic. Integration tests perform end-to-end add/list/execute flows.

5. Project Structure

src
├── main
│   ├── java
│   │   └── com/jetbrains/analytics
│   │       ├── AnalyticsDashboardApplication.java   # Spring Boot entry point
│   │       ├── config
│   │       │   └── CacheConfig.java                 # Enables Spring Cache
│   │       ├── loader
│   │       │   └── DataLoader.java                  # Loads titanic.csv at startup
│   │       ├── controller
│   │       │   └── QueryController.java             # /queries and /execute endpoints
│   │       ├── service
│   │       │   └── QueryService.java                # Validation, timeout, row cap, caching
│   │       └── repository
│   │           └── QueryRepository.java             # Stores SQL text
│   └── resources
│       ├── schema.sql                               # DDL + indexes
│       └── data/titanic.csv                         # Sample dataset
└── test
    └── java/... (unit & integration tests)

Full rationale, trade-offs and improvement ideas are in DESIGN.md.