能看各种网站的浏览器,wordpress文章名称背景,wordpress做电商网站,音乐网站后台管理模板需求
由于生产数据库不能直接连接下载数据#xff0c;所以需要在监控系统上做一个根据sql动态查询并下载数据的功能。
实现思路
写一个接口#xff0c;传入需要查询的数据库信息和sql#xff0c;将查询的接口导出到Excel中
实现细节
入参
Data
public class ExportDat…需求
由于生产数据库不能直接连接下载数据所以需要在监控系统上做一个根据sql动态查询并下载数据的功能。
实现思路
写一个接口传入需要查询的数据库信息和sql将查询的接口导出到Excel中
实现细节
入参
Data
public class ExportDataReq {/*** jdbc:mysql://localhost:3306/your_database;*/NotEmptyString url;NotEmptyString user;NotEmptyString password;NotEmptyString sql;NotEmptyString[] titles;
}
controller
Slf4j
Controller
RequestMapping(/export/)
public class ExportController {AutowiredExportService exportService;ApiOperation(value 下载数据信息, notes 日期格式yyyy-MM-dd, httpMethod POST)PostMapping(value /exportData)ResponseBodypublic BaseResponse exportData(RequestBody Valid ExportDataReq req,HttpServletResponse response) {BaseResponse baseResponse exportService.exportData(req, response);return baseResponse;}
}
接口
public interface ExportService {BaseResponse exportData(ExportDataReq req, HttpServletResponse response);
}
服务 Service
public class ExportServiceImpl implements ExportService {Overridepublic BaseResponse exportData(ExportDataReq req, HttpServletResponse response) {try (Connection conn DriverManager.getConnection(req.getUrl(), req.getUser(), req.getPassword());Statement stmt conn.createStatement();ResultSet rs stmt.executeQuery(req.getSql())) {//创建wbHSSFWorkbook wb new HSSFWorkbook();//创建sheetHSSFSheet sheet wb.createSheet(data);//添加标题addTitle(sheet,req.getTitles());//添加内容addRow(sheet,rs);//导出writeToResponse(response,wb,datadateNow());return ResponseUtils.success();} catch (Exception ex) {ex.printStackTrace();return ResponseUtils.fail(ex.getMessage());}finally {}}protected void writeToResponse(HttpServletResponse response, HSSFWorkbook wb, String fileName) throws IOException {response.addHeader(Content-Disposition, attachment;filename URLEncoder.encode(fileName,utf-8).xls);response.setContentType(application/vnd.ms-excel;charsetutf-8);response.setCharacterEncoding(UTF-8);OutputStream out response.getOutputStream();wb.write(out);out.flush();wb.close();}private void addRow(HSSFSheet sheet, ResultSet rs) throws SQLException {ResultSetMetaData rsMetaData rs.getMetaData();int columnCount rsMetaData.getColumnCount();int rowNum 1;while (rs.next()) {Row row sheet.createRow(rowNum);for (int i 1; i columnCount; i) {Cell cell row.createCell(i - 1);String columnName rsMetaData.getColumnName(i);switch (rsMetaData.getColumnType(i)) {case Types.VARCHAR:case Types.CHAR:cell.setCellValue(rs.getString(columnName));break;case Types.INTEGER:cell.setCellValue(rs.getInt(columnName));break;case Types.DOUBLE:cell.setCellValue(rs.getDouble(columnName));break;// Add other types as neededdefault:cell.setCellValue(rs.getString(columnName));}}}}private void addTitle(HSSFSheet sheet, String[] titles) {HSSFRow row sheet.createRow((int) 0);for (int i0;ititles.length;i) {HSSFCell cell row.createCell(i);cell.setCellValue(titles[i]);sheet.autoSizeColumn(i);//自动设宽}}private String dateNow(){SimpleDateFormat sdfnew SimpleDateFormat(yyyyMMddhhmmss);return sdf.format(new java.util.Date());}
}
然后通过postman即可导出数据因为入参类型比较复杂sql可能较长所以此处使用post请求。
测试
入参
{url:jdbc:mysql://XXXX:63306/gac_order,
user:XXX,
password:xxx,
sql:select order_id,create_time,user_id,status from order_info limit 1000,
titles:[order_id,create_time,user_id,status]}
postman send按钮下拉的Send and Download 可以顺利下载下数据
