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

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

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

hsqldb1.png

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的數據及其它相關的資料內容

發表迴響