Using the Embedded SQL Database Engine

HSYCO embeds HSQLDB (HyperSQL Database), a powerful SQL database engine you can use in your applications via the standard JDBC API.

HSQLDB (HyperSQL DataBase) is a leading SQL relational database engine written in Java. It has a JDBC driver and supports nearly full ANSI-92 SQL plus many SQL:2008 enhancements. The HSQLDB documentation is available at: http://hsqldb.org.

The JDBC API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases. The JDBC API provides a call-level API for SQL-based database access. The JDBC documentation is available at: http://java.sun.com/products/jdbc/overview.html.

HSQLDB offers a small, fast, multithreaded and transactional database engine which offers in-memory and disk-based tables and supports embedded and server modes.

HSQLDB is directly integrated in the core HSYCO package, so there is no need to add any additional jar file to the HSYCO classpath.

The database that HSYCO uses is called “hsyco” and its files are saved in the data/ directory under the base directory, where hsyco.ini and all other configuration files are also located.

We consider the data/ directory a reserved HSYCO path and discourage the use of it for storing user’s databases or in general for any customized purpose.

We suggest using userdata/ as the general base path for all customized file storage needs, including the location of your own HSQLDB databases.

HSYCO uses HSQLDB to store persistent variables.

It is very likely that we will use it for other purposes in the future, so it is important that you follow a few simple guidelines to avoid potential conflicts in future releases.

Moreover, the resources load of the SQL engine has a direct impact on the performance and stability of HSYCO SERVER.

Connect

First of all, define the imports:

import java.sql.*;

This connects to a DB called mydb in the userdata/ directory, with a write delay of 100 milliseconds. You can of course use different connection parameters:

static Connection dbConnection = DriverManager.getConnection("jdbc:hsqldb:file:userdata/mydb;hsqldb.write_delay_millis=100", "user", "password");
dbConnection.setAutoCommit(true);

You should normally set the auto-commit mode if you don’t need transactions.

Disconnect

try {
	dbConnection.createStatement().execute("shutdown");
	dbConnection.close();
} catch (Exception e) {}

This is the normal shutdown command, followed by a call to close the database connection. Use this sequence if you are not going to access again the database for some time.

You should also consider using a special form of closing the database, using the SHUTDOWN COMPACT command. This should be done periodically, to clean up and minimize the size of data files.

Tables

HyperSQL defines three types if database tables, according to the way the data is stored. These are memory tables, cached tables and text tables.

Memory tables are the default type when the CREATE TABLE command is used.

Memory tables are persistent, but saving data to the files and reading back when the database is opened becomes very time consuming if the database is large.

Cached tables are created with the CREATE CACHED TABLE command. Only part of their data or indexes is held in memory, which is good for handling large tables. The database engine takes less time to start up. The disadvantage of cached tables is a reduction in speed. Do not use cached tables if your data set is relatively small.

The following example shows how to create a table:

try {
	dbConnection.createStatement().execute("create table mytable (id varchar(255) not null unique, value varchar(65536))");
} catch (Exception e) {}

Statement

Tables can be managed to execute query, insert and update operations. For this purpose, use the Statement and PreparedStatement classes.

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 PreparedStatement object is created, the engine keeps the compiled SQL statement for reuse, until the PreparedStatement object is closed. As a result, repeated use of a PreparedStatement is much faster than using a Statement object.

Query

The following examples show how to make a query, an insert and an update operation in a database table.

try {
	PreparedStatement stmt = dbConnection.prepareStatement("select value from mytable where id = ?");
	stmt.setString(1, id);
	ResultSet rs = stmt.executeQuery();
	if (rs.next()) {
		String value = rs.getString(1);
		rs.close();
		return value;
	} else {
		return null;
	}
} catch (Exception e) {}