Java實現(xiàn)根據(jù)sql動態(tài)查詢并下載數(shù)據(jù)到excel

更新時間：2024年04月20日 11:43:04 作者：bxp1321

這篇文章主要為大家詳細介紹了如何使用Java實現(xiàn)根據(jù)sql動態(tài)查詢并下載數(shù)據(jù)到excel的功能,文中的示例代碼講解詳細,有需要的可以參考下

需求

由于生產(chǎn)數(shù)據(jù)庫不能直接連接下載數(shù)據(jù)，所以需要在監(jiān)控系統(tǒng)上做一個根據(jù)sql動態(tài)查詢并下載數(shù)據(jù)的功能。

實現(xiàn)思路

寫一個接口，傳入需要查詢的數(shù)據(jù)庫信息和sql，將查詢的接口導出到Excel中

實現(xiàn)細節(jié)

入?yún)?/p>

@Data
public class ExportDataReq {
    /**
     * "jdbc:mysql://localhost:3306/your_database";
     */
    @NotEmpty
    String url;
    @NotEmpty
    String user;
    @NotEmpty
    String password;
    @NotEmpty
    String sql;
    @NotEmpty
    String[] titles;
}

controller

@Slf4j
@Controller
@RequestMapping("/export/")
public class ExportController {
    @Autowired
    ExportService exportService;
 
    @ApiOperation(value = "下載數(shù)據(jù)信息", notes = "日期格式：yyyy-MM-dd", httpMethod = "POST")
    @PostMapping(value = "/exportData")
    @ResponseBody
    public 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 {
    @Override
    public 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())) {
            //創(chuàng)建wb
            HSSFWorkbook wb =  new HSSFWorkbook();
            //創(chuàng)建sheet
            HSSFSheet sheet = wb.createSheet("data");
            //添加標題
            addTitle(sheet,req.getTitles());
            //添加內容
            addRow(sheet,rs);
            //導出
            writeToResponse(response,wb,"data"+dateNow());
            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;charset=utf-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 needed
                    default:
                        cell.setCellValue(rs.getString(columnName));
                }
            }
        }
    }
 
    private void addTitle(HSSFSheet sheet, String[] titles) {
        HSSFRow row = sheet.createRow((int) 0);
        for (int i=0;i<titles.length;i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            sheet.autoSizeColumn(i);//自動設寬
        }
    }
    private String dateNow(){
        SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddhhmmss");
        return sdf.format(new java.util.Date());
    }
}

然后通過postman即可導出數(shù)據(jù)，因為入?yún)㈩愋捅容^復雜，sql可能較長，所以此處使用post請求。

測試

入?yún)?/p>

{"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 可以順利下載下數(shù)據(jù)

到此這篇關于Java實現(xiàn)根據(jù)sql動態(tài)查詢并下載數(shù)據(jù)到excel的文章就介紹到這了,更多相關Java sql動態(tài)查詢并下載數(shù)據(jù)內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: