# 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
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
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
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
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;
}
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++) {
}
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]
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 型態之要求 :
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;}
}
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();
}
}
public class mytest {
public static void main(String[] args) {
JT.connectDB("test.mdb");
JT.addField("users","age","int"); //添加 age 欄位
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() records=new HashMap();
否則會出現如下錯誤 :
Note: mytest.java uses unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.
# Java中Set的使用
# 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?
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;
}
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();
}
}
true
[name, id, gender] (欄位 age 被刪除了)
[books, test, users, zins] (刪除資料表 users 之前)
true
[books, test, zins] (資料表 users 被刪除了)
OK, 常用的 ACCESS 函式庫到此大功告成, 明天要向 putty 進軍了.
#
沒有留言 :
張貼留言