Java 資料庫存取 : 使用 ACCESS (續)

今天回過頭來再整理 ACCESS 資料庫存取的函式庫, 為了簡短起見, 常用函式庫也改名為 JT.java (Java Tools 之意), 最近函式庫收尾後, 過兩週授課完畢, 就要準備改寫公司的資料擷取程式. 上一篇 ACCESS 筆記參見 :

# http://yhhuang1966.blogspot.tw/2014/03/java-access_14.html 
# http://docs.oracle.com/javase/7/docs/api/

首先改寫了連線函式 :

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.text.*;
import java.util.regex.*;
public class JT {
  static Connection conn;
  static Statement stat;
  public static void connectDB(String db) {
    connectDB(db, "", "");
    }
  public static void connectDB(String db, String user, String pwd){
    try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      dsn="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" +
              db + ";DriverID=22;READONLY=false}";
      conn=DriverManager.getConnection(dsn, user, pwd);
      stat=conn.createStatement();
      }
    catch (Exception e) {System.out.println(e);}
    }
  public static void closeDB() {
    try {conn.close();}
    catch (Exception e) {System.out.println(e);}
    conn=null;
    stat=null;
    }
  public static ResultSet runSQL(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;
    }
  public static boolean createTable(String table) {
    try {stat.executeUpdate("CREATE TABLE " + table);}
    catch (Exception e) {System.out.println(e);return false;}
    return true;
    }
  public static boolean addField(String table, String field, String type) {
    String SQL="ALTER TABLE " + table + " ADD " + field + " " + type;
    try {stat.executeUpdate(SQL);}
    catch (Exception e) {System.out.println(e);return false;}
    return true;
    }
 }

在 connectDB() 方法裡加入了資料庫帳號密碼參數, 利用方法多載, 設定預設值為空字串 (即資料庫無密碼). 事實上只要前三個方法就可以搞定一切 ACCESS 資料庫的存取操作, 只要寫好 SQL 指令丟給 runSQL() 執行即可. 但為了方便, 還是多寫一些函式來用, 這樣就可以不需要使用 SQL 指令了.

首先準備一個空白資料庫 test.mdb, 先來新增資料表與資料欄位, 這裡用 createTable() 與 addField() 這兩個方法來包裝了 SQL 指令 :

import java.util.*;
public class mytest {
  public static void main(String[] args) {
    JT.connectDB("test.mdb");
    System.out.println(JT.createTable("users"));  //輸出 true
    System.out.println(JT.addField("users","name","varchar(20)"));  //輸出 true
    System.out.println(JT.addField("users","id","AUTOINCREMENT PRIMARY KEY"));  //輸出 true
    System.out.println(JT.addField("users","gender","VARCHAR(5)"));  //輸出 true
    JT.closeDB();
    }
  }

createTable() 與 addField() 方法執行 SQL 成功時傳回 true, 可見已在 test.mdb 中建立了三個欄位 name, id, 與 gender.

其次要來抓出資料庫中的全部資料表名稱, 我們要給 JT.java 添加下列方法 getTables() :

  public static String[] getTables() {
    ArrayList list=new ArrayList();
    try {
      DatabaseMetaData md=conn.getMetaData();
      String[] types={"TABLE"};
      ResultSet rs=md.getTables(null, null, "%", types);    
      while (rs.next()) {list.add(rs.getString("TABLE_NAME"));}
      }
    catch(Exception e) {System.out.println(e);}
    list.trimToSize();
    String[] array=list.toArray(new String[list.size()]);
    return array;          
    }

這裡用 ArrayList 來儲存資料表名稱, 呼叫 Connection 物件的 getMetaData() 方法即可取得資料庫資訊 DatabaseMetaData 物件, 呼叫此物件的 getTables() 可取得全部資料表名稱, 用迴圈將資料表名稱存入 ArrayList 後再轉成陣列傳回. 參見 :

# How to get all table names from database?
# How to get all table names from a database?

範例如下 :

import java.util.*;
public class mytest {
  public static void main(String[] args) {
    JT.connectDB("test.mdb");
    System.out.println(Arrays.deepToString(JT.getTables()));  //輸出 [users]
    System.out.println(JT.createTable("books"));                       //輸出 true
    System.out.println(Arrays.deepToString(JT.getTables()));  //輸出 [books,users]
    JT.closeDB();
    }
  }

這裡用 java.util.Arrays 的 deepToString() 來顯示物件陣列的內容, 可見資料表名稱會照字母順序排列. 既然已可取得資料表名稱, 那麼就可以搜尋資料庫裏面是否已存在指定之資料表, 這可以利用 java.util.Arrays 的 binaraySearch() 方法, 我們為 JT.java 添加 findTable() 方法如下 :

  public static boolean findTable(String table) {
    if (Arrays.binarySearch(getTables(), table) < 0) {return false;}
    else {return true;}  
    }

我們把 JT.getTables() 傳回的資料表名稱陣列作為 binarySearch() 之第一參數, 去搜尋是否已存在指定之 table 陣列, 若傳回值為負表示沒找到, 就傳回 false, 非負值表示有找到, 傳回 true. 範例如下 :

import java.util.*;
public class mytest {
  public static void main(String[] args) {
    JT.connectDB("test.mdb");
    System.out.println(JT.findTable("users"));    //輸出 true
    System.out.println(JT.findTable("user"));     //輸出 false
    System.out.println(JT.findTable("books"));  //輸出 true
    System.out.println(JT.findTable("book"));   //輸出 false
    JT.closeDB();
    }
  }

資料庫 test.mdb 目前只有 users 與 books 兩個資料表, 沒有 user 與 book, 因此傳回 false.

欄位名稱與型態也是可以比照取得資料表名稱的方法寫成函式, 但資料表名稱是利用 DatabaseMetaData 物件取得, 而資料表欄位資訊則是利用 ResultSetMetaData 物件, 分別呼叫其 getColumnLabel() 與 getColumnTypeName() 方法即可取得欄位名稱與型態. 因此我們又在 JT.java 添加了四個方法 :

  public static String[] getFieldNames(String table) {
    ArrayList list=new ArrayList();
    try {
      ResultSet rs=runSQL("SELECT * FROM " + table);
      ResultSetMetaData md=rs.getMetaData();
      for (int i=1; i<=md.getColumnCount(); i++) {
        list.add(md.getColumnLabel(i));
        }
      }
    catch(Exception e) {System.out.println(e);}
    list.trimToSize();
    String[] array=list.toArray(new String[list.size()]);
    return array;           
    }
  public static String[] getFieldTypes(String table) {
    ArrayList list=new ArrayList();
    try {
      ResultSet rs=runSQL("SELECT * FROM " + table);
      ResultSetMetaData md=rs.getMetaData();
      for (int i=1; i<=md.getColumnCount(); i++) {
        list.add(md.getColumnTypeName(i));
        }
      }
    catch(Exception e) {System.out.println(e);}
    list.trimToSize();
    String[] array=list.toArray(new String[list.size()]);
    return array;           
    }
  public static Integer[] getFieldSizes(String table) {
    ArrayList list=new ArrayList();
    try {
      ResultSet rs=runSQL("SELECT * FROM " + table);
      ResultSetMetaData md=rs.getMetaData();
      for (int i=1; i<=md.getColumnCount(); i++) {
        list.add(md.getColumnDisplaySize(i));
        }
      }
    catch(Exception e) {System.out.println(e);}
    list.trimToSize();
    Integer[] array=(Integer[])list.toArray(new Integer[list.size()]);
    return array;
    }
  public static String getFieldType(String table, String field) {
      String[] names=getFieldNames(table);
      String[] types=getFieldTypes(table);
      String type="";
      for (int i=0; i<names.length; i++) {
        if (names[i].equals(field)) {type=types[i];break;}
        }
    return type;
    }
  public static int getFieldSize(String table, String field) {
    String[] names=getFieldNames(table);
    Integer[] sizes=getFieldSizes(table);
    int size=0;
    for (int i=0; i<names.length; i++) {
      if (names[i].equals(field)) {size=sizes[i];break;}
      }
    return size;
    }
  public static boolean findField(String table, String field) {
    if (Arrays.binarySearch(getFieldNames(table), field) < 0) {return false;}
    else {return true;}    
    }

getFieldSizes() 部分使用 Integer[] 包裹類別陣列來將 toArray() 傳回值強制轉型, 使用上與 int[] 一樣, 參考 :

# How to convert an ArrayList containing Integers to primitive int array?

範例如下 :

import java.util.*;
public class mytest {
  public static void main(String[] args) {
    JT.connectDB("test.mdb");
    System.out.println(JT.createTable("test")); 
    System.out.println(JT.addField("test","fid","AUTOINCREMENT PRIMARY KEY")); 
    System.out.println(JT.addField("test","fvarchar","varchar(9)")); 
    System.out.println(JT.addField("test","fchar","char(9)")); 
    System.out.println(JT.addField("test","fsmallint","smallint")); 
    System.out.println(JT.addField("test","fint","int")); 
    System.out.println(JT.addField("test","ffloat","float")); 
    System.out.println(JT.addField("test","fmemo","memo")); 
    System.out.println(JT.addField("test","ftext","text")); 
    System.out.println(JT.addField("test","fdate","date")); 
    System.out.println(JT.addField("test","fyesno","yesno"));
    System.out.println(Arrays.deepToString(JT.getFieldNames("test")));  
    System.out.println(Arrays.deepToString(JT.getFieldTypes("test")));  
    System.out.println(Arrays.deepToString(JT.getFieldSizes("test")));
    System.out.println(JT.findField("test","id"));   //輸出 flase
    System.out.println(JT.findField("test","fid"));  //輸出 true
    System.out.println(JT.getFieldType("test", "fid"));  //輸出 COUNTER
    System.out.println(JT.getFieldSize("test", "fmemo"));  //輸出 1073741823
    JT.closeDB();
    }
  }

[fid, fvarchar, fchar, fsmallint, fint, ffloat, fmemo, ftext, fdate, fyesno]
[COUNTER, VARCHAR, CHAR, SMALLINT, INTEGER, DOUBLE, LONGCHAR, VARCHAR, DATETIME, BIT]
[11, 9, 9, 6, 11, 22, 1073741823, 255, 19, 1]
false
true
COUNTER
1073741823

由此可知 ACCESS 的每一種資料類型代號, 比較特別的是自動增量欄位傳回值是 COUNTER, 而非 INTEGER; 而 MEMO 為 LONGCHAR, 其容量比 TEXT 的 VARCHAR 還要大; 適合儲存 remark 之類的長文句. YESNO 欄位類型為 BIT. 欄位型態與傳回值對照如下 :

欄位型態	傳回值
AUTOINCREMENT	COUNTER
VARCHAR	VARCHAR
CHAR	CHAR
SMALLINT	SMALLINT
INT	INTEGER
FLOAT	DOUBLE
MEMO	LONGCHAR
TEXT	VARCHAR
DATE	DATETIME
YESNO	BIT

這些欄位中, 有六個屬於文字類型, 亦即在 INSERT 時其值要用單引號刮起來, 為了方便分辨是否為文字類型欄位, 有必要在 JT.java 添加一個函式 isTextField() 來處理 :

  public static boolean isTextField(String table, String field) {
    String type=getFieldType(table, field);
    boolean isText=type.equals("CHAR")||type.equals("VARCHAR")||
                   type.equals("LONGCHAR")||type.equals("BIT")||
                   type.equals("DATETIME");
    if (isText) {return true;}
    else {return false;}  
    }

範例如下 :

import java.util.*;
public class mytest {
  public static void main(String[] args) {
    JT.connectDB("test.mdb");
    System.out.println(JT.isTextField("test","fid"));        //輸出 false
    System.out.println(JT.isTextField("test","fmemo"));  //輸出 true
    JT.closeDB();
    }
  }

接下來要在 JT.java 添加一個新增紀錄的 insert() 函式, 這裡我用 HashMap 來儲存要寫入的資料, 其 key 為欄位名稱, value 則為欄位值, 不管欄位型態, 一律以字串輸入以配合 HashMap 須指定 K,V 型態之要求 :

  public static boolean insert(String table, HashMap records) {
    String[] fields=new String[records.size()];
    String[] values=new String[records.size()];
    int i=0;
    for (Object key:records.keySet()) {
      fields[i]=key.toString();  //儲存欄位名稱
      values[i]="'" + records.get(key) + "'";  //儲存值
      ++i;
      }
    String SQL="INSERT INTO " + table + "(" + join(fields) +
               ") VALUES(" + join(values) + ")";
    try {stat.executeUpdate(SQL);return true;}
    catch (Exception e) {System.out.println(e);return false;}
    }

這裡呼叫 HashMap 的 keySet() 方法會將 HashMap 的鍵以 Set 集合方式傳回, 然後用 for each 迴圈將 key 與 value 存入字串陣列, 最後呼叫 join() 來組成 SQL 指令. 關於 join() 參見下列實作 :

  public static String join(String[] arr) {
    return join(arr, ",");
    }
  public static String join(String[] arr, String g) {
    if (arr.length==0) {return "";}
    else {
      StringBuilder sb=new StringBuilder();
      sb.append(arr[0]);
      for (int i=1; i<arr.length; i++) {
        sb.append(g).append(arr[i]);
        }
      return sb.toString();
      }
    }

以下範例為在 users 資料表裡插入一筆紀錄 :

import java.util.*;
public class mytest {
  public static void main(String[] args) {
    JT.connectDB("test.mdb");
    JT.addField("users","age","int");  //添加 age 欄位
    HashMap records=new HashMap();
    records.put("name","Tony");
    records.put("gender","male");
    records.put("age","15");  //即使為 int 欄位亦以字串輸入
    System.out.println(JT.insert("users",records)); //輸出 true, 插入成功
    JT.closeDB();
    }
  }

這裡 HashMap 的每一個欄位與其值是用 put() 方法儲存的, 感覺好像不如直接用 runSQL() 方法來得乾脆, 就當作是 HashMap 用法的練習好了. 注意, HashMap 須指定 K-V 型態, 不能用如下宣告 :

HashMap() records=new HashMap();

否則會出現如下錯誤 :

Note: mytest.java uses unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.

參考 :
# Java 中 map 集合的用法    
# Java中Set的使用

# HashMap和Hashtable的区别 以及Hashmap的用法

# Java HashMap question?

本來還想再從 Javascript 函式庫移植 search, update, delete 過來的, 但覺得不僅實作麻煩, 而且不如直接使用 SQL 簡潔快速, 還可以保持對 SQL 語法的熟悉, 因此就此打住, 以後很閒時再說. 

還有兩個函式需要移植到 JT.java, 就是刪除欄位與資料表的 dropField() 與 dropTable() :

  public static boolean dropField(String table, String field) {
    String SQL="ALTER TABLE " + table + " DROP " + field;
    try {stat.executeUpdate(SQL);}
    catch (Exception e) {System.out.println(e);return false;}
    return true;
    }
  public static boolean dropTable(String table) {
    try {stat.executeUpdate("DROP TABLE " + table);}
    catch (Exception e) {System.out.println(e);return false;}
    return true;
    }

我們就以刪除上例中 users 資料表新增的 age 欄位與刪除 books 資料表為例, 範例如下 :

import java.util.*;
public class mytest {
  public static void main(String[] args) {
    JT.connectDB("test.mdb");
    System.out.println(Arrays.deepToString(JT.getFieldNames("users")));
    System.out.println(JT.dropField("users","age"));
    System.out.println(Arrays.deepToString(JT.getFieldNames("users")));
    System.out.println(Arrays.deepToString(JT.getTables()));
    System.out.println(JT.dropTable("users"));
    System.out.println(Arrays.deepToString(JT.getTables()));
    JT.closeDB();
    }
  }

執行結果如下 :

[name, id, gender, age]  (刪除 age 欄位前)
true
[name, id, gender]  (欄位 age 被刪除了)
[books, test, users, zins]  (刪除資料表 users 之前)
true
[books, test, zins]  (資料表 users 被刪除了)

OK, 常用的 ACCESS 函式庫到此大功告成, 明天要向 putty 進軍了.

# How to find whether a ResultSet is empty or not in Java?