Today we'll dive into databases, entity design, and how to wire all of that into our app. If you remember, in previous lectures we already covered Spring Data JPA, ORM annotations like @Entity, @Id, @Column, built repositories, and even tried basic CRUD operations. Now it's time to apply everything in practice and bring theory and logic together for the final project.
1. Designing the database structure
We already touched a bit on database design a couple of lectures ago. Now let's dig deeper into the process.
Often the success of your app depends on correct database design. Imagine data being unstructured or impossible to fetch quickly. It's like dealing with a pile of code with no comments.
In practice you should consider:
- Logical relationships between data.
- Query optimization.
- Avoiding redundancy.
To better visualize the whole process, let's create an ER diagram for our database. For our final project let's assume we're building a Task Management System where users can create tasks, set deadlines, and track status.
ER diagram
Based on the requirements, the database could look like this:
USER
---------------------
id (PK)
username
email
password
role
TASK
---------------------
id (PK)
title
description
deadline
status
user_id (FK -> USER.id)
As you can see, we have two main objects: User and Task. They're related (a User can have many Tasks, but each Task is tied to a single User).
- USER:
- Holds user data (login, email, password, role, etc.).
- TASK:
- Holds task data (title, description, deadline, status, link to owner).
2. Implementing entities in code
Let's move on to writing the data model in the Spring app. We'll use JPA annotations to define entities.
Class User
package com.example.taskmanagement.model;
import jakarta.persistence.*;
import java.util.List;
@Entity
@Table(name = "users") // Explicit table name: SQL "user" is a reserved word
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY) // Auto-increment for the primary key
private Long id;
@Column(nullable = false, unique = true) // Username must be unique
private String username;
@Column(nullable = false, unique = true)
private String email;
@Column(nullable = false)
private String password;
@Column(nullable = false)
private String role; // You can set a role, e.g. ADMIN or USER
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<Task> tasks; // Relation to tasks, lazy fetching
// Getters, setters, constructors...
}
Class Task
Main points:
- The
@OneToManyinUsermakes it possible to get all a user's tasks. - The
@ManyToOneinTasklets each task be bound to a single user. - We use
LAZYloading so that querying a user (or a task) doesn't pull the related entity immediately from the DB.
3. Initializing the database
Now that we have entities, let's create scripts to populate the DB with test data. You can use H2 locally or PostgreSQL for production.
Schema creation script
Create a file called schema.sql:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
role VARCHAR(20) NOT NULL
);
CREATE TABLE tasks (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
description TEXT,
deadline DATE NOT NULL,
status VARCHAR(20) NOT NULL,
user_id BIGINT NOT NULL,
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (id)
);
Data seeding script
File data.sql:
-- Users
INSERT INTO users (username, email, password, role) VALUES
('john_doe', 'john@example.com', 'password123', 'USER'),
('admin', 'admin@example.com', 'admin123', 'ADMIN');
-- Tasks
INSERT INTO tasks (title, description, deadline, status, user_id) VALUES
('Complete homework', 'Finish math and science homework', '2023-12-01', 'PENDING', 1),
('Fix server', 'Resolve critical issue on production server', '2023-11-25', 'IN_PROGRESS', 2);
Configuration for application.yml (H2 Database) If you want to use H2 and test everything locally:
spring:
datasource:
url: jdbc:h2:mem:testdb
driver-class-name: org.h2.Driver
username: sa
password:
jpa:
hibernate:
ddl-auto: create
show-sql: true
When you start the Spring Boot app it will automatically create tables based on the annotated entities.
4. Verifying it works in practice
Let's write repositories and test inserting data.
Repository for User
package com.example.taskmanagement.repository;
import com.example.taskmanagement.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<User, Long> {
User findByUsername(String username);
}
Repository for Task
package com.example.taskmanagement.repository;
import com.example.taskmanagement.model.Task;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
public interface TaskRepository extends JpaRepository<Task, Long> {
List<Task> findByUserId(Long userId);
}
A couple lines to test. Create a test service or method to check:
@Autowired
private UserRepository userRepository;
@Autowired
private TaskRepository taskRepository;
public void testDatabase() {
User user = userRepository.findByUsername("john_doe");
System.out.println("User: " + user.getUsername());
List<Task> tasks = taskRepository.findByUserId(user.getId());
tasks.forEach(task -> System.out.println("Task: " + task.getTitle()));
}
If everything is set up correctly, you should see the data from your data.sql in the console.
At this point you can see the basics of working with databases in a Spring app: from entity definition to data initialization and relationship setup. Everything's ready to add an API on top of this data and implement project logic.
GO TO FULL VERSION