一.业务场景
每天需要将数据库中的无效url替换成新的有效url，每在浏览器打开一次url，linux服务器上的日志文件会产生一条日志。我们需要将该日志经过处理，并且和本次打开的url一起存入数据库。
二.实现逻辑
1.读取excel中的url，遍历，指定浏览器打开。
2.读取指定服务器的日志文件。
3.过滤过滤日志字符串。
4.遍历数据库的无效url，并替换，并在excel中对该条url做标记。
5.继续遍历excel中未标记的url。
6.循环1-5条，递归。
三.代码
import jxl.Cell;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.commons.io.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import java.io.*;
import java.lang.reflect.Method;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelExec;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import org.openqa.selenium.By;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.firefox.FirefoxOptions;
import org.openqa.selenium.firefox.FirefoxProfile;
import java.util.Random;
/**
* 补链接
*/
@RequestMapping("/link")
@Controller
public class LinkController {
@Autowired
private CityCountyCodeService cityCountyCodeService;
@Autowired
private RefreshOrderService refreshOrderService;
@Autowired
private HotelInfoOperationService hotelInfoOperationService;
/**
* 逻辑
* 1.链接服务器
* 2.遍历excel链接。
* 3.执行命令
* 4.读取日志文件
* 5.根据时间判断分析是该日志是否是打开该链接生成的
* 6.存库。
*
* @throws Exception
*/
@RequestMapping("/add")
public void add() throws Exception {
//链接位置
String filePath = "C:\\Users\\administrator\\Documents\\Tencent Files\\1000000\\FileRecv\\刷单链接9.28.xls";
//日志路径
String path = "/home/logs/2018-09-28/OTA/2018-09-28_53.txt";
//服务器账号密码
String passwd = "123456";
String host = "120.0.0.1";
List<Link> links = readFile(filePath);
if (links.size() > 0) {
int i = addLink(links, path, passwd, host, filePath);
if (i == 0) {
System.out.println("-----------触发条件----不再回调--------------");
return;
}
System.out.println("==========================补链接进入下一轮================");
add();//如果一次没有补录完毕 继续补录
}
System.out.println("-----------链接补录结束--------------");
}
/**
* 配置连接
*
* @param user
* @param passwd
* @param host
* @param post
* @throws Exception
*/
public static Session connect(String user, String passwd, String host, int post) throws Exception {
JSch jsch = new JSch();
Session session = jsch.getSession(user, host, post);
if (session == null) {
throw new Exception("session is null");
}
session.setPassword(passwd);
java.util.Properties config = new java.util.Properties();
//第一次登陆
config.put("StrictHostKeyChecking", "no");
session.setConfig(config);
try {
session.connect(30000);
} catch (Exception e) {
String msg = "连接远程端口无效或用户名密码错误";
System.out.println(msg);
throw new Exception(msg);
}
return session;
}
public static String[] getArr(String str) {
String[] arr = str.split(":");
return arr;
}
/**
* linux 命令找日志
*
* @param command shell 命令
* @param user 用户名
* @param passwd 密码
* @param host ip地址
* @param post 端口号
* @throws Exception
*/
public static String execCmd(String command, String user, String passwd, String host, int post) throws Exception {
System.out.println(command);
Session session = connect(user, passwd, host, post);
Channel channel = null;
try {
channel = session.openChannel("exec");
((ChannelExec) channel).setCommand(command);
channel.setInputStream(null);
((ChannelExec) channel).setErrStream(System.err);
channel.connect();
InputStream in = channel.getInputStream();
ByteArrayOutputStream result = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int length;
while ((length = in.read(buffer)) != -1) {
result.write(buffer, 0, length);
}
in.close();
String str = result.toString(StandardCharsets.UTF_8.name());
return str;
} catch (IOException e) {
e.printStackTrace();
} finally {
channel.disconnect();
session.disconnect();
}
return null;
}
/**
* 打开默认游览器的网页
*
* @param url
*/
public static Date openURL(String url) {
try {
return browse(url);
} catch (Exception e) {
}
return new Date();
}
/**
* 打开默认浏览器网页
*
* @param url
* @return
* @throws Exception
*/
private static Date browse(String url) throws Exception {
//获取操作系统的名字
String osName = System.getProperty("os.name", "");
if (osName.startsWith("Mac OS")) {
//苹果的打开方式
Class fileMgr = Class.forName("com.apple.eio.FileManager");
Method openURL = fileMgr.getDeclaredMethod("openURL", new Class[]{String.class});
openURL.invoke(null, new Object[]{url});
} else if (osName.startsWith("Windows")) {
//windows的打开方式。
Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + url);
} else {
// Unix or Linux的打开方式
String[] browsers = {"firefox", "opera", "konqueror", "epiphany", "mozilla", "netscape"};
String browser = null;
for (int count = 0; count < browsers.length && browser == null; count++)
//执行代码，在brower有值后跳出，
//这里是如果进程创建成功了，==0是表示正常结束。
if (Runtime.getRuntime().exec(new String[]{"which", browsers[count]}).waitFor() == 0)
browser = browsers[count];
if (browser == null)
throw new Exception("Could not find web browser");
else
//这个值在上面已经成功的得到了一个进程。
Runtime.getRuntime().exec(new String[]{browser, url});
}
return new Date();
}
/**
* 读取filePath路径下excel
*
* @param filePath 文件路径
* @return
*/
private static List<Link> readFile(String filePath) {
List<Link> links = new ArrayList<>();
try {
// 创建输入流，读取Excel
File is = new File(filePath);
// jxl提供的Workbook类
Workbook wb = Workbook.getWorkbook(is);
//只获取第一个sheet
Sheet sheet = wb.getSheet(0);
// sheet.getRows()返回该页的总行数
// 第一行列名称，不读取
for (int i = 1; i < sheet.getRows(); i++) {
String index = sheet.getCell(0, i).getContents();
String link = sheet.getCell(1, i).getContents();
if (sheet.getColumns() > 2) {
Cell cell = sheet.getCell(2, i);
String status = cell.getContents();
if (!"1".equals(status)) {
Link lk = new Link();
System.out.println("index:" + index + ",link:" + link);
lk.setIndex(Integer.valueOf(index));
lk.setLink(link);
links.add(lk);
}
} else {
System.out.println("index:" + index + ",link:" + link);
Link lk = new Link();
lk.setIndex(Integer.valueOf(index));
lk.setLink(link);
links.add(lk);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return links;
}
public static String formatDate(Date date, String strFmt) {
if (date == null) {
return "";
}
SimpleDateFormat dateFormat = new SimpleDateFormat(strFmt);
return dateFormat.format(date);
}
/**
* @param links excel的链接
* @param path 日志路径
* @param passwd 服务器密码
* @param host 服务器地址
* @param filePath 链接地址
* @throws Exception
*/
private int addLink(List<Link> links, String path, String passwd, String host, String filePath) throws Exception {
List<RefreshOrder> orderLogList;
synchronized (this) {
orderLogList = refreshOrderService.getAllUnUsableRefreshOrder();
}
int i = 0;
for (Link link : links) {
//开始找body
String stringLink = beginLink(link.getLink(), path, passwd, host);
if (null != stringLink) {
//将链接补入数据库
if (i < orderLogList.size()) {
RefreshOrder refreshOrder = orderLogList.get(i);
refreshOrder.setNumber(refreshOrder.getId());
refreshOrder.setPath(link.getLink());
refreshOrder.setCheck_order(stringLink);
refreshOrder.setStatus(1);
refreshOrderService.updateRefreshOrderById(refreshOrder);
i++;
writeLinkStatus(filePath, link);
} else {
RefreshOrder refreshOrder = new RefreshOrder();
refreshOrder.setNumber(refreshOrder.getId());
refreshOrder.setPath(link.getLink());
refreshOrder.setCheck_order(stringLink);
refreshOrder.setStatus(1);
refreshOrderService.insertRefreshOrder(refreshOrder);
i++;
writeLinkStatus(filePath, link);
}
}
}
return i;
}
/**
* 获得满足条件的日志
*
* @param link
* @param path
* @param passwd
* @param host
* @return
* @throws Exception
*/
private String beginLink(String link, String path, String passwd, String host) throws Exception {
Date date = openURL(link);
String time = formatDate(date, "yyyy-MM-dd HH:mm:ss");
String executeTime = time.substring(0, time.length() - 1);
String command = "grep '" + executeTime + "' " + path;
String user = "root";
int post = 22;
Thread.sleep(2000);//休眠几秒 不然找linux命令查询不到
String logString = execCmd(command, user, passwd, host, post);
if (null != logString) {
String[] logs = logString.split("#" + executeTime);
return getLink(logs, link);
}
return null;
}
/**
* 在表格中标记哪些链接是补录成功了的
*
* @param filePath
* @param link
*/
private void writeLinkStatus(String filePath, Link link) {
WritableWorkbook wwb = null;
try {
File file = new File(filePath);
Workbook wb = Workbook.getWorkbook(file);
if (!file.exists()) {
// 判断是否存在xls文件，若没有则新建，并创建一个默认表单sheet1，不要用上面的creatNewFile()
wwb = Workbook.createWorkbook(new FileOutputStream(file));
wwb.createSheet("sheet1", 0);
wwb.write();
wwb.close();
}
//为了实现覆盖写入，即可以在原有数据上叠加，需要用到以下方法实例化WritableWorkbook
wwb = Workbook.createWorkbook(file, wb);
WritableSheet sheet = wwb.getSheet(0);
if (sheet == null) {
// 若表单不存在，则新建名字为“data”的表单，参数“0”是索引，表示第一个
sheet = wwb.createSheet("data", 1);
}
// 创建Label，一个Label相当于一个单元格，参数1：列索引； 参数2：行索引； 参数3：内容。
Label status = new Label(2, link.getIndex(), "1");
sheet.addCell(status);
//调用write函数，将数据写到workbook上
wwb.write();
//BiffException WriteException IOException
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
} finally {
try {
wwb.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* body
*
* @param logs
* @param link
* @return
*/
private String getLink(String[] logs, String link) {
List<String> body = new ArrayList<>();
String startLink = "&start=";
String endLink = "&end=";
String linkStr = subString(link, startLink, endLink);
String startCity = "&city=";
String endCity = "&source=";
String city = subString(link, startCity, endCity);
if (null == city || null == linkStr) {
return null;
}
CityCountyCode cityCountyCode = new CityCountyCode();
cityCountyCode.setId(Integer.parseInt(city));
CityCountyCode cityCode = null;
try {
cityCode = cityCountyCodeService.getCityCode(cityCountyCode);
} catch (Exception e) {
return null;
}
if (null == cityCode) {
return null;
}
for (String log : logs) {
if (log.length() > 0) {
if (log.contains("ali validate order request:")) {
//截取body
String strStart = "ali validate order request: {\"body\":";
String strEnd = ",\"head\":{";
String mubiao = subString(log, strStart, strEnd);
if (null == mubiao) {
continue;
}
DomesticCheckRoomAvail domesticCheckRoomAvail = (DomesticCheckRoomAvail) JsonUtil.jsonToBean(mubiao, DomesticCheckRoomAvail.class);
String departureDate = formatDate(domesticCheckRoomAvail.getArrivalDate(), "yyyy-MM-dd");
HotelInfo hotelInfo = hotelInfoOperationService.getHotelInfoByHotelCode(domesticCheckRoomAvail.getHotelCode());
//判断日期。和该酒店的城市。
if (linkStr.equals(departureDate) && hotelInfo.getCityCode().equals(cityCode.getCityCode().toString())) {
System.out.println("找到body:" + mubiao);
body.add(mubiao);
}
}
}
}
//有且仅有一条时，才算找到
if (body.size() == 1) {
//找到
return body.get(0);
}
return null;
}
/**
* 字符串截取
*
* @param str
* @param strStart
* @param strEnd
* @return
*/
public static String subString(String str, String strStart, String strEnd) {
int strStartIndex = str.indexOf(strStart);
int strEndIndex = str.indexOf(strEnd); /* index 为负数 即表示该字符串中 没有该字符 */
if (strStartIndex < 0) {
return null;
}
if (strEndIndex < 0) {
return null;
} /* 开始截取 */
String result = str.substring(strStartIndex, strEndIndex).substring(strStart.length());
return result;
}
四、entity
@Data
public class Link {
private Integer index;
private String link;
}
五.pom.xml
<!-- https://mvnrepository.com/artifact/com.jcraft/jsch -->
<dependency>
<groupId>com.jcraft</groupId>
<artifactId>jsch</artifactId>
<version>0.1.54</version>
</dependency>
<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.seleniumhq.selenium/selenium-firefox-driver -->
<dependency>
<groupId>org.seleniumhq.selenium</groupId>
<artifactId>selenium-firefox-driver</artifactId>
<version>3.14.0</version>
</dependency>
网友评论