oracle数据库备份+锁表+操作数据库

package cn.com.threeInOneRoad.task;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import cn.com.threeInOneRoad.entity.TSysLog;
import cn.com.threeInOneRoad.service.TSysLogService;
import cn.com.threeInOneRoad.util.CommonKeys;
import cn.com.threeInOneRoad.util.DateUtil;
import cn.com.threeInOneRoad.util.Tools;

/**
* 备份oracle数据库 7天备份一次实时数据，清空一次实时数据
*
* @author x_luwl
*
*/
@Configuration
@EnableScheduling
@Component
public class OracleDatabaseBackup {

@Value("${spring.datasource.username}")
private String userName;

@Value("${spring.datasource.password}")
private String password;

@Value("${SID}")
private String SID;

@Value("${savePath}")
private String savePath;

@Value("${tablesName}")
private String tablesName;

@Value("${oracleLog}")
private String oracleLog;

@Value("${owner}")
private String owner;

@Value("${spring.datasource.url}")
private String strUrl;

@Value("${spring.datasource.driver-class-name}")
private String driver;

@Resource(name = "tSysLogService")
private TSysLogService tSysLogService;

private final Logger logger = LoggerFactory.getLogger(OracleDatabaseBackup.class);


//每七天的凌晨1点进行备份，删除实时表
@Async
@Scheduled(cron = "0 0 1 1/7 * ?")
public void exportDatabaseTool() {
Process process = null;
String str = "exp " + userName + "/" + password + "@" + SID + " file=" + savePath + "/oracle_"
+ DateUtil.getCurrentDate() + ".dmp log=" + oracleLog + DateUtil.getCurrentDate() + ".log tables=("
+ tablesName + ")";
try {
File saveFile = new File(savePath);
if (!saveFile.exists()) {// 如果目录不存在
saveFile.mkdirs();// 创建文件夹
}
long startTime = System.currentTimeMillis();
// 执行命令
process = Runtime.getRuntime().exec(str);
int waitStatus = process.waitFor();
long endTime = System.currentTimeMillis();
float excTime = (float) (endTime - startTime) / 1000;
// 备份成功
if (waitStatus == 0) {
logger.debug("数据库备份完成,当前时间为:" + DateUtil.getCurrentTime(), "共消耗时间为:" + excTime + "s");
// 清除数据
String tableNames[] = tablesName.split(",");
for (String string : tableNames) {
deleteDataInf(string);
}
} else {
// 备份失败,不删除表
logger.debug("数据库备份失败,当前时间为:" + DateUtil.getCurrentTime());
}
} catch (IOException | SQLException | InterruptedException e) {
e.printStackTrace();
setErrorToLog(e, "OracleDatabaseBackup", "exportDatabaseTool", "备份数据库异常");
logger.debug("数据库备份异常:" + e.getMessage());
} finally {
if (process != null) {
process.destroy();
logger.debug("销毁进程，当前时间为:" + DateUtil.getCurrentTime());
}
}
}

// 传入表名
//锁表+删除数据
private void deleteDataInf(String tableName) throws SQLException {
// 数据库连接对象象
Connection connect = null;
Statement stmt = null;
try {
connect = getConnection();
// 设置手动提交事务
connect.setAutoCommit(false);
stmt = connect.createStatement();
// 锁表操作
stmt.addBatch("lock table " + tableName + " in exclusive mode");
// 执行锁表命令
stmt.executeBatch();
// 执行数据库操作
stmt.executeQuery("truncate table " + tableName);
// 提交事务，并且解开表
connect.commit();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
connect.rollback();
} finally {
// 释放当前数据资源
stmt.close();
if (closeConnection(connect)) {
logger.debug("关闭数据库连接对象成功,当前时间为:" + DateUtil.getCurrentTime() + "当前操作表为:" + tableName);
} else {
logger.debug("关闭数据库连接对象失败,当前时间为:" + DateUtil.getCurrentTime() + "当前操作表为:" + tableName);
}
}
}

// 创建数据库连接
private Connection getConnection() throws ClassNotFoundException, SQLException {
logger.debug("创建数据库连接,当前时间为:" + DateUtil.getCurrentTime());
// 加载驱动
Class.forName(driver);
// 获取连接
return DriverManager.getConnection(strUrl, userName, password);
}

// 关闭数据库连接
private boolean closeConnection(Connection conn) throws SQLException {
if (conn != null) {
conn.close();
}
return conn.isClosed();
}

/**
*
* @param e
* 异常
* @param function
* 类名
* @param method
* 方法名
* @param memo
* 描述
*/
private void setErrorToLog(Exception e, String function, String method, String memo) {
TSysLog tlog = new TSysLog();
tlog.setInsTime(Tools.getTimeStamp(DateUtil.getCurrentTime()));
tlog.setIpAddr("");
tlog.setIsdelete((short) 0);
tlog.setOpTime(Tools.getTimeStamp(DateUtil.getCurrentTime()));
tlog.setOpType(CommonKeys.TSYSLOG.TYPE_HOUTAIRENWU);
tlog.setOpFunc(function);
tlog.setOpResult(CommonKeys.TSYSLOG.RESULT_FAIL);
tlog.setOpAction(method);
tlog.setErrMsg(getExceptionDetail(e));
tlog.setMemo("定时任务报错:" + memo + "---------------" + e.getMessage());
tlog.setOpUserid("");
tSysLogService.save(tlog);
}

private String getExceptionDetail(Exception e) {
StringBuffer stringBuffer = new StringBuffer(e.toString() + " ");
StackTraceElement[] messages = e.getStackTrace();
int length = messages.length;
for (int i = 0; i < length; i++) {
stringBuffer.append(" " + messages[i].toString() + " ");
}
return stringBuffer.substring(0, 250).toString();
}

}