HSQLDB introduction and basic usage

HSQLDB is an open-source Java database with standard SQL syntax and Java interfaces. It can be freely used and distributed, which is very simple and fast. It has three modes: SERVER mode, in-process mode, and memory-only mode. The HSQLDB. jar package is required to run HSQLDB. It contains some components and programs. Each program needs different commands to run. It is located in the lib directory of the project. The current version is 1.8.0.5. Official website: http: // prdownloads.sourceforge.net/hsqldb/hsqldb_00008_0_5.zip? Download
Before introducing these modes, we need to understand some files involved in HSQLDB. Each hsqld database contains 2 to 5 files with the same name but different extensions, which are located in the same directory. For example, a database named "test" contains the following files:

• Test. Properties

• Test. Script

• Test. Log

• Test. Data

• Test. Backup

The properties file describes the basic configuration of the database. The script file records the definitions of tables and other database objects. The log file records recent database updates. The data file contains the data of the cached table, while the backup file compresses and backs up the data file, which contains the final state data of the data file last time. All these files must be deleted without authorization. However, if your database does not have a buffer table (cached table), the test. Data and test. Backup files will not exist.

Next, we will give a brief introduction to the three HSQLDB modes, as well as the startup methods of some tools.

I. Server Mode

 

Server mode provides maximum accessibility. The application (client) connects to the server through the JDBC driver of HSQLDB. In server mode, the server can be specified as a maximum of 10 databases during running. Depending on the communication protocol between the client and the server, the server mode can be divided into the following three types:

1. HSQLDB serve

This mode is preferred and fastest. It uses HSQLDB proprietary communication protocol. To start the server, you must write a batch processing command. All tools provided by HSQLDB can be run in the standard mode of Java class archive files (that is, Jar. Assume that HSQLDB. jar is located under./lib of the current path. Our command will write as follows:

Java-CP ../lib/HSQLDB. Jar org. HSQLDB. Server-database.0 mydb-dbname.0 demodb

Now you may wonder why [-database.0] and [dbname.0] are followed by [0]. _...... Didn't we specify 10 databases when we run the service mode? If there are multiple databases, then continue to write the command line parameter-database.1 AA-dbname.1 AA-database.2 bb-dbname.2 BB ......

Create a text file and save the preceding command. The file name can be customized. Change the suffix name to bat, and then run the batch file directly. The command for executing the startup tool described later adopts the same method.

The above commands start the server with a database (default). This database is named "mydb. * "file, these files are mydb. properties, mydb. script, mydb. log files. Demodb is the alias of mydb and can be used when connecting to the database.

2. HSQLDB Web Server

This mode can only be used to access the database server host through HTTP. The only reason for this mode is that the firewall on the client or server imposes limitations on the network connection of the database. In other cases, this mode is not recommended.

When running the web server, you only need to replace the main class in the command line with org. HSQLDB. webserver.

3. HSQLDB Servlet

This mode adopts the same HTTP protocol as Web server. This mode can be used when servlet engines such as Tomcat or resin (or Application Server) provide database access. However, the servlet mode cannot be started independently from the servlet engine. To provide database connection, you must place the hsqlservlet class in HSQLDB. jar on the application server.

The Web server and Servlet modes can only be accessed on the client through the JDBC driver. In servlet mode, only one separate database can be started. Note that this mode is usually not used as the database engine of the application server.

Connect to a database running in Server Mode

When the HSQLDB server is running, the client program can connect to the database through the hsqldb jdbc driver contained in HSQLDB. jar.

Java code

1. Try {
3. Class. forname ("org. HSQLDB. jdbcdriver ");
5. } Catch (classnotfoundexception e ){
7. E. printstacktrace ();
9. }
11. Connection c = drivermanager. getconnection ("JDBC: HSQLDB: hsql: // localhost/XDB", "sa ","");

Note: The default user of HSQLDB is that the SA password is blank. We will introduce how to change the default password in the tool usage section.

Ii. In-process mode

The in-process mode is also called the standalone mode. In this mode, the database engine runs in the same JVM as part of the application. For some applications, this mode is faster because data does not need to be converted or transmitted over the network. The main drawback is that the database cannot be connected from outside the application by default. Therefore, when an application is running, you cannot use an external tool similar to database manager to view the database content. In 1.8.0 In version, you can run a server instance from a thread of the same JVM to provide external connections to access your in-process database.

We recommend that you use the in-process mode: use an HSQLDB server instance for the database during development, and then convert it to the in-process mode during the subordinates.

An in-process database is started from a JDBC Statement. The connection URL contains the specified database file path as part of JDBC. For example, if the database name is testdb and its database file is located in the same directory as the command for running the application, the following code can be used to connect to the database:

Connection c = drivermanager. getconnection ("JDBC: HSQLDB: file: testdb
"," Sa ","");

The path format of the database file is specified on both the Linux host and the Windows host using the forward slash ("/"). Therefore, the expressions of relative paths or directories in the same partition are consistent. When using relative paths, these paths represent the execution paths relative to the shell commands used to start JVM.

Iii. memry-only Database

Memory-only databases are not persistent but all in random access memory. Because there is no information written on the disk. This mode is specified through mem: Protocol:

Connection c = drivermanager. getconnection ("JDBC: HSQLDB: Mem: dbname", "sa ","");

You can also specify the same URL in server. properties to run a memory-only (only in memory) server instance.

Note: When a server instance is started or an in-process database connection is established, if the specified path does not have a database, a new empty database will be created. The side effect of this feature is to make new users confused. If an error occurs when you specify the path of an existing database to be connected, a connection pointing to the new database will be established. To solve this problem, you can specify a connection property ifexists = true to only allow connections to existing databases to avoid creating new databases. If the database does not exist, getconnection () method will throw an exception.

Iv. Use of tools

HSQLDB provides the following tool classes:

• Org. HSQLDB. util. databasemanager
• Org. HSQLDB. util. databasemanagerswing
• Org. HSQLDB. util. Transfer
• Org. HSQLDB. util. querytool
• Org. HSQLDB. util. sqltool

Databasemanage and SQL tool can only be run using command line parameters. You can add the parameter -? To view the list of available parameters of these tools. Other tools can be started through the main interface of databasemanager to facilitate interactive operations.

For ease of operation, we also make the commands started by these tools into batch files. The method is the same as the method used to create commands for starting a service. Here we emphasize the location of HSQLDB. Jar again, because all the startup commands are written by referring to the location of HSQLDB. jar.

If you are in trouble, you can also use absolute paths to write commands.

Now let's run the databasemanager tool of the AWT version together. The HSQLDB. jar is located under the ../lib file relative to the current path. The command is as follows:

Java-CP ../lib/HSQLDB. Jar org. HSQLDB. util. databasemanager

Save the command as a batch file with the suffix bat and save it as databasemanager. bat. You can also name it according to your habits. Run databasemanager. BAT and you will see the following screen :.

Now we will give a brief introduction to this simple logon interface *_*

• Recent: select your latest logon solution. [Optional]
• Setting name: the name of the logon plan. If this logon succeeds, you will be able to see your successful logon plan in the recent drop-down list when you log on next time. [Optional]
• Type: logon mode, including in-memory mode, standalone (in-process) mode, server mode, webserver mode... [required]
• Driver: The driver connecting to the database [required]
• URL: URL to connect to the database [required]
• User: User name [required]
• Password: Password [unless the password is blank]

Note: If you select server mode or webserver mode for type items, you must first start the corresponding service mode. Standalone (in-process) does not support databasemanager connections by default. We have explained the specific cause before. As for In-memory, you can log on at will, and all the data in the operations will not be recorded on the local disk. Type has many other options. For specific usage instructions, refer to the official documentation, which is located in the HSQLDB directory/doc/GUIDE/guide.pdf.

If you want to run databasemanagerswing, it is also very easy. I believe you have come up with it. We only need to modify the command to start databasemanager:
Java-CP ../lib/HSQLDB. Jar org. HSQLDB. util. databasemanagerswing

The operation methods of the two tools are similar, so we will not repeat them here.

I almost forgot. I mentioned earlier that I would like to give a method to change the SA user password. It takes up a little time. When you log on successfully using SA via databasemanager, the following page appears:

Enter SET Password "newpassword" in the blank area in the upper right corner and click execute.

Here, we will end with a brief introduction to HSQLDB.

This article is based on the official HSQLDB documentation. If your personal level is limited, errors may inevitably occur,

1. BMP
Description:
File Size:	483 Kb
Read:	File downloaded or viewed 1003 times
Download

2. BMP
Description:
File Size:	288 KB
Read:	File downloaded or viewed 957 times
Download