# Java 程式連線 ACCESS 資料庫問題
我用的資料庫仍然是 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 資料庫有下列幾種型態 :
- VARCHAR (可變長度字串)
- CHAR (固定長度字串)
- SMALLINT (整數)
- INT (長整數)
- FLOAT (浮點數)
- TEXT (文字, 最大 255 字元)
- MEMO (備忘, 最大 65535 字元)
- DATE (日期)
- YESNO (是否)
- CURRENCY (貨幣)
- AUTOINCREMENT PRIMARY KEY (自動增量主鍵)
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();
}
}
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) 了.
~未完待續~
參考資料
沒有留言:
張貼留言