扯遠了, 幹完雜事, 看到架上的 Java, 又想起 Derby 資料庫還沒搞定 (上回只是用 ij 指令玩玩而已). 參考 :
# Java 資料庫存取 : 使用 Derby
於是乎把書上的範例檔原封不動執行看看 :
import java.sql.*;
public class mytest {
public static void main(String[] args) {
String dsn="jdbc:derby:testdb;create=true";
String user="", pwd="";
try {
//Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); //不需要
Connection conn=DriverManager.getConnection(dsn, user, pwd);
Statement stat=conn.createStatement();
System.out.println("Derby connected!");
conn.close();
}
catch (Exception e) {System.out.println(e);}
}
}
執行結果 :
H:\Java\JT>java mytest
java.lang.ClassNotFoundException: org.apache.derby.jdbc.EmbeddedDriver
找不到 JDK 的內建 Derby 驅動程式, 原來是必須在環境變數 CLASSPATH 中加入 JDK 安裝目錄下的 db\lib\derby.jar 才會載入驅動程式, 參考 :
# Configure Embedded Derby
以我的電腦而言, 是在 C:\Program Files (x86)\Java\jdk1.7.0_55\db\lib\derby.jar :
設定好後按確定, 一定要重新開啟命令提示字元視窗才會套用新的環境變數, 再次執行上面的程式就 ok 了 :
H:\Java\JT>java mytest
Derby connected!
注意, Class.forName() 不需要設定也是可以的 (對於 ACCESS 的 MDB 檔案也是一樣, 都不需要了). 接下來就可以開始操作資料庫了, 參考 Derby 的 SQL 手冊 :
# http://db.apache.org/derby/docs/10.6/ref/
# Derby Data types
下面的範例是新增資料表與紀錄 :
import java.sql.*;
public class mytest {
public static void main(String[] args) {
String dsn="jdbc:derby:testdb;create=true";
String user="", pwd="";
String SQL;
try {
Connection conn=DriverManager.getConnection(dsn, user, pwd);
Statement stat=conn.createStatement();
System.out.println("Derby connected!");
SQL="CREATE TABLE users(name VARCHAR(20),age SMALLINT)";
stat.executeUpdate(SQL);
SQL="INSERT INTO users(name,age) VALUES ('愛咪',12)," +
"('彼得',14),('凱莉',16)";
stat.executeUpdate(SQL);
System.out.println("Records inserted!");
conn.close();
}
catch (Exception e) {System.out.println(e);}
}
}
打開另一個 DOS 視窗用 ij 去查詢資料表, 可見紀錄已寫入資料表中 :
G:\Java\JT>ij
ij 版本 10.8
ij> connect 'jdbc:derby:testdb';
ij> select * from users;
NAME |AGE
---------------------------
愛咪 |12
彼得 |14
凱莉 |16
已選取 3 列
注意, ij 介面用完需退出, 否則程式無法連線, 出現下列錯誤 :
G:\Java\JT>java mytest
java.sql.SQLException: 無法使用類別載入器 sun.misc.Launcher$AppClassLoader@e3280
2 啟動資料庫 'testdb',請參閱下個異常狀況,以取得詳細資料。
下面的範例是用 ResultSet 來儲存查詢資料 :
import java.sql.*;
public class mytest {
public static void main(String[] args) {
String dsn="jdbc:derby:testdb;create=true";
String user="", pwd="";
String SQL;
try {
Connection conn=DriverManager.getConnection(dsn, user, pwd);
Statement stat=conn.createStatement();
SQL="SELECT * FROM users";
ResultSet rs=stat.executeQuery(SQL);
ResultSetMetaData md=rs.getMetaData();
int columns=md.getColumnCount();
for (int i=1; i<=columns; i++) {
System.out.printf(md.getColumnLabel(i) + "\t");
}
System.out.println("");
while (rs.next()) { //拜訪全部 ReseltSet 紀錄
for (int i=1; i<=columns; i++) {
System.out.print(rs.getString(i) + "\t");
}
System.out.println("");
}
}
catch (Exception e) {System.out.println(e);}
}
}
G:\Java\JT<java mytest
NAME AGE
愛咪 12
彼得 14
凱莉 16
下面的範例是利用 ResultSetMetaData 顯示資料表的欄位資訊 :
import java.sql.*;
public class mytest {
public static void main(String[] args) {
String dsn="jdbc:derby:testdb;create=true";
String user="", pwd="";
String SQL;
try {
Connection conn=DriverManager.getConnection(dsn, user, pwd);
Statement stat=conn.createStatement();
SQL="SELECT * FROM users";
ResultSet rs=stat.executeQuery(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));
}
conn.close();
}
catch (Exception e) {System.out.println(e);}
}
}
G:\Java\JT>java mytest
欄位名稱 資料型態 顯示大小 自動增量 可不填
NAME VARCHAR 20 false 1
AGE SMALLINT 6 false 1
看來是 OK 的, 下面將修改 JT.java 常用函式庫, 把 Derby 納入 :
import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.text.*;
import java.util.regex.*;
import java.security.MessageDigest;
public class JT {
static Connection conn;
static Statement stat;
public static void connectMDB(String db) {
connectMDB(db, "", "");
}
public static void connectMDB(String db, String user, String pwd) {
try {
String dsn="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};" +
"DBQ=" + db + ";READONLY=false}";
conn=DriverManager.getConnection(dsn, user, pwd);
stat=conn.createStatement();
}
catch (Exception e) {System.out.println(e);}
}
public static void connectDerby(String db){
connectDerby(db, "", "");
}
public static void connectDerby(String db, String user, String pwd){
try {
String dsn="jdbc:derby:" + db + ";create=true";
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;
}
}
這樣就可以用 JT 類別來縮短程式碼了 :
import java.sql.*;
public class derby1 {
public static void main(String[] args) {
JT.connectDerby("testdb");
try {
ResultSet rs=JT.runSQL("SELECT * FROM users");
ResultSetMetaData md=rs.getMetaData();
int columns=md.getColumnCount();
for (int i=1; i<=columns; i++) {
System.out.printf(md.getColumnLabel(i) + "\t");
}
System.out.println("");
while (rs.next()) { //拜訪全部 ReseltSet 紀錄
for (int i=1; i<=columns; i++) {
System.out.print(rs.getString(i) + "\t");
}
System.out.println("");
}
}
catch(Exception e) {System.out.println(e);}
JT.closeDB();
}
}
只要把 JT.connectMDB() 改為 JT.connectDerby() 就可以立刻改為連線 Derby 資料庫了.
接下來要參考上回 ACCESS 那篇來逐一測試那些為 ACCESS 寫的函式是否需要修改 :
# Java 資料庫存取 : 使用 ACCESS (續)
先來試試 dropTable(), 把上面建立的 users 資料表刪除後再重建 :
import java.sql.*;
public class derby2 {
public static void main(String[] args) {
JT.connectDerby("testdb");
System.out.println(JT.dropTable("users")); //輸出 true (成功刪除)
JT.closeDB();
}
}
可見資料表已經成功刪除了. 如果再次執行 java derby2, 就會報錯, 因為 users 已經不存在啦 :
H:\Java\JT<java derby2
java.sql.SQLSyntaxErrorException: 因為 'DROP TABLE' 不存在,所以無法在 'USERS'
上執行它。
false
# how to create table if it doesnt exist using Derby Db
# Creating the database and running SQL
# Java DB (Derby) - Creating New Tables
# Derby "create table if not exists"
但原先給 ACCESS 用的 JT.createTable() 在 Derby 不能用, 使用原先的 JT.createTable("users") 會出現語法錯誤 :
java.sql.SQLSyntaxErrorException: 語法錯誤:Encountered "
n 18。
看來 Derby 不能像 ACCESS 那樣先用 CREATE TABLE users 建立一個空的 users 資料表, 而是必須至少指定一個欄位才行, 所以我修改了 JT.java, 添加一個可傳入欄位定義參數的多載方法 :
public static boolean createTable(String table) { //僅 ACCESS 使用, Derby 不能用
try {stat.executeUpdate("CREATE TABLE " + table);}
catch (Exception e) {System.out.println(e);return false;}
return true;
}
public static boolean createTable(String table, String columns) { //ACCESS, Derby 均可用
try {stat.executeUpdate("CREATE TABLE " + table + " (" + columns + ")");}
catch (Exception e) {System.out.println(e);return false;}
return true;
}
參考 Derby 的欄位型態定義如下 :
# Derby Data types
我們在 ACCESS 資料庫常使用的欄位型態與 Derby 幾乎相同, 差異最大的是作為流水號的自動增量主鍵, 在 ACCESS 是用 :
id AUTOINCREMENT PRIMARY KEY
但在 Derby 就比較囉唆, 如下是指定 id 欄位為自動增量主鍵, 其鍵名為 primary_key (各資料表之主鍵名為唯一, 須各自命名) :
id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT primary_key PRIMARY KEY
其次是儲存較長文字的欄位, 在 ACCESS 是用 MEMO, 可儲存 65536 個字元; 但在 Derby 則是使用 LONG VARCHAR, 可儲存 32700 個字元, 僅為 ACCESS 的一半. 其他常用形態, SMALLINT, CHAR, VARCHAR, DATE 等用法都一樣. 故原來為 ACCESS 寫的 JT.addField() 不用修改, 還是可用 :
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;
}
下面範例即利用 addField() 於 createTable() 後添加欄位 :
import java.sql.*;
import java.util.*;
public class derby3 {
public static void main(String[] args) {
JT.connectDerby("testdb");
String col="id INT NOT NULL GENERATED ALWAYS AS IDENTITY " +
"CONSTRAINT users_key PRIMARY KEY,gender CHAR(1)";
System.out.println(JT.createTable("users", col)); //第一個資料表 users
System.out.println(JT.addField("users","name","VARCHAR(20)")); //輸出 true
System.out.println(JT.addField("users","height","FLOAT")); //輸出 true
System.out.println(JT.addField("users","age","SMALLINT")); //輸出 true
System.out.println(JT.addField("users","birthday","DATE")); //輸出 true
System.out.println(JT.addField("users","remark","LONG VARCHAR"));
col="id INT NOT NULL GENERATED ALWAYS AS IDENTITY " +
"CONSTRAINT books_key PRIMARY KEY,gender CHAR(1)";
System.out.println(JT.createTable("books", col)); //第二個資料表 books
System.out.println(JT.addField("books","name","VARCHAR(50)")); //輸出 true
try {
ResultSet rs=JT.runSQL("SELECT * FROM users");
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);}
System.out.println(Arrays.deepToString(JT.getTables()));
JT.closeDB();
}
}
執行結果 :
true
true
true
true
true
true
true
true
欄位名稱 資料型態 顯示大小 自動增量 可不填
ID INTEGER 11 true 0
GENDER CHAR 1 false 1
NAME VARCHAR 20 false 1
HEIGHT DOUBLE 22 false 1
AGE SMALLINT 6 false 1
BIRTHDAY DATE 10 false 1
REMARK LONG VARCHAR 32700 false 1
[USERS,BOOKS]
可見 float 最終也是以 double 儲存. 上例也顯示 getTables() 函式不需要修改, 可正常顯示所建立的兩個資料表.
接著要把資料寫進資料表中 :
import java.sql.*;
public class derby4 {
public static void main(String[] args) {
JT.connectDerby("testdb");
String SQL="INSERT INTO users(name,gender,age,height,remark) VALUES " +
"('愛咪','女',12,157,'Amy'),('彼得','男',14,171,'Peter')," +
"('凱莉','女',16,165,'Kelly')";
System.out.println(JT.runSQL(SQL)); //輸出 null
try {
ResultSet rs=JT.runSQL("SELECT * FROM users");
ResultSetMetaData md=rs.getMetaData();
int columns=md.getColumnCount();
for (int i=1; i<=columns; i++) {
System.out.printf(md.getColumnLabel(i) + "\t");
}
System.out.println("");
while (rs.next()) { //拜訪全部 ReseltSet 紀錄
for (int i=1; i<=columns; i++) {
System.out.print(rs.getString(i) + "\t");
}
System.out.println("");
}
}
catch (Exception e) {System.out.println(e);}
JT.closeDB();
}
}
結果輸出 :
null
ID GENDER NAME HEIGHT AGE BIRTHDAY REMARK
1 女 愛咪 157.0 12 null Amy
2 男 彼得 171.0 14 null Peter
3 女 凱莉 165.0 16 null Kelly
此處我們一次寫入三筆紀錄, 每筆用逗號隔開. 下面繼續測試 dropField(), FindTable() 等方法 :
import java.sql.*;
import java.util.*;
public class derby5 {
public static void main(String[] args) {
JT.connectDerby("testdb");
System.out.println(JT.findTable("users")); //輸出 false
System.out.println(JT.findTable("user")); //輸出 false
System.out.println(Arrays.deepToString(JT.getFieldNames("users")));
System.out.println(Arrays.deepToString(JT.getFieldTypes("users")));
System.out.println(Arrays.deepToString(JT.getFieldSizes("users")));
System.out.println(JT.getFieldType("users","name"));
System.out.println(JT.getFieldSize("users","name")); //輸出 0
System.out.println(JT.findField("users","gender")); //輸出 false
System.out.println(JT.findField("users","email")); //輸出 false
System.out.println(JT.isTextField("users","gender")); //輸出 false
System.out.println(JT.isTextField("users","age")); //輸出 false
JT.closeDB();
}
}
輸出結果 :
false
false
[ID, GENDER, NAME, HEIGHT, AGE, BIRTHDAY, REMARK]
[INTEGER, CHAR, VARCHAR, DOUBLE, SMALLINT, DATE, LONG VARCHAR]
[11, 1, 20, 22, 6, 10, 32700]
0
false
false
false
false
明明有 "users" 資料表, 怎會輸出 false 呢? 觀察其輸出可發現, Derby 不管你的資料表或欄位名稱用大寫或小寫, 它都一律改成大寫, 如下修改 derby5.java 即知 :
import java.sql.*;
import java.util.*;
public class derby5 {
public static void main(String[] args) {
JT.connectDerby("testdb");
System.out.println(JT.findTable("USERS")); //輸出 true
System.out.println(JT.findTable("USER")); //輸出 false
System.out.println(Arrays.deepToString(JT.getFieldNames("USERS")));
System.out.println(Arrays.deepToString(JT.getFieldTypes("USERS")));
System.out.println(Arrays.deepToString(JT.getFieldSizes("USERS")));
System.out.println(JT.getFieldType("USERS","NAME"));
System.out.println(JT.getFieldSize("USERS","NAME"));
System.out.println(JT.findField("USERS","GENDER")); //輸出 true
System.out.println(JT.findField("USERS","EMAIL")); //輸出 FALSE
System.out.println(JT.isTextField("USERS","GENDER")); //輸出 true
System.out.println(JT.isTextField("USERS","AGE")); //輸出 FALSE
JT.closeDB();
}
}
輸出結果 :
true
false
[ID, GENDER, NAME, HEIGHT, AGE, BIRTHDAY, REMARK]
[INTEGER, CHAR, VARCHAR, DOUBLE, SMALLINT, DATE, LONG VARCHAR]
[11, 1, 20, 22, 6, 10, 32700]
VARCHAR
20
true
false
true
false
我不打算修改 JT.java 來處理 Derby 大寫問題, 只要記得使用 Derby 時, table 與 field 名稱都要用大寫來判斷. 下面是 UPDATE 與 DELETE 的測試 :
import java.sql.*;
import java.util.*;
public class derby6 {
public static void main(String[] args) {
JT.connectDerby("testdb");
String SQL="UPDATE users SET remark='凱莉',name='Kelly' WHERE id=3";
System.out.println(JT.runSQL(SQL)); //輸出 null
SQL="DELETE FROM users WHERE id=2";
System.out.println(JT.runSQL(SQL)); //輸出 null
try {
ResultSet rs=JT.runSQL("SELECT * FROM users");
ResultSetMetaData md=rs.getMetaData();
int columns=md.getColumnCount();
for (int i=1; i<=columns; i++) {
System.out.printf(md.getColumnLabel(i) + "\t");
}
System.out.println("");
while (rs.next()) { //拜訪全部 ReseltSet 紀錄
for (int i=1; i<=columns; i++) {
System.out.print(rs.getString(i) + "\t");
}
System.out.println("");
}
}
catch (Exception e) {System.out.println(e);}
JT.closeDB();
}
}
輸出結果 :
null
null
ID GENDER NAME HEIGHT AGE BIRTHDAY REMARK
1 女 愛咪 157.0 12 null Amy
3 女 Kelly 165.0 16 null 凱莉
注意, 這裡我們一次更新兩個欄位之值, 中間用逗號隔開. 其次, Derby 的 DELETE 語法不可像 ACCESS 那樣加 * 號. 最後測試 dropField() 與 dropTable() :
import java.sql.*;
import java.util.*;
public class derby7 {
public static void main(String[] args) {
JT.connectDerby("testdb");
System.out.println(JT.dropField("users","remark")); //輸出 true
System.out.println(JT.dropTable("books")); //輸出 true
try {
ResultSet rs=JT.runSQL("SELECT * FROM users");
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);}
System.out.println(Arrays.deepToString(JT.getTables()));
JT.closeDB();
}
}
輸出結果 :
true
true
欄位名稱 資料型態 顯示大小 自動增量 可不填
ID INTEGER 11 true 0
GENDER CHAR 1 false 1
NAME VARCHAR 20 false 1
HEIGHT DOUBLE 22 false 1
AGE SMALLINT 6 false 1
BIRTHDAY DATE 10 false 1
[USERS]
可見 users 資料表的 remark 欄位被刪除了; books 資料表也被刪除了. Derby 測試結束.
參考資料 :
# Apache Derby: Documentation
# Step 2: ij Basics
# Apache Derby: Quick Start
# dblook examples
# Connecting to database
沒有留言 :
張貼留言