HyperSQL数据库引擎（HSQLDB）2.2用户指南

4星 · 超过85%的资源需积分: 10 188 浏览量更新于2024-07-27 收藏 1.66MB PDF 举报

"HSQLDB用户手册是针对HyperSQL Database Engine 2.2版本的一份详细指南，由Blaine Simpson和Fred Toussi编辑。这份手册涵盖了运行、使用HSQLDB以及SQL语言的各个方面，旨在帮助用户理解和操作这个开源的关系型数据库管理系统。手册的版权归属于HSQL Development Group，并遵循HSQLDB的许可协议分发。" 在"1. Running and Using HyperSQL"章节中，介绍了如何启动和使用HSQLDB。HSQLDB的核心是其JAR文件，它提供了数据库访问工具。该章节讲解了如何创建和访问一个HyperSQL数据库，包括在进程内访问数据库目录和使用不同的服务器模式，如HSQL Server和HTTP Server。连接到数据库服务器时，安全考虑是至关重要的，手册也对此进行了讨论。此外，还介绍了如何管理多个数据库、读取数据、关闭数据库以及创建新数据库。 "2. SQL Language"章节深入讲解了SQL语言支持的标准，包括数据和表的处理。这里提到了临时表和持久表的概念，以及大对象（LOB）数据的存储。对各种数据类型有一个简短的指南，这对于理解如何在HSQLDB中存储不同类型的数据至关重要。手册详细阐述了如何使用SQL语句来创建、查询、更新和删除数据，以及如何处理数据类型，这是数据库操作的基础。除了这些基础内容，手册可能还包含了关于事务管理、索引、视图、存储过程、触发器、备份与恢复、性能优化等高级主题。这些内容对于开发者和数据库管理员来说，都是管理和维护HSQLDB数据库系统不可或缺的知识。 "HSQLDB用户手册"是一本全面的参考资料，它提供了关于HSQLDB数据库引擎的详细信息，帮助用户从初识到熟练地运用这个数据库系统，进行数据存储、查询和管理。通过学习这份手册，读者可以掌握SQL语言在HSQLDB中的应用，以及如何有效地利用HSQLDB来满足各种数据库需求。

Running and Using HyperSQL

In-Process Access to Database Catalogs

In general, JDBC is used for all access to databases. This is done by making a connection to the database, then using

various methods of the java.sql.Connection object that is returned to access the data. Access to an in-process

database is started from JDBC, with the database path specified in the connection URL. For example, if the file:

database name is "testdb" and its files are located in the same directory as where the command to run your application

was issued, the following code is used for the connection:

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:testdb", "SA", "");

The database file path format can be specified using forward slashes in Windows hosts as well as Linux hosts. So

relative paths or paths that refer to the same directory on the same drive can be identical. For example if your database

directory in Linux is /opt/db/ containing a database testdb (with files named testdb.*),

then the database file path is /opt/db/testdb. If you create an identical directory structure on

the C: drive of a Windows host, you can use the same URL in both Windows and Linux:

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:/opt/db/testdb", "SA", "");

When using relative paths, these paths will be taken relative to the directory in which the shell command to start the

Java Virtual Machine was executed. Refer to the Javadoc for JDBCConnection for more details.

Paths and database names for file databases are treated as case-sensitive when the database is created or the first

connection is made to the database. But if a second connection is made to an open database, using a path and name

that differs only in case, then the connection is made to the existing open database. This measure is necessary because

in Windows the two paths are equivalent.

A mem: database is specified by the mem: protocol. For mem: databases, the path is simply a name. Several mem:

databases can exist at the same time and distinguished by their names. In the example below, the database is called

"mymemdb":

Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:mymemdb", "SA", "");

A res: database, is specified by the res: protocol. As it is a Java resource, the database path is a Java URL (similar to the

path to a class). In the example below, "resdb" is the root name of the database files, which exists in the directory "org/

my/path" within the classpath (probably in a Jar). A Java resource is stored in a compressed format and is decompressed

in memory when it is used. For this reason, a res: database should not contain large amounts of data and is always

read-only.

Connection c = DriverManager.getConnection("jdbc:hsqldb:res:org.my.path.resdb", "SA", "");

The first time in-process connection is made to a database, some general data structures are initialised and a few helper

threads are started. After this, creation of connections and calls to JDBC methods of the connections execute as if they

are part of the Java application that is making the calls. When the SQL command "SHUTDOWN" is executed, the

global structures and helper threads for the database are destroyed.

Note that only one Java process at a time can make in-process connections to a given file: database. However, if the

file: database has been made read-only, or if connections are made to a res: database, then it is possible to make in-

process connections from multiple Java processes.

Server Modes

For most applications, in-process access is faster, as the data is not converted and sent over the network. The main

drawback is that it is not possible by default to connect to the database from outside your application. As a result

you cannot check the contents of the database with external tools such as Database Manager while your application

is running.

Running and Using HyperSQL

Server modes provide the maximum accessibility. The database engine runs in a JVM and opens one or more in-

process catalogs. It listens for connections from programs on the same computer or other computers on the network.

It translates these connections into in-process connections to the databases.

Several different programs can connect to the server and retrieve or update information. Applications programs (clients)

connect to the server using the HyperSQL JDBC driver. In most server modes, the server can serve an unlimited number

of databases that are specified at the time of running the server, or optionally, as a connection request is received.

A Sever mode is also the preferred mode of running the database during development. It allows you to query the

database from a separate database access utility while your application is running.

There are three server modes, based on the protocol used for communications between the client and server. They are

briefly discussed below. More details on servers is provided in the HyperSQL Network Listeners (Servers) chapter.

HyperSQL HSQL Server

This is the preferred way of running a database server and the fastest one. A proprietary communications protocol is

used for this mode. A command similar to those used for running tools and described above is used for running the

server. The following example of the command for starting the server starts the server with one (default) database with

files named "mydb.*" and the public name of "xdb". The public name hides the file names from users.

java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb

The command line argument --help can be used to get a list of available arguments.

HyperSQL HTTP Server

This method of access is used when the computer hosting the database server is restricted to the HTTP protocol. The

only reason for using this method of access is restrictions imposed by firewalls on the client or server machines and it

should not be used where there are no such restrictions. The HyperSQL HTTP Server is a special web server that allows

JDBC clients to connect via HTTP. The server can also act as a small general-purpose web server for static pages.

To run an HTTP server, replace the main class for the server in the example command line above with the following:

org.hsqldb.server.WebServer

The command line argument --help can be used to get a list of available arguments.

HyperSQL HTTP Servlet

This method of access also uses the HTTP protocol. It is used when a separate servlet engine (or application server)

such as Tomcat or Resin provides access to the database. The Servlet Mode cannot be started independently from the

servlet engine. The Servlet class, in the HSQLDB jar, should be installed on the application server to provide the

connection. The database is specified using an application server property. Refer to the source file src/org/

hsqldb/server/Servlet.java to see the details.

Both HTTP Server and Servlet modes can only be accessed using the JDBC driver at the client end. They do not

provide a web front end to the database. The Servlet mode can serve only a single database.

Please note that you do not normally use this mode if you are using the database engine in an application server. In

this situation, connections to a catalog are usually made in-process, or using a separate Server

Connecting to a Database Server

When a HyperSQL server is running, client programs can connect to it using the HSQLDB JDBC Driver contained

in hsqldb.jar. Full information on how to connect to a server is provided in the Java Documentation for

Running and Using HyperSQL

JDBCConnection (located in the /doc/apidocs directory of HSQLDB distribution). A common example is

connection to the default port (9001) used for the hsql: protocol on the same machine:

Example 1.1. Java code to connect to the local hsql Server

try {

Class.forName("org.hsqldb.jdbc.JDBCDriver" );

} catch (Exception e) {

System.err.println("ERROR: failed to load HSQLDB JDBC driver.");

e.printStackTrace();

return;

}

Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "SA", "");

If the HyperSQL HTTP server is used, the protocol is http: and the URL will be different:

Example 1.2. Java code to connect to the local http Server

Connection c = DriverManager.getConnection("jdbc:hsqldb:http://localhost/xdb", "SA", "");

Note in the above connection URL, there is no mention of the database file, as this was specified when running the

server. Instead, the public name defined for dbname.0 is used. Also, see the HyperSQL Network Listeners (Servers)

chapter for the connection URL when there is more than one database per server instance.

Security Considerations

When a HyperSQL server is run, network access should be adequately protected. Source IP addresses may be restricted

by use of our Access Control List feature , network filtering software, firewall software, or standalone firewalls. Only

secure passwords should be used-- most importantly, the password for the default system user should be changed

from the default empty string. If you are purposefully providing data to the public, then the wide-open public network

connection should be used exclusively to access the public data via read-only accounts. (i.e., neither secure data nor

privileged accounts should use this connection). These considerations also apply to HyperSQL servers run with the

HTTP protocol.

HyperSQL provides two optional security mechanisms. The encrypted SSL protocol , and Access Control Lists .

Both mechanisms can be specified when running the Server or WebServer. On the client, the URL to connect to an

SSL server is slightly different:

Example 1.3. Java code to connect to the local secure SSL hsql and http Servers

Connection c = DriverManager.getConnection("jdbc:hsqldb:hsqls://localhost/xdb", "SA", "");

Connection c = DriverManager.getConnection("jdbc:hsqldb:https://localhost/xdb", "SA", "");

The security features are discussed in detail in the HyperSQL Network Listeners (Servers) chapter.

Using Multiple Databases

A server can provide connections to more than one database. In the examples above, more than one set of database

names can be specified on the command line. It is also possible to specify all the databases in a .properties file,

instead of the command line. These capabilities are covered in the HyperSQL Network Listeners (Servers) chapter

Accessing the Data

As shown so far, a java.sql.Connection object is always used to access the database. But the speed and

performance depends on the type of connection.

Running and Using HyperSQL

Establishing a connection and closing it has some overheads, therefore it is not good practice to create a new connection

to perform a small number of operations. A connection should be reused as much as possible and closed only when

it is not going to be used again for a long while.

Reuse is more important for server connections. A server connection uses a TCP port for communications. Each time

a connection is made, a port is allocated by the operating system and deallocated after the connection is closed. If

many connections are made from a single client, the operating system may not be able to keep up and may refuse

the connection attempt.

A java.sql.Connection object has some methods that return further java.sql.* objects. All these objects

belong to the connection that returned them and are closed when the connection is closed. These objects can be reused,

but if they are not needed after performing the operations, they should be closed.

A java.sql.DatabaseMetaData object is used to get metadata for the database.

A java.sql.Statement object is used to execute queries and data change statements. A

java.sql.Statement can be reused to execute a different statement each time.

A java.sql.PreparedStatement object is used to execute a single statement repeatedly. The SQL

statement usually contains parameters, which can be set to new values before each reuse. When a

java.sql.PreparedStatement object is created, the engine keeps the compiled SQL statement for

reuse, until the java.sql.PreparedStatement object is closed. As a result, repeated use of a

java.sql.PreparedStatement is much faster than using a java.sql.Statement object.

A java.sql.CallableStatement object is used to execute an SQL CALL statement. The SQL

CALL statement may contain parameters, which should be set to new values before each reuse. Similar

to java.sql.PreparedStatement, the engine keeps the compiled SQL statement for reuse, until the

java.sql.CallableStatement object is closed.

A java.sql.Connection object also has some methods for transaction control.

The commit() method performs a COMMIT while the rollback() method performs a ROLLBACK SQL statement.

The setSavepoint(String name) method performs a SAVEPOINT <name> SQL statement and returns

a java.sql.Savepoint object. The rollback(Savepoint name) method performs a ROLLBACK TO

SAVEPOINT <name> SQL statement.

The Javadoc for JDBCConnection , JDBCDriver , JDBCDatabaseMetadata JDBCResultSet

, JDBCStatement , JDBCPreparedStatement list all the supported JDBC methods together with

information that is specific to HSQLDB.

Closing the Database

All databases running in different modes can be closed with the SHUTDOWN command, issued as an SQL statement.

When SHUTDOWN is issued, all active transactions are rolled back. The catalog files are then saved in a form that

can be opened quickly the next time the catalog is opened.

A special form of closing the database is via the SHUTDOWN COMPACT command. This command rewrites the

.data file that contains the information stored in CACHED tables and compacts it to its minimum size. This command

should be issued periodically, especially when lots of inserts, updates or deletes have been performed on the cached

tables. Changes to the structure of the database, such as dropping or modifying populated CACHED tables or indexes

also create large amounts of unused file space that can be reclaimed using this command.

Databases are not closed when the last connection to the database is explicitly closed via JDBC. A connection property,

shutdown=true, can be specified on the first connection to the database (the connection that opens the database)

to force a shutdown when the last connection closes.

剩余319页未读，继续阅读

竹山居士

粉丝: 12
资源: 27

HyperSQL数据库引擎（HSQLDB）2.2用户指南

HSQLDB中文帮助文档

hsqldb包和使用说明

HSQLDB 中文文档

hsqldb安装和使用

hsqldb.jar下载使用

hsqldb怎么监控数据库连接池？

hsqldb中建一个数据库

如何在IntelliJ IDEA中添加hsqldb数据源？

hsqldb的jar包为什么打不开

springboot实现用户登录注册功能

最新资源