網頁

▼

2021/6/24

Spring Data JPA 自訂序號產生器 custom sequence generator for Oracle

Spring Data JPA使用自定序號產生器產生Oracle資料表的自訂流水號。


通常資料表的主鍵會設計為一個自動遞增的序號欄位，每次新增一筆資料就加1，在Oracle會設定一個序列(Sequence)搭配trigger來達成。沒特殊需求的情況直接以1, 2, 3, ... , N-1, N的方式增加，在Spring Data JPA會在entity類以下面方式設定。

@Entity
public class Employee implements Serializable {
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "EMPLOYEE_SEQ") // 使用資料庫Sequence產生序號，使用名為"EMPLOYEE_SEQ"的序號產生器
    @SequenceGenerator(sequenceName = "EMPLOYEE_SEQ", allocationSize = 1, name = "EMPLOYEE_SEQ") // 使用資料庫的序列EMPLOYEE_SEQ，序列產生器名稱為"EMPLOYEE_SEQ"
    private long id;
    
    ...
}

但有時會要在序號前後加一些其他文字，例如前面補0固定長度(e.g. 001, 002, 003)、前面加日期(e.g. 202106240001, 202106240002)。


在Spring Data JPA可透過自訂序號產生器來達成。

例如現在EMPLOYEE.ID的序號要以M2021062500001的方式產生，M是固定前墜字，20210625是新增當下的日期，00001是流水號。

建立自訂序號產生器DemoSequenceGenerator，繼承hibernate SequenceStyleGenerator類並覆寫configure()及generate()如下。

DemoSequenceGenerator

package com.abc.demo.entity.seq;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.id.enhanced.SequenceStyleGenerator;
import org.hibernate.internal.util.config.ConfigurationHelper;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.type.LongType;
import org.hibernate.type.Type;

import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

public class DemoSequenceGenerator extends SequenceStyleGenerator {

    public static final String STRATEGY = "com.abc.demo.entity.seq.DemoSequenceGenerator";

    public static final String PARAM_PREFIX = "prefix";
    public static final String PARAM_DATETIME_FORMAT = "datetimeFormat";
    public static final String PARAM_SEQ_LENGTH = "seqLength";
    public static final String PARAM_PADDING_CHAR = "paddingChar";

    public static final String DATETIME_FORMAT_YYYYMMDD = "yyyyMMdd";

    private String prefix;
    private String datetimeFormat;
    private int seqLength;
    private char paddingChar;

    @Override
    public void configure(Type type, Properties params, ServiceRegistry serviceRegistry) {
        super.configure(LongType.INSTANCE, params, serviceRegistry);

        prefix = ConfigurationHelper.getString(PARAM_PREFIX, params, "");

        SimpleDateFormat sdf = new SimpleDateFormat(ConfigurationHelper.getString(PARAM_DATETIME_FORMAT, params, DATETIME_FORMAT_YYYYMMDD));
        datetimeFormat = sdf.format(new Date());

        seqLength = Integer.parseInt(ConfigurationHelper.getString(PARAM_SEQ_LENGTH, params, "0"));
        paddingChar = ConfigurationHelper.getString(PARAM_PADDING_CHAR, params, "").charAt(0);
    }

    @Override
    public Serializable generate(SharedSessionContractImplementor session, Object object) {
        long seq = (Long) super.generate(session, object); // generate sequence from Oracle Sequence 'EMPLOYEE_SEQ'
        
        String seqStr = String.valueOf(seq);
        String paddingSeq = StringUtils.leftPad(seqStr, seqLength, paddingChar);
        
        return String.format("%s%s%s", prefix, datetimeFormat, paddingSeq);
    }
    
}

configure()負責取得在Employee.id上@Parameter設定的參數，並呼叫super.configure()做預設的序列配置。第一個參數Type必須設為LongType.INSTANCE。

generate()負責產生序號，所以序號的產生邏輯寫在這裡。首先呼叫super.generate()取得Oracle EMPLOYEE_SEQ產生的序號，在以此為基礎搭配configure()取得的參數組合成自訂的序號格式。


在Employee.id改用@GenericGenerator設定如下，注意name為Oracle序列名稱EMPLOYEE_SEQ、strategy為自訂產生器的完整類別名稱(full qualified name)。

Employee

package com.abc.demo.entity;

import com.abc.demo.entiy.DemoSequenceGenerator;
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Parameter;

import javax.persistence.*;

@Entity
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "EMPLOYEE_SEQ")
    @GenericGenerator(
            name = "EMPLOYEE_SEQ",
            strategy = DemoSequenceGenerator.STRATEGY,
            parameters = {
                    @Parameter(name = DemoSequenceGenerator.PARAM_PREFIX, value = "M"),
                    @Parameter(name = DemoSequenceGenerator.PARAM_DATETIME_FORMAT, value = DemoSequenceGenerator.DATETIME_FORMAT_YYYYMMDD),
                    @Parameter(name = DemoSequenceGenerator.PARAM_SEQ_LENGTH, value = "5"),
                    @Parameter(name = DemoSequenceGenerator.PARAM_PADDING_CHAR, value = "0")
            })
    private String id;
    private String name;
    private Integer age;
    
    // getters, setters, hashCode(), equals(), toString()

}

測試


DemoSequenceGeneratorTests

package com.abc.demo.entity.seq;

import com.abc.demo.entity.Employee;
import com.abc.demo.repository.EmployeeRepository;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

@SpringBootTest
public class DemoSequenceGeneratorTests {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Test
    public void test() {

        Employee employee = new Employee();
        employee.setName("John");
        employee.setAge(22);
        employee = employeeRepository.save(employee);
        System.out.println(employee.getId()); // M2021062500001
    }
}


沒有留言:

張貼留言