Spring JPA

Integrar un backend con spring jpa con una base de datos postgresql:

• Configurar un proyecto spring boot multimodulo
• Crear entity y repository
• Crear controller y service

Configurar docker postgresql

Estructura dell proyecto

proyecto-postgres
├── docker-compose.yml
├── init.sql
└── postgres-data/   ← esta carpeta guardará los datos de PostgreSQL

Configuración docker

version: '3.9'

services:
  db:
    image: postgres:16-alpine
    container_name: postgres-local
    restart: unless-stopped
    environment:
      POSTGRES_USER: usuario
      POSTGRES_PASSWORD: password
      POSTGRES_DB: db-local
    ports:
      - "5432:5432"
    volumes:
      - ./postgres-data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

Script inicial creación de tablas init.sql

-- Módulo de clientes
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Módulo de pedidos (orders)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2),
    status VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Módulo de pagos (payments)
CREATE TABLE payments (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    payment_method VARCHAR(50),
    amount DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- Módulo de productos (products)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10, 2)
);

-- Tabla intermedia para relación many-to-many entre products y orders
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Diagrama de relación entre tablas

@startuml

entity customers {
  * id : SERIAL
  --
  name : varchar
  email : varchar
}

entity orders {
  * id : SERIAL
  --
  customer_id : int
  order_date : timestamp
  total_amount : decimal
  status : varchar
}

entity payments {
  * id : SERIAL
  --
  order_id : int
  payment_date : timestamp
  payment_method : varchar
  amount : decimal
}

entity products {
  * id : SERIAL
  --
  name : varchar
  description : text
  price : decimal
}

entity order_items {
  * order_id : int
  * product_id : int
  --
  quantity : int
}

' Relaciones
customers ||--o{ orders : "realiza"
orders ||--o{ payments : "tiene"
orders ||--o{ order_items : "contiene"
products ||--o{ order_items : "aparece en"

@enduml

Inserción datos iniciales

-- Inserción inicial en customers
INSERT INTO customers (name, email) VALUES
('Alice Smith', 'alice@example.com'),
('Bob Johnson', 'bob@example.com');

-- Inserción inicial en products
INSERT INTO products (name, description, price) VALUES
('Laptop', 'Ordenador portátil de 15 pulgadas', 1200.00),
('Smartphone', 'Teléfono inteligente gama media', 450.00),
('Headphones', 'Auriculares inalámbricos', 99.99);

-- Inserción inicial en orders
INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES
(1, NOW() - INTERVAL '3 days', 1749.99, 'COMPLETED'),
(1, NOW() - INTERVAL '1 day', 99.99, 'PENDING'),
(2, NOW() - INTERVAL '2 days', 450.00, 'COMPLETED');

-- Inserción inicial en payments
INSERT INTO payments (order_id, payment_date, payment_method, amount) VALUES
(1, NOW() - INTERVAL '2 days', 'CREDIT_CARD', 1749.99),
(3, NOW() - INTERVAL '1 day', 'PAYPAL', 450.00);

-- Inserción inicial en order_items (relación entre orders y products)
INSERT INTO order_items (order_id, product_id, quantity) VALUES
(1, 1, 1), -- Laptop
(1, 2, 1), -- Smartphone
(1, 3, 1), -- Headphones
(2, 3, 1), -- Headphones
(3, 2, 1); -- Smartphone

Levantar postgresql

docker compose up -d

docker exec -it postgres-local psql -U usuario db-local