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>
날짜타입이나 숫자 타입은 형변환을 해야 에러가 안 생긴다.
'프로그램 > 스프링' 카테고리의 다른 글
스프링 대용량 멀티 배치_스케줄 (0) | 2022.08.16 |
---|---|
전자정부 프레임워크 Swagger UI 적용 (0) | 2022.01.13 |
[PWA] 자바로 pwa push 알림 기능 개발하기(12) (0) | 2021.08.03 |
[PWA] 자바로 pwa push 알림 기능 개발하기(11) (0) | 2021.08.03 |
[PWA] 자바로 pwa push 알림 기능 개발하기(10) (0) | 2021.08.03 |
댓글