最近做的一些客户项目需要每天给account executive每天发数据，因自己懒的每天去发送所以写了一个小工具，实现了远程SSH连接服务器后从数据库导出数据为Excel并发送到指定邮箱。用linux做了一个定时器，每天固定时间点发送，偷个懒，下边是关键代码，其他用到的工具类都是简单使用，所以就不贴了。
1import java.sql.Connection; 2import java.sql.ResultSet; 3import java.sql.ResultSetMetaData; 4import java.sql.SQLException; 5import java.sql.Statement; 6import java.text.DateFormat; 7import java.text.ParseException; 8import java.text.SimpleDateFormat; 9 10import com.jcraft.jsch.JSch; 11import com.jcraft.jsch.Session; 12import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; 13 14import java.util.ArrayList; 15import java.util.Date; 16import java.util.HashMap; 17import java.util.List; 18import java.util.Map; 19import java.util.Random; 20import java.util.concurrent.Executors; 21import java.util.concurrent.ScheduledExecutorService; 22import java.util.concurrent.TimeUnit; 23 24import javax.mail.MessagingException; 25import javax.mail.internet.AddressException; 26 27 28public class SQLConnection { 29 private static Connection connection = null; 30 private static Session session = null; 31 private static String driverName = "com.mysql.jdbc.Driver"; 32 private static int localPort = 8740;// any free port can be used 33 private static String mailUser = "***"; 34 private static String mailPwd = "***"; 35 private static String mail = "***"; 36 private static String mailSmtp = "smtp.qq.com"; 37 private static String rootPath; 38 private static SystemConfig sysConfig; 39 40 private static void connectToServer(SSHConfig sshConfig) throws SQLException { 41 connectSSH(sshConfig); 42 connectToDataBase(sshConfig); 43 } 44 45 /** 46 * 连接SSH 47 * @param sshConfig 48 * @throws SQLException 49 */ 50 private static void connectSSH(SSHConfig sshConfig) throws SQLException { 51 if(session != null) 52 { 53 return; 54 } 55 try 56 { 57 java.util.Properties config = new java.util.Properties(); 58 JSch jsch = new JSch(); 59 session = jsch.getSession(sshConfig.getSshUserName(), sshConfig.getSshHost(), sshConfig.getSshProt()); 60 session.setPassword(sshConfig.getSshPassword()); 61 62 config.put("StrictHostKeyChecking", "no"); 63 config.put("ConnectionAttempts", "3"); 64 session.setConfig(config); 65 session.connect(); 66 67 System.out.println("SSH Connected"); 68 69 Class.forName(driverName).newInstance(); 70 71 int assinged_port = session.setPortForwardingL(localPort, sshConfig.getDbHost(), sshConfig.getDbProt()); 72 73 System.out.println("localhost:" + assinged_port + " -> " + sshConfig.getDbHost() + ":" + sshConfig.getDbProt()); 74 System.out.println("Port Forwarded"); 75 } catch (Exception e) { 76 e.printStackTrace(); 77 } 78 } 79 80 /** 81 * 通过ssh连接数据库 82 * @param sshConfig 83 * @throws SQLException 84 */ 85 private static void connectToDataBase(SSHConfig sshConfig) throws SQLException { 86 if(connection != null) 87 { 88 return; 89 } 90 String localSSHUrl = "localhost"; 91 try { 92 93 // mysql database connectivity 94 MysqlDataSource dataSource = new MysqlDataSource(); 95 dataSource.setServerName(localSSHUrl); 96 dataSource.setPortNumber(localPort); 97 dataSource.setUser(sshConfig.getDbUser()); 98 dataSource.setAllowMultiQueries(true); 99 100 dataSource.setPassword(sshConfig.getDbPassword()); 101 dataSource.setDatabaseName(sshConfig.getDbDataBaseName()); 102 103 connection = dataSource.getConnection(); 104 105 System.out.print("Connection to server successful!:" + connection + "\n\n"); 106 } catch (Exception e) { 107 e.printStackTrace(); 108 } 109 } 110 111 /** 112 * 关闭SSH连接与数据库连接 113 */ 114 private static void closeConnections() { 115 CloseDataBaseConnection(); 116 CloseSSHConnection(); 117 } 118 119 /** 120 * 关闭数据库 121 */ 122 private static void CloseDataBaseConnection() { 123 try { 124 if (connection != null && !connection.isClosed()) { 125 System.out.println("Closing Database Connection"); 126 connection.close(); 127 } 128 } catch (SQLException e) { 129 e.printStackTrace(); 130 } 131 132 } 133 134 /** 135 * 切换SSH配置 136 * 自动关闭上一个SSH连接等 137 * @param config 138 * @throws SQLException 139 */ 140 public static void switchoverConfig(SSHConfig config) throws SQLException 141 { 142 closeConnections(); 143 connectToServer(config); 144 } 145 146 /** 147 * 关闭SSH 148 */ 149 private static void CloseSSHConnection() { 150 if (session != null && session.isConnected()) { 151 System.out.println("Closing SSH Connection"); 152 session.disconnect(); 153 } 154 } 155 156 /** 157 * 查询数据结果集 158 * @param query 159 * @return 160 */ 161 public static ResultSet executeMyQuery(String query) { 162 ResultSet resultSet = null; 163 try { 164 Statement stmt = connection.createStatement(); 165 resultSet = stmt.executeQuery(query); 166 System.out.println("Database connection success"); 167 } catch (SQLException e) { 168 e.printStackTrace(); 169 } 170 171 return resultSet; 172 } 173 174 public static void sendEmail(SSHConfig config) 175 { 176 try 177 { 178 connectToServer(config); 179 String date = DateUtils.getStrYesterdayDate(); 180 181 String sql = config.getSql().replace("{startDate}", date).replace("{endDate}", date); 182 183 System.out.println(sql); 184 ResultSet rs = executeMyQuery(sql); 185 186 ResultSetMetaData rsmd = rs.getMetaData(); 187 188 int titleCount = rsmd.getColumnCount(); 189 190 String[][] titleName = new String[titleCount][2]; 191 192 for(int i = 0;i < titleCount;i++) 193 { 194 titleName[i][0] = rsmd.getColumnName(i + 1); 195 titleName[i][1] = rsmd.getColumnName(i + 1); 196 } 197 198 List<Map<String,String>> dataMapList = new ArrayList<Map<String,String>>(); 199 Map<String,String> objectMap = null; 200 201 while(rs.next()) { 202 objectMap = new HashMap<>(); 203 204 for(int i = 0;i < titleCount;i++) 205 { 206 objectMap.put(titleName[i][0], rs.getString(i + 1)); 207 } 208 209 dataMapList.add(objectMap); 210 } 211 212 ExportExcel excel = null; 213 214 String title = config.getTitle() + DateUtils.getStrDate(); 215 String content = title + "数据 ,发送时间 ：" + DateUtils.getStrDateTime(); 216 217 excel = new ExportExcel("Sheet1", titleName, dataMapList); 218 String filePath = rootPath + title + ".xls"; 219 220 excel.save(rootPath, title + ".xls"); 221 222 MailUtil.send(config.getToEmail(), mail, title, content, mailSmtp, mailUser, mailPwd,"自动发送", filePath); 223 System.out.println("source send Email!"); 224 } catch (Exception s) { 225 s.printStackTrace(); 226 } 227 } 228 229 /** 230 * 获取指定时间对应的毫秒数 231 * @param time "HH:mm:ss" 232 * @return 233 */ 234 private static long getTimeMillis(String time) { 235 try { 236 DateFormat dateFormat = new SimpleDateFormat("yy-MM-dd HH:mm:ss"); 237 DateFormat dayFormat = new SimpleDateFormat("yy-MM-dd"); 238 Date curDate = (Date) dateFormat.parse(dayFormat.format(new Date()) + " " + time); 239 return curDate.getTime(); 240 } catch (ParseException e) { 241 e.printStackTrace(); 242 } 243 return 0; 244 } 245 246 public static void main(String[] args) { 247 248 sysConfig = SystemConfig.instants(); 249 250 rootPath = sysConfig.getValue("rootPath"); 251 252 SSHConfig config = new SSHConfig(); 253 254 config.setSshHost(sysConfig.getValue("sshHost")); 255 config.setSshProt(sysConfig.getIntValue("sshProt")); 256 config.setSshUserName(sysConfig.getValue("sshUserName")); 257 config.setSshPassword(sysConfig.getValue("sshPassword")); 258 259 config.setDbHost(sysConfig.getValue("dbHost")); 260 config.setDbProt(sysConfig.getIntValue("dbProt")); 261 config.setDbUser(sysConfig.getValue("dbUser")); 262 config.setDbPassword(sysConfig.getValue("dbPassword")); 263 config.setDbDataBaseName(sysConfig.getValue("dbDataBaseName")); 264 265 config.setTitle(sysConfig.getValue("title")); 266 config.setSql(sysConfig.getValue("sql")); 267 config.setToEmail(sysConfig.getValue("toEmail")); 268 269 sendEmail(config); 270 /* 271 Runnable runnable = new Runnable() { 272 public void run() { 273 try { 274 Thread.sleep(50); 275 } catch (InterruptedException e) { 276 e.printStackTrace(); 277 } 278 System.out.println("This is a echo server. The current time is " + System.currentTimeMillis() + "."); 279 280 } 281 }; 282 283 long oneDay = 24 * 60 * 60 * 1000; 284 long initDelay = getTimeMillis("12:29:00") - System.currentTimeMillis(); 285 286 System.out.println(initDelay); 287 System.out.println(oneDay); 288 289 initDelay = initDelay > 0 ? initDelay : oneDay + initDelay; 290 291 292 ScheduledExecutorService service = Executors.newSingleThreadScheduledExecutor(); 293 // 第二个参数为首次执行的延时时间，第三个参数为定时执行的间隔时间 294 // service.scheduleAtFixedRate(runnable, 1, 1, TimeUnit.MINUTES); 295 296 service.scheduleAtFixedRate( 297 runnable, 298 initDelay, 299 oneDay, 300 TimeUnit.MILLISECONDS); 301 */ 302 } 303 304}