09. Persistence + Databases

RU | EN | DE

1. Connecting to SQL Database (PostgreSQL / MySQL)

📄 pom.xml:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

📄 application.yml:

spring:
    datasource:
        url: jdbc:postgresql://localhost:5432/demo
        username: demo
        password: secret
        driver-class-name: org.postgresql.Driver
    jpa:
        hibernate:
            ddl-auto: validate  # create, update, validate, none
            show-sql: true
            properties:
                hibernate.format_sql: true

📌 Recommendation:

• ddl-auto=update — only for dev,
• ddl-auto=validate — for prod (with Flyway/Liquibase migrations).

2. Working with JPA/Hibernate (Review + Details)

Entity:

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(nullable=false)
    private String name;
    private String email;
}

Repository:

public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByEmailContaining(String part);
}

3. Redis (Cache and Sessions)

📄 Dependency:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>

📄 Configuration:

spring:
    redis:
        host: localhost
        port: 6379

📄 Example of Cache Usage:

@Service
public class UserService {
    @Cacheable("users")
    public User findById(Long id) {
        simulateSlowService();
        return repo.findById(id).orElseThrow();
    }
    @CacheEvict(value = "users", key = "#id")
    public void delete(Long id) {
        repo.deleteById(id);
    }
    private void simulateSlowService() {
        try { Thread.sleep(3000); } catch (InterruptedException ignored) {}
    }
}

📄 Enabling Cache:

@SpringBootApplication
@EnableCaching
public class DemoApplication {}

4. NoSQL (Example with MongoDB)

📄 Dependency:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency>

📄 Configuration:

spring:
    data:
        mongodb:
            uri: mongodb://localhost:27017/demo

📄 Document:

@Document(collection = "products")
public class Product {
    @Id
    private String id;
    private String name;
    private double price;
}

📄 Repository:

public interface ProductRepository extends MongoRepository<Product, String> {
    List<Product> findByPriceGreaterThan(double price);
}

5. Testcontainers (Integration Tests with Real Database)

📄 Dependency:

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.20.1</version>
    <scope>test</scope>
</dependency>

📄 Test:

@SpringBootTest
@Testcontainers
class UserRepositoryTest {
    @Container
    static PostgreSQLContainer<?> postgres =
        new PostgreSQLContainer<>("postgres:15")
            .withDatabaseName("demo")
            .withUsername("demo")
            .withPassword("secret");
    @DynamicPropertySource
    static void properties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }
 
    @Autowired
    UserRepository repo;
 
    @Test
    void testSave() {
        User u = new User();
        u.setName("Vitaliy");
        repo.save(u);
        assertThat(repo.findByName("Vitaliy")).isNotEmpty();
    }
}

📌 Now tests work with a real PostgreSQL inside Docker.

6. H2 Database (In-Memory for Tests)

📄 Dependency:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

📄 Configuration:

spring:
    datasource:
        url: jdbc:h2:mem:testdb
        driver-class-name: org.h2.Driver
        username: sa
        password: password

📄 Access to H2 console: http://localhost:8080/h2-console

7. Flyway / Liquibase (Migrations)

📄 Flyway:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

📄 src/main/resources/db/migration/V1__init.sql:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

📌 Upon startup, Flyway will apply all Vx__*.sql.

8. Best Practices

• ✅ SQL Database → PostgreSQL (standard in enterprise).
• ✅ Redis → caching queries and storing sessions.
• ✅ Testcontainers → for integration tests (better than H2).
• ✅ Flyway → database version control, ddl-auto=validate.
• ✅ DTOs for API, Entities only for the database.
• ✅ Repositories are thin (only access to the database), business logic in services.