存取資料庫+讀寫EXCEL

package dbConnection;

import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import jxl.Workbook;
import jxl.write.Alignment;
import jxl.write.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class jdbcmysql {
 private Connection con = null; // Database objects
 // 連接object
 private Statement stat = null;
 // 執行,傳入之sql為完整字串
 private ResultSet rs = null;
 // 結果集
 private PreparedStatement pst = null;

 // 執行,傳入之sql為預儲之字申,需要傳入變數之位置
 // 先利用?來做標示

 public jdbcmysql() {
  

 }

 // 建立table的方式
 // 可以看看Statement的使用方式
 public void createTable() {
  String createdbSQL = "CREATE TABLE User (" + "    id     INTEGER "
    + "  , name    VARCHAR(20) " + "  , passwd  VARCHAR(20))";
  try {
   stat = con.createStatement();
   stat.executeUpdate(createdbSQL);
  } catch (SQLException e) {
   System.out.println("CreateDB Exception :" + e.toString());
  } finally {
   Close();
  }
 }

 // 新增資料
 // 可以看看PrepareStatement的使用方式
 public void insertTable(String name, String passwd) {
  String insertdbSQL = "insert into User(id,name,passwd) "
    + "select ifNULL(max(id),0)+1,?,? FROM User";
  try {
   pst = con.prepareStatement(insertdbSQL);

   pst.setString(1, name);
   pst.setString(2, passwd);
   pst.executeUpdate();
  } catch (SQLException e) {
   System.out.println("InsertDB Exception :" + e.toString());
  } finally {
   Close();
  }
 }

 // 刪除Table,
 // 跟建立table很像
 public void dropTable() {
  String dropdbSQL = "DROP TABLE User ";
  try {
   stat = con.createStatement();
   stat.executeUpdate(dropdbSQL);
  } catch (SQLException e) {
   System.out.println("DropDB Exception :" + e.toString());
  } finally {
   Close();
  }
 }

 /**
  * 取得 Oracle連線conn
  * 
  * @return
  * @throws IOException
  * @throws ClassNotFoundException
  * @throws SQLException
  */
 public static Connection OracleConn() throws IOException,
   ClassNotFoundException, SQLException {

  // Oracle 正式機  

 }

 /**
  * 取得 MSSQL連線conn
  * 
  * @return
  * @throws IOException
  * @throws ClassNotFoundException
  * @throws SQLException
  */
 public static Connection MSSQLConn() throws IOException,
   ClassNotFoundException, SQLException {

  

 }

 /**
  * 取得 Oracle連線conn
  * 
  * @return
  * @throws IOException
  * @throws ClassNotFoundException
  * @throws SQLException
  */
 public static Connection OracleConnTest() throws IOException,
   ClassNotFoundException, SQLException {

  

 }

 /**
  * 取得 MSSQL連線conn
  * 
  * @return
  * @throws IOException
  * @throws ClassNotFoundException
  * @throws SQLException
  */
 public static Connection MSSQLConnTest() throws IOException,
   ClassNotFoundException, SQLException {

 
 }

 // 查詢資料
 // 可以看看回傳結果集及取得資料方式
 public static ResultSet MSSelectTable(String Comm) throws IOException,
   SQLException, ClassNotFoundException {

  Connection connectino = null; // Database objects
  // 連接object
  Statement statement = null;
  // 執行,傳入之sql為完整字串
  ResultSet resultSet = null;
  connectino = MSSQLConn();
  statement = connectino.createStatement();

  // 取繳款書統計使用數量中，有使用的日期。
  resultSet = statement.executeQuery(Comm);
  return resultSet;

 }

 public static Integer MSTableCount(String Comm) throws IOException,
   SQLException, ClassNotFoundException {

  Connection connectino = null; // Database objects
  // 連接object
  Statement statement = null;
  // 執行,傳入之sql為完整字串
  ResultSet resultSet = null;
  connectino = MSSQLConn();
  statement = connectino.createStatement();

  // 取繳款書統計使用數量中，有使用的日期。
  resultSet = statement.executeQuery(Comm);
  return resultSet.getInt(1);

 }

 public static ResultSet ORSelectTable(String Comm) throws IOException,
   SQLException, ClassNotFoundException {

  Connection connectino = null; // Database objects
  // 連接object
  Statement statement = null;
  // 執行,傳入之sql為完整字串
  ResultSet resultSet = null;
  connectino = OracleConn();
  statement = connectino.createStatement();

  // 取繳款書統計使用數量中，有使用的日期。
  resultSet = statement.executeQuery(Comm);
  return resultSet;

 }

 public static void ExportEXL(String FileName, String inputp[][], int rows,
   int columns) {

  try {
   /*
    * 1. 建立Workbook 也就是整份的Excel檔案，可指定檔名。 WritableWorkbook workbook =
    * Workbook.createWorkbook(new File("test.xls"));
    */
   System.out.println("建立Workbook!");
   WritableWorkbook workbook = Workbook.createWorkbook(new File(
     FileName + ".xls"));

   /*
    * 2. 建立Sheet 也就是每個Excel檔案下面可以跳tag的sheet表。 WritableSheet sheet =
    * workbook.createSheet("My Sheet", 0); 第一個參數是sheet
    * name，第二個參數是第幾張sheet，從0開始。
    */
   System.out.println("建立Sheet!");
   WritableSheet sheet = workbook.createSheet("First Sheet", 0);

   // 3. 設定Cell格式(可省略，就會是預設的格式)
   System.out.println("設定Cell格式!");
   WritableCellFormat cellFormat = new WritableCellFormat();
   WritableFont myFont = new WritableFont(WritableFont
     .createFont("標楷體"), 12);
   cellFormat.setFont(myFont); // 指定字型
   myFont.setColour(Colour.BLACK);
   // cellFormat.setBackground(Colour.LIGHT_BLUE); // 背景顏色
   cellFormat.setAlignment(Alignment.CENTRE); // 對齊方式

   // 4. 增加一個文字儲存格Cell
   System.out.println("建立EXCEL內的title!");
   Label label = new Label(0, 0, "Table Name");

   System.out.println("建立EXCEL內的內文!");

   for (int c = 0; c < columns; c++) {
    for (int r = 0; r < rows; r++) {
     label = new Label(c, r, inputp[c][r]);
     sheet.addCell(label);
    }
   }

   // 5. 寫入及結束文件
   workbook.write(); // 寫入
   System.out.println("寫入完成!");
   workbook.close(); // 關閉
   System.out.println("關閉完成!");
  } catch (IOException e) {
   System.out.println("EXL IOException:" + e.toString());
  } catch (Exception e) {
   System.out.println("EXL Exception:" + e.toString());
  }

 }

 // 完整使用完資料庫後,記得要關閉所有Object
 // 否則在等待Timeout時,可能會有Connection poor的狀況
 private void Close() {
  try {
   if (rs != null) {
    rs.close();
    rs = null;
   }
   if (stat != null) {
    stat.close();
    stat = null;
   }
   if (pst != null) {
    pst.close();
    pst = null;
   }
  } catch (SQLException e) {
   System.out.println("Close Exception :" + e.toString());
  }
 }

 public static String[][] RStoArray(ResultSet resultSet, String rsData[][])
   throws SQLException {
  ResultSetMetaData rsmd = resultSet.getMetaData();
  int columnsCount = rsmd.getColumnCount();
  int rowCount = 0;
  String[][] temp = new String[columnsCount][100000];
  while (resultSet.next()) {
   for (int r = 1; r <= columnsCount; r++) {
    String CN = rsmd.getColumnName(r);
    temp[r - 1][rowCount] = resultSet.getString(CN);
   }
   rowCount += 1;
  }
  rsData = new String[columnsCount][rowCount];
  for (int i = 0; i < temp.length; i++) {
   System.arraycopy(temp[i], 0, rsData[0], i, rowCount);
  }

  // for (int c = 0; c < rowCount; c++) {
  // for (int r = 0; r < rows; r++) {
  // inputp[][] = temp[][] ;
  // }
  // }
  int a = 0;
  a++;
  return rsData;
 }

 // 查詢所有table名稱和數量
 public void SelectMSSQLAllTableNameAndCount() throws IOException,
   ClassNotFoundException {
  try {
   // MSSQL select all table name
   String SQLCommStr = "Select table_name From information_schema.tables Where table_type='BASE TABLE'";
   // Oracle select all table name
   // String SQLCommStr = "Select table_name From User_Tables Order by table_name"

   ResultSet resultSet = null;
   System.out.println("SQL指令撈資料!");
   resultSet = MSSelectTable(SQLCommStr);
   System.out.println("SQL指令撈資料[完成]!");

   // 轉換
   ResultSetMetaData rsmd = resultSet.getMetaData();
   int columnsCount = rsmd.getColumnCount();
   // int rowsCount = MSTableCount()
   String[][] rsData = new String[columnsCount][];
   rsData = RStoArray(resultSet, rsData);
   // 取得資料庫資訊

   System.out.println("轉換完成!");

   String[] Count = new String[rsData[0].length];
   String[] Time = new String[rsData[0].length];

   for (int i = 0; i < rsData[0].length; i++) {

    SQLCommStr = "Select Count(*) From " + rsData[0][i];
    long StartTime = System.currentTimeMillis(); // 取出目前時間
    resultSet = MSSelectTable(SQLCommStr);
    long ProcessTime = System.currentTimeMillis() - StartTime; // 計算處理時間
    Time[i] = Long.toString(ProcessTime) + " ms" ;
    
    // 轉換
    rsmd = resultSet.getMetaData();
    columnsCount = rsmd.getColumnCount();
    // int rowsCount = MSTableCount()
    String[][] tempCount = new String[columnsCount][];
    System.out.println(i + 1 + " : " + rsData[0][i] + " OK! - " + Time[i]);
    tempCount = RStoArray(resultSet, tempCount);
    // 取得資料庫資訊
    Count[i] = tempCount[0][0];
   }

   String[][] printStr = new String[3][rsData[0].length];
   System.arraycopy(rsData[0], 0, printStr[0], 0, rsData[0].length);
   System.arraycopy(Count, 0, printStr[1], 0, rsData[0].length);
   System.arraycopy(Time, 0, printStr[2], 0, rsData[0].length);
   
   ExportEXL("20120508 MSSQL", printStr, rsData[0].length, 3);
   System.out.println("產出完成 OK!");

   // for (int i=0 ; i<=rsData)
   // SQLCommStr = "Select Count(*) From";

  } catch (IOException e) {
   System.out.println("EXL IOException:" + e.toString());
  } catch (Exception e) {
   System.out.println("EXL Exception:" + e.toString());
  }

 }

 public void SelectOracleAllTableNameAndCount() throws IOException,
   ClassNotFoundException {
  try {
   // MSSQL select all table name
   //String SQLCommStr = "Select table_name From information_schema.tables Where table_type='BASE TABLE'";
   // Oracle select all table name
    String SQLCommStr = "Select table_name From User_Tables Order by table_name" ;

   ResultSet resultSet = null;
   System.out.println("SQL指令撈資料!");
   resultSet = ORSelectTable(SQLCommStr);
   System.out.println("SQL指令撈資料[完成]!");

   // 轉換
   ResultSetMetaData rsmd = resultSet.getMetaData();
   int columnsCount = rsmd.getColumnCount();
   // int rowsCount = MSTableCount()
   String[][] rsData = new String[columnsCount][];
   rsData = RStoArray(resultSet, rsData);
   // 取得資料庫資訊

   System.out.println("轉換完成!");

   String[] Count = new String[rsData[0].length];
   String[] Time = new String[rsData[0].length];

   for (int i = 0; i < rsData[0].length; i++) {
    
    SQLCommStr = "Select Count(*) From " + rsData[0][i];
    
    long StartTime = System.currentTimeMillis(); // 取出目前時間
    resultSet = ORSelectTable(SQLCommStr);
    long ProcessTime = System.currentTimeMillis() - StartTime; // 計算處理時間
    Time[i] = Long.toString(ProcessTime) + " ms";
    // 轉換
    rsmd = resultSet.getMetaData();
    columnsCount = rsmd.getColumnCount();
    // int rowsCount = MSTableCount()
    String[][] tempCount = new String[columnsCount][];
    System.out.println(i + 1 + " : " + rsData[0][i] + " OK! - " + Time[i]);
    tempCount = RStoArray(resultSet, tempCount);
    // 取得資料庫資訊
    Count[i] = tempCount[0][0];
   }

   String[][] printStr = new String[3][rsData[0].length];
   System.arraycopy(rsData[0], 0, printStr[0], 0, rsData[0].length);
   System.arraycopy(Count, 0, printStr[1], 0, rsData[0].length);
   System.arraycopy(Time, 0, printStr[2], 0, rsData[0].length);

   ExportEXL("20120508 Oracle", printStr, rsData[0].length, 3);
   System.out.println("產出完成 OK!");

   // for (int i=0 ; i<=rsData)
   // SQLCommStr = "Select Count(*) From";

  } catch (IOException e) {
   System.out.println("EXL IOException:" + e.toString());
  } catch (Exception e) {
   System.out.println("EXL Exception:" + e.toString());
  }

 }

 public static void main(String[] args) throws IOException,
   ClassNotFoundException {
  // 測看看是否正常
  jdbcmysql SQL = new jdbcmysql();
  SQL.SelectMSSQLAllTableNameAndCount();
  SQL.SelectOracleAllTableNameAndCount();
 }
}