利用shell脚本访问远程数据库，实现百万级数据导出csv文件
背景：工作中报表导出大多用的poi方式进行导出excel文件，数据量在几千，几万的不用考虑性能问题，但是部分报表中数据量需要导出百万数据，导出操作为了有好体验，避免页面假死且可进行后续操作，导出操作均是跳转新的页面，而项目中服务的页面ngnix响应时间是30秒，而即使使用poi多线程导出，100万数据测试了下大概也需要90秒左右，完全超过了页面响应时间导致页面错误，不可能为了这一个页面去调整ngnix参数，因此想到了用脚本导出。
思路：1.java程序创建线程，通过RUmtime执行随项目打包到服务器上的脚本
2.服务器脚本访问远程Mysql数据库，实现数据查询并导出追加到指定路径下的文件
3.Java程序访问指定文件夹，用输出流下载该文件并上传到浏览器，实现下。
4.避免文件堆积在下载完成之后，进行删除服务器文件操作。
技术知识点：
- java程序调用脚本，并对脚本执行结果的获取打印（执行脚本的命令需要使用数据编写，中间不能带有空格，查询sql动态生成，直接作为一个整体的参数进行传输）；
- 访问远程mysql数据库实现数据导出的Shell脚本的编写（服务器需要安装mysql客户端，才能支持Mysql命令，切注意数据库文件的输出编码格式，输出文件名不能带有中文）
- 下载功能实现，输出流以及http对象response的运用
具体实现过程：
- java部分
- 1)生成sql以及调用shell脚本
- 2)实现下载
/**
* @param bo
* @param response
* @throws Exception
* 脚本导出csv文件，拼接sql
* 2018年12月20日
* @auther:liming
*/
public void importExcel(ReportBo bo,HttpServletResponse response) throws Exception {
//1.拼接导出查询数据sql
StringBuilder sb = new StringBuilder();
sb.append("select v.prov_desc as provName, ifnull(v.area_name,'') as cityName,");
sb.append("ifnull(v.yxcp,'') as marketProduct");
sb.append(" from clyx_app_baobiao.tc_user_operater_info v");
sb.append(" where 1 = 1 ");
if(bo.getProvId()==null){
String [] provIds = bo.getProvIds();
StringBuilder ids = new StringBuilder();
for(int i=0;i<provIds.length;i++){
ids.append(provIds[i]+",");
}
ids.toString().substring(0, ids.length()-1);
sb.append("and v.prov_id in ("+ids+")");
}
--------部分sql生成过程已省略---
if(bo.getCityName()!=null&&!bo.getCityName().equals("")) {
sb.append("and v.area_name like CONCAT('%','"+bo.getCityName()+"','%') ");
}
if(bo.getActivityName()!=null&&!bo.getActivityName().equals("")){
sb.append("and v.activity_name like CONCAT('%','"+bo.getActivityName()+"','%')");
}
if(bo.getOptionState().equals("1")){
sb.append("and v.is_operate = '操作'");
if(bo.getStartDate()!=null&&!bo.getStartDate().equals("")){
sb.append("and v.contact_date >= str_to_date('"+bo.getStartDate()+"','%Y-%m-%d %H:%i:%s') ");
}
if(bo.getEndDate()!=null&&!bo.getEndDate().equals("")){
sb.append(" and v.contact_date <= str_to_date(date_add(DATE_FORMAT('"+bo.getEndDate()+"','%Y-%m-%d %H:%i:%s'),INTERVAL '23:59:59' hour_second),'%Y-%m-%d %H:%i:%s')");
}
}
if(bo.getOptionState().equals("2")){
sb.append("and v.is_operate != '操作'");
if(bo.getStartDate()!=null&&!bo.getStartDate().equals("")){
sb.append("and v.date_id >= '"+ bo.getStartDate()+"'");
}
if(bo.getEndDate()!=null&&!bo.getEndDate().equals("")){
sb.append(" and v.date_id <= '"+bo.getEndDate()+"'");
}
}
if(bo.getOptionState().equals("3")){
if(bo.getStartDate()!=null&&!bo.getStartDate().equals("")){
sb.append("and v.date_id >= '"+ bo.getStartDate()+"'");
}
if(bo.getEndDate()!=null&&!bo.getEndDate().equals("")){
sb.append(" and v.date_id <= '"+bo.getEndDate()+"'");
}
}
sb.append(" order by v.pop_time desc,v.date_id desc");
final String sql = sb.toString();
System.out.println("拼接sql操作完成,sql语句为："+sql);
//生成导出文件名
String now = new SimpleDateFormat("YYYYMMDDHHmmss").format(new Date());
final String fileName = "tcOption" + now+".csv" ; //文件名前缀(导出文件名+当前时间)tcOption2018122114410
String filePath = ClassUtils.getDefaultClassLoader().getResource("").getPath()+"files/"; //项目的根路径，文件路径
//2.调用脚本导出文件
shellExportExcel(sql, filePath+fileName);
//3.下载文件需要下载的路径+文件名
String fullFilePath = filePath+fileName;
System.out.println("要查找的文件是" + fullFilePath);
File file = new File(fullFilePath);
long commandTime = new Date().getTime();
long nowTime = new Date().getTime();
long timeout = 600*1000;
while(true){
if(file.exists()){
System.out.println("找到文件了，开始下载文件");
break;
}else{
if (nowTime - commandTime > timeout) {
System.out.println("导出时间超出10分钟，导出失败");
throw new Exception("导出时间超出10分钟，导出失败");
} else {
nowTime = new Date().getTime();
System.out.println("没有找到文件，30秒后继续查找文件");
Thread.sleep(30*1000);
}
}
}
download(fileName, filePath,response);
}
//调用脚本
public void shellExportExcel(String sql,String filePathAndName) {
Process process = null;
String shellPath = "/usr/local/tomcat/webapps/clyxstatcount/WEB-INF/classes/TcReport.sh";
//脚本目录以及名称 两个参数：sql 导出文件名
System.out.println("------导出文件名"+filePathAndName);
System.out.println("------脚本所在目录为"+shellPath);
// String[] command = {"/bin/bash",shellPath,filePathAndName,"\""+sql+"\""}; //加双引号避免空格问题
String[] command = {"/bin/bash",ClassUtils.getDefaultClassLoader().getResource("").getPath()+"test.sh"};
long timeout = 600 * 1000;
try {
System.out.println("开始执行shell命令");
process = Runtime.getRuntime().exec(command);
CommandWaitForThread commandThread = new CommandWaitForThread(process);
commandThread.run();
long commandTime = new Date().getTime();
long nowTime = new Date().getTime();
boolean timeoutFlag = false;
while (!commandThread.isFinish()) { //判断脚本是否执行完成
if (nowTime - commandTime > timeout) {
timeoutFlag = true;
break;
} else {
Thread.sleep(1000);
nowTime = new Date().getTime();
}
}
if (timeoutFlag) {
// 命令超时
System.out.println("正式执行命令：" + command + "超时");
} else if (commandThread.getExitValue() == 0) {
System.out.println("正式执行命令：" + command + "完毕");
}
} catch (IOException | InterruptedException e) {
e.printStackTrace();
} finally {
if (process != null) {
process.destroy();
System.out.println("process destroy");
}
}
}
//下载
public void download(String downloadFileName, String filePath,HttpServletResponse response) {
// 定义输出流
OutputStream outputStream = null;
InputStream inputStream = null;
try {
// 重置输出对象
response.reset();
response.setContentType("text/plain");
// 设置输出头信息
response.setHeader("Content-disposition", "attachment;filename=" + downloadFileName);
response.setHeader("Pragma", "No-cache");
// 设置输出流
outputStream = response.getOutputStream();
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
// 读取导出文件
String realPath = request.getServletContext().getRealPath("/");
//inputStream = new FileInputStream(new File(new StringBuilder(realPath).append("/files/export_files/").append(downloadFileName).toString()));
inputStream = new FileInputStream(new File(new StringBuilder(filePath).append(downloadFileName).toString()));
byte[] buff = new byte[1024];
int readed = -1;
while ((readed = inputStream.read(buff)) > 0) {
outputStream.write(buff, 0, readed);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != outputStream) {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
}
}
if (null != inputStream) {
try {
inputStream.close();
} catch (IOException e) {
//e.printStackTrace();
}
}
//不管报错没报错，都执行删除文件操作
File file = new File(filePath+downloadFileName);
if(file.exists()){
log.info(file+"文件存在开始删除");
if(file.delete()){
log.info(file+"文件删除成功");
}else{
log.info(file+"文件删除失败");
}
}else{
log.info(file+"文件不存在，不执行删除操作");
}
}
}脚本的内容如下：
#!/bin/bash
echo 'export start'
mysql --default-character-set=utf8 -h数据库地址 -P3306 -u用户名 -p'密码' 数据库名 -e"${@:2}" > $1
echo 'export end'
exit脚本注意点：
- java程序调用脚本的时候，sql作为参数当中会存在空格，脚本中${@:2}，是将其他所有的参数都当作为第二个参数
- $1为参数1，文件名称
- 编写脚本时，在linux环境下编写，如果在windows环境下编写会存在转义问题，例如换行符问题，查看脚本属于哪个环境
- 脚本存放路径为项目的根目录
- vi 脚本名称
:set ff （查看脚本环境） dos 问windows环境，unix为Linux环境
- 补充：
dos2unix ---> 是把windows格式的文本转换成linux下格式
unix2dos --->是把linux下文本格式转换成windows下的文本格式
5.脚本中涉及路径问题，直接写绝对路径，避免因为路径问题文件没找到
6.服务部署的时候没有安装mysql在执行mysql脚本会报错，因此需要安装mysql客户端，由于我的服务是在容器中部署，在构建镜像文件dockerfiles需要添加mysql
经过测试脚本导出操作100万数据大概10秒左右生成文件，下载依据网速不同时间不等，但10秒这个时间完全在页面响应之内。
版权声明：本文为博主原创文章，转载请注明本页地址：https://blog.csdn.net/l1994m/article/details/86186851。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
