1.???? hsql 學習
1.1.???? 學習目的
本文檔是針對hSQL 數據庫方面的基礎學習，為了使項目組成員能夠達到使用hSQL 數據庫的目的。
1.2.???? 培訓對象
開發人員
1.3.???? 常用詞及符號說明
常用詞：
hsql：一種免費的跨平臺的數據庫系統
E:\hsqldb：表示是在dos 命令窗口下面
1.4.???? 參考信息
doc\guide\guide.pdf
2.???? HSQL
2.1.???? HSQL 運行工具
java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManager
注意hsqldb.jar 文件的文件路徑,最好能放到classpath 里面,或者放到當前路徑下.
java -cp hsqldb.jar org.hsqldb.util.DatabaseManager
2.2.???? 運行數據庫
啟動方式: Server Modes and
In-Process Mode (also called Standalone Mode).
一個test 數據庫會包含如下文件:
? test.properties
? test.script
? test.log
? test.data
? test.backup
test.properties 文件包含關于數據庫的一般設置.
test.script?? 文件包含表和其它數據庫,插入沒有緩存表的數據.
test.log 文件包含當前數據庫的變更.
test.data 文件包含緩存表的數據
test.backup 文件是最近持久化狀態的表的數據文件的壓縮備份文件
所有以上這個文件都是必要的,不能被刪除.如果數據庫沒有緩存表,test.data 和test.backup 文件將不會存在.另外,除了以上文件HSQLDB 數據庫可以鏈接到任何文本文件,比如cvs 文件.
當操作test 數據庫的時候, test.log 用于保存數據的變更. 當正常SHUTDOWN,這個文件將被刪除. 否則(不是正常shutdown),這個文件將用于再次啟動的時候,重做這些變更.test.lck 文件也用于記錄打開的數據庫的事實, 正常SHUTDOWN,文件也被刪除.在一些情況下,test.data.old 文件會被創建,并刪除以前的.
2.3.???? Server Mode
java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 xdb
命令行方式:
啟動數據,數據庫文件mydb,數據庫名稱xdb
也可以在 server.properties 文件中定義啟動的數據庫,最多10個
例如: server.properties:
server.database.0=file:E:/hsqldb/data/mydb
server.dbname.0=xdb
server.database.1=file:E:/hsqldb/data/testdb
server.dbname.1=testdb
server.database.2=mem:adatabase
server.dbname.2=quickdb
啟動命令: java -cp ../lib/hsqldb.jar org.hsqldb.Server
運行結果如下
java 測試程序:
package test;
import junit.framework.TestCase;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestConnect extends TestCase {
???? Connection connection;
???? protected void setUp()
???? {????????
???????? try {
???????????? Class.forName("org.hsqldb.jdbcDriver" );
???????????? connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb","sa","");
????????????
????????????
???????? } catch (Exception e) {
???????????? // TODO Auto-generated catch block
???????????? e.printStackTrace();
???????? }
???? }
???? public void testselect()
???? {
???????? Statement stmt=null;
???????? ResultSet rs=null;
???????? try {
???????????? stmt = connection.createStatement();
???????????? String sql ="select * from test";
???????????? rs=stmt.executeQuery( sql);
???????????? while(rs.next() )
???????????? {
???????????????? System.out.println("id="+rs.getString("id"));
???????????????? System.out.println("name="+rs.getString("name"));
???????????? }
????????????
???????? } catch (SQLException e) {
???????????? // TODO Auto-generated catch block
???????????? e.printStackTrace();
???????? }
???????? finally
???????? {
???????????? try {
???????????????? rs.close() ;
???????????????? stmt.close();
???????????? } catch (SQLException e) {
???????????????? // TODO Auto-generated catch block
???????????????? e.printStackTrace();
???????????? }????????????
???????? }????
????????
???? }
???? protected void tearDown()
???? {
???????? try {
???????????? connection.close();
???????? } catch (Exception e) {
???????????? // TODO Auto-generated catch block
???????????? e.printStackTrace();
???????? }
???? }
}
以上在eclipse 中測試通過.
2.4.???? In-Process (Standalone) Mode
不需要啟動server
connection = DriverManager.getConnection("jdbc:hsqldb:file:E:/hsqldb/data/mydb","sa","");
這樣就可以連接數據庫。
只能在一個jvm 中使用，不能在多個jvm 中使用。
這種模式是在相同的jvm 下作為你的應用程序的一部分，運行數據庫引擎。對大多數應用程序，這種模式運行會相當快，作為數據，不需要轉換和網絡傳輸。
主要的缺點就是不可能從外面的應用程序訪問到默認數據庫，因此當你的應用運行時候，你不能通過別的工具檢查數據庫內容。在1.8.0 版本中,你可以在相同jvm 中的線程中運行數據庫初始化，并提供外面訪問你的進程內數據庫。
???? 推薦在開發應用中使用這種方式。
連接串：
Windows: DriverManager.getConnection("jdbc:hsqldb:file:E:/hsqldb/data/mydb","sa","");
Unix: DriverManager.getConnection("jdbc:hsqldb:file:/opt/db/testdb","sa","");
2.5.???? Memory-Only Databases
當隨即訪問內存，數據庫不固定時，可以采用內存的方式運行數據庫，由于沒有數據寫到硬盤上，這種方式使用在應用數據和applets 和特殊應用的內部進程中使用，URL：
Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:aname", "sa", "");
2.6.???? Using Multiple Databases in One JVM
2.7.???? Different Types of Tables
HSQLDB 支持 TEMP 表和三種類型的持久表（MEMORY 表, CACHED 表，TEXT表）
當使用 CREATE TABLE?? 命令時，Memory 表時默認類型，它們的數據整體保存在內存當中，但是任何改變它們的結構或者內容，它們會被寫到<dbname>.script 文件中。這個腳本文件在數據庫下一次打開的時候被對出，內存表重新被創建內容，根temp 表不同，內存表時持久化的。
CACHED 表通過CREATE CACHED TABLE 命令建立. 只有部分的它們的數據或者索引被保存在內存中，允許大表占用幾百兆的內存空間。例外一個優點，在數據庫引擎中，啟動大量數據的緩存表需要花費少量的時間，缺點是減慢了運行和使用Hsqldb 的速度。表相對小的時候，不要使用cache 表，在小表中使用內存數據庫。
從版本 1.7.0 以后，支持text 表，使用 CSV (Comma Separated Value)?? 或者其它分隔符文本文件作為它們的數據源。你可以特殊指定一個存在的CSV 文件，例如從其它的數據或者程序中導出文件，作為TXT 表的數據源。 同時,你可以指定一個空文件，通過數據庫引擎填充數據。TEXT 表將比cache 表更加效率高。Text 表可以指向不同的數據文件。
* memory-only databases 數據庫只支持memory 表和cache 表，不支持text 表。
2.8.???? 約束和索引
HSQLDB 支持 PRIMARY KEY, NOT NULL, UNIQUE, CHECK and FOREIGN KEY 約束.
3.???? sql 命令
3.1.???? sql 支持
select top 1 * from test;
select limit 0 2 * from test;
DROP TABLE test IF EXISTS;
3.2.???? Constraints and Indexes
主健約束：PRIMARY KEY
唯一約束：
唯一索引：
外健：
CREATE TABLE child(c1 INTEGER, c2 VARCHAR, FOREIGN KEY (c1, c2) REFERENCES parent(p1, p2));
3.3.???? 索引和查詢速度
索引提高查詢速度，比提高排序速度。
主健和唯一所列自動創建索引，否則需要自己創建CREATE INDEX command。
索引： 唯一索引和非唯一索引
多列的索引，如果只是使用后面的，不使用第一個，將不會條查詢速度。
(TB is a very large table with only a few rows where TB.COL3 = 4)
SELECT * FROM TA JOIN TB ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;
SELECT * FROM TB JOIN TA ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;(faster)
原因是 TB.COL3 可以被快速的估計,如果TB 表放到前面(index on TB.COL3):
一般規則是把縮小條件的列的表放在前面
3.4.???? 使用where 還是join
使用 WHERE?? 條件鏈接表可能會降低運行速度.
下面的例子將會比較慢,即使使用了索引:
???? SELECT ... FROM TA, TB, TC WHERE TC.COL3 = TA.COL1 AND TC.COL3=TB.COL2 AND TC.COL4 = 1
這個查詢隱含TA.COL1 = TB.COL2 ,但是沒有直接設定這個條件.如果 TA 和 TB 每個表都包含100 條記錄,10000 組合將和 TC 關聯,用于TC這個列的條件,盡管有索引在這個列上.使用JOIN 關鍵字, 在組合TC 之前,TA.COL1 = TB.COL2 條件直接并縮小組合 TA 和 TB 的行數, 在運行大數據量的表的結果是,將會很快:
???? SELECT ... FROM TA JOIN TB ON TA.COL1 = TB.COL2 JOIN TC ON TB.COL2 = TC.COL3 WHERE TC.COL4 = 1
這個查詢可以提高一大步,如果改變表的順序, 所以 TC.COL1 = 1 將最先使用,這樣更小的集合將組合在一起:
???? SELECT ... FROM TC JOIN TB ON TC.COL3 = TB.COL2 JOIN TA ON TC.COL3 = TA.COL1 WHERE TC.COL4 = 1
以上例子,數據引擎自動應用于TC.COL4 = 1 組合小的集合于其它表關聯. Indexes TC.COL4, TB.COL2?? TA.COL1 都將使用索引,提高查詢速度.
3.5.???? Subqueries and Joins
使用join 和調整表的順序提高效率.
例如:, 第二個查詢的速度將更快一些(TA.COL1 和TB.COL3都有索引):
Example 2.2. Query comparison
???? SELECT ... FROM TA WHERE TA.COL1 = (SELECT MAX(TB.COL2) FROM TB WHERE TB.COL3 = 4)
???? SELECT ... FROM (SELECT MAX(TB.COL2) C1 FROM TB WHERE TB.COL3 = 4) T2 JOIN TA ON TA.COL1 = T2.C1
第二個查詢將 MAX(TB.COL2) 與一個單記錄表相關聯. 并使用TA.COL1索引,這將變得非常快. 第一個查詢是將 TA 表中的每一條記錄不斷地與MAX(TB.COL2)匹配.
3.6.???? 數據類型
TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a decimal point) are supported integral types and map to byte, short, int, long and BigDecimal in Java.
Integral Types:
TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL
Other Numeric Types:
REAL, FLOAT or DOUBLE
Bit and Boolean Types:
???? BOOLEAN: UNDEFINED,TRUE,FALSE??
NULL values are treated as undefined.
Storage and Handling of Java Objects
Sequences and Identity
Identity Auto-Increment Columns:
The next IDENTITY value to be used can be set with the
ALTER TABLE ALTER COLUMN <column name> RESTART WITH <new value>;
Sequences:
SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...
????
3.7.???? 事務問題:
SET PROPERTY "sql.tx_no_multi_rewrite" TRUE
4.???? Connections
通用驅動jdbc:hsqldb:?? 下列協議標識(mem: file: res: hsql: http: hsqls: https:)
Table 4.1. Hsqldb URL Components
Driver and Protocol???? Host and Port???? Database
jdbc:hsqldb:mem:
???? not available???? accounts
jdbc:hsqldb:mem:.
jdbc:hsqldb:file:
???? not available???? mydb
/opt/db/accounts
C:/data/mydb
數據庫路徑.
jdbc:hsqldb:res:
???? not available???? /adirectory/dbname
jars files are accessed in Java programs. The /adirectory above stands for a directory in one of the jars.
jdbc:hsqldb:hsql:
jdbc:hsqldb:hsqls:
jdbc:hsqldb:http:
jdbc:hsqldb:https:
???? //localhost
//192.0.0.10:9500
//dbserver.somedomain.com
???? /an_alias
/enrollments
/quickdb
別名在server.properties or webserver.properties文件中指定
???? database.0=file:/opt/db/accounts
???? dbname.0=an_alias
???? database.1=file:/opt/db/mydb
???? dbname.1=enrollments
???? database.2=mem:adatabase
???? dbname.2=quickdb
In the example below, the database files lists.* in the /home/dbmaster/ directory are associated with the empty alias:
???? database.3=/home/dbmaster/lists
???? dbname.3=
4.1.???? Connection properties
Connection properties are specified either by establishing the connection via the:
???? DriverManager.getConnection (String url, Properties info);
method call, or the property can be appended to the full Connection URL.
Table 4.2. Connection Properties
get_column_name???? true???? column name in ResultSet
This property is used for compatibility with other JDBC driver implementations. When true (the default), ResultSet.getColumnName(int c) returns the underlying column name
When false, the above method returns the same value as ResultSet.getColumnLabel(int column) Example below:
???? jdbc:hsqldb:hsql://localhost/enrollments;get_column_name=false
????????????????????
When a ResultSet is used inside a user-defined stored procedure, the default, true, is always used for this property.
ifexists???? false???? connect only if database already exists
Has an effect only with mem: and file: database. When true, will not create a new database if one does not already exist for the URL.
When false (the default), a new mem: or file: database will be created if it does not exist.
Setting the property to true is useful when troubleshooting as no database is created if the URL is malformed. Example below:
???? jdbc:hsqldb:file:enrollments;ifexists=true
shutdown???? false???? shut down the database when the last connection is closed
This mimics the behaviour of 1.7.1 and older versions. When the last connection to a database is closed, the database is automatically shut down. The property takes effect only when the first connection is made to the database. This means the connection that opens the database. It has no effect if used with subsequent, simultaneous connections.
This command has two uses. One is for test suites, where connections to the database are made from one JVM context, immediately followed by another context. The other use is for applications where it is not easy to configure the environment to shutdown the database. Examples reported by users include web application servers, where the closing of the last connection conisides with the web app being shut down.
4.2.???? Properties Files
大小寫敏感 (e.g. server.silent=FALSE will have no effect, but server.silent=false will work).
屬性文件和設定存儲如下 :
Table 4.3. Hsqldb Server Properties Files
File Name???? Location???? Function
server.properties???? the directory where the command to run the Server class is issued???? settings for running HSQLDB as a database server communicating with the HSQL protocol
webserver.properties???? the directory where the command to run the WebServer class is issued???? settings for running HSQLDB as a database server communicating with the HTTP protocol
<dbname>.properties???? the directory where all the files for a database are located???? settings for each particular database
Properties files for running the servers are not created automatically. You should create your own files that contain server.property=value pairs for each property.
4.2.1.???? Server and Web Server Properties
server.properties and webserver.properties 文件支持如下設定:
Table 4.4. Property File Properties
Value???? Default???? Description
server.database.0???? test???? the path and file name of the first database file to use
server.dbname.0???? ""???? lowercase server alias for the first database file
server.urlid.0???? NONE???? SqlTool urlid used by UNIX init script. (This property is not used if your are running Server/Webserver on a platform other than UNIX, or of you are not using our UNIX init script).
server.silent???? true???? no extensive messages displayed on console
server.trace???? false???? JDBC trace messages displayed on console
In 1.8.0, 每個服務器支持同時啟動10個不同的數據庫. The server.database.0 property defines the filename / path whereas the server.dbname.0 defines the lowercase alias used by clients to connect to that database. The digit 0 is incremented for the second database and so on. Values for the server.database.{0-9} property can use the mem:, file: or res: prefixes and properties as discussed above under CONNECTIONS. For example,
???? database.0=mem:temp;sql.enforce_strict_size=true;
Values specific to server.properties are:
Table 4.5. Server Property File Properties
Value???? Default???? Description
server.port???? 9001???? TCP/IP port used for talking to clients. All databases are served on the same port.
server.no_system_exit???? true???? no System.exit() call when the database is closed
Values specific to webserver.properties are:
Table 4.6. WebServer Property File Properties
Value???? Default???? Description
server.port???? 80???? TCP/IP port used for talking to clients
server.default_page???? index.html???? the default web page for server
server.root???? ./???? the location of served pages
.<extension>???? ????? multiple entries such as .html=text/html define the mime types of the static files served by the web server. See the source for WebServer.java for a list.
All the above values can be specified on the command line to start the server by omitting the server. prefix.
5.???? SqlTool
Mem 數據庫:
E:\hsqldb>java -jar ./lib/hsqldb.jar mem
Hsql Server:
(前提是xdb server 已經啟動):
(java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 xdb)
java -jar ./hsqldb.jar xdb