전자정부 스케줄링 crontab 설정 티베로에서 postgresql 연계

pom.xml 의존성 추가

  <!-- https://mvnrepository.com/artifact/org.quartz-scheduler/quartz -->
	<dependency>
	    <groupId>org.quartz-scheduler</groupId>
	    <artifactId>quartz</artifactId>
	    <version>2.3.2</version>
	</dependency>

WEB-INF/config/egovframework/springmvc/egov-com-scheduler.xml 파일 생성

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.3.xsd">
		
		 <!-- 환경설정 기본정보를 globals.properties 에서 참조하도록 propertyConfigurer 설정  -->
    <util:properties id="propertyConfigurer" location="classpath:/egovframework/egovProps/globals.properties"/>
	<context:property-placeholder properties-ref="propertyConfigurer" />
	
	<context:annotation-config />
	
	<bean id="postScheduleService" class="egovframework.tibero.schedule.test.service.PostScheduleServiceImpl" />
	
	<bean id="testScheduleService" class="egovframework.tibero.schedule.test.service.TestScheduleServiceImpl" />
	
	
	
	<!-- MapBuilder Per Hour Cron Quartz Scheduler -->
	<bean id="serviceTestCronQuartzJob" class="org.springframework.scheduling.quartz.JobDetailFactoryBean">
		<property name="jobClass" value="egovframework.tibero.schedule.test.service.TestJobScheduler" />
		<property name="jobDataAsMap">
			<map>
				<entry key="testScheduleService">
					<ref bean="testScheduleService"/>
				</entry>
			</map>
		</property>

	</bean>
 
	<!-- MapBuilder Per Hour Scheduler Trigger cronExpression setting --> 
	<bean id="serviceTestCronTrigger" class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">
		<property name="jobDetail" ref="serviceTestCronQuartzJob"/>
		<property name="cronExpression" value="${scheduler.tibero.stat1.cron.expression}"/>
	</bean>
	 
	 
	<!-- MapBuilder Per Hour Cron Quartz Scheduler -->
	<bean id="serviceTestCronQuartzJob2" class="org.springframework.scheduling.quartz.JobDetailFactoryBean">
		<property name="jobClass" value="egovframework.tibero.schedule.test.service.TestJobScheduler2" />
		<property name="jobDataAsMap">
			<map>
				<entry key="postScheduleService">
					<ref bean="postScheduleService"/>
				</entry>
			</map> 
		</property>

	</bean>
 
	<!-- MapBuilder Per Hour Scheduler Trigger cronExpression setting --> 
	<bean id="serviceTestCronTrigger2" class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">
		<property name="jobDetail" ref="serviceTestCronQuartzJob2"/>
		<property name="cronExpression" value="${scheduler.tibero.stat2.cron.expression}"/>
	</bean>
	
	
	<!-- Scheduler Trigger  -->
	<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
		<property name="triggers">
			<list>
			    <ref bean="serviceTestCronTrigger"/>
			    <!-- 
				<ref bean="serviceTestCronTrigger2"/>
				-->
			</list>
		</property>
	
		<property name="quartzProperties">
			<props>
				<prop key="org.quartz.threadPool.class">org.quartz.simpl.SimpleThreadPool</prop>
    			<prop key="org.quartz.threadPool.threadCount">5</prop>
    			<prop key="org.quartz.threadPool.threadPriority">4</prop>
    			<prop key="org.quartz.jobStore.class">org.quartz.simpl.RAMJobStore</prop>
    			<prop key="org.quartz.jobStore.misfireThreshold">60000</prop>
			</props>
		</property>
	
	</bean>

</beans>

egovProps/globals.properties 파일에 스케줄 시간 설정

# 스케줄러 매일 매시 매분 1초에 실행
scheduler.tibero.stat1.cron.expression = 1 * * * * ?

10초마다 실행
scheduler.tibero.stat2.cron.expression = 0/10 * * * * ?

 

TestJobScheduler.java 파일 생성 

package egovframework.tibero.schedule.test.service;

import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.quartz.QuartzJobBean;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class TestJobScheduler extends QuartzJobBean {
	
	
	@Autowired
	private TestScheduleService testScheduleService;
	
	 public TestScheduleService getTestScheduleService()
	 {
	     return this.testScheduleService;
	 }
	 
	 public void setTestScheduleService(TestScheduleService testScheduleService)
	 {
	   this.testScheduleService = testScheduleService;
	 }
   

	@Override
	protected void executeInternal(JobExecutionContext context) throws JobExecutionException {
		
		 try
	     {
		   log.debug("testScheduleService Job Scheduler Start : " + this.testScheduleService);
	       
	       this.testScheduleService.executeTestTibero();
	 
	       log.info("testScheduleService Job Scheduler Job End!");
	     } catch (Exception e) {
	    	 log.error("스케줄 실행 오류");
	     }
		 
		
	}

}

TestScheduleService.java 파일 생성

package egovframework.tibero.schedule.test.service;

public abstract interface TestScheduleService {
	public abstract void executeTestTibero()  throws Exception;
}

TestScheduleServiceImpl.java 파일생성

package egovframework.tibero.schedule.test.service;

import javax.annotation.Resource;

import org.quartz.DisallowConcurrentExecution;
import org.springframework.stereotype.Service;

import egovframework.frmprd.link.service.TiberoToPostgresService;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service("testScheduleService")
@DisallowConcurrentExecution
public class TestScheduleServiceImpl implements TestScheduleService{
	
	
	@Resource(name = "tiberoToPostgresService")
	private TiberoToPostgresService tiberoToPostgresService;

	@Override
	public void executeTestTibero() throws Exception {

		log.debug("TestScheduleServiceImpl executeTestTibero Start");
		
		tiberoToPostgresService.linkTiberoNcomCdDtl();
		
		log.debug("TestScheduleServiceImpl executeTestTibero End");
		    
	}

}

티베로에서 postgresql로 db_link가 안되어서 일정한 시간마다 연동해서 가져오는 자바 소스 생성

TiberoToPostgreService.java 파일 생성

package egovframework.frmprd.link.service;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.nexacro.uiadapter17.spring.core.data.DataSetRowTypeAccessor;
import com.nexacro17.xapi.data.DataSet;

import egovframework.com.cmm.exception.InvakoException;
import egovframework.frmprd.link.mybatis.mapper.PostgresLinkMapper;
import egovframework.frmprd.link.mybatisTibero.mapper.TiberoLinkMapper;
import egovframework.invako.util.SessionData;
import lombok.extern.slf4j.Slf4j;

/**
 * <pre>
 * @title   
 * @desc    postgres와 tibero 연동을 위한 서비스
 * -        TiberoToPostgresService
 * @package egovframework.frmprd.link.service.TiberoToPostgresService
 * <pre>
 * @author  CBW
 * @since   2021. 08. 17.
 * @version 1.0
 * @see
 * =================== 변경 내역 ==================
 * 날짜			변경자		내용
 * ------------------------------------------------
 * 2021. 08. 17. CBW	최초작성
 * 
 */

@Slf4j
@Service("tiberoToPostgresService")
public class TiberoToPostgresService {
	
	@Resource(name = "tiberoLinkMapper")
	private TiberoLinkMapper tiberoLinkMapper;
	
	@Resource(name = "postgresLinkMapper")
	private PostgresLinkMapper postgresLinkMapper;
	
	
	/* job 스케줄 티베로 select */
	public void linkTiberoNcomCdDtl() throws InvakoException {
		List<Map<String, Object>> list = tiberoLinkMapper.getTiberoNcomCdDtl();
		
		registPostgresNcomCdDtl(list);
		//log.debug("list : " + list );
    } 
	
	/* job 스케줄 postgres 등록 */
	public void registPostgresNcomCdDtl(List<Map<String, Object>> regist) throws InvakoException {

		int size = regist.size();
		for (int i=0; i<size; i++) {
			Map<String,Object> registMap = regist.get(i);
			
			//registMap.put("lgnId", SessionData.id());

			postgresLinkMapper.insertNcomCdDtl(registMap);
			
		}

	}

}

TiberoLinkMapper.java 파일 생성

package egovframework.frmprd.link.mybatisTibero.mapper;

import java.util.List;
import java.util.Map;

import egovframework.invako.util.TiberoMapper;

/**
 * <pre>
 * @title   
 * @desc    postgres와 tibero 연동을 위한 서비스
 * -        TiberoLinkMapper
 * @package egovframework.frmprd.link.mybatisTibero.mapper.TiberoLinkMapper
 * <pre>
 * @author  CBW
 * @since   2021. 08. 17.
 * @version 1.0
 * @see
 * =================== 변경 내역 ==================
 * 날짜			변경자		내용
 * ------------------------------------------------
 * 2021. 08. 17. CBW	최초작성
 * 
 */

@TiberoMapper("tiberoLinkMapper")
public interface TiberoLinkMapper {

	List<Map<String, Object>> getTiberoNcomCdDtl();

}

PostgresLinkMapper.java

package egovframework.frmprd.link.mybatis.mapper;

import java.util.Map;

import egovframework.rte.psl.dataaccess.mapper.Mapper;

/**
 * <pre>
 * @title   
 * @desc    postgres와 tibero 연동을 위한 서비스
 * -        PostgresLinkMapper
 * @package egovframework.frmprd.link.mybatis.mapper.PostgresLinkMapper
 * <pre>
 * @author  CBW
 * @since   2021. 08. 17.
 * @version 1.0
 * @see
 * =================== 변경 내역 ==================
 * 날짜			변경자		내용
 * ------------------------------------------------
 * 2021. 08. 17. CBW	최초작성
 * 
 */

@Mapper("postgresLinkMapper")
public interface PostgresLinkMapper {

	void insertNcomCdDtl(Map<String, Object> registMap);

}

티베로에서 데이터 조회하여 postgreSql 로 데이터 이관처리 하는 쿼리 작업

먼저 티베로에서 읽어 온다.

TiberoToPostgres_SQL.xml

resultMap 으로 데이터 타입을 먼저 지정해준다. 티베로와 postgres와 날짜타입이나 숫자 타입이 안맞아서 그냥 조회하여 처리 하면 postgres에 등록할때 에러가 발생한다. 모든 타입을 일단 스트링으로 읽어온후 postgresql에 등록할때는 해당 타입으로 타입캐스팅 하여 등록하여야 에러가 없다.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="egovframework.frmprd.link.mybatisTibero.mapper.TiberoLinkMapper">
 
    <resultMap id="ResMap" type="egovMap">
		<result property="CD_ID" column="CD_ID" javaType="java.lang.String" />
		<result property="CD_VAL" column="CD_VAL" javaType="java.lang.String" />
		<result property="CD_VAL_NM" column="CD_VAL_NM" javaType="java.lang.String" />
		<result property="CD_VAL_ENG_NM" column="CD_VAL_ENG_NM" javaType="java.lang.String" />
		<result property="CD_VAL_ETC_NM" column="CD_VAL_ETC_NM" javaType="java.lang.String" />
		<result property="CD_VAL_EXPL" column="CD_VAL_EXPL" javaType="java.lang.String" />
		<result property="CD_LVL_DVS_CD" column="CD_LVL_DVS_CD" javaType="java.lang.String" />
		<result property="HP_CD_VAL" column="HP_CD_VAL" javaType="java.lang.String" />
		<result property="EFTV_YN" column="EFTV_YN" javaType="java.lang.String" />
		<result property="RMK" column="RMK" javaType="java.lang.String" />
		<result property="REGR_ID" column="REGR_ID" javaType="java.lang.String" />
		<result property="REG_TM" column="REG_TM" javaType="java.lang.String" />
		<result property="UPDR_ID" column="UPDR_ID" javaType="java.lang.String" />	
		<result property="UPD_TM" column="UPD_TM" javaType="java.lang.String" />
		<result property="ARA_ORDR" column="ARA_ORDR" javaType="java.lang.String" />
	</resultMap> 
     
    <select id="getTiberoNcomCdDtl" parameterType="java.util.Map" resultMap="ResMap">
    
      /* getTiberoNcomCdDtl */
        /* 티베로 공통코드 조회 */
             SELECT CD_ID,
			        CD_VAL,
			        CD_VAL_NM,
			        CD_VAL_ENG_NM,
			        CD_VAL_ETC_NM,
			        CD_VAL_EXPL,
			        CD_LVL_DVS_CD,
			        HP_CD_VAL,
			        EFTV_YN,
			        RMK,
			        REGR_ID,
			        REG_TM,
			        UPDR_ID,
			        UPD_TM,
			        ARA_ORDR
  			FROM NAQSCOM.NCOM_CD_DTL
	  

    </select>
	

    
</mapper>

티베로에서 읽은 데이터를 postgresql에 등록하는 쿼리를 작성한다.

PosrgreToTibero_SQL.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="egovframework.frmprd.link.mybatis.mapper.PostgresLinkMapper">

	 <insert id="insertNcomCdDtl" parameterType="java.util.Map">
        /* insertNcomCdDtl */
        /* postgres 공통코드 등록 */
        
	       WITH UPSERT AS  
					 (
					 	UPDATE naqscom.ncom_cd_dtl 
							SET 
							    cd_id = #{cdId}, 
							    cd_val = #{cdVal}, 
							    cd_val_nm = #{cdValNm}, 
							    cd_val_eng_nm = #{cdValEngNm}, 
							    cd_val_etc_nm = #{cdValEtcNm}, 
							    cd_val_expl = #{cdValExpl}, 
							    cd_lvl_dvs_cd = #{cdLvlDvsCd}, 
							    hp_cd_val = #{hpCdVal}, 
							    eftv_yn = #{eftvYn}, 
							    rmk = #{rmk}, 
							    regr_id = #{regrId}, 
							    reg_tm = to_timestamp(#{regTm}, 'YYYY-MM-DD HH24:MI:SS'), 
							    updr_id = #{updrId}, 
							    upd_tm = to_timestamp(#{updTm}, 'YYYY-MM-DD HH24:MI:SS'), 
							    ara_ordr = cast(#{araOrdr} as NUMERIC)  
							WHERE 
							    cd_id = #{cdId}
							AND cd_val = #{cdVal}
						 RETURNING * 
					 )
			INSERT INTO naqscom.ncom_cd_dtl
				( 
			        cd_id, 
			        cd_val, 
			        cd_val_nm, 
			        cd_val_eng_nm, 
			        cd_val_etc_nm, 
			        cd_val_expl, 
			        cd_lvl_dvs_cd, 
			        hp_cd_val, 
			        eftv_yn, 
			        rmk, 
			        regr_id, 
			        reg_tm, 
			        updr_id, 
			        upd_tm, 
			        ara_ordr 
			    ) 
			    SELECT
					#{cdId}, 
					#{cdVal}, 
					#{cdValNm}, 
					#{cdValEngNm},
					#{cdValEtcNm},
					#{cdValExpl},
					#{cdLvlDvsCd},
					#{hpCdVal}, 
					#{eftvYn},
					#{rmk},
					#{regrId},
					to_timestamp(#{regTm}, 'YYYY-MM-DD HH24:MI:SS'),
					#{updrId}, 
					to_timestamp(#{updTm}, 'YYYY-MM-DD HH24:MI:SS'), 
					cast(#{araOrdr} as NUMERIC)
			  WHERE NOT EXISTS(SELECT * FROM UPSERT)
   
      

    </insert>

    
</mapper>

날짜타입이나 숫자 타입은 형변환을 해야 에러가 안 생긴다.