最近做的一些客户项目需要每天给account executive每天发数据，因自己懒的每天去发送所以写了一个小工具，实现了远程SSH连接服务器后从数据库导出数据为Excel并发送到指定邮箱。用linux做了一个定时器，每天固定时间点发送，偷个懒，下边是关键代码，其他用到的工具类都是简单使用，所以就不贴了。
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
import javax.mail.MessagingException;
import javax.mail.internet.AddressException;
public class SQLConnection {
private static Connection connection = null;
private static Session session = null;
private static String driverName = "com.mysql.jdbc.Driver";
private static int localPort = 8740;// any free port can be used
private static String mailUser = "***";
private static String mailPwd = "***";
private static String mail = "***";
private static String mailSmtp = "smtp.qq.com";
private static String rootPath;
private static SystemConfig sysConfig;
private static void connectToServer(SSHConfig sshConfig) throws SQLException {
connectSSH(sshConfig);
connectToDataBase(sshConfig);
}
/**
* 连接SSH
* @param sshConfig
* @throws SQLException
*/
private static void connectSSH(SSHConfig sshConfig) throws SQLException {
if(session != null)
{
return;
}
try
{
java.util.Properties config = new java.util.Properties();
JSch jsch = new JSch();
session = jsch.getSession(sshConfig.getSshUserName(), sshConfig.getSshHost(), sshConfig.getSshProt());
session.setPassword(sshConfig.getSshPassword());
config.put("StrictHostKeyChecking", "no");
config.put("ConnectionAttempts", "3");
session.setConfig(config);
session.connect();
System.out.println("SSH Connected");
Class.forName(driverName).newInstance();
int assinged_port = session.setPortForwardingL(localPort, sshConfig.getDbHost(), sshConfig.getDbProt());
System.out.println("localhost:" + assinged_port + " -> " + sshConfig.getDbHost() + ":" + sshConfig.getDbProt());
System.out.println("Port Forwarded");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 通过ssh连接数据库
* @param sshConfig
* @throws SQLException
*/
private static void connectToDataBase(SSHConfig sshConfig) throws SQLException {
if(connection != null)
{
return;
}
String localSSHUrl = "localhost";
try {
// mysql database connectivity
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setServerName(localSSHUrl);
dataSource.setPortNumber(localPort);
dataSource.setUser(sshConfig.getDbUser());
dataSource.setAllowMultiQueries(true);
dataSource.setPassword(sshConfig.getDbPassword());
dataSource.setDatabaseName(sshConfig.getDbDataBaseName());
connection = dataSource.getConnection();
System.out.print("Connection to server successful!:" + connection + "\n\n");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 关闭SSH连接与数据库连接
*/
private static void closeConnections() {
CloseDataBaseConnection();
CloseSSHConnection();
}
/**
* 关闭数据库
*/
private static void CloseDataBaseConnection() {
try {
if (connection != null && !connection.isClosed()) {
System.out.println("Closing Database Connection");
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 切换SSH配置
* 自动关闭上一个SSH连接等
* @param config
* @throws SQLException
*/
public static void switchoverConfig(SSHConfig config) throws SQLException
{
closeConnections();
connectToServer(config);
}
/**
* 关闭SSH
*/
private static void CloseSSHConnection() {
if (session != null && session.isConnected()) {
System.out.println("Closing SSH Connection");
session.disconnect();
}
}
/**
* 查询数据结果集
* @param query
* @return
*/
public static ResultSet executeMyQuery(String query) {
ResultSet resultSet = null;
try {
Statement stmt = connection.createStatement();
resultSet = stmt.executeQuery(query);
System.out.println("Database connection success");
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
public static void sendEmail(SSHConfig config)
{
try
{
connectToServer(config);
String date = DateUtils.getStrYesterdayDate();
String sql = config.getSql().replace("{startDate}", date).replace("{endDate}", date);
System.out.println(sql);
ResultSet rs = executeMyQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int titleCount = rsmd.getColumnCount();
String[][] titleName = new String[titleCount][2];
for(int i = 0;i < titleCount;i++)
{
titleName[i][0] = rsmd.getColumnName(i + 1);
titleName[i][1] = rsmd.getColumnName(i + 1);
}
List<Map<String,String>> dataMapList = new ArrayList<Map<String,String>>();
Map<String,String> objectMap = null;
while(rs.next()) {
objectMap = new HashMap<>();
for(int i = 0;i < titleCount;i++)
{
objectMap.put(titleName[i][0], rs.getString(i + 1));
}
dataMapList.add(objectMap);
}
ExportExcel excel = null;
String title = config.getTitle() + DateUtils.getStrDate();
String content = title + "数据 ,发送时间 ：" + DateUtils.getStrDateTime();
excel = new ExportExcel("Sheet1", titleName, dataMapList);
String filePath = rootPath + title + ".xls";
excel.save(rootPath, title + ".xls");
MailUtil.send(config.getToEmail(), mail, title, content, mailSmtp, mailUser, mailPwd,"自动发送", filePath);
System.out.println("source send Email!");
} catch (Exception s) {
s.printStackTrace();
}
}
/**
* 获取指定时间对应的毫秒数
* @param time "HH:mm:ss"
* @return
*/
private static long getTimeMillis(String time) {
try {
DateFormat dateFormat = new SimpleDateFormat("yy-MM-dd HH:mm:ss");
DateFormat dayFormat = new SimpleDateFormat("yy-MM-dd");
Date curDate = (Date) dateFormat.parse(dayFormat.format(new Date()) + " " + time);
return curDate.getTime();
} catch (ParseException e) {
e.printStackTrace();
}
return 0;
}
public static void main(String[] args) {
sysConfig = SystemConfig.instants();
rootPath = sysConfig.getValue("rootPath");
SSHConfig config = new SSHConfig();
config.setSshHost(sysConfig.getValue("sshHost"));
config.setSshProt(sysConfig.getIntValue("sshProt"));
config.setSshUserName(sysConfig.getValue("sshUserName"));
config.setSshPassword(sysConfig.getValue("sshPassword"));
config.setDbHost(sysConfig.getValue("dbHost"));
config.setDbProt(sysConfig.getIntValue("dbProt"));
config.setDbUser(sysConfig.getValue("dbUser"));
config.setDbPassword(sysConfig.getValue("dbPassword"));
config.setDbDataBaseName(sysConfig.getValue("dbDataBaseName"));
config.setTitle(sysConfig.getValue("title"));
config.setSql(sysConfig.getValue("sql"));
config.setToEmail(sysConfig.getValue("toEmail"));
sendEmail(config);
/*
Runnable runnable = new Runnable() {
public void run() {
try {
Thread.sleep(50);
} catch (InterruptedException e) {
e.printStackTrace();
}
System.out.println("This is a echo server. The current time is " + System.currentTimeMillis() + ".");
}
};
long oneDay = 24 * 60 * 60 * 1000;
long initDelay = getTimeMillis("12:29:00") - System.currentTimeMillis();
System.out.println(initDelay);
System.out.println(oneDay);
initDelay = initDelay > 0 ? initDelay : oneDay + initDelay;
ScheduledExecutorService service = Executors.newSingleThreadScheduledExecutor();
// 第二个参数为首次执行的延时时间，第三个参数为定时执行的间隔时间
// service.scheduleAtFixedRate(runnable, 1, 1, TimeUnit.MINUTES);
service.scheduleAtFixedRate(
runnable,
initDelay,
oneDay,
TimeUnit.MILLISECONDS);
*/
}
}