TP1 - Datasource Configuration and Connection Pool

Objectives of the TP

In this TP, you will:

1. ✅ Configure an H2 datasource for development
2. ✅ Configure a PostgreSQL datasource for production
3. ✅ Optimize HikariCP parameters
4. ✅ Monitor the connection pool with Actuator
5. ✅ Simulate pool saturation

Estimated duration: 1 hour

---

Prerequisites

• Modules 1 to 6 completed
• IntelliJ IDEA or VS Code
• PostgreSQL installed (or Docker)
• Maven

---

Part 1: Project Creation

1.1 Create the Project with Spring Initializr

Create a new project with:

• Artifact: datasource-demo
• Dependencies:
  • Spring Web
  • Spring Data JPA
  • H2 Database
  • PostgreSQL Driver
  • Spring Boot Actuator
  • Lombok

1.2 Project Structure

datasource-demo/
├── src/main/java/com/example/datasourcedemo/
│   ├── DatasourceDemoApplication.java
│   ├── model/
│   │   └── Product.java
│   ├── repository/
│   │   └── ProductRepository.java
│   ├── service/
│   │   └── ProductService.java
│   └── controller/
│       └── ProductController.java
└── src/main/resources/
    ├── application.properties
    ├── application-dev.properties
    └── application-prod.properties

---

Part 2: Entity and Repository

2.1 Create the Product Entity

// src/main/java/com/example/datasourcedemo/model/Product.java
package com.example.datasourcedemo.model;

import jakarta.persistence.*;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;

import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Table(name = "products")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    private String description;

    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;

    @Column(nullable = false)
    private Integer stock;

    @Column(name = "created_at")
    private LocalDateTime createdAt = LocalDateTime.now();
}

2.2 Create the Repository

// src/main/java/com/example/datasourcedemo/repository/ProductRepository.java
package com.example.datasourcedemo.repository;

import com.example.datasourcedemo.model.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
    List<Product> findByStockLessThan(Integer threshold);
}

2.3 Create the Service

// src/main/java/com/example/datasourcedemo/service/ProductService.java
package com.example.datasourcedemo.service;

import com.example.datasourcedemo.model.Product;
import com.example.datasourcedemo.repository.ProductRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
@RequiredArgsConstructor
@Slf4j
public class ProductService {

    private final ProductRepository productRepository;

    public List<Product> getAllProducts() {
        log.info("Retrieving all products");
        return productRepository.findAll();
    }

    public Product createProduct(Product product) {
        log.info("Creating product: {}", product.getName());
        return productRepository.save(product);
    }

    public Product getProduct(Long id) {
        return productRepository.findById(id)
                .orElseThrow(() -> new RuntimeException("Product not found: " + id));
    }

    // Method to simulate a slow request
    public List<Product> getAllProductsSlow() throws InterruptedException {
        log.info("Starting slow request...");
        Thread.sleep(5000);  // 5 seconds
        log.info("Ending slow request");
        return productRepository.findAll();
    }
}

2.4 Create the Controller

// src/main/java/com/example/datasourcedemo/controller/ProductController.java
package com.example.datasourcedemo.controller;

import com.example.datasourcedemo.model.Product;
import com.example.datasourcedemo.service.ProductService;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/products")
@RequiredArgsConstructor
public class ProductController {

    private final ProductService productService;

    @GetMapping
    public List<Product> getAllProducts() {
        return productService.getAllProducts();
    }

    @GetMapping("/{id}")
    public Product getProduct(@PathVariable Long id) {
        return productService.getProduct(id);
    }

    @PostMapping
    public Product createProduct(@RequestBody Product product) {
        return productService.createProduct(product);
    }

    // Endpoint to test connections
    @GetMapping("/slow")
    public List<Product> getProductsSlow() throws InterruptedException {
        return productService.getAllProductsSlow();
    }
}

---

Part 3: Development Configuration (H2)

3.1 Basic Configuration

# application.properties (common configuration)

# Default active profile
spring.profiles.active=dev

# Actuator
management.endpoints.web.exposure.include=health,metrics,info
management.endpoint.health.show-details=always

# Logging
logging.level.com.zaxxer.hikari=DEBUG
logging.level.org.hibernate.SQL=DEBUG

3.2 H2 Configuration (Development)

# application-dev.properties

# ===== H2 DATASOURCE =====
spring.datasource.url=jdbc:h2:mem:devdb;DB_CLOSE_DELAY=-1
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

# H2 Console
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

# ===== JPA =====
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

# ===== HIKARI (Connection Pool) =====
spring.datasource.hikari.pool-name=DevPool
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=600000

# Leak detection (30 seconds)
spring.datasource.hikari.leak-detection-threshold=30000

3.3 Test

# Start the application
mvn spring-boot:run

# Or with explicit profile
mvn spring-boot:run -Dspring-boot.run.profiles=dev

Checks:

1. Open http://localhost:8080/h2-console
2. JDBC URL: jdbc:h2:mem:devdb
3. Connect with sa / (empty)

---

Part 4: Production Configuration (PostgreSQL)

4.1 Start PostgreSQL with Docker

# Create and start PostgreSQL
docker run --name postgres-demo \
  -e POSTGRES_USER=demo \
  -e POSTGRES_PASSWORD=demo123 \
  -e POSTGRES_DB=productdb \
  -p 5432:5432 \
  -d postgres:15

4.2 PostgreSQL Configuration

# application-prod.properties

# ===== POSTGRESQL DATASOURCE =====
spring.datasource.url=jdbc:postgresql://localhost:5432/productdb
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=demo
spring.datasource.password=demo123

# H2 Console disabled
spring.h2.console.enabled=false

# ===== JPA =====
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

# ===== HIKARI (Production optimized pool) =====
spring.datasource.hikari.pool-name=ProdPool
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000

# Connection validation
spring.datasource.hikari.connection-test-query=SELECT 1

# Leak detection (60 seconds)
spring.datasource.hikari.leak-detection-threshold=60000

4.3 Start in Production Mode

# With prod profile
mvn spring-boot:run -Dspring-boot.run.profiles=prod

---

Part 5: Monitoring with Actuator

5.1 HikariCP Endpoints

Start the application and test these URLs:

# Global health
curl http://localhost:8080/actuator/health

# HikariCP metrics
curl http://localhost:8080/actuator/metrics/hikaricp.connections

# Active connections
curl http://localhost:8080/actuator/metrics/hikaricp.connections.active

# Idle connections
curl http://localhost:8080/actuator/metrics/hikaricp.connections.idle

# Pending connections
curl http://localhost:8080/actuator/metrics/hikaricp.connections.pending

# Connection acquisition time
curl http://localhost:8080/actuator/metrics/hikaricp.connections.acquire

5.2 Create a Diagnostic Endpoint

// src/main/java/com/example/datasourcedemo/controller/PoolInfoController.java
package com.example.datasourcedemo.controller;

import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariPoolMXBean;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@RestController
@RequestMapping("/api/pool")
@RequiredArgsConstructor
public class PoolInfoController {

    private final DataSource dataSource;

    @GetMapping("/info")
    public Map<String, Object> getPoolInfo() {
        HikariDataSource hikariDS = (HikariDataSource) dataSource;
        HikariPoolMXBean poolMXBean = hikariDS.getHikariPoolMXBean();

        Map<String, Object> info = new HashMap<>();
        info.put("poolName", hikariDS.getPoolName());
        info.put("maximumPoolSize", hikariDS.getMaximumPoolSize());
        info.put("minimumIdle", hikariDS.getMinimumIdle());
        info.put("activeConnections", poolMXBean.getActiveConnections());
        info.put("idleConnections", poolMXBean.getIdleConnections());
        info.put("totalConnections", poolMXBean.getTotalConnections());
        info.put("threadsAwaitingConnection", poolMXBean.getThreadsAwaitingConnection());

        return info;
    }
}

Test:

curl http://localhost:8080/api/pool/info

Expected result:

{
  "poolName": "DevPool",
  "maximumPoolSize": 5,
  "minimumIdle": 2,
  "activeConnections": 0,
  "idleConnections": 2,
  "totalConnections": 2,
  "threadsAwaitingConnection": 0
}

---

Part 6: Simulate Pool Saturation

6.1 Configuration for the Test

Temporarily modify application-dev.properties:

# Very small pool for testing
spring.datasource.hikari.maximum-pool-size=2
spring.datasource.hikari.connection-timeout=5000  # 5 seconds

6.2 Test Script

Create a script to send multiple simultaneous requests:

# test-pool.sh (Linux/Mac)
#!/bin/bash

echo "Sending 5 simultaneous requests..."

for i in {1..5}; do
    curl -s http://localhost:8080/api/products/slow &
done

wait
echo "Done!"

Or with PowerShell (Windows):

# test-pool.ps1
Write-Host "Sending 5 simultaneous requests..."

$jobs = @()
for ($i = 1; $i -le 5; $i++) {
    $jobs += Start-Job -ScriptBlock {
        Invoke-RestMethod -Uri "http://localhost:8080/api/products/slow"
    }
}

$jobs | Wait-Job | Receive-Job
Write-Host "Done!"

6.3 Observe the Logs

HikariPool-1 - Pool stats (total=2, active=2, idle=0, waiting=3)
HikariPool-1 - Connection is not available, request timed out after 5001ms.

6.4 Fix

# Increase the pool
spring.datasource.hikari.maximum-pool-size=10

# Or increase the timeout
spring.datasource.hikari.connection-timeout=60000

---

Part 7: Test Data

7.1 Create data.sql

-- src/main/resources/data.sql
INSERT INTO products (name, description, price, stock, created_at) VALUES
('Laptop Pro', 'High-performance laptop', 1299.99, 50, CURRENT_TIMESTAMP),
('Wireless Mouse', 'Ergonomic Bluetooth mouse', 49.99, 200, CURRENT_TIMESTAMP),
('Mechanical Keyboard', 'RGB gaming keyboard', 129.99, 75, CURRENT_TIMESTAMP),
('27-inch Monitor', '4K IPS monitor', 399.99, 30, CURRENT_TIMESTAMP),
('HD Webcam', '1080p camera with microphone', 79.99, 100, CURRENT_TIMESTAMP);

7.2 Enable Initialization

# application-dev.properties
spring.jpa.defer-datasource-initialization=true
spring.sql.init.mode=always

---

Final Verification

Checklist

• The application starts with the dev profile (H2)
• The H2 console is accessible
• The endpoints /api/products work
• Actuator metrics are available
• /api/pool/info returns pool stats
• The saturation test shows timeouts
• The application starts with the prod profile (PostgreSQL)

Tests to Perform

# 1. Create a product
curl -X POST http://localhost:8080/api/products \
  -H "Content-Type: application/json" \
  -d '{"name":"Test","price":99.99,"stock":10}'

# 2. List products
curl http://localhost:8080/api/products

# 3. Check the pool
curl http://localhost:8080/api/pool/info

# 4. Check health
curl http://localhost:8080/actuator/health

---

Summary

In this TP, you learned to:

Skill	Description
Multi-profile configuration	dev (H2) vs prod (PostgreSQL)
HikariCP	Connection pool parameters
Actuator	Metrics monitoring
Diagnostic	Custom endpoint for the pool
Load testing	Simulate saturation

---

🎯 Next Step

Now that you master datasource configuration, learn to manage transactions in the next TP.

➡️ TP7.2: Transactions and Rollback

---

⬅️ Back to Module 7