PostgreSQL Product Catalog Service

Complete Step-by-Step Implementation

1. Project Setup & Configuration

1.1 Initialize Spring Boot Project

bash

Copy

Download

# Using Spring Initializr
curl https://start.spring.io/starter.zip \
  -d dependencies=web,data-jpa,postgresql,flyway \
  -d javaVersion=17 \
  -d type=maven-project \
  -d groupId=com.productcatalog \
  -d artifactId=product-service \
  -o product-service.zip
unzip product-service.zip
cd product-service

1.2 Configure PostgreSQL in application.yml

yaml

Copy

Download

# src/main/resources/application.yml
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/productcatalog
    username: admin
    password: secret
    hikari:
      maximum-pool-size: 10
  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
  flyway:
    locations: classpath:db/migration

2. Database Schema Setup

2.1 Create Flyway Migration

sql

Copy

Download

# src/main/resources/db/migration/V1__Initial_schema.sql
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    sku VARCHAR(8) UNIQUE NOT NULL CHECK (sku ~ '^[A-Z]{3}-\d{4}$'),
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    inventory INTEGER NOT NULL DEFAULT 0 CHECK (inventory >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE product_categories (
product_id BIGINT REFERENCES products(id),
category_id BIGINT,
PRIMARY KEY (product_id, category_id)
);

2.2 Add Update Trigger

sql

Copy

Download

# src/main/resources/db/migration/V2__Add_update_trigger.sql
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_product_timestamp
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

3. Implement Core Components

3.1 JPA Entity with Auditing

java

Copy

Download

// src/main/java/com/productcatalog/model/Product.java
@Entity
@Table(name = "products")
@EntityListeners(AuditingEntityListener.class)
public class Product {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Column</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>nullable <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token boolean" style="color: rgb(183, 107, 1);">false</span><span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> unique <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token boolean" style="color: rgb(183, 107, 1);">true</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">String</span> sku<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Column</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>nullable <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token boolean" style="color: rgb(183, 107, 1);">false</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">String</span> name<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Column</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>precision <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token number" style="color: rgb(183, 107, 1);">10</span><span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> scale <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token number" style="color: rgb(183, 107, 1);">2</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">BigDecimal</span> price<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">Integer</span> inventory<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@CreatedDate</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">Instant</span> createdAt<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@LastModifiedDate</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">Instant</span> updatedAt<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

<span class="token comment" style="color: rgb(160, 161, 167); font-style: italic;">// Enums</span>
<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Enumerated</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token class-name" style="color: rgb(183, 107, 1);">EnumType</span><span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token constant" style="color: rgb(183, 107, 1);">STRING</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">ProductStatus</span> status<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

<span class="token comment" style="color: rgb(160, 161, 167); font-style: italic;">// Relationships</span>
<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@OneToMany</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>mappedBy <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token string" style="color: rgb(80, 161, 79);">"product"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> cascade <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token class-name" style="color: rgb(183, 107, 1);">CascadeType</span><span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token constant" style="color: rgb(183, 107, 1);">ALL</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">List</span><span class="token generics"><span class="token punctuation" style="color: rgb(56, 58, 66);">&lt;</span><span class="token class-name" style="color: rgb(183, 107, 1);">ProductVariant</span><span class="token punctuation" style="color: rgb(56, 58, 66);">&gt;</span></span> variants <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token keyword" style="color: rgb(166, 38, 164);">new</span> <span class="token class-name" style="color: rgb(183, 107, 1);">ArrayList</span><span class="token generics"><span class="token punctuation" style="color: rgb(56, 58, 66);">&lt;</span><span class="token punctuation" style="color: rgb(56, 58, 66);">&gt;</span></span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

}

3.2 Repository with Custom Queries

java

Copy

Download

// src/main/java/com/productcatalog/repository/ProductRepository.java
public interface ProductRepository extends JpaRepository<Product, Long> {
<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Query</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"SELECT p FROM Product p WHERE "</span> <span class="token operator" style="color: rgb(64, 120, 242);">+</span>
       <span class="token string" style="color: rgb(80, 161, 79);">"(:name IS NULL OR p.name ILIKE %:name%) AND "</span> <span class="token operator" style="color: rgb(64, 120, 242);">+</span>
       <span class="token string" style="color: rgb(80, 161, 79);">"(:minPrice IS NULL OR p.price &gt;= :minPrice) AND "</span> <span class="token operator" style="color: rgb(64, 120, 242);">+</span>
       <span class="token string" style="color: rgb(80, 161, 79);">"(:maxPrice IS NULL OR p.price &lt;= :maxPrice)"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token class-name" style="color: rgb(183, 107, 1);">Page</span><span class="token generics"><span class="token punctuation" style="color: rgb(56, 58, 66);">&lt;</span><span class="token class-name" style="color: rgb(183, 107, 1);">Product</span><span class="token punctuation" style="color: rgb(56, 58, 66);">&gt;</span></span> <span class="token function" style="color: rgb(64, 120, 242);">searchProducts</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>
    <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Param</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"name"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token class-name" style="color: rgb(183, 107, 1);">String</span> name<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span>
    <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Param</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"minPrice"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token class-name" style="color: rgb(183, 107, 1);">BigDecimal</span> minPrice<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span>
    <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Param</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"maxPrice"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token class-name" style="color: rgb(183, 107, 1);">BigDecimal</span> maxPrice<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span>
    <span class="token class-name" style="color: rgb(183, 107, 1);">Pageable</span> pageable<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Lock</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token class-name" style="color: rgb(183, 107, 1);">LockModeType</span><span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token constant" style="color: rgb(183, 107, 1);">PESSIMISTIC_WRITE</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Query</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"SELECT p FROM Product p WHERE p.id = :id"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token class-name" style="color: rgb(183, 107, 1);">Optional</span><span class="token generics"><span class="token punctuation" style="color: rgb(56, 58, 66);">&lt;</span><span class="token class-name" style="color: rgb(183, 107, 1);">Product</span><span class="token punctuation" style="color: rgb(56, 58, 66);">&gt;</span></span> <span class="token function" style="color: rgb(64, 120, 242);">findByIdForUpdate</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Param</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"id"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token class-name" style="color: rgb(183, 107, 1);">Long</span> id<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

}

4. Business Logic Implementation

4.1 Inventory Service with Transaction

java

Copy

Download

// src/main/java/com/productcatalog/service/InventoryService.java
@Service
@RequiredArgsConstructor
public class InventoryService {
private final ProductRepository productRepository;
<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Transactional</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">public</span> <span class="token keyword" style="color: rgb(166, 38, 164);">void</span> <span class="token function" style="color: rgb(64, 120, 242);">updateInventory</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token class-name" style="color: rgb(183, 107, 1);">Long</span> productId<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> <span class="token keyword" style="color: rgb(166, 38, 164);">int</span> quantityChange<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">{</span>
    <span class="token class-name" style="color: rgb(183, 107, 1);">Product</span> product <span class="token operator" style="color: rgb(64, 120, 242);">=</span> productRepository<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">findByIdForUpdate</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>productId<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
        <span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">orElseThrow</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token operator" style="color: rgb(64, 120, 242);">-&gt;</span> <span class="token keyword" style="color: rgb(166, 38, 164);">new</span> <span class="token class-name" style="color: rgb(183, 107, 1);">ProductNotFoundException</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>productId<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
    
    <span class="token keyword" style="color: rgb(166, 38, 164);">int</span> newInventory <span class="token operator" style="color: rgb(64, 120, 242);">=</span> product<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">getInventory</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token operator" style="color: rgb(64, 120, 242);">+</span> quantityChange<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
    <span class="token keyword" style="color: rgb(166, 38, 164);">if</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>newInventory <span class="token operator" style="color: rgb(64, 120, 242);">&lt;</span> <span class="token number" style="color: rgb(183, 107, 1);">0</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">{</span>
        <span class="token keyword" style="color: rgb(166, 38, 164);">throw</span> <span class="token keyword" style="color: rgb(166, 38, 164);">new</span> <span class="token class-name" style="color: rgb(183, 107, 1);">InsufficientInventoryException</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
    <span class="token punctuation" style="color: rgb(56, 58, 66);">}</span>
    
    product<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">setInventory</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>newInventory<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
<span class="token punctuation" style="color: rgb(56, 58, 66);">}</span>

}

4.2 Product Search with Specifications

java

Copy

Download

// src/main/java/com/productcatalog/service/ProductSearchService.java
public class ProductSearchService {
<span class="token keyword" style="color: rgb(166, 38, 164);">public</span> <span class="token keyword" style="color: rgb(166, 38, 164);">static</span> <span class="token class-name" style="color: rgb(183, 107, 1);">Specification</span><span class="token generics"><span class="token punctuation" style="color: rgb(56, 58, 66);">&lt;</span><span class="token class-name" style="color: rgb(183, 107, 1);">Product</span><span class="token punctuation" style="color: rgb(56, 58, 66);">&gt;</span></span> <span class="token function" style="color: rgb(64, 120, 242);">hasName</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token class-name" style="color: rgb(183, 107, 1);">String</span> name<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">{</span>
    <span class="token keyword" style="color: rgb(166, 38, 164);">return</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>root<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> query<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> cb<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token operator" style="color: rgb(64, 120, 242);">-&gt;</span> 
        name <span class="token operator" style="color: rgb(64, 120, 242);">==</span> <span class="token keyword" style="color: rgb(166, 38, 164);">null</span> <span class="token operator" style="color: rgb(64, 120, 242);">?</span> <span class="token keyword" style="color: rgb(166, 38, 164);">null</span> <span class="token operator" style="color: rgb(64, 120, 242);">:</span> cb<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">like</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>cb<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">lower</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>root<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">get</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"name"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> <span class="token string" style="color: rgb(80, 161, 79);">"%"</span> <span class="token operator" style="color: rgb(64, 120, 242);">+</span> name<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">toLowerCase</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token operator" style="color: rgb(64, 120, 242);">+</span> <span class="token string" style="color: rgb(80, 161, 79);">"%"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
<span class="token punctuation" style="color: rgb(56, 58, 66);">}</span>

<span class="token keyword" style="color: rgb(166, 38, 164);">public</span> <span class="token keyword" style="color: rgb(166, 38, 164);">static</span> <span class="token class-name" style="color: rgb(183, 107, 1);">Specification</span><span class="token generics"><span class="token punctuation" style="color: rgb(56, 58, 66);">&lt;</span><span class="token class-name" style="color: rgb(183, 107, 1);">Product</span><span class="token punctuation" style="color: rgb(56, 58, 66);">&gt;</span></span> <span class="token function" style="color: rgb(64, 120, 242);">priceBetween</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token class-name" style="color: rgb(183, 107, 1);">BigDecimal</span> min<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> <span class="token class-name" style="color: rgb(183, 107, 1);">BigDecimal</span> max<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">{</span>
    <span class="token keyword" style="color: rgb(166, 38, 164);">return</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>root<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> query<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> cb<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token operator" style="color: rgb(64, 120, 242);">-&gt;</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">{</span>
        <span class="token class-name" style="color: rgb(183, 107, 1);">List</span><span class="token generics"><span class="token punctuation" style="color: rgb(56, 58, 66);">&lt;</span><span class="token class-name" style="color: rgb(183, 107, 1);">Predicate</span><span class="token punctuation" style="color: rgb(56, 58, 66);">&gt;</span></span> predicates <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token keyword" style="color: rgb(166, 38, 164);">new</span> <span class="token class-name" style="color: rgb(183, 107, 1);">ArrayList</span><span class="token generics"><span class="token punctuation" style="color: rgb(56, 58, 66);">&lt;</span><span class="token punctuation" style="color: rgb(56, 58, 66);">&gt;</span></span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
        <span class="token keyword" style="color: rgb(166, 38, 164);">if</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>min <span class="token operator" style="color: rgb(64, 120, 242);">!=</span> <span class="token keyword" style="color: rgb(166, 38, 164);">null</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> predicates<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">add</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>cb<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">ge</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>root<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">get</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"price"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> min<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
        <span class="token keyword" style="color: rgb(166, 38, 164);">if</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>max <span class="token operator" style="color: rgb(64, 120, 242);">!=</span> <span class="token keyword" style="color: rgb(166, 38, 164);">null</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> predicates<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">add</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>cb<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">le</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>root<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">get</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"price"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> max<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
        <span class="token keyword" style="color: rgb(166, 38, 164);">return</span> cb<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">and</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>predicates<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">toArray</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token keyword" style="color: rgb(166, 38, 164);">new</span> <span class="token class-name" style="color: rgb(183, 107, 1);">Predicate</span><span class="token punctuation" style="color: rgb(56, 58, 66);">[</span><span class="token number" style="color: rgb(183, 107, 1);">0</span><span class="token punctuation" style="color: rgb(56, 58, 66);">]</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
    <span class="token punctuation" style="color: rgb(56, 58, 66);">}</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
<span class="token punctuation" style="color: rgb(56, 58, 66);">}</span>

}

5. API Layer Implementation

5.1 REST Controller with Validation

java

Copy

Download

// src/main/java/com/productcatalog/controller/ProductController.java
@RestController
@RequestMapping("/api/products")
@Validated
public class ProductController {
<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@PostMapping</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">public</span> <span class="token class-name" style="color: rgb(183, 107, 1);">ResponseEntity</span><span class="token generics"><span class="token punctuation" style="color: rgb(56, 58, 66);">&lt;</span><span class="token class-name" style="color: rgb(183, 107, 1);">Product</span><span class="token punctuation" style="color: rgb(56, 58, 66);">&gt;</span></span> <span class="token function" style="color: rgb(64, 120, 242);">createProduct</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>
    <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Valid</span> <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@RequestBody</span> <span class="token class-name" style="color: rgb(183, 107, 1);">ProductCreateRequest</span> request<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">{</span>
    <span class="token comment" style="color: rgb(160, 161, 167); font-style: italic;">// Implementation</span>
<span class="token punctuation" style="color: rgb(56, 58, 66);">}</span>

<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@GetMapping</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"/search"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">public</span> <span class="token class-name" style="color: rgb(183, 107, 1);">Page</span><span class="token generics"><span class="token punctuation" style="color: rgb(56, 58, 66);">&lt;</span><span class="token class-name" style="color: rgb(183, 107, 1);">Product</span><span class="token punctuation" style="color: rgb(56, 58, 66);">&gt;</span></span> <span class="token function" style="color: rgb(64, 120, 242);">searchProducts</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>
    <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@RequestParam</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>required <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token boolean" style="color: rgb(183, 107, 1);">false</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token class-name" style="color: rgb(183, 107, 1);">String</span> name<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span>
    <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@RequestParam</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>required <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token boolean" style="color: rgb(183, 107, 1);">false</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Min</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token number" style="color: rgb(183, 107, 1);">0</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token class-name" style="color: rgb(183, 107, 1);">BigDecimal</span> minPrice<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span>
    <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@RequestParam</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>required <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token boolean" style="color: rgb(183, 107, 1);">false</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Max</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token number" style="color: rgb(183, 107, 1);">10000</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token class-name" style="color: rgb(183, 107, 1);">BigDecimal</span> maxPrice<span class="token punctuation" style="color: rgb(56, 58, 66);">,</span>
    <span class="token class-name" style="color: rgb(183, 107, 1);">Pageable</span> pageable<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">{</span>
    <span class="token comment" style="color: rgb(160, 161, 167); font-style: italic;">// Implementation</span>
<span class="token punctuation" style="color: rgb(56, 58, 66);">}</span>

}

5.2 DTO with Validation

java

Copy

Download

public class ProductCreateRequest {
@NotBlank
@Pattern(regexp = "^[A-Z]{3}-\d{4}$")
private String sku;
<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@NotBlank</span> <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Size</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>max <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token number" style="color: rgb(183, 107, 1);">255</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">String</span> name<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Positive</span> <span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Digits</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>integer <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token number" style="color: rgb(183, 107, 1);">8</span><span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> fraction <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token number" style="color: rgb(183, 107, 1);">2</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">BigDecimal</span> price<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

}

6. Testing Strategy

6.1 Testcontainers Configuration

java

Copy

Download

// src/test/java/com/productcatalog/TestConfig.java
@TestConfiguration
public class TestConfig {
@Bean
@ServiceConnection
public PostgreSQLContainer<?> postgresContainer() {
return new PostgreSQLContainer<>("postgres:15")
.withDatabaseName("testdb")
.withUsername("test")
.withPassword("test");
}
}

6.2 Integration Test Example

java

Copy

Download

// src/test/java/com/productcatalog/ProductRepositoryIT.java
@SpringBootTest
@AutoConfigureTestDatabase(replace = Replace.NONE)
@Import(TestConfig.class)
class ProductRepositoryIT {
<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Autowired</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">private</span> <span class="token class-name" style="color: rgb(183, 107, 1);">ProductRepository</span> repository<span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>

<span class="token annotation punctuation" style="color: rgb(56, 58, 66);">@Test</span>
<span class="token keyword" style="color: rgb(166, 38, 164);">void</span> <span class="token function" style="color: rgb(64, 120, 242);">shouldPersistProductWithVariant</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span> <span class="token punctuation" style="color: rgb(56, 58, 66);">{</span>
    <span class="token class-name" style="color: rgb(183, 107, 1);">Product</span> product <span class="token operator" style="color: rgb(64, 120, 242);">=</span> <span class="token keyword" style="color: rgb(166, 38, 164);">new</span> <span class="token class-name" style="color: rgb(183, 107, 1);">Product</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"ABC-1234"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> <span class="token string" style="color: rgb(80, 161, 79);">"Test Product"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
    product<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">addVariant</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token keyword" style="color: rgb(166, 38, 164);">new</span> <span class="token class-name" style="color: rgb(183, 107, 1);">ProductVariant</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token string" style="color: rgb(80, 161, 79);">"RED"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">,</span> <span class="token string" style="color: rgb(80, 161, 79);">"Large"</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
    
    <span class="token class-name" style="color: rgb(183, 107, 1);">Product</span> saved <span class="token operator" style="color: rgb(64, 120, 242);">=</span> repository<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">save</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>product<span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
    <span class="token function" style="color: rgb(64, 120, 242);">assertThat</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span>saved<span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">getVariants</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">.</span><span class="token function" style="color: rgb(64, 120, 242);">hasSize</span><span class="token punctuation" style="color: rgb(56, 58, 66);">(</span><span class="token number" style="color: rgb(183, 107, 1);">1</span><span class="token punctuation" style="color: rgb(56, 58, 66);">)</span><span class="token punctuation" style="color: rgb(56, 58, 66);">;</span>
<span class="token punctuation" style="color: rgb(56, 58, 66);">}</span>

}

7. Deployment & Monitoring

7.1 Docker Compose Setup

yaml

Copy

Download

# docker-compose.yml version: '3.8'

services: postgres: image: postgres:15 environment: POSTGRES_USER: admin POSTGRES_PASSWORD: secret POSTGRES_DB: productcatalog ports: - "5432:5432" volumes: - pgdata:/var/lib/postgresql/data

app: build: . ports: - "8080:8080" depends_on: - postgres environment: SPRING_DATASOURCE_URL: jdbc:postgresql://postgres:5432/productcatalog

volumes: pgdata:

7.2 Actuator Configuration

yaml

Copy

Download

# application-prod.yml
management:
endpoints:
web:
exposure:
include: health, info, metrics, flyway
endpoint:
health:
show-details: always
group:
db:
include: db

Key PostgreSQL Features Utilized

Feature	Implementation
Transactions	@Transactional annotations
Constraints	CHECK, NOT NULL, UNIQUE
Indexes	Flyway migration scripts
Stored Procedures	Trigger for updated_at
Full-Text Search	to_tsvector in custom queries

Next Steps & Improvements

Performance Tuning:

sql

Copy

Download

CREATE INDEX idx_product_name_gin ON products USING gin(to_tsvector('english', name));

Read Replicas:

yaml

Copy

Download

spring:
  datasource:
    read:
      url: jdbc:postgresql://replica:5432/productcatalog

Connection Pool Monitoring:

java

Copy

Download

@Bean
public MeterRegistryCustomizer<MeterRegistry> metrics() {
    return registry -> registry.config().commonTags("application", "product-service");
}

2. Product Catalog Service‐Postgresql Edition‐ step‐by‐step - Wiz-DevTech/prettygirllz GitHub Wiki

PostgreSQL Product Catalog Service

Complete Step-by-Step Implementation

1. Project Setup & Configuration

1.1 Initialize Spring Boot Project

1.2 Configure PostgreSQL in application.yml

2. Database Schema Setup

2.1 Create Flyway Migration

2.2 Add Update Trigger

3. Implement Core Components

3.1 JPA Entity with Auditing

3.2 Repository with Custom Queries

4. Business Logic Implementation

4.1 Inventory Service with Transaction

4.2 Product Search with Specifications

5. API Layer Implementation

5.1 REST Controller with Validation

5.2 DTO with Validation

6. Testing Strategy

6.1 Testcontainers Configuration

6.2 Integration Test Example

7. Deployment & Monitoring

7.1 Docker Compose Setup

7.2 Actuator Configuration

Key PostgreSQL Features Utilized

Next Steps & Improvements

⚠️ GitHub.com Fallback ⚠️

2. Product Catalog Service‐Postgresql Edition‐ step‐by‐step - Wiz-DevTech/prettygirllz GitHub Wiki

PostgreSQL Product Catalog Service

Complete Step-by-Step Implementation

1. Project Setup & Configuration

1.1 Initialize Spring Boot Project

1.2 Configure PostgreSQL in application.yml

2. Database Schema Setup

2.1 Create Flyway Migration

2.2 Add Update Trigger

3. Implement Core Components

3.1 JPA Entity with Auditing

3.2 Repository with Custom Queries

4. Business Logic Implementation

4.1 Inventory Service with Transaction

4.2 Product Search with Specifications

5. API Layer Implementation

5.1 REST Controller with Validation

5.2 DTO with Validation

6. Testing Strategy

6.1 Testcontainers Configuration

6.2 Integration Test Example

7. Deployment & Monitoring

7.1 Docker Compose Setup

7.2 Actuator Configuration

Key PostgreSQL Features Utilized

Next Steps & Improvements

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️