반응형
spring batch
spring boot 였다면 더 편했겠지만 legacy에서 작업해서 정보가 많이 없었다.
하지만 @Schedule을 이용한 방법이 간단하게 많은 수정이 필요하지 않아 사용해보았다.
- context관련 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:task="http://www.springframework.org/schema/task"// task를 위한 추가 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.0.xsd">// task를 위한 추가 <!-- context:component-scan base-package="egovframework" --> <context:component-scan base-package="ncdc.cancermonitor"> <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" /> </context:component-scan> // 어노테이션 스캔동작 <task:annotation-driven /> // 실제 배치동작을 수행할 파일 <bean id="jobScheduler" class="ncdc.cancermonitor.util.BatchRunner" /> <bean id="messageSource" class="org.springframework.context.support.ReloadableResourceBundleMessageSource"> <property name="basenames"> <list> <value>classpath:/egovframework/message/message-common</value> </list> </property> <property name="defaultEncoding"> <value>UTF-8</value> </property> <property name="cacheSeconds"> <value>60</value> </property> </bean> <bean id="egovMessageSource" class="ncdc.cancermonitor.cmmn.EgovMessageSource"> <property name="reloadableResourceBundleMessageSource"> <ref bean="messageSource" /> </property> </bean> <bean id="messageSourceAccessor" class="org.springframework.context.support.MessageSourceAccessor"> <constructor-arg ref="messageSource" /> </bean> <bean id="message" class="ncdc.cancermonitor.util.MessageUtil"> <property name="messageSourceAccessor" ref="messageSourceAccessor" /> </bean> <bean id="leaveaTrace" class="egovframework.rte.fdl.cmmn.trace.LeaveaTrace"> <property name="traceHandlerServices"> <list> <ref bean="traceHandlerService" /> </list> </property> </bean> <bean id="traceHandlerService" class="egovframework.rte.fdl.cmmn.trace.manager.DefaultTraceHandleManager"> <property name="reqExpMatcher"> <ref bean="antPathMater" /> </property> <property name="patterns"> <list> <value>*</value> </list> </property> <property name="handlers"> <list> <ref bean="defaultTraceHandler" /> </list> </property> </bean> <bean id="antPathMater" class="org.springframework.util.AntPathMatcher" /> <bean id="defaultTraceHandler" class="egovframework.rte.fdl.cmmn.trace.handler.DefaultTraceHandler" /> <!-- 파일업로드 --> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <property name="maxUploadSize" value="104857600" /> <property name="maxInMemorySize" value="100000000" /> <property name="defaultEncoding" value="UTF-8" /> </bean> <!-- 파일 업로드 경로 --> <bean id="uploadPath" class="java.lang.String"> <constructor-arg value="C:\dev\work2\cancerMonitor\src\main\webapp\upload\cancerData" /> </bean> <!-- 파일 다운로드를 위한 뷰리졸브 --> <bean class="org.springframework.web.servlet.view.BeanNameViewResolver"> <property name="order" value="0" /> </bean> <bean id="download" class="ncdc.cancermonitor.util.DownloadView" /> <bean id="excelDownloadView" class="ncdc.cancermonitor.web.admin.service.ExcelDownloadView" /> </beans> - 배치 동작할 파일 작성 예시로 스케쥴을 1초단위로 실행하면서 log를 찍는 것을 작성
package ncdc.cancermonitor.util; import java.text.SimpleDateFormat; import java.util.Calendar; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.scheduling.annotation.Scheduled; public class BatchRunner { private static final Logger logger = LoggerFactory.getLogger(BatchRunner.class); @Scheduled(cron="0/1 * * * * *") // 1초 주기로 실행하라는 의미 public void jobMethod() throws Exception { Calendar calendar = Calendar.getInstance(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); logger.info("스케줄 실행 : " + dateFormat.format(calendar.getTime())); } }
참고 > 크론식 변환 사이트
http://www.cronmaker.com/;jsessionid=node01btdr6qlwpczqvdyg8rtwtnom831207.node0?0
CronMaker
www.cronmaker.com
해당 코드를 수정하여 sql파일을 실행시키게 만들면 구상한 기능이 구현된다
더보기
POM.xml에 의존성 추가
...
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>3.2.0</version>
</dependency>
...
SQL파일로 처리
package ncdc.cancermonitor.util;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Scheduled;
public class BatchRunner {
private static final Logger logger = LoggerFactory.getLogger(BatchRunner.class);
//@Scheduled(cron="0/1 * * * * *") // 1초 주기로 실행하라는 의미
//@Scheduled(cron="0 59 23 1/1 * *")
@Scheduled(cron="0 0/1 * 1/1 * *")
public void jobMethod() throws Exception {
Calendar calendar = Calendar.getInstance();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
logger.info("스케줄 실행 : " + dateFormat.format(calendar.getTime()));
// Connection전에 사용할 JDBC드라이버를 지정해 줘야함
Class.forName("org.mariadb.jdbc.Driver");
// 경로는 OS상의 실제 파일 경로로 입력해야함
executeSqlFile("C:/Users/mobil/Documents/script.sql");
}
private void executeSqlFile(String filePath) throws Exception {
try (
Connection connection = DriverManager.getConnection("jdbc:mariadb://[hostIP]/[DB명]", "[DB계정]", "[계정암호]");
Statement statement = connection.createStatement();
BufferedReader reader = new BufferedReader(new FileReader(filePath))) {
String line;
StringBuilder sqlScript = new StringBuilder();
while ((line = reader.readLine()) != null) {
sqlScript.append(line).append(" ");
}
String[] sqlCommands = sqlScript.toString().split(";");
for (String sqlCommand : sqlCommands) {
if (!sqlCommand.trim().isEmpty()) {
statement.execute(sqlCommand);
}
}
logger.info("SQL file executed successfully.");
} catch (Exception e) {
logger.error("Error executing SQL file: " + e.getMessage(), e);
throw e;
}
}
}
JAVA에서 처리
package ncdc.cancermonitor.util;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class BatchRunner {
private static final Logger logger = LoggerFactory.getLogger(BatchRunner.class);
@Autowired
private JdbcTemplate jdbcTemplate;
//@Scheduled(cron="0/1 * * * * *") // 1초 주기로 실행하라는 의미
//@Scheduled(cron="0 59 23 1/1 * *")
@Scheduled(cron="0 0/5 * 1/1 * *")
public void jobMethod() throws Exception {
Calendar calendar = Calendar.getInstance();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
logger.info("스케줄 실행 : " + dateFormat.format(calendar.getTime()));
String sourceUrl = "jdbc:mysql://192.168.0.12/cancerMonitor";
String sourceUser = "nccmonitor";
String sourcePassword = "surveillance123!";
String targetUrl = "jdbc:mysql://localhost/openapi";
String targetUser = "root";
String targetPassword = "rhanghzM21!";
List<String> tablesToCopy = List.of("TableName1", "TableName2", "TableName3");
try {
// Source Database Connection
try (Connection sourceConnection = DriverManager.getConnection(sourceJdbcUrl, sourceUsername, sourcePassword)) {
// Target Database Connection
try (Connection targetConnection = DriverManager.getConnection(targetJdbcUrl, targetUsername, targetPassword)) {
for (String tableName : tablesToCopy) {
copyTable(sourceConnection, targetConnection, tableName);
}
}
}
System.out.println("Tables copied successfully.");
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void copyTable(Connection sourceConnection, Connection targetConnection, String tableName)
throws SQLException {
// Select data from source table
String selectQuery = "SELECT * FROM " + tableName;
try (PreparedStatement selectStatement = sourceConnection.prepareStatement(selectQuery);
ResultSet resultSet = selectStatement.executeQuery()) {
// Insert data into target table
String insertQuery = "INSERT INTO " + tableName + " VALUES (DATA_SEQ, DATA_CODE, DATA_CODE_FRONT, DATA_CODE_MIDDLE, DATA_CODE_BACK, CANCER_CODE, SIDO_KOR_NAME, SIGUNGU_KOR_NAME, DIVISION, CANCER_TYPE, PEOPLE, CONFIRMED, FREQUENCY, RATE, OLD_RATE, YEAR, UPDATE_DATE)";
try (PreparedStatement insertStatement = targetConnection.prepareStatement(insertQuery)) {
while (resultSet.next()) {
// Set values for each column
// Modify this part to match the actual columns in your tables
insertStatement.setInt(1, resultSet.getInt("column1"));
insertStatement.setString(2, resultSet.getString("column2"));
// Add more columns as needed...
// Execute the insert query
insertStatement.executeUpdate();
}
}
}
}
}
Microsoft 무선 터치패드 키보드
F5가 안눌리는 이슈
fn이 잠겨 있어서 그렇다. fn키를 누르면서 CapsLock을 눌리면 활성화 된다
but CapsLock을 먼저 누르면 안됨.... 그리고 재부팅 되면 초기화 되어 다시 키를 눌러줘야함
반응형