@Scheduled 어노테이션

배치 프로그램 개발을 위해 @Scheduled 어노테이션과 프로시저를 이용해 작성한 코드를 정리하였습니다.

 

• 배치란?
  • 일정량이 될 때까지 모아 두었다가 일괄적으로 처리하는 방식

---

1. @Scheduled 어노테이션 설정 방법

[1] XML 설정

-  task 네임스페이스와 annotation-driven 요소를 추가

<beans
xmlns:task="http://www.springframework.org/schema/task"
xsi:schemaLocation="http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-3.0.xsd">

<task:annotation-driven />

[2] 어노테이션 설정

@Component
public class TaskBatch {
	
	@Autowired
	BatchServiceImpl batchService;

//	매일 오전 1시 30분
	@Scheduled(cron = "0 30 1 * * *")
	public void feeProcCall() {
		batchService.totalfeeProcedure();
	}
}

 

2. 배치 호출 흐름 순서

- TaskBatch → BatchService 호출 → BatchDao 호출

 

3. 코드 상세

- BatchService.java

public interface BatchService {
	public void totalfeeProcedure();
}

- BatchServiceImpl.java

@Service
public class BatchServiceImpl implements BatchService {
	@Autowired
	private SqlSession sqlSession;

	@Override
	public void totalfeeProcedure() {
		BatchDao batchDao = sqlSession.getMapper(BatchDao.class);		
		batchDao.totalfeeProcedure();
	}

}

- BatchDao.java

public interface BatchDao {
	public void totalfeeProcedure();
}

- BatchDaoImpl.xml

<mapper namespace="com.stevebank.batchkim.dao.BatchDao">
	<select id="totalfeeProcedure" statementType="CALLABLE">
	    <![CDATA[
	        { CALL dailyCnt()}
	    ]]>
	</select>
</mapper>

- 프로시저 dailyCnt

BEGIN

	DECLARE p_brch_code INT(11);
	DECLARE p_balance INT(11);
	DECLARE p_loan_sum INT(11);
	DECLARE p_loan_seq INT(11);
	DECLARE p_no_more_data BOOLEAN default FALSE;
	
-- 1. cursor (보유총액 & 대출총액 & 대출건수)
	DECLARE c_balance CURSOR FOR
		SELECT am.brch_code, sum(i.loan_total), SUM(am.balance), COUNT(i.loan_seq)
		FROM tb_acct_master am, (
					SELECT loan_seq, id, loan_total
					FROM tb_loan_master
					WHERE DATE(acct_dt) = DATE(SUBDATE(NOW(), INTERVAL 1 DAY))
					) i
		WHERE am.id = i.id AND DATE(create_dt) = DATE(SUBDATE(NOW(), INTERVAL 1 DAY))
		GROUP BY (am.brch_code);

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET p_no_more_data := TRUE;

	OPEN c_balance;
	
-- 1. loop (보유총액 & 대출총액 & 대출건수) - INSERT 시작!
	FIRST_LOOP: LOOP

		FETCH FROM c_balance INTO p_brch_code, p_loan_sum, p_balance, p_loan_seq ;
			IF p_no_more_data THEN
				CLOSE c_balance;
				LEAVE FIRST_LOOP;
			ELSE
				INSERT INTO tb_statistics(stat_dt, brch_code, loan_sum, bank_sum, loan_cnt)
				VALUES(NOW(), p_brch_code, p_loan_sum, p_balance, p_loan_seq);
    		END IF;

-- 2. block 시작 (통장개설건수)
	FIRST_BLOCK_INNER: BEGIN

		DECLARE p_brch_code INT(11);
		DECLARE p_acct_cnt INT(11);
		DECLARE p_no_more_data BOOLEAN default FALSE;
	
-- 2. cursor (통장개설건수)
	DECLARE c_acct_cnt CURSOR FOR
		SELECT COUNT(acct_no), brch_code
		FROM tb_acct_master
		WHERE DATE(create_dt) = DATE(SUBDATE(NOW(), INTERVAL 1 DAY))
		GROUP BY(brch_code);

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET p_no_more_data := TRUE;

	OPEN c_acct_cnt;

-- 2. loop (통장개설건수)
	SECOND_LOOP: LOOP

		FETCH FROM c_acct_cnt INTO p_acct_cnt, p_brch_code;
			IF p_no_more_data THEN
				CLOSE c_acct_cnt;
				LEAVE SECOND_LOOP;
			ELSE
				UPDATE tb_statistics
				SET acct_cnt = p_acct_cnt
				WHERE brch_code = p_brch_code	AND DATE(stat_dt) = DATE(NOW());
    		END IF;
    		
		SECOND_BLOCK_INNER: BEGIN
			CALL dailySum();
		END SECOND_BLOCK_INNER;		
 	END LOOP SECOND_LOOP;
--
	END FIRST_BLOCK_INNER;		
	END LOOP FIRST_LOOP; 
 
END

- 프로시저 dailySum

BEGIN

	DECLARE p_brch_code INT(11);
	DECLARE p_delay_amount INT(11);
	DECLARE p_no_more_data BOOLEAN default FALSE;
	
-- 1. cursor (대출지연수수료)
	DECLARE c_loan_list CURSOR FOR
		SELECT am.brch_code, sum(i.delay_amount)
		FROM tb_acct_master am, (
					SELECT acct_no, delay_amount
					FROM tb_loan_schedule
					WHERE DATE(regit_dt) = DATE(SUBDATE(NOW(), INTERVAL 1 DAY))
					) i
		WHERE am.acct_no = i.acct_no
		GROUP BY (am.brch_code);

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET p_no_more_data := TRUE;

	OPEN c_loan_list;
	
-- 1. loop (대출지연수수료)
	FIRST_LOOP: LOOP

		FETCH FROM c_loan_list INTO p_brch_code, p_delay_amount;
			IF p_no_more_data THEN
				CLOSE c_loan_list;
				LEAVE FIRST_LOOP;
			ELSE
				UPDATE tb_statistics
				SET delay_amount_sum = p_delay_amount
				WHERE brch_code = p_brch_code AND date(stat_dt) = DATE(NOW());
    		END IF;
			
-- 2. block 시작 (이체수수료)
	FIRST_BLOCK_INNER: BEGIN

		
		DECLARE p_transfer_fee INT(11);
		DECLARE p_no_more_data BOOLEAN DEFAULT FALSE;
		
-- 2. cursor (이체수수료)
		DECLARE c_fee_list CURSOR FOR

			SELECT am.brch_code, SUM(ah.comm_val)
			FROM tb_acct_master am, (
				SELECT acct_no, cc.comm_val
				FROM tb_acct_history ah, tb_comm_code cc
				WHERE ah.transfer_fee = cc.comm_code
				AND DATE(ah.acct_dt) = DATE(SUBDATE(NOW(), INTERVAL 1 DAY))
			) ah
			WHERE am.acct_no = ah.acct_no
			GROUP BY(am.brch_code);

		DECLARE CONTINUE HANDLER FOR NOT FOUND SET p_no_more_data := TRUE;

		OPEN c_fee_list;
-- 2. loop (이체수수료)
		SECOND_LOOP: LOOP
		
			FETCH FROM c_fee_list INTO p_brch_code, p_transfer_fee;
				IF p_no_more_data THEN
					CLOSE c_fee_list;
					LEAVE SECOND_LOOP;
				ELSE
					UPDATE tb_statistics
					SET transfer_fee_sum = p_transfer_fee
					WHERE brch_code = p_brch_code AND date(stat_dt) = DATE(NOW());
				END IF;
--
-- 3. block 시작 (은행몰수수료)

	SECOND_BLOCK_INNER: BEGIN

		DECLARE p_credit_fee INT(11);
		DECLARE p_no_more_data BOOLEAN DEFAULT FALSE;
		
-- 3. cursor (은행몰수수료)
		DECLARE c_mall_list CURSOR FOR
			SELECT brch_code, SUM(credit_fee)
			FROM tb_acct_master am , tb_mall_history mh
			WHERE am.id = mh.id AND DATE(mh.credit_dt) = DATE(SUBDATE(NOW(), INTERVAL 1 DAY))
			GROUP BY(brch_code);

		DECLARE CONTINUE HANDLER FOR NOT FOUND SET p_no_more_data := TRUE;

		OPEN c_mall_list;
		
-- 3. loop (은행몰수수료)
		THIRD_LOOP: LOOP
		
			FETCH FROM c_mall_list INTO p_brch_code, p_credit_fee;
				IF p_no_more_data THEN
					CLOSE c_mall_list;
					LEAVE THIRD_LOOP;
				ELSE
					UPDATE tb_statistics
					SET credit_fee_sum = p_credit_fee
					WHERE brch_code = p_brch_code AND date(stat_dt) = DATE(NOW());
				END IF;

		END LOOP THIRD_LOOP;
	END SECOND_BLOCK_INNER;
				
--
		END LOOP SECOND_LOOP;
	END FIRST_BLOCK_INNER;
		
	END LOOP FIRST_LOOP;

END