Java Learning: hsqldb documentation (i)

Recent research Hsqldb, try to translate its documents, posted here and share with you, welcome everyone and I exchange: wwccff@163.net
　　

What is a hsqldb?
　　
HSQLDB has the following characteristics:
　　
is an open source Java database
　　
has standard SQL syntax and Java interfaces
　　
HSQLDB is free to use and distribute
　　
Very concise and fast.
　　
Three ways of having memory database, independent database and C/s database
　　
But it's used in applets.
　　
More details:
　　
Indexes can be created and used automatically
　　
Support transaction Processing
　　
Allow Table Association
　　
Integrity references and constraints
　　
Support for Java stored procedures and functions
　　
The database can generate SQL scripts
　　
Use security mechanisms such as user name, password, access rights, etc.
　　
Can be compiled by JAVA1.1 and JAVA2
　　
Built on the basis of Hypersonicsql, HSQLDB is a general-purpose database, very small and easy to install and use. can be used in applets, in the test, in the application system.

By providing a standard SQL and JDBC interface, HSQLDB can facilitate data conversion with other databases.
　　
The current latest version of HSQLDB is 1.7.1, provided in the form of a compressed package, including jar files, documentation, source code, test procedures, examples, etc. that can be used.
　　
Introduction of two operation modes
HSQLDB has two modes of operation:
　　
In-process mode (applications that are only used in the same JVM can access the database)
　　
c/S mode (multiple computers/systems can access the same database)
　　
In-process access mode
In-process access mode is also a standalone mode. The standalone mode here is relative to the C/S mode (client program accesses the database server). Here, the database and the application run under the same JVM. This time the database is actually the equivalent of the code base called by the application. Programs and databases communicate through common JDBC calls, but such calls are internal and do not need to be communicated over the network.
　　
In this mode, a database can only have one application access at the same time, otherwise, it is necessary to use the C/S mode (allow multiple JVMs or computers to access the same database at the same time).
　　
The URL for JDBC in this mode is as follows:
　　
Jdbc:hsqldb:test
　　
Here, test is the database file name. Another example (under Windows System):
　　
Jdbc:hsqldb:c:db EST
　　
c/S access mode
　　
In this mode, the database and the application are not running under the same JVM process, but have their own independent processes or stand-alone machines. No client program is required to enter the server's file system. The database operation pattern in this mode is no different from some large databases (such as SQL server,oracle, etc.). Can be on the Internet or intranet.
　　
In addition to its own access protocol, HSQLDB supports the standard HTTP protocol, which allows access to the database through firewalls or proxy servers.
　　
In the all Server modes the actual database file name was specified in the Java command that starts the server. This can is the dot "." For all-in-memory operation or the ' path for the ' database name
　　
There are three types of server modes: Server,webserver and servlet.
　　
SERVER
　　
The communication protocols in this mode are hsql proprietary protocols based on TCP/IP. Each client has a separate connection. This mode of response speed is very fast, if the use of C/S mode, should be more use of this service mode.
　　
The JDBC URL in this mode is:
　　
Jdbc:hsqldb:hsql://hsqldbsrv
　　
Here, Hsqldbsrv is the machine name. If you run multiple servers on a single machine, you need to specify the port, for example: jdbc:hsqldb:hsql://hsqldbsrv:9002, and if it is a local computer, use Localhost:jdbc:hsqldb:hsql://localhost.
　　
WEBSERVER
　　
Sometimes, due to the presence of firewalls or proxy servers, the HTTP protocol is required to communicate, and the system provides a small and simple webserver for database queries, such as:
　　
Jdbc:hsqldb:http://websrv
　　
Servlet
　　
This pattern is similar to the webserver pattern, where the database runs in a servlet, and the servlet can run in almost all webserver. It is also compatible with the Java Servlete API (test environment is j2dk2.1). This is accessed directly over the network. If your servlet does not have direct access to the database, do not use this pattern.
Full memory access (all-in-memory) mode
　　
The so-called full memory access mode, that is, all data (including indexes and records) are stored in the main memory. This means that the size of the database is limited by the size of the memory (not exceeding the memory size). The reasons for supporting this pattern are:
　　
In non-log mode, this mode is slightly faster
　　
Can be used under the applet
　　
Used to store temporary data (the application system's data cache) all-in-memory
　　
The JDBC URL is as follows:
　　
JDBC:HSQLDB:.
　　
Memory and hard drive combined access mode
　　
In this mode, the database changes are written to the hard disk, which means that when the database is started, the tables in memory are recreated according to their data. Alternatively, you can create a table to hold the data, and when you access the database, there are only a few records stored in memory. You can use the ´create CACHED Table´ to replace ´create Table´ when you create it. This allows for large tables (records that are too large for memory). The index of the cached table can also be saved to the hard disk. Therefore, the size of the database can be not limited by the size of memory. It is slower to go into the cache table than to fetch data from the memory tables. Starting with version 1.7.0, a third mode is supported: Data can be stored in text files (such as CSV-formatted files). The corresponding statement: ´create TEXT Table´.
　　
The current state is saved to disk before the database is closed. The data in the cached table is saved to a separate file. When Hsqldb is started, the database loads data from the disk (SQL scripts are executed) and the data is not lost if the database is destroyed (for example, by using CTRL + C or power off). This is because the next time the database restarts, it uses the script to revert to the state of the most recent (the one that had the script file).
　　
Mixed binding mode
　　
All patterns can be used in one program, and the system can use these four modes at the same time to connect four different databases, for example:
　　
C1=drivermanager.getconnection ("Jdbc:hsqldb:.", "sa", "");
C2=drivermanager.getconnection ("Jdbc:hsqldb:test", "sa", "");
C3=drivermanager.getconnection ("Jdbc:hsqldb:http://dbserver", "sa", "");
C4=drivermanager.getconnection ("Jdbc:hsqldb:hsql://dbserver", "sa", "");
　　
In this example, four connections are opened:
　　
The C1 is the memory database, the C2 is the local database test;c3 the DBServer database is connected using the HTTP protocol, and C4 is connected to the DBServer machine, but the faster Hsql protocol is used. The limitation here is that there is only one process in which the full memory process is available.
Comparison
　　
Each pattern or configuration has different details and good or bad two aspects:
　　
Transaction processing
　　
For webserver and servlet schemas, because the HTTP protocol is stateless, a new connection is established for each query database. Each query needs to send a username and password to the database, then a new connection is established, and a new transaction is established (because the transaction is bound to the connection). can use ' cookies ', but it hasn't been achieved yet.
　　
Concurrent access
　　
The server mode allows system and administrative tools (such as Databasemanager to access the database simultaneously).
　　
Database performance Optimization Factors
　　
The memory database does not require access to the system and is therefore the fastest. Other schema databases require access to the file system, and each insert/update/delete operation is saved to disk, so it is slower. If the select and delete queries hit the cached table information, the speed is almost as fast as the memory table, otherwise it will be much slower (because you want to interact with the operating system's file system).
　　
Transmission time for each statement
　　
In server mode, each statement needs to be routed to the server via the TCP/IP protocol, and then returns the results to the client. The webserver and servlet modes require more time, because the connection needs to be reconnected each time the statement is created. In contrast, in-process mode is the transmission of data within a system, much faster.
　　
Run as Applet
　　
This is the full memory operation.