[Java]JDBC連線操作HSQLDB資料庫的範例

hsqldb是個不用安裝也不用啟動的database資料庫，在openoffice裡的base資料庫也是使用此種資料庫，只是再把檔案入放進一個壓縮檔裡而已，所以如果想在openoffice裡操作base資料庫，需要先把檔案解開（在程式裡），再取裡面的資料載來做操作，因為不需要安裝，所以在使用jdbc連線後，如需關閉資料庫，需要使用傳送SQL語法使關閉Statement.execute(“SHUTDOWN”);。

底下有一個簡單的範裡，包含了新增、刪除、查詢、建立資料表及刪除資料表等操作，範例如果如下：

1.先下載jdbc driver

http://sourceforge.net/project/showfiles.php?group_id=23316

解壓縮後把資料夾裡的lib/hsqldb.jar放到classpath裡

2.測試的程式碼

package db;

//import org.hsqldb.jdbcDriver;
import java.sql.*;

public class Ooo {
    private Connection con = null; // Database objects

    private Statement stat = null;
    private ResultSet rs = null;
    private PreparedStatement pst = null;

    private String dropdbSQL = "DROP TABLE User1 IF EXISTS ";

    private String createdbSQL = "CREATE TABLE User1 (  id     INTEGER identity , name    VARCHAR(500)   , passwd  VARCHAR(500))";

    private String insertdbSQL = "insert into User1(id,name,passwd) "
            + "values(?,?,?)";

    private String selectMaxId = "select (nvl(max(id),0)+1) as max_id FROM User1";
    private String selectSQL = "select * from User1 ";

    public Ooo() {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
            con = DriverManager.getConnection("jdbc:hsqldb:file:database",
                    "SA", "");

        } catch (ClassNotFoundException e) {
            System.out.println("DriverClassNotFound :" + e.toString());
        } catch (SQLException x) {
            System.out.println("Exception :" + x.toString());
        }

    }

    public static java.sql.Connection getConnection() {
        java.sql.Connection con = null;
        try {
            Class.forName("org.hsqldb.jdbcDriver");
            con = DriverManager.getConnection("jdbc:hsqldb:file:database",
                    "SA", "");

        } catch (ClassNotFoundException e) {
            System.out.println("DriverClassNotFound :" + e.toString());
        } catch (SQLException x) {
            System.out.println("Exception :" + x.toString());
        }
        return con;
    }

    public void createTable() {
        try {
            stat = con.createStatement();
            stat.executeUpdate(createdbSQL);

            // con.prepareStatement("GRANT select ON User1 TO public;"+
            // "GRANT all ON User1 TO public;"
            // ).executeUpdate();
        } catch (SQLException e) {
            System.out.println("CreateDB Exception :" + e.toString());
        } finally {
            Close();
        }
    }

    private int idx = 1;

    public void insertTable(String name, String passwd) {
        try {
            pst = con.prepareStatement(selectMaxId);
            rs = pst.executeQuery();
            int id = 0;
            if (rs.next()) {
                id = rs.getInt("max_id");
            }
            pst = con.prepareStatement(insertdbSQL);
            pst.setInt(1, id);
            pst.setString(2, name);
            pst.setString(3, passwd);
            // pst.setInt(3, idx++);
            pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("InsertDB Exception :" + e.toString());
        } finally {
            Close();
        }
    }

    public void dropTable() {
        try {
            stat = con.createStatement();
            stat.executeUpdate(dropdbSQL);
        } catch (SQLException e) {
            System.out.println("DropDB Exception :" + e.toString());
        } finally {
            Close();
        }
    }

    public void SelectTable() {
        try {
            stat = con.createStatement();
            rs = stat.executeQuery(selectSQL);
            System.out.println("ID\t\tName\t\tPASSWORD");
            while (rs.next()) {
                System.out.println(rs.getInt("id") + "\t\t"
                        + rs.getString("name") + "\t\t"
                        + rs.getString("passwd"));
            }
        } catch (SQLException e) {
            System.out.println("DropDB Exception :" + e.toString());
        } finally {
            Close();
        }
    }

    private void Close() {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stat != null) {
                stat.close();
                stat = null;
            }
            if (pst != null) {
                pst.close();
                pst = null;
            }
        } catch (SQLException e) {
            System.out.println("Close Exception :" + e.toString());
        }
    }

    public void shutdown() {

        try {
            stat = con.createStatement();
            stat.execute("SHUTDOWN");
            // if there are no other open connection
        } catch (java.sql.SQLException e) {
            System.out.println(e.toString());
        } finally {
            Close();
            if (con != null) {
                try {
                    con.close();
                } catch (java.sql.SQLException e) {
                    System.out.println(e.toString());
                }
                con = null;
            }

        }
    }

    public static void main(String[] args) {
        Ooo test = new Ooo();
        test.dropTable();
        test.createTable();
        test.insertTable("yku", "12356");
        test.insertTable("yku2", "7890");
        test.SelectTable();
        test.shutdown();

    }
}

3.其它補充部份

HSQLDB.jar包含以下

HSQLDB RDBMS	Database引擎
HSQLDB JDBC Driver	JDBC Driver
Database Manager	管理工具
Query Tool (AWT)	查詢工具
Sql Tool (command line)	SQL工具

HSQLDB.jar可執行的Main-Class如下

	org.hsqldb.util.DatabaseManager 
	org.hsqldb.util.DatabaseManagerSwing
	org.hsqldb.util.Transfer
	org.hsqldb.util.QueryTool 
	org.hsqldb.util.SqlTool

執行的方法

java -cp ./lib/hsqldb.jar org.hsqldb.util.DatabaseManager

檔案說明:

副檔名為properties是hsqldb的基本設定檔

副檔名為script是hsqldb的文件包含了Table定義、non-cached table的數據及其它相關的資料內容