2014年4月28日 星期一

Java 資料庫存取 : 使用 MySQL

處理完 Java 存取 ACCESS 與 Derby 資料庫的測試, 還有一個常用的資料庫就是 MySQL. 學 PHP 時都會安裝 Apache+PHP+MySQL 套件, 因此電腦裡應該都有 MySQL 伺服器在跑, 如果沒有刻意關掉的話, 一開機就已經開啟服務, 所以相當於有一個現成的資料庫可用, 而且附有 phpMyAdmin 這個好用的 GUI 介面, 用瀏覽器就能管理資料庫. 我想測試完 MySQL 後做個效能評比. 以下所用到的 JT 類別方法詳見之前文章, 此處不再重複 :

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

MySQL 資料庫的操作與 Derby, ACCESS 類似, 但有一些 SQL 方言上的差異, 例如 ACCESS 刪除記錄是用 DELETE * FROM, 而 Derby 與 MySQL 則不需星號. 因此我想直接修改 JT.java 類別增加 connectMySQL() 連線方法如下 :

  public static void connectMySQL(String db, String user, String pwd){  //預設 localhost 
    connectMySQL(db, user, pwd, "localhost");
    }
  public static void connectMySQL(String db, String user, String pwd, String host) {
    try {
      //Class.forName("com.mysql.jdbc.Driver");  //不指定亦可
      String dsn="jdbc:mysql://" + host + ":3306/" + db;
      conn=DriverManager.getConnection(dsn, user, pwd);
      stat=conn.createStatement();
      }
    catch (Exception e) {System.out.println(e);}
    }

這裡我是利用安裝 AppServ 套件時所安裝的 MySQL 來用, MySQL 埠號為 3306, 因此只要將連線字串指向 Localhost 下的 3306 port 即可. 還有一點是 MySQL 安裝時需設定帳號密碼, 連線時也必須傳入帳號密碼, 因此此處不需為預設無帳號密碼多加一個方法多載. 反倒是 MySQL 伺服器不一定位於本機, 因此要多加一個參數 host, 預設為 localhost.

首先進入 phpMyAdmin 介面新增一個資料庫 testdb :

CREATE DATABASE `testdb` DEFAULT CHARACTER SET big5 COLLATE big5_chinese_ci;

這樣就可以在 testdb 新增資料庫了. 跟 Derby 一樣, MySQL 不允許先建立一個無欄位的空資料表 (只有 ACCESS 可以這樣), 也就是說下列這指令在 MySQL 無法執行 :

CRETATE DATABASE users

出現下列錯誤 :

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: A table must have at
least 1 column

至少須先指定一個欄位, 其餘欄位再用 ALTER TABLE 來添加, 也就是我們的 JT.addField() 方法. 在 ACCESS 中常用的資料型態在 MySQL 中都有, 而且還更多, 但我們常用的還是 ACCESS 中的那幾個, 參見 :

# CREATE TABLE Syntax

在新建資料表時, 主要的 SQL 語法差異有兩個, 一是通常作為 id 流水號的自動增量主鍵, 在 MySQL 要這樣設 :

id INT NOT NULL  AUTO_INCREMENT PRIMARY KEY

下列範例中, 我們在

import java.sql.*;
import java.util.*;
public class mysql1 {
  public static void main(String[] args) {  
    JT.connectMySQL("testdb","root","mysql","127.0.0.1");
    String col="id INT NOT NULL AUTO_INCREMENT PRIMARY KEY";
    System.out.println(JT.createTable("users",col)); //第一個資料表 users
    System.out.println(JT.addField("users","name","VARCHAR(20)"));
    System.out.println(JT.addField("users","gender","CHAR(1)"));
    System.out.println(JT.addField("users","height","FLOAT")); 
    System.out.println(JT.addField("users","age","SMALLINT")); 
    System.out.println(JT.addField("users","birthday","DATE"));
    System.out.println(JT.addField("users","remark","LONGTEXT"));
    col="id INT NOT NULL AUTO_INCREMENT PRIMARY KEY";
    System.out.println(JT.createTable("books", col)); //第二個資料表 books
    System.out.println(JT.addField("books","name","VARCHAR(50)"));
    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
true
欄位名稱        資料型態        顯示大小        自動增量        可不填
id      INT     11      true    0
name    VARCHAR 20      false   1
gender  CHAR    1       false   1
height  FLOAT   12      false   1
age     SMALLINT        6       false   1
birthday        DATE    10      false   1
remark  VARCHAR 1073741823      false   1
[books, users]

SQL 方言的第二個差異是儲存較長文字的欄位, 在 ACCESS 為 MEMO (65535 字元), 在 Derby 為 LONG VARCHAR, 在 MySQL 則有 LONGTEXT (短一些用 MEDIUMTEXT 或 TINYTEXT), 但我試過用 Derby 的 LONG VARCHAR 也可以, 字元長度為 8388607, 與 MEDIUMTEXT 一樣大.

資料表建好後, 接下來就可以插入紀錄了 :

import java.sql.*;
public class mysql2 {
  public static void main(String[] args) {
    JT.connectMySQL("testdb","root","mysql");
    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      name    gender  height  age     birthday        remark
1       愛咪    女      157     12      null    Amy
2       彼得    男      171     14      null    Peter
3       凱莉    女      165     16      null    Kelly

接著測試資料表相關方法如下 :

import java.sql.*;
import java.util.*;
public class mysql3 {
  public static void main(String[] args) {
    JT.connectMySQL("testdb","root","mysql");
    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")); //輸出 VARCHAR
    System.out.println(JT.getFieldSize("users","name"));  //輸出 20
    System.out.println(JT.findField("users","gender"));     //輸出 false
    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, name, gender, height, age, birthday, remark]
[INT, VARCHAR, CHAR, FLOAT, SMALLINT, DATE, VARCHAR]
[11, 20, 1, 12, 6, 10, 8388607]
VARCHAR
20
false
false
true
false

可見 MySQL 傳回的欄位名稱不像 Derby 那樣一律為大寫, 但欄位型態則一律大寫. 很奇怪的是, 明明有 gender 這個欄位, 但 findField() 卻傳回 false, WHY?

下面是 UPDATE 與 DELETE 的操作 :

import java.sql.*;
import java.util.*;
public class mysql4 {
  public static void main(String[] args) {
    JT.connectMySQL("test","root","mysql");
    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      name    gender  height  age     birthday        remark
1       愛咪    女      157     12      null    Amy
3       Kelly   女      165     16      null    凱莉

這裡要注意, MySQL 與 Derby 一樣, 在 DELETE 指令不可以用 DELETE * FROM, 要去掉 * 號最後測試 dropField() 與 dropTable() :

import java.sql.*;
import java.util.*;
public class mysql5 {
  public static void main(String[] args) {
    JT.connectMySQL("test","root","mysql");
    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      INT     11      true    0
name    VARCHAR 20      false   1
gender  CHAR    1       false   1
height  FLOAT   12      false   1
age     SMALLINT        6       false   1
birthday        DATE    10      false   1
[users]

可見欄位 remark 與資料表 books 都成功刪除了. 除了上面 gender 欄位用 findField() 傳回 false 的問題待釐清外, Java 資料庫測試至此全部結束.


沒有留言 :