package org.ufida.common;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import java.io.*;
import java.sql.*;
/**
* @title MS_SQL数据库导出Excel
* @author 刘当
* @email liu_dang@163.com
*/
public class ExportDb {
static Connection con;
/**
* 初始化数据库连接
*/
String p;
public ExportDb(String url, String user, String pass) {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url, user, pass);
} catch (Exception e) {
System.out.println("初始化数据库连接异常..." + e.getMessage());
}
}
/**
* 检索库中所有表
*/
public void execExport() {
System.out.println();
System.out.println("开始导出数据库......");
try {
// Statement selectAllTable = con.createStatement();
// ResultSet rset=selectAllTable.executeQuery("SELECT
// name From sysobjects WHERE xtype = 'u' and
// name!='dtproperties'");
//
// while(rset.next())
// {
// String tabName = rset.getString("name");
// this.exportTable(tabName);
// }
this.exportTable("1","select uuid as Thread_No ,method as method,count(*) as count,sum(time) as summary,avg(time) as average from pinspect group by uuid ,method order by uuid,count" );
this.exportTable("2","select method as method,count(*) as count,sum(time) as summary,avg(time) as average from pinspect group by method order by count" );
} catch (Exception e) {
System.out.println("检索库中所有表时异常..." + e.getMessage());
}
}
/**
* 导出特定表
*/
public void exportTable(String page,String sql) {
System.out.println("正在导出表:pinspect");
try {
Statement selectTable = con.createStatement();
ResultSet rset = selectTable
.executeQuery(sql);
this.p = page;
this.writeTable(rset, "性能统计分析"+page+".xls");
} catch (Exception e) {
System.out.println("导出表时异常..." + e.getMessage());
}
}
/**
* 写特定表的数据到xls
*/
private void writeTable(ResultSet rs, String xlsName) {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
// workbook.setSheetName(0,xlsName,HSSFWorkbook.ENCODING_UTF_16);
workbook.setSheetName(0, "分頁" + String.valueOf(0),
(short) HSSFWorkbook.ENCODING_UTF_16);
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell = null;
ResultSetMetaData md = rs.getMetaData();
// 写字段
int nColumn = md.getColumnCount();
for (int i = 1; i <= nColumn; i++) {
cell = row.createCell((short) (i - 1));
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// cell.setCellValue(md.getColumnLabel(i)+"您好");
switch (i) {
case 1 :cell.setCellValue("请求的编号");
break;
case 2 :cell.setCellValue("当前请求的执行方法数量");
break;
case 3 :cell.setCellValue("当前请求的执行方法总时间");
break;
case 4 :cell.setCellValue("当前请求的执行方法平均时间");
break;
}
}
// 写记录
int iRow = 1;
String uuidName = null;
while (rs.next()) {
row = sheet.createRow((short) iRow);
for (int j = 1; j <= nColumn; j++) {
cell = row.createCell((short) (j - 1));
cell
.setCellType(HSSFCell.CELL_TYPE_STRING);
cell
.setEncoding(HSSFCell.ENCODING_UTF_16);
String val = null;
if(this.p.equals("1")){
if(j ==1){
if(rs.getObject(j).toString().equals(uuidName)){
val = " ";
}
else{
uuidName = rs.getObject(j).toString();
val = uuidName;
}
}else if(j == 2){
val = rs.getObject(j).toString().split("classes/")[1];
}else{
val = rs.getObject(j).toString();
}
}else if(j == 1){
val = rs.getObject(j).toString().split("classes/")[1];
}else{
val = rs.getObject(j).toString();
}
if (val == null) {
cell.setCellValue("NULL");
} else {
cell.setCellValue(val);
}
}
iRow++;
}
// 保存文件
FileOutputStream fOut = new FileOutputStream(xlsName);
workbook.write(fOut);
fOut.flush();
fOut.close();
System.out.println("导出数据完成!");
} catch (Exception e) {
System.out.println("写表时异常..." + e.getMessage());
}
}
public static void main(String arg[]) throws IOException {
// InputStreamReader inputReader=new
// InputStreamReader(System.in);
// BufferedReader bufferStream=new BufferedReader(inputReader);
String url = "jdbc:mysql://localhost:3306/ufida"; // myDB为数据库名
String user = "root";
String password = "mysql";
// System.out.print("请输入数据库名:");
// System.out.flush();
// String
// url="jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName="+bufferStream.readLine().trim();
//
// System.out.print("请输入用户名:");
// System.out.flush();
// String user=bufferStream.readLine().trim();
//
// System.out.print("请输入密码:");
// System.out.flush();
// String password=bufferStream.readLine().trim();
ExportDb obj = new ExportDb(url, user, password);
obj.execExport();
}
}