2014年3月14日 星期五

Java 資料庫存取 : 使用 ACCESS

之前為單位營運所需使用 Javascript 寫的 WSH 函式庫, 在電腦升級為 Win7 後無法存取 ACCESS 資料庫, 導致資料抓取工作停擺. 最近測試 Java 發現只要 JRE 是安裝 X86 (32 位元) 版就可以存取 ACCESS, 不用管 Win7 是 32 還是 64 位元都可以順利存取 32 位元的 ACCESS MDB 資料庫, 關於 Win7 上 Java 連線 ACCESS 問題請參考 :

Java 程式連線 ACCESS 資料庫問題 


因此我打算將原 Javascript 函式庫全部改寫為 Java 版.

我用的資料庫仍然是 ACCESS 2003, 也就是 mdb 檔案. 我為此建立一個 JAccess 類別, 含有 Connection conn 與 Statement stat 兩個靜態成員, 以便在靜態方法中存取. 所有方法均為靜態方法, 這樣就不需要建立實體物件, 直接呼叫靜態方法即可.

存取資料庫第一件事就是建立資料庫連線, 然後就可以用 SQL 指令存取資料表, 結束之後要關閉連線, 下面這個 JAccess 類別的靜態方法 connect() 就是用來連線指定的 mdb 資料庫, run() 方法用來執行 SQL 指令; 而 disconnect() 方法則是用來關閉資料庫連線 :

import java.sql.*;
public class JAccess {
  static Connection conn;
  static Statement stat;
  public static void connect(String database) {
    try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      String dsn="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};" +
                       "DBQ=" + database + ";DriverID=22;READONLY=false}";
      conn=DriverManager.getConnection(dsn, "", "");  //帳號與密碼均為空字串 (未設)
      stat=conn.createStatement();  //取得 Statement 物件
      }
    catch (Exception e) {System.out.println(e);}
    }
  public static void disconnect() {
    try {conn.close();}
    catch (Exception e) {System.out.println(e);}
    conn=null;
    stat=null;
    }
  public static ResultSet run(String SQL) {
    ResultSet rs=null;
    try {
      if (SQL.indexOf("SELECT") == -1) {stat.executeUpdate(SQL);}
      else {rs=stat.executeQuery(SQL);}
      }
    catch (Exception e) {System.out.println(e);}
    return rs;
    }
 }

跟檔案 IO 一樣, 資料庫存取指令因為不保證成功, 因此全部都要用 try catch 語法捕捉可能之例外, 否則無法通過編譯. 執行資料庫異動 (INSERT, UPDATE, DELETE, CREATE, DROP) 需呼叫 Statement 物件的 executeUpdate 方法; 而查詢動作 (SELECT) 則是呼叫 executeQuery() 方法, 因此在 JAccess 類別中使用字串類別的 indexOf 來偵測傳入之 SQL 指令中是否含有 SELECT 字串, 如無就傳回 -1, 要呼叫 Statement 物件之 executeUpdate() 方法, 否則即為查詢指令, 要呼叫 executeQuery() 方法, 並傳回 ResultSet 物件.

現在就馬上利用 JAccess 類別來建立一個 users 資料表, 首先準備一個空白資料庫 test.mdb, 然後寫一個 access1.java 程式直接調用 JAccess 類別, 如下範例 1 所示 :

CREATE TABLE table_name(field1 type1, field2 type2,...,fieldn typen)

這裡的 field 為欄位名稱, type 為資料型態, 在 ACCESS 資料庫有下列幾種型態 :
  1. VARCHAR (可變長度字串)   
  2. CHAR (固定長度字串)    
  3. SMALLINT (整數)    
  4. INT (長整數)
  5. FLOAT (浮點數)           
  6. TEXT (文字, 最大 255 字元)  
  7. MEMO (備忘, 最大 65535 字元)   
  8. DATE (日期)   
  9. YESNO (是否)      
  10. CURRENCY (貨幣)  
  11. AUTOINCREMENT PRIMARY KEY (自動增量主鍵)
其中 CHAR 與 VARCHAR 須用小括弧指定字元長度, 但其用法不同 : VARCHAR(255) 表示最大長度為 255 字元, 只要填入字元不超過 255 即可; 但 CHAR(4) 卻一定要存入四個字元, 少一個也不行. 另外, ACCESS 的欄位名稱有保留, 不可使用下列這些名稱 :

account, date, point, size, time, percent, procedure, class, group, level 等等.

若使用這些保留字當欄位名稱, 則 INSERT 時會出現 “Microsoft JET Database Engine 錯誤”. 下列範例 1 就使用 JAccess 的類別方法 run() 來建立資料表, 可見程式碼精簡不少, 不用再去處理 connection 與 statement, 只要搞定 SQL 即可 :

測試範例 1 :  http://mybidrobot.allalla.com/javatest/access1.zip  [ 看原始碼] 

//import java.sql.*;
public class access1 {
  public static void main(String[] args) {
    JAccess.connect("test.mdb");
    String SQL="CREATE TABLE users(id autoincrement PRIMARY KEY," +
                         "name VARCHAR(50),age SMALLINT,gender VARCHAR(5)," +
                         "email VARCHAR(255),password VARCHAR(30))";
    JAccess.run(SQL);
    JAccess.disconnect();
    }
  }

透過 JAccess 類別, 只要四個指令就可以搞定新增資料表作業, 這裡因為都只是呼叫 JAccess 類別的靜態方法, 沒有直接使用 java.sql 函式庫的類別, 因此不需要匯入 java.sql.* 也是可以的. 以上 JAccess.java 與 access1.java 因為都用 utf-8 格式存檔, 因此編譯時要指明參數, 否則會出現編譯錯誤 :

javac -encoding utf-8 JAccess.java
javac -encoding utf-8 access1.java
java access1.java

執行完畢後打開 test.mdb 可見資料表 users 已經建好了 :



其次要新增資料到資料表 users 中, 這要使用 INSERT INTO 的 SQL 指令 :

INSERT INTO table_name(field1,field2,...,fieldn) VALUES(val1,val2,...,valn)

如下範例 2 所示 :

測試範例 2 :  http://mybidrobot.allalla.com/javatest/access2.zip  [ 看原始碼] 


//import java.sql.*;
public class access2 {
  public static void main(String[] args) {
    String SQL;
    JAccess.connect("test.mdb");
    SQL="INSERT INTO users(name,age,gender,email,password) " +
              "VALUES('愛咪','12','女','amy@gmail.com','123')";

    JAccess.run(SQL);
    SQL="INSERT INTO users(name,age,gender,email,password) " +
              "VALUES('彼得',14,'男','peter@gmail.com','456')"
;
    JAccess.run(SQL);
    SQL="INSERT INTO users(name,age,gender,email,password) " +
               "VALUES('凱莉',16,'女','kelly@gmail.com','789')"
;
    JAccess.run(SQL);
    JAccess.disconnect();
    }
  }


可見三筆資料已填進資料表 users 中了.  這裡要注意的是, 如果欄位資料類型是文字類 (CHAR, VARCHAR, TEXT, MEMO), 其值必須用單引號括起來; 但數字類 (INT, SMALLINT, FLOAT, DATE), 其值則不需要用單引號括起來, 但若為了統一起見用單引號括起來也是可以的, 例如上面第一筆插入的資料, 其年齡欄位型態為 SMALLINT, 但卻用單引號括起來. .

接下來, 我們要從 users 資料表中讀取紀錄, 這要用到 SQL 的 SELECT 指令 :

SELECT * FROM table [WHERE field='value' [AND field='value']]  

以上中括號表示選項, 如果沒有 WHERE 條件式, 表示讀取該資料表內全部紀錄; 有 WHERE 條件式就只讀取符合要求的紀錄. 條件式還可以使用 NOT 或 OR 等邏輯運算, 詳見 :

# 最常用的 SQL 指令 


下列範例 3 使用 SELECT 指令讀取資料表 users 全部資料, 這裡因為會用到 ResultSet 與 ResultSetMetaData 兩個物件, 所以必須匯入 java.sql.* 類別庫 :

測試範例 3 :  http://mybidrobot.allalla.com/javatest/access3.zip  [ 看原始碼]


import java.sql.*;   //必須匯入
public class access3 {
  public static void main(String[] args) {
    String SQL;
    JAccess.connect("test.mdb");
    SQL="SELECT * FROM users";
    try {
      ResultSet rs=JAccess.run(SQL);  //傳回 ResultSet 實例
      ResultSetMetaData md=rs.getMetaData()
//傳回 ResultSetMetaData 實例     
      int columns=md.getColumnCount();  //傳回欄位數目
      for (int i=1; i<=columns; i++) {
           System.out.printf(md.getColumnLabel(i) + "\t");  //傳回欄位名稱 (索引 1 起始)
           }
      System.out.println("");
      while (rs.next()) {  //拜訪全部 ReseltSet 紀錄
             for (int i=1; i<=columns; i++) {  //拜訪這筆紀錄中的每個欄位
                  System.out.print(rs.getString(i) + "\t");  //傳回欄位值
  (索引 1 起始)
                  }
             System.out.println("");
             }
      }
    catch(Exception e) {System.out.println(e);}
    JAccess.disconnect();
    }
  }


可見全部三筆資料都列出來了, 這裡我們用傳回的 ResultSet 來呼叫 getMetaData() 方法就能在傳回來的 ResultSetMetaData 介面物件中取得資料表的欄位訊息, 包括欄位數目 (getColumnCount), 欄位名稱 (getColumnLabel), 以及欄位型態 (getColumnTypeName). 此例中我們只用了前兩種, 欄位型態下面範例 4 再來測試. 要特別注意的是, 不管是取得 ResultSetMetaData 介面物件的欄位資訊, 或是 ResultSet 物件中的欄位值, 其索引都是從 1 起算的.

下面範例 4 主要是測試上例中提到的 ResultSetMetaData 介面之物件的 getColumnTypeName() 方法, 以取得資料表各欄位的資料型態, 此介面還定義了許多方法, 參見 ResultSetMetaData :

測試範例 4 :  http://mybidrobot.allalla.com/javatest/access4.zip  [ 看原始碼]


import java.sql.*;
public class access4 {
  public static void main(String[] args) {
    String SQL;
    JAccess.connect("test.mdb");
    SQL="SELECT * FROM users";
    try {
      ResultSet rs=JAccess.run(SQL);
      ResultSetMetaData md=rs.getMetaData();
      int columns=md.getColumnCount();
      System.out.println("欄位名稱\t資料型態\t顯示大小\t自動增量\t可不填");
      for (int i=1; i<=columns; i++) {
           System.out.print(md.getColumnLabel(i) + "\t");          
//欄位名稱 (1 起始)
           System.out.print(md.getColumnTypeName(i) + "\t"); //欄位型態 (1 起始)
           System.out.print(md.getColumnDisplaySize(i) + "\t"); //欄位大小          

           System.out.print(md.isAutoIncrement(i) + "\t");   //是否自動增量 (true/false)       
           System.out.println(md.isNullable(i));                 //否可不填 (0/1)
           }
      }
    catch(Exception e) {System.out.println(e);}
    JAccess.disconnect();
    }
  }





上面範例 2 中新增資料時是全部欄位都有指定欄位值, 其實新增時可以僅指定部分欄位值 (沒有指定的欄位其值預設為 NULL), 然後再用 SQL 的 UPDATE 指令來更新欄位值. SQL 的 UPDATE 指令格式如下 :

UPDATE table SET field1='value1' [, field2='value2', ... fieldn='valuen'] [WHERE field=value]

其中 SET 後面至少要指定一個欄位, 可以同時更新多個欄位, 只要以逗號隔開即可. 最後要用 WHERE 指定要更新哪一筆記錄, 注意, 如果沒有 WHERE, 則此資料表內全部紀錄都會被更新.

下面範例 5 先用 INSERT 新增一筆不完整記錄, 然後範例 6 再用 UPDATE 更新其他欄位 :


//import java.sql.*;
public class access5 {
  public static void main(String[] args) {    
    String SQL;
    JAccess.connect("test.mdb");
    SQL="INSERT INTO users(name) VALUES('東尼')";
    JAccess.run(SQL);
    JAccess.disconnect();
    }
  }

此範例執行後, 打開 test.mdb 的 users 資料表, 可見已經新增了一筆東尼的紀錄, 其他欄位空白 (NULL 值). 下列範例 6 我們用 UPDATE 指令來補足空白欄位 :

測試範例 6 :  http://mybidrobot.allalla.com/javatest/access6.zip  [ 看原始碼]

//import java.sql.*;
public class access6 {
  public static void main(String[] args) {  
    String SQL;
    JAccess.connect("test.mdb");
    SQL="UPDATE users SET age='48',gender='男',email='tony@gmail.co'," +
"password='abc' WHERE name='東尼'";
    JAccess.run(SQL);
    JAccess.disconnect();
    }
  }

可見東尼這筆記錄資料就完整了. 注意 WHERE 條件別忘記加了, 否則全部紀錄都會變成一樣, 都是東尼的紀錄, 像這樣 :


可見除了 name 以外, 每筆記錄的各欄位值都一樣了.

下面範例 7 是用 DELETE 指令來刪除紀錄, 其格式如下 :

DELETE * FROM table [WHERE field='value']

同樣地通常要加上 WHERE 條件去指定刪除的對象, 否則此資料表全部資料都會被刪除, 不可不慎.

測試範例 7 :  http://mybidrobot.allalla.com/javatest/access7.zip  [ 看原始碼]

import java.sql.*;
public class access7 {
  public static void main(String[] args) {  
    String SQL;
    JAccess.connect("test.mdb");
    SQL="DELETE * FROM users WHERE gender='男' AND age=14";
    JAccess.run(SQL);
    JAccess.disconnect();
    }
  }

可見第二筆記錄 (彼得) 已經被刪除, 因為這裡的 WHERE 條件是性別為男性, 且年齡為 14 歲者. 如果僅 gender='男' 一個條件, 則會刪除東尼與彼得兩筆記錄.

特別注意, 上面 INSERT 或 SELECT 操作, 欄位值不論是數字或文字, 新增或更新欄位值時我們一律用單引號括起來都沒問題, 但是在 WHERE 條件式中, 則不可以將數字欄位值加上單引號, 而文字欄位值一定要加單引號. 否則執行時會出現 "準則運算式的資料類型不符合" 例外, 例如若上面範例 7 的 SQL 改為下面這樣 :

SQL="DELETE * FROM users WHERE gender='男' AND age='14'";

因為 age 為數字欄位, 其值在 WHERE 中不可加單引號, 因此發生如下例外 ::


接下來是要為資料表添加欄位, 這要使用 ALTER TABLE 指令, 其格式如下 :

ALTER TABLE table ADD field1 type1,field2 type2,...,fieldn typen

可以一次添加多個欄位, 但 ACCESS 無法像 MySQL 那樣在最後面用 AFTER field 指定添加到哪個欄位後面, 如下列範例 8 所示 :

測試範例 8 :  http://mybidrobot.allalla.com/javatest/access8.zip  [ 看原始碼]

//import java.sql.*;
public class access8 {
  public static void main(String[] args) {    
    String SQL;
    JAccess.connect("test.mdb");
    SQL="ALTER TABLE users ADD telephone VARCHAR(20),city VARCHAR(20)";
    JAccess.run(SQL);
    JAccess.disconnect();
    }
  }

可見添加的兩個欄位 telephone 與 city 會排在最後面. 如果要移除欄位, 指令格式為 :

 ALTER TABLE table DROP field1,field2,...,fieldn

下列範例 9 我們就將上面範例 8 所增加的兩個欄位 telephone 與 city 一次刪除 :

測試範例 9 :  http://mybidrobot.allalla.com/javatest/access9.zip  [ 看原始碼]

import java.sql.*;
public class access9 {
  public static void main(String[] args) {  
    String SQL;
    JAccess.connect("test.mdb");
    SQL="ALTER TABLE users DROP telephone,city";
    JAccess.run(SQL);
    JAccess.disconnect();
    }
  }

關於欄位還有一個操作, 即修改欄位屬性, 例如 name 原設為 VARCHAR(50) 欲增加為 VARCHAR(100), age 欄位原為 SMALLINT 欲改為 INT, 可以使用 ALTER TABLE 的 ALTER 指令, 格式如下 :

ALTER TABLE table ALTER field type

注意, 改欄位屬性一次只能改一個, 我們把上面範例 9 改為如下範例 10 :

測試範例 10 :  http://mybidrobot.allalla.com/javatest/access10.zip  [ 看原始碼]

import java.sql.*;
public class access10 {
  public static void main(String[] args) {   
    String SQL;
    JAccess.connect("test.mdb");
    SQL="ALTER TABLE users ALTER name VARCHAR(100)";
    JAccess.run(SQL);
    SQL="SELECT * FROM users";
    try {
      ResultSet rs=JAccess.run(SQL);
      ResultSetMetaData md=rs.getMetaData();
      int columns=md.getColumnCount();
      System.out.println("欄位名稱\t資料型態\t顯示大小\t自動增量\t可不填");
      for (int i=1; i<=columns; i++) {
           System.out.print(md.getColumnLabel(i) + "\t");    
           System.out.print(md.getColumnTypeName(i) + "\t");
           System.out.print(md.getColumnDisplaySize(i) + "\t");       
           System.out.print(md.isAutoIncrement(i) + "\t");         
           System.out.println(md.isNullable(i));               
           }
      }
    catch(Exception e) {System.out.println(e);}
    JAccess.disconnect();
    }
  }



可見 name 欄位長度已從 VARCHAR(50) 改為 VARCHAR(100) 了.



~未完待續~


參考資料
  1. JDK 7 ResultSetMetaData 介面
  2. 如何用JAVA 寫資料庫的程式 ?

沒有留言 :