GlueSequence 개발 예

다음은 sequence incrementer를 지원하지 않는 MariaDB 을 사용하는 경우의 예시입니다.

1. applicationContext.xml : dao 의 sequence 설정

   <beans ...>
       <bean id="dao" class="com.poscoict.glueframework.dao.jdbc.GlueJdbcDao">
           <property name="dataSource"><ref local="dataSource"/></property>
           <property name="queryManager"><ref local="queryManager"/></property>
           <property name="sequence"><ref local="sequence"/></property>
       </bean>
       <bean id="sequence" class="MySQLSequence">
           <constructor-arg><ref local="dataSource"/></constructor-arg>
       </bean>
   </beans>
   

2. GlueSequence 구현

   import javax.sql.DataSource;
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   public class MySQLSequence implements GlueSequence{
       private DataSource dataSource;
   
       public MySQLSequence( DataSource ds ){
           this.dataSource = ds;
       }
   
       @Override
       public long getNextLongValue( String sequenceName ) {
           return getNextKey( sequenceName );
       }
   
       @Override
       public int getNextIntValue( String sequenceName ) {
           return return (int)getNextKey();;
       }
   
       @Override
       public String getNextStringValue( String sequenceName ) {
           return Long.toString(getNextKey());
       }
   
       private String getSequenceQuery( String sequenceName ){
           return "select nextval('" + sequenceName + "')";
       }
   
       private long getNextKey( String sequenceName ) throws DataAccessException {
           Connection con = dataSource.getConnection();
           Statement stmt = null;
           ResultSet rs = null;
           try {
               stmt = con.createStatement();
               rs = stmt.executeQuery( getSequenceQuery(sequenceName) );
               long l;
               if (rs.next()) {
                   return rs.getLong(1);
               }
           }catch (SQLException ex){
           }finally{
               rs.close();
               stmt.close();
               con.close();
           }
       }
   }
   

3. MariaDB 에 nextval() function 생성

   DELIMITER //
   
   CREATE FUNCTION 'nextval'( p_seq_nm varchar(50)) RETURNS bigint(20)
   begin
       declare nLast_val int;
   
       set nLast_val =  (select last_cached_val
                         from mysql_seq_table
                         where seq_nm = p_seq_nm);
       if nLast_val is null then
           set nLast_val = 0;
           insert into mysql_seq_table (seq_nm,seq_val)
           values (p_seq_nm,nLast_Val);
       else
           set nLast_val = nLast_val + 1;
           update mysql_seq_table set last_cached_val = nLast_val
           where seq_nm = p_seq_nm;
       end if;
   
       return nLast_val;
   end
   
   //
   DELIMITER ;
   

4. nextval() function 에서 사용할 table 생성

   create table mysql_seq_table (
       seq_nm  varchar(50)   not null,
       last_cached_val  bigint   not null )
   engine=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   
   alter table seq2 add constraint mysql_seq_table primary key ( 'seq_nm' );
   

5. GlueSequence의 getNextLongValue()의 인자와 관련된 업무 table 예제

   create table biz_table1 (
       biz_column1 bigint not null,
       ...
   )
   
   alter table SQ_BIZ_TABLE1 add constraint mysql_seq_table primary key ( 'biz_column1' );