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();
}
}
