使用java寫單機小程序或者測試項目的時候，使用hsqldb是一個不錯的選擇。
介紹可以看這里：
http://www.javaeye.com/topic/78887
http://hi.baidu.com/mum0532/blog/item/b08c0c6d88e0faf9431694e5.html
從官網(wǎng)下載的壓縮包，解壓后的demo目錄中，可以運行runManagerSwing，打開可視化管理界面（但沒有可視化創(chuàng)建表，創(chuàng)建字段的功能）
連接的時候，先選擇server模式，然后在下圖的紅框處輸入啟動服務(wù)的端口和數(shù)據(jù)庫名稱。例如jdbc:hsqldb:hsql://localhost:9002/test
這里9002是我程序啟動hsqldb的時候使用的端口，test是建立的數(shù)據(jù)庫名稱
HSQLDB創(chuàng)建數(shù)據(jù)庫和基本的數(shù)據(jù)庫訪問：
http://hi.baidu.com/hivemind/blog/item/83873bdf36611c1462279825.html
HSLQDB的sql語法跟MySQL的略有不同，使得很郁悶啊~~開始我都不知道怎么初始化HSQLdb的數(shù)據(jù)
HSQLDB和Hibernate結(jié)合的一個小例子：
http://hi.baidu.com/hivemind/blog/item/2c77fb00830e5a16738b6506.html
我自己寫的一個Java類：（只需要加入hsqldb.jar即可）
/** * 方便單機程序使用HSQL的工具類，包括啟動，關(guān)閉，連接。數(shù)據(jù)庫默認不加密，用戶為sa，密碼空 * @author 鄭高強 */public class HSQL_Util { public static final int PORT = 9002; public static final String DB_NAME = "kenko"; //數(shù)據(jù)庫文件名，同時也是本類中的數(shù)據(jù)庫名 public static final String DB_PATH = "./db/"; public static final String USER_NAME = "sa"; public static final String PASSWORD = ""; public static final int SERVER_MODE = 0; public static final int STAND_ALONE_MODE = 1; //In-Process public static int mode = SERVER_MODE; //記錄當前用什么模式，開發(fā)時用Server，發(fā)布時用standalone /** * 啟動數(shù)據(jù)庫服務(wù) */ public static boolean startHSQL() { if (mode == SERVER_MODE) { Server server = new Server();//它可是hsqldb.jar里面的類啊。 server.setDatabaseName(0, DB_NAME); server.setDatabasePath(0, DB_PATH + DB_NAME); server.setPort(PORT); server.setSilent(true); server.start(); //自動多線程運行 System.out.println("hsqldb started..."); } else if (mode == STAND_ALONE_MODE) { //standalone模式，打開連接就同時啟動數(shù)據(jù)庫，所以這里可以什么都不做 } try { Thread.sleep(800); // 等待Server啟動 } catch (InterruptedException e) { } return true; } /** * 關(guān)閉數(shù)據(jù)庫服務(wù) */ public static boolean stopHSQL() { try { Statement statement = getConnection().createStatement(); statement.executeUpdate("SHUTDOWN;"); return true; } catch (SQLException ex) { Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex); return false; } } /** * 獲取連接 */ public static Connection getConnection() { Connection conn = null; try { Class.forName("org.hsqldb.jdbcDriver"); if (mode == SERVER_MODE) { conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:" + PORT + "/" + DB_NAME, USER_NAME, PASSWORD); } else if (mode == STAND_ALONE_MODE) { conn = DriverManager.getConnection("jdbc:hsqldb:file:" + DB_PATH + DB_NAME, USER_NAME, PASSWORD); } } catch (ClassNotFoundException ex) { Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex); } return conn; } /** * 測試 */ public static void main(String[] args) { HSQL_Util.mode = HSQL_Util.STAND_ALONE_MODE; HSQL_Util.startHSQL(); Connection conn = HSQL_Util.getConnection(); try { Statement statement = getConnection().createStatement(); statement.executeUpdate("create table customer(id integer not null primary key,firstname varchar,lastname varchar)"); for (int i = 10; i < 20; i++) { statement.executeUpdate("insert into customer values(" + i + ",'liu','zhaoyang')"); } statement.close(); } catch (SQLException ex) { Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex); } HSQL_Util.stopHSQL(); }}
/** * 方便單機程序使用HSQL的工具類，包括啟動，關(guān)閉，連接。數(shù)據(jù)庫默認不加密，用戶為sa，密碼空
* @author 鄭高強 */ public class HSQL_Util { public static final int PORT = 9002; public static final String DB_NAME = "kenko"; //數(shù)據(jù)庫文件名，同時也是本類中的數(shù)據(jù)庫名 public static final String DB_PATH = "./db/"; public static final String USER_NAME = "sa"; public static final String PASSWORD = ""; public static final int SERVER_MODE = 0; public static final int STAND_ALONE_MODE = 1; //In-Process public static int mode = SERVER_MODE; //記錄當前用什么模式，開發(fā)時用Server，發(fā)布時用standalone /** * 啟動數(shù)據(jù)庫服務(wù) */ public static boolean startHSQL() { if (mode == SERVER_MODE) {
Server server = new Server();//它可是hsqldb.jar里面的類啊。 server.setDatabaseName(0, DB_NAME);
server.setDatabasePath(0, DB_PATH + DB_NAME);
server.setPort(PORT);
server.setSilent(true);
server.start(); //自動多線程運行 System.out.println("hsqldb started...");
} else if (mode == STAND_ALONE_MODE) { //standalone模式，打開連接就同時啟動數(shù)據(jù)庫，所以這里可以什么都不做 } try {
Thread.sleep(800); // 等待Server啟動 } catch (InterruptedException e) {
} return true;
} /** * 關(guān)閉數(shù)據(jù)庫服務(wù) */ public static boolean stopHSQL() { try {
Statement statement = getConnection().createStatement();
statement.executeUpdate("SHUTDOWN;"); return true;
} catch (SQLException ex) {
Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex); return false;
}
} /** * 獲取連接 */ public static Connection getConnection() {
Connection conn = null; try {
Class.forName("org.hsqldb.jdbcDriver"); if (mode == SERVER_MODE) {
conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:" + PORT + "/" + DB_NAME, USER_NAME, PASSWORD);
} else if (mode == STAND_ALONE_MODE) {
conn = DriverManager.getConnection("jdbc:hsqldb:file:" + DB_PATH + DB_NAME, USER_NAME, PASSWORD);
}
} catch (ClassNotFoundException ex) {
Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex);
} return conn;
} /** * 測試 */ public static void main(String[] args) {
HSQL_Util.mode = HSQL_Util.STAND_ALONE_MODE;
HSQL_Util.startHSQL();
Connection conn = HSQL_Util.getConnection(); try {
Statement statement = getConnection().createStatement();
statement.executeUpdate("create table customer(id integer not null primary key,firstname varchar,lastname varchar)"); for (int i = 10; i < 20; i++) {
statement.executeUpdate("insert into customer values(" + i + ",'liu','zhaoyang')");
}
statement.close();
} catch (SQLException ex) {
Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex);
}
HSQL_Util.stopHSQL();
}
}
運行上述的類，在項目目錄，會生成一個db文件夾，里邊有數(shù)據(jù)庫的一些基本文件：
HSQL速度測試：
String note = "XXXX"; //這里省略，實際是800個中文字 System.out.println(note.length()); HSQL_Util.mode = HSQL_Util.STAND_ALONE_MODE; HSQL_Util.startHSQL(); Connection conn = HSQL_Util.getConnection(); try { Statement statement = getConnection().createStatement(); statement.executeUpdate("create cached table customer(id integer not null primary key,firstname varchar,lastname varchar)"); for (int i = 1; i < 60000; i++) { //插入6萬條數(shù)據(jù)用了43秒，生成262M的文件 statement.executeUpdate("insert into customer values(" + i + ",'" + note + "','zhaoyang')"); } statement.executeQuery("select * from customer where id = 40"); //6萬條數(shù)據(jù)用了9秒 statement.close(); } catch (SQLException ex) { Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex); } HSQL_Util.stopHSQL(); }
String note = "XXXX"; //這里省略，實際是800個中文字 System.out.println(note.length());
HSQL_Util.mode = HSQL_Util.STAND_ALONE_MODE;
HSQL_Util.startHSQL();
Connection conn = HSQL_Util.getConnection(); try {
Statement statement = getConnection().createStatement();
statement.executeUpdate("**create cached table** customer(id integer not null primary key,firstname varchar,lastname varchar)"); for (int i = 1; i < 60000; i++) { //插入6萬條數(shù)據(jù)用了43秒，生成262M的文件 statement.executeUpdate("insert into customer values(" + i + ",'" + note + "','zhaoyang')");
}
statement.executeQuery("select * from customer where id = 40"); //6萬條數(shù)據(jù)用了9秒 statement.close();
} catch (SQLException ex) {
Logger.getLogger(HSQL_Util.class.getName()).log(Level.SEVERE, null, ex);
}
HSQL_Util.stopHSQL();
}
使用HSQL的時候，就需要注意創(chuàng)建表用什么類型了?。?！
開始我不知道，使用了默認的Momery表，“****create table****” 測試了一下，速度慢得吐血。寫6萬條記錄，用了五十多秒，而且只是寫到3萬多就崩潰了，內(nèi)存溢出哈哈，其實也正常，十幾M數(shù)據(jù)爆了然后從這3萬個記錄，select * from custemer where id = XX，用了幾秒鐘
后來我再找方法，換成cached方法，****create cached table****試了一下，果然有效果，直接寫6萬個記錄，沒有爆，用了43秒。
再select就用了9秒。相對而言，比momery表好一點。但看了一下數(shù)據(jù)文件，我~~~竟然生成了262M的數(shù)據(jù)文件!!!!!
