Java/Spring+MySQL中如何生成年度重置的自定义实体ID

Hey there! I totally get where you're coming from—custom ID formats that reset annually are super common for apps like order tracking or record management, and it's easy to get stuck figuring out a reliable, standard way to implement this. Let's break down a solid, battle-tested approach using your tech stack.

Core Approach: Database-Driven Counter (Best for Small Apps) #

Since your counter needs to reset every year, a dedicated database table to track per-year counts is the most straightforward and reliable method. This ensures atomicity (no duplicate IDs even under concurrent requests) and persistence (counts survive app restarts).

Step 1: Create the Counter Table #

First, set up a simple table in MySQL to store the current count for each year:

CREATE TABLE id_counter (
    year_code CHAR(2) NOT NULL PRIMARY KEY, -- Stores last two digits of the year (e.g., "18")
    current_count INT NOT NULL DEFAULT 0
);

Step 2: Implement the Generator in Spring #

Use Spring's JdbcTemplate (or Spring Data JPA if you prefer) to handle the atomic update and ID formatting. Here's a complete service class example:

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

@Service
public class CustomIdGenerator {
    private final JdbcTemplate jdbcTemplate;

    // Constructor injection (preferred over @Autowired)
    public CustomIdGenerator(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Transactional // Ensures count update and read are atomic
    public String generateNextId() {
        // Get last two digits of current year
        String yearCode = LocalDate.now().format(DateTimeFormatter.ofPattern("yy"));
        
        // Atomic upsert: insert new year if not exists, increment count if it does
        String upsertSql = """
            INSERT INTO id_counter (year_code, current_count)
            VALUES (?, 1)
            ON DUPLICATE KEY UPDATE current_count = current_count + 1;
            """;
        jdbcTemplate.update(upsertSql, yearCode);
        
        // Fetch the updated count
        Integer currentCount = jdbcTemplate.queryForObject(
            "SELECT current_count FROM id_counter WHERE year_code = ?",
            Integer.class,
            yearCode
        );
        
        // Format into your desired ID pattern (e.g., MDF18-001)
        return String.format("MDF%s-%03d", yearCode, currentCount);
    }
}

Key Notes for Reliability #

• Atomicity: The INSERT ... ON DUPLICATE KEY UPDATE statement is handled atomically by MySQL, so even if multiple requests hit at the same time, you won't get duplicate counts.
• Transaction Safety: The @Transactional annotation ensures that if something goes wrong after updating the count (like a business logic failure), the count rollbacks to avoid gaps.
• Zero Padding: %03d in the formatter ensures the count is always three digits (e.g., 1 becomes 001, 10 becomes 010).

Alternative: Redis for High Concurrency #

If your app grows and needs to handle very high request volumes, you could swap the database counter for Redis. Redis's INCR command is atomic, and you can set an expiration on the key at the end of the year to auto-reset the count. But for a small program, the database approach is more than sufficient.

Final Tips #

• Test Edge Cases: Make sure to test what happens on January 1st—does the generator correctly switch to the new year and start counting from 001?
• Prefix Flexibility: If you ever need to change the prefix (from MDF to something else), just update the String.format line.

内容的提问来源于stack exchange，提问作者Simon