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

晚上整理書房, 把一堆已經不會再看的書裝箱帶回鄉下, 例如線性代數, 機率論, 工程數學等等, 現在還要看這些書幹啥? 只是徒然佔據書架空間而已. 如果可以挖個洞的話, 真想把這些書放進時空膠囊裡埋藏地下呢! 這些都是唸電機必修的教科書, 但老實說, 除了當教授外, 一點屁用都沒有. 就算是做統計, 其實也不需要先讀機率論, 因為都是一些枯燥乏味的定理證明.

扯遠了, 幹完雜事, 看到架上的 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 "" at line 1, colum
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