2. PostgreSQL Project Structure for Product Catalog Service - Wiz-DevTech/prettygirllz GitHub Wiki

PostgreSQL Project Structure for Product Catalog Service Here's the updated Maven project structure for your Java Product Catalog Service using PostgreSQL instead of MongoDB, with all necessary adjustments:

product-service/
├── src/
│   ├── main/
│   │   ├── java/
│   │   │   └── com/
│   │   │       └── productcatalog/
│   │   │           ├── config/                # Configuration classes
│   │   │           │   ├── JpaConfig.java     # Replaces MongoConfig
│   │   │           │   ├── SwaggerConfig.java
│   │   │           │   └── FlywayConfig.java  # New for migrations
│   │   │           ├── controller/            # API endpoints
│   │   │           │   ├── ProductController.java
│   │   │           │   ├── CandyController.java
│   │   │           │   └── FashionController.java
│   │   │           ├── model/                 # JPA entities
│   │   │           │   ├── Product.java       # Now with @Entity
│   │   │           │   ├── SKU.java           # Now with @Table
│   │   │           │   └── ColorVariant.java
│   │   │           ├── repository/            # JPA repositories
│   │   │           │   ├── ProductRepository.java
│   │   │           │   └── SKURepository.java
│   │   │           ├── service/               # Business logic
│   │   │           │   ├── ProductService.java
│   │   │           │   ├── impl/              # Service implementations
│   │   │           │   │   ├── ProductServiceImpl.java
│   │   │           │   │   └── InventoryServiceImpl.java
│   │   │           ├── exception/             # Custom exceptions
│   │   │           │   ├── InvalidSKUException.java
│   │   │           │   └── GlobalExceptionHandler.java
│   │   │           └── ProductCatalogApplication.java
│   │   └── resources/
│   │       ├── application.yml                # Configs (YAML format)
│   │       ├── bootstrap.yml
│   │       ├── db/
│   │       │   └── migration/                # Flyway scripts (new)
│   │       │       ├── V1__Initial_schema.sql
│   │       │       └── V2__Add_constraints.sql
│   │       └── messages.properties           # I18n
│   └── test/                                 # Mirror main structure
│       └── java/
│           └── com/
│               └── productcatalog/
│                   ├── controller/
│                   │   └── ProductControllerTest.java
│                   ├── service/
│                   │   └── ProductServiceTest.java
│                   └── integration/
│                       └── ProductRepositoryIT.java
├── target/                                   # Generated builds
├── pom.xml                                  # Updated for PostgreSQL
├── Dockerfile                               # Updated for PostgreSQL
└── README.md

Key PostgreSQL-Specific Changes

  1. Configuration Updates JpaConfig.java (replaces MongoConfig):

java @Configuration @EnableJpaRepositories @EnableTransactionManagement public class JpaConfig {

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource) {
    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
    em.setDataSource(dataSource);
    em.setPackagesToScan("com.productcatalog.model");
    em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
    return em;
}

} FlywayConfig.java (new):

java @Configuration public class FlywayConfig {

@Bean
public Flyway flyway(DataSource dataSource) {
    return Flyway.configure()
        .dataSource(dataSource)
        .locations("classpath:db/migration")
        .load();
}

} 2. Entity Changes (JPA Annotations) Product.java:

java @Entity @Table(name = "products") public class Product { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id;

@Column(nullable = false, unique = true)
private String sku;

@Column(nullable = false)
private String name;

@OneToMany(mappedBy = "product", cascade = CascadeType.ALL)
private List<ColorVariant> variants = new ArrayList<>();

// Getters and setters

} SKU.java:

java @Entity @Table(name = "skus") public class SKU { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id;

@Column(name = "code", unique = true, length = 8)
private String code;  // Format: "ABC-1234"

@Column(name = "inventory")
private int inventory;

@ManyToOne
@JoinColumn(name = "product_id")
private Product product;

} 3. Repository Updates ProductRepository.java:

java public interface ProductRepository extends JpaRepository<Product, Long> {

@Query("SELECT p FROM Product p JOIN FETCH p.variants WHERE p.sku = :sku")
Optional<Product> findBySkuWithVariants(@Param("sku") String sku);

@Query(value = "SELECT * FROM products WHERE name % :query", nativeQuery = true)
List<Product> fuzzySearchByName(@Param("query") String query);

} 4. Flyway Migration Scripts V1__Initial_schema.sql:

sql CREATE TABLE products ( id BIGSERIAL PRIMARY KEY, sku VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP );

CREATE TABLE skus ( id BIGSERIAL PRIMARY KEY, code VARCHAR(8) NOT NULL UNIQUE CHECK (code ~ '^[A-Z]{3}-\d{4}$'), inventory INTEGER NOT NULL DEFAULT 0, product_id BIGINT REFERENCES products(id) );

CREATE INDEX idx_skus_product_id ON skus(product_id); 5. Updated Test Structure ProductRepositoryIT.java:

java @DataJpaTest @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) @Testcontainers class ProductRepositoryIT {

@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 ProductRepository repository;

@Test
void shouldSaveProductWithVariants() {
    Product product = new Product("TEST-123", "Test Product");
    product.getVariants().add(new ColorVariant("Red", "RED001"));
    
    Product saved = repository.save(product);
    assertNotNull(saved.getId());
    assertEquals(1, saved.getVariants().size());
}

} Key PostgreSQL Advantages in This Structure Relational Modeling:

Proper table relationships with @OneToMany and @ManyToOne

Foreign key constraints in the database

Data Integrity:

Column constraints (NOT NULL, UNIQUE)

CHECK constraints for SKU format validation

Referential integrity through foreign keys

Performance:

Proper indexing strategy

Native PostgreSQL features like fuzzy search (% operator)

Migration Support:

Flyway for version-controlled schema changes

Separate migration scripts for each schema change

Transaction Management:

Spring's @Transactional support

ACID compliance for all operations

Updated Maven Dependencies (pom.xml) xml org.springframework.boot spring-boot-starter-data-jpa 

<!-- PostgreSQL Driver -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

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

<!-- Test Containers -->
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>
⚠️ **GitHub.com Fallback** ⚠️