2014년 9월 23일

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

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


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

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


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


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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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);
     
}


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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<integer sequencer=""> getAllSequencer() { 
  Map<integer sequencer=""> sequencers = new HashMap<integer sequencer="">(); 
  JdbcTemplate template = new JdbcTemplate(dataSource);
  List<jdbcsequencer> list = template.query(
    "SELECT SEQUENCER_ID, NAME, VALUE FROM V2_SEQUENCER",
    new ResultSetExtractor<list dbcsequencer="">>() {
     public List<jdbcsequencer> extractData(ResultSet rs) throws SQLException, DataAccessException {
     List<jdbcsequencer> l = new ArrayList<jdbcsequencer>();
     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;
 }
  
}
 
</jdbcsequencer></jdbcsequencer></jdbcsequencer></list></jdbcsequencer></integer></integer></integer>


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
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();
 }
      
}


싱글톤 형태로 생성하여 사용하면 되며 테이블은 아래와 같이 생성한다.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
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 '시퀀서 값'; 
작성자: 시간:
라벨:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)