2. Product Catalog Service‐Postgresql Edition‐ step‐by‐step guide Java Product Catalog Service Guide (PostgreSQL Edition) Complete Implementation with Testing PostgreSQL Architecture Diagram (Visual representation of PostgreSQL-based components)

3. Environment Setup (PostgreSQL Edition) 1.1 Install Prerequisites bash

sudo apt install openjdk-17-jdk

sudo apt install maven

docker run -d -p 5432:5432 --name postgres
-e POSTGRES_USER=admin
-e POSTGRES_PASSWORD=secret
-e POSTGRES_DB=productcatalog
postgres:15

sudo snap install intellij-idea-ultimate --classic 1.2 Verify Installations bash java -version # Should show 17+ mvn -v # Should show 3.8+ docker ps # Should show PostgreSQL running 2. Project Initialization (PostgreSQL) 2.1 Create Maven Project bash mvn archetype:generate -DgroupId=com.productcatalog -DartifactId=product-service -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false cd product-service 2.2 Update pom.xml for PostgreSQL xml 4.0.0

<!-- PostgreSQL -->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <scope>runtime</scope>
</dependency>

<!-- Flyway for migrations -->
<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-core</artifactId>
</dependency>

<!-- Testing -->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-test</artifactId>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>postgresql</artifactId>
  <scope>test</scope>
</dependency>

import jakarta.persistence.*; import org.hibernate.annotations.Check;

@Entity @Table(name = "products") @Check(constraints = "sku ~ '^[A-Z]{3}-\d{4}$'") public class Product { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id;

@Column(unique = true, nullable = false)
private String sku;  // Format: ABC-1234

private String name;
private int inventory;

// Constructors, Getters, Setters

} 3.2 Repository Layer (JPA) java // src/main/java/com/productcatalog/repository/ProductRepository.java package com.productcatalog.repository;

import com.productcatalog.model.Product; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query;

public interface ProductRepository extends JpaRepository<Product, Long> { @Query("SELECT p FROM Product p WHERE p.sku = :sku") Product findBySku(String sku); } 3.3 Service Layer with Transaction Management java // src/main/java/com/productcatalog/service/ProductService.java package com.productcatalog.service;

import com.productcatalog.exception.InvalidSKUException; import com.productcatalog.model.Product; import com.productcatalog.repository.ProductRepository; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional;

@Service public class ProductService { private final ProductRepository repository;

public ProductService(ProductRepository repository) {
    this.repository = repository;
}

@Transactional
public Product createProduct(Product product) throws InvalidSKUException {
    if (!product.getSku().matches("^[A-Z]{3}-\\d{4}$")) {
        throw new InvalidSKUException("SKU must match format ABC-1234");
    }
    return repository.save(product);
}

} 3.4 Database Migration (Flyway) sql -- src/main/resources/db/migration/V1__Create_products_table.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, inventory INTEGER NOT NULL DEFAULT 0, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP );

CREATE INDEX idx_products_sku ON products(sku); 4. Testing Implementation (PostgreSQL) 4.1 Unit Tests java // src/test/java/com/productcatalog/service/ProductServiceTest.java package com.productcatalog.service;

import com.productcatalog.exception.InvalidSKUException; import com.productcatalog.model.Product; import com.productcatalog.repository.ProductRepository; import org.junit.jupiter.api.Test; import org.mockito.InjectMocks; import org.mockito.Mock; import org.springframework.boot.test.context.SpringBootTest;

import static org.junit.jupiter.api.Assertions.*; import static org.mockito.ArgumentMatchers.any; import static org.mockito.Mockito.when;

@SpringBootTest class ProductServiceTest { @Mock private ProductRepository repository;

@InjectMocks
private ProductService productService;

@Test
void whenInvalidSKU_thenThrowsException() {
    Product product = new Product();
    product.setSku("INVALID");
    assertThrows(InvalidSKUException.class, () -> productService.createProduct(product));
}

} 4.2 Integration Test with Testcontainers java // src/test/java/com/productcatalog/ProductIntegrationTest.java package com.productcatalog;

import com.productcatalog.model.Product; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase; import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest; import org.springframework.test.context.DynamicPropertyRegistry; import org.springframework.test.context.DynamicPropertySource; import org.testcontainers.containers.PostgreSQLContainer; import org.testcontainers.junit.jupiter.Container; import org.testcontainers.junit.jupiter.Testcontainers;

import javax.persistence.EntityManager;

import static org.assertj.core.api.Assertions.assertThat;

@DataJpaTest @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) @Testcontainers class ProductIntegrationTest { @Container static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:15");

@DynamicPropertySource
static void configureProperties(DynamicPropertyRegistry registry) {
    registry.add("spring.datasource.url", postgres::getJdbcUrl);
    registry.add("spring.datasource.username", postgres::getUsername);
    registry.add("spring.datasource.password", postgres::getPassword);
}

@Autowired
private EntityManager entityManager;

@Test
void whenSaveProduct_thenCanRetrieve() {
    Product product = new Product();
    product.setSku("ABC-1234");
    product.setName("Test Product");
    product.setInventory(10);
    
    entityManager.persist(product);
    entityManager.flush();
    
    Product found = entityManager.find(Product.class, product.getId());
    assertThat(found.getSku()).isEqualTo(product.getSku());
}

} 4.3 API Test (MockMVC) java // src/test/java/com/productcatalog/controller/ProductControllerTest.java package com.productcatalog.controller;

import com.fasterxml.jackson.databind.ObjectMapper; import com.productcatalog.model.Product; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.http.MediaType; import org.springframework.test.web.servlet.MockMvc;

import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.post; import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;

@SpringBootTest @AutoConfigureMockMvc class ProductControllerTest { @Autowired private MockMvc mockMvc;

@Test
void whenValidInput_thenReturns200() throws Exception {
    Product product = new Product();
    product.setSku("ABC-1234");
    product.setName("Test Product");
    product.setInventory(10);
    
    mockMvc.perform(post("/api/products")
            .contentType(MediaType.APPLICATION_JSON)
            .content(new ObjectMapper().writeValueAsString(product)))
            .andExpect(status().isOk());
}

} 5. Running and Verification (PostgreSQL) 5.1 Start the Application bash mvn spring-boot:run 5.2 Test Endpoints bash

curl -X POST http://localhost:8080/api/products
-H "Content-Type: application/json"
-d '{"sku":"ABC-1234","name":"Sample Product","inventory":10}'

docker exec -it postgres psql -U admin -d productcatalog

SELECT * FROM products; 5.3 Run All Tests bash mvn test

6. Deployment (PostgreSQL) 6.1 Build JAR bash mvn clean package 6.2 Docker Deployment dockerfile

FROM eclipse-temurin:17-jdk COPY target/product-service-0.0.1-SNAPSHOT.jar app.jar ENTRYPOINT ["java","-jar","app.jar"] Build and run:

bash docker build -t product-service . docker run -p 8080:8080 --network host product-service PostgreSQL-Specific Features ✅ Relational Integrity: Foreign keys and constraints ✅ ACID Transactions: Full transaction support ✅ Advanced Querying: Complex joins and aggregations ✅ Performance: Indexing and query optimization ✅ Schema Management: Flyway for versioned migrations

Next Steps Add Swagger UI for API documentation

Implement JWT Authentication

Set up PostgreSQL Replication for high availability

Configure Connection Pooling with HikariCP

Would you like me to expand on any PostgreSQL-specific features? 🐘