프라이머리 키 생성(Generate primary key)

아래와 같은 요구사항을 충족하는 프라이머리 키 생성기를 만들어 보자.

• 숫자 형식
• 클러스터링 환경을 지원

키 생성은 데이터베이스 기반으로 구현하였으며 클러스터링 및 성능을 고려하여 키 생성을 담당하는 객체는 미리 정한 크기만큼 미리 수를 가져와 사용하는 방식으로 구현하였다.

이러한 방식이라면 키 생성을 위하여 매번 데이터베이스에 접속하지 않아도 되며, 다수의 키 생성기가 동작하더라도 중복키가 생성되지 않는다.


클래스구현은 springframework 를 사용하여 구현하였다.

package architecture.common.jdbc.sequencer;

public interface Sequencer
{

    public abstract String getName();
 
    public abstract long getNext() ;

    public abstract int getBlockSize();
    
    public abstract void setBlockSize(int blockSize);
    
}

package architecture.ee.jdbc.sequencer;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import architecture.common.jdbc.sequencer.Sequencer;


public class SequencerFactory {

 private Log log = LogFactory.getLog(getClass());

 private DataSource dataSource;

 public SequencerFactory(DataSource dataSource) {
   this.dataSource = dataSource;
 }

 public boolean isSetDataSource() {
  if (dataSource == null)
   return false;
  else
   return true;
 }

 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
 }

 public JdbcSequencer createJdbcSequencer(int sequenceID, String sequencerName, int blockSize) {
  JdbcSequencer impl = new JdbcSequencer(sequenceID);
  impl.setConfiguration(configuration);
  impl.setDataSource(dataSource);
  impl.setName(sequencerName);
  impl.afterPropertiesSet();
  return impl;
 }

 public Map getAllSequencer() {  
  Map sequencers = new HashMap();  
  JdbcTemplate template = new JdbcTemplate(dataSource);
  List list = template.query(
    "SELECT SEQUENCER_ID, NAME, VALUE FROM V2_SEQUENCER", 
    new ResultSetExtractor>() {
     public List extractData(ResultSet rs) throws SQLException, DataAccessException {
     List l = new ArrayList();
     while (rs.next()) {
      int sequencerID = rs.getInt(1);
      String name = rs.getString(2);
      long value = rs.getLong(3);
      JdbcSequencer sequencer = new JdbcSequencer(sequencerID);
      sequencer.setName(name);
      l.add(sequencer);
     }
     return l;
    }
  });

  for (JdbcSequencer sequencer : list) {
   sequencer.setConfiguration(configuration);
   sequencer.setDataSource(dataSource);
   sequencers.put(sequencer.getSequencerId(), sequencer);
  }
  return sequencers;
 }
 
}

package architecture.ee.jdbc.sequencer;

import java.sql.Types;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import architecture.common.jdbc.sequencer.Sequencer;


public class JdbcSequencer extends JdbcDaoSupport implements Sequencer {
   
    private Log log = LogFactory.getLog(getClass());
    
    private int type;

    private String name;
       
    private long currentID;
    
    private long maxID;
    
    private int blockSize;
    
    private Configuration configuration;
   
    public JdbcSequencer() {
  super();
    }

    public JdbcSequencer(int seqType) {
     super();
        this.type = seqType;
        this.blockSize = 10;
        this.currentID = 0L;
        this.maxID = 0L;  
    }    
 

 public int getSequencerId(){
  return type;
 }
 
    public String getName() {
  return name;
 }

    public void setName(String name) {
  this.name = name;
 }

 /**
  * @return
  * @uml.property  name="blockSize"
  */
 public int getBlockSize() {
        return blockSize;
    }

    public void setBlockSize(int blockSize) {
        this.blockSize = blockSize;
    }

    public synchronized long getNext() {
        if (!(currentID < maxID)) {
            getNextBlock(3);
        }
        long id = currentID;
        currentID++;
        return id;
    }
    
    private long createNewID(int type) {
     long newID = 1L ;     
        getJdbcTemplate().update(getBoundSql("INSERT INTO V2_SEQUENCER (VALUE,NAME,SEQUENCER_ID) VALUES (?, ?, ?)").getSql(), new Object[]{newID, name, type}, new int[]{Types.INTEGER, Types.VARCHAR, Types.INTEGER });    
        return newID;
    }
    
    private int getNextSequencerId(){
     int max = getJdbcTemplate().queryForInt("SELECT MAX ( SEQUENCER_ID ) + 1 FROM V2_SEQUENCER");
     return max + 1; 
    }
    
    /**
     *  
     *  
     * @param count 실패하였을 경우 재 시도 횟수
     */
    private void getNextBlock(int count) {
        
        boolean success = false;
        long currentID = 1;
        try {
            currentID = getJdbcTemplate().queryForLong( "SELECT VALUE FROM V2_SEQUENCER WHERE SEQUENCER_ID = ?", new Object[] { this.type }, new int[] { Types.INTEGER });
        } catch (IncorrectResultSizeDataAccessException e) {
            this.type = getNextSequencerId();     
            currentID = createNewID(this.type);
        }
        long newID = currentID + blockSize;
        success = (1 == getJdbcTemplate().update("UPDATE V2_SEQUENCER SET VALUE = ? WHERE SEQUENCER_ID = ? AND VALUE = ?",
                new Object[] { newID, type, currentID },
                new int[] { Types.INTEGER, Types.INTEGER, Types.INTEGER }));        
        if (success) {
            this.currentID = currentID;
            this.maxID = newID;
        }
        
        if( !success ){
            try {
  Thread.sleep(75L);
     } catch (InterruptedException e) {}
     getNextBlock( count - 1);
        }        
    }

 @Override
 public boolean equals(Object obj) {
  JdbcSequencer other = (JdbcSequencer)obj;
  return other.getSequencerId() == getSequencerId();
 }
     
}

싱글톤 형태로 생성하여 사용하면 되며 테이블은 아래와 같이 생성한다.

  CREATE TABLE V2_SEQUENCER (
      SEQUENCER_ID           INTEGER NOT NULL,
      NAME                   VARCHAR2(200) NOT NULL,
      VALUE                  INTEGER NOT NULL,
      CONSTRAINT V2_SEQUENCER_PK PRIMARY KEY (SEQUENCER_ID)
  );    
  
  CREATE UNIQUE INDEX V2_SEQUENCER_NAME_IDX ON V2_SEQUENCER (NAME);  
        
  COMMENT ON TABLE "V2_SEQUENCER"  IS '애플리케이션 전역에서 사용되는 시퀀서 정보';
  COMMENT ON COLUMN "V2_SEQUENCER"."SEQUENCER_ID" IS '시퀀서 ID'; 
        COMMENT ON COLUMN "V2_SEQUENCER"."NAME" IS '시퀀서 이름';  
  COMMENT ON COLUMN "V2_SEQUENCER"."VALUE" IS '시퀀서 값';