Mastering Product Management: Build SPU/SKU CRUD with Snowflake IDs in Java

1. Snowflake Algorithm for Distributed IDs

Instantiate IdWorker with data center and machine IDs, then generate unique IDs in a loop:

IdWorker idWorker = new IdWorker(1, 1);
for (int i = 0; i < 10000; i++) {
    long id = idWorker.nextId();
    System.out.println(id);
}

Configuring the ID Generator in Spring

Copy IdWorker.java to the util package.

Add a bean definition in applicationContext-service.xml:

<!-- Snowflake ID Generator -->
<bean id="idWorker" class="com.qingcheng.util.IdWorker">
    <constructor-arg index="0" value="1"/>
    <constructor-arg index="1" value="1"/>
</bean>

2. Adding and Modifying Products

2.1 Table Structure

t_spu stores product (SPU) information such as id, name, brand_id, category IDs, images, and status flags.

t_sku stores SKU details like id, price, stock, spec, and links back to the SPU via spu_id.

2.2 Requirements and Implementation

The front‑end sends a JSON payload containing an spu object and a skuList array. Example (truncated):

{
  "spu": {
    "name": "Sample Product",
    "brandId": 12,
    "category1Id": 558,
    "image": "http://example.com/img1.jpg",
    "introduction": "Product details...",
    "specItems": {"Color": ["Red","Green"]},
    "templateId": 42
  },
  "skuList": [
    {"sn":"001","price":900000,"spec":{"Color":"Red"}},
    {"sn":"002","price":600000,"spec":{"Color":"Green"}}
  ]
}

2.3 Code Implementation

Entity for the combined product:

public class Goods implements Serializable {
    private Spu spu;
    private List<Sku> skuList;
}

Service method to save a product (both insert and update):

@Transactional
public void saveGoods(Goods goods) {
    Spu spu = goods.getSpu();
    if (spu.getId() == null) { // new product
        spu.setId(idWorker.nextId() + "");
        spuMapper.insert(spu);
    } else { // update
        // delete old SKUs
        Example ex = new Example(Sku.class);
        ex.createCriteria().andEqualTo("spuId", spu.getId());
        skuMapper.deleteByExample(ex);
        // update SPU
        spuMapper.updateByPrimaryKeySelective(spu);
    }
    // save SKUs
    Date now = new Date();
    for (Sku sku : goods.getSkuList()) {
        if (sku.getId() == null) {
            sku.setId(idWorker.nextId() + "");
            sku.setCreateTime(now);
        }
        // build SKU name = SPU name + spec values
        String name = spu.getName();
        Map<String,String> specMap = JSON.parseObject(sku.getSpec(), Map.class);
        for (String v : specMap.values()) {
            name += " " + v;
        }
        sku.setName(name);
        sku.setSpuId(spu.getId());
        sku.setCategoryId(spu.getCategory3Id());
        sku.setCategoryName(categoryMapper.selectByPrimaryKey(spu.getCategory3Id()).getName());
        sku.setCommentNum(0);
        sku.setSaleNum(0);
        skuMapper.insert(sku);
    }
    // link category and brand (many‑to‑many)
    CategoryBrand cb = new CategoryBrand();
    cb.setBrandId(spu.getBrandId());
    cb.setCategoryId(spu.getCategory3Id());
    if (categoryBrandMapper.selectCount(cb) == 0) {
        categoryBrandMapper.insert(cb);
    }
}

3. Category‑Brand Association

Because the front‑end needs brand lists per category, a junction table tb_category_brand is created with a composite primary key ( categoryId, brandId).

@Table(name="tb_category_brand")
@Data
public class CategoryBrand implements Serializable {
    @Id private Integer categoryId;
    @Id private Integer brandId;
}

Mapper interface:

public interface CategoryBrandMapper extends Mapper<CategoryBrand> {}

4. Querying a Product by ID

Service method returns a Goods object containing the SPU and its SKU list:

public Goods findGoodsById(String id) {
    Spu spu = spuMapper.selectByPrimaryKey(id);
    Example ex = new Example(Sku.class);
    ex.createCriteria().andEqualTo("spuId", id);
    List<Sku> skuList = skuMapper.selectByExample(ex);
    Goods goods = new Goods();
    goods.setSpu(spu);
    goods.setSkuList(skuList);
    return goods;
}

5. Save vs. Update Logic

Use the presence of spu.id to decide between insert and update.

When updating, delete existing SKUs before inserting the new list.

SKU insertion checks whether sku.id exists to avoid regenerating IDs.

6. Handling SKUs Without Specifications

If a SKU has a null or empty spec, set it to an empty JSON object to prevent null‑pointer errors:

if (sku.getSpec() == null || "".equals(sku.getSpec())) {
    sku.setSpec("{}");
}

7. Product Audit and Status Management

7.1 Audit

When a product passes audit, its status becomes 1 and it is automatically marked as marketable ( isMarketable = "1").

@Transactional
public void audit(String id, String status, String message) {
    Spu spu = new Spu();
    spu.setId(id);
    spu.setStatus(status);
    if ("1".equals(status)) {
        spu.setIsMarketable("1"); // auto‑publish
    }
    spuMapper.updateByPrimaryKeySelective(spu);
    // audit log and product log would be recorded here
}

7.2 Pull (Take Down)

public void pull(String id) {
    Spu spu = spuMapper.selectByPrimaryKey(id);
    spu.setIsMarketable("0"); // off‑shelf
    spuMapper.updateByPrimaryKeySelective(spu);
}

7.3 Put (Publish)

Only products with status = "1" (approved) can be put on shelf.

public void put(String id) {
    Spu spu = spuMapper.selectByPrimaryKey(id);
    if (!"1".equals(spu.getStatus())) {
        throw new RuntimeException("Product not approved");
    }
    spu.setIsMarketable("1");
    spuMapper.updateByPrimaryKeySelective(spu);
    // record product log here
}

7.4 Batch Publish

Front‑end sends an array of IDs; the service updates all matching records that are currently off‑shelf and approved.

public int putMany(Long[] ids) {
    Spu spu = new Spu();
    spu.setIsMarketable("1");
    Example ex = new Example(Spu.class);
    ex.createCriteria()
      .andIn("id", Arrays.asList(ids))
      .andEqualTo("isMarketable", "0")
      .andEqualTo("status", "1")
      .andEqualTo("isDelete", "0");
    return spuMapper.updateByExampleSelective(spu, ex);
}

8. Logical Deletion and Restoration

Logical delete sets is_delete = 1 on the spu record.

Recycle‑bin view filters records where is_delete = 1.

Restoration resets is_delete = 0.

Physical deletion is allowed only after a logical delete and after confirming the record’s state.

All code snippets above are ready to be placed in the corresponding Spring service, controller, and mapper layers to build a fully functional product management module.