<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Copyright 2004-2009 H2 Group. Multiple-Licensed under the H2 License, Version 1.0,
and under the Eclipse Public License, Version 1.0
(http://h2database.com/html/license.html).
Initial Developer: H2 Group
-->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" /><title>
Tutorial
</title><link rel="stylesheet" type="text/css" href="stylesheet.css" />
<!-- [search] { -->
<script type="text/javascript" src="navigation.js"></script>
</head><body onload="frameMe();">
<table class="content"><tr class="content"><td class="content"><div class="contentDiv">
<!-- } -->
<h1>Tutorial</h1>
<a href="#tutorial_starting_h2_console">
Starting and Using the H2 Console</a><br />
<a href="#console_settings">
Settings of the H2 Console</a><br />
<a href="#connecting_using_jdbc">
Connecting to a Database using JDBC</a><br />
<a href="#creating_new_databases">
Creating New Databases</a><br />
<a href="#using_server">
Using the Server</a><br />
<a href="#using_hibernate">
Using Hibernate</a><br />
<a href="#using_toplink">
Using TopLink and Glassfish</a><br />
<a href="#web_applications">
Using Databases in Web Applications</a><br />
<a href="#csv">
CSV (Comma Separated Values) Support</a><br />
<a href="#upgrade_backup_restore">
Upgrade, Backup, and Restore</a><br />
<a href="#command_line_tools">
Command Line Tools</a><br />
<a href="#open_office">
Using OpenOffice Base</a><br />
<a href="#web_start">
Java Web Start / JNLP</a><br />
<a href="#connection_pool">
Using a Connection Pool</a><br />
<a href="#fulltext">
Fulltext Search</a><br />
<a href="#user_defined_variables">
User-Defined Variables</a><br />
<a href="#date_time">
Date and Time</a><br />
<a href="#spring">
Using Spring</a><br />
<br /><a name="tutorial_starting_h2_console"></a>
<h2>Starting and Using the H2 Console</h2>
<p>
The H2 Console application lets you access a SQL database using a browser interface.
This can be a H2 database, or another database that supports the JDBC API.
</p>
<img src="images/console.png" alt="Web Browser - H2 Console Server - H2 Database" />
<p>
This is a client / server application, so both a server and a client (a browser) are required to run it.
</p><p>
Depending on your platform and environment, there are multiple ways to start the application:
</p>
<table><tr><th>OS</th><th>Start</th></tr>
<tr>
<td>Windows</td>
<td>
Click [Start], [All Programs], [H2], and [H2 Console (Command Line)]<br />
When using the Sun JDK 1.4 or 1.5, a window with the title 'H2 Console ' should appear.
When using the Sun JDK 1.6, an icon will be added to the system tray:
<img src="images/db-16.png" alt="[H2 icon]" /><br />
If you don't get the window and the system tray icon,
then maybe Java is not installed correctly (in this case, try another way to start the application).
A browser window should open and point to the Login page http://localhost:8082).
</td>
</tr>
<tr>
<td>Windows</td>
<td>
Open a file browser, navigate to h2/bin, and double click on h2.bat.<br />
A console window appears. If there is a problem, you will see an error message
in this window. A browser window will open and point to the Login page
(URL: http://localhost:8082).
</td>
</tr>
<tr>
<td>Any</td>
<td>
Open a console window, navigate to the directory 'h2/bin' and type:
<pre>
java -cp h2*.jar org.h2.tools.Server
</pre>
</td>
</tr>
</table>
<h3>Firewall</h3>
<p>
If you start the server, you may get a security warning from the firewall (if you have installed one).
If you don't want other computers in the network to access the application on your machine, you can
let the firewall block those connections. The connection from the local machine will still work.
Only if you want other computers to access the database on this computer, you need allow remote connections
in the firewall.
</p>
<p>
It has been reported that when using Kaspersky 7.0 with firewall, the H2 Console is very slow when
connecting over the IP address. A workaround is to connect using localhost, however this only works
on the local machine.
</p>
<p>
A small firewall is already built into the server: other computers may not connect to the server by default.
To change this, go to 'Preferences' and select 'Allow connections from other computers'.
</p>
<h3>Native Version</h3>
<p>
The native version does not require Java, because it is compiled using GCJ.
However H2 does currently not run stable with GCJ on Windows
It is possible to compile the software to different platforms.
</p>
<h3>Testing Java</h3>
<p>
To check the Java version you have installed, open a command prompt and type:
</p>
<pre>
java -version
</pre>
<p>
If you get an error message, you may need to add the Java binary directory to the path environment variable.
</p>
<h3>Error Message 'Port is in use'</h3>
<p>
You can only start one instance of the H2 Console,
otherwise you will get the following error message:
<code>Port is in use, maybe another ... server already running on...</code>.
It is possible to start multiple console applications on the same computer (using different ports),
but this is usually not required as the console supports multiple concurrent connections.
</p>
<h3>Using another Port</h3>
<p>
If the port is in use by another application, you may want to start the H2 Console on a different port.
This can be done by changing the port in the file .h2.server.properties. This file is stored
in the user directory (for Windows, this is usually in "Documents and Settings/<username>").
The relevant entry is webPort.
</p>
<h3>Starting Successfully</h3>
<p>
If starting the server from a console window was successful,
a new window will open and display the following text:
</p>
<pre>
H2 Server running on port 9092
Webserver running on https://localhost:8082/
</pre>
<p>
Don't click inside this window; otherwise you might block the application (if you have the Fast-Edit mode enabled).
</p>
<h3>Connecting to the Server using a Browser</h3>
<p>
If the server started successfully, you can connect to it using a web browser.
The browser needs to support JavaScript, frames and cascading stylesheets (css).
If you started the server on the same computer as the browser, go to http://localhost:8082 in the browser.
If you want to connect to the application from another computer, you need to provide the IP address of the server, for example:
<a href="http://192.168.0.2:8082">http://192.168.0.2:8082</a>.
If you enabled SSL on the server side, the URL needs to start with HTTPS.
</p>
<h3>Multiple Concurrent Sessions</h3>
<p>
Multiple concurrent browser sessions are supported. As that the database objects reside on the server,
the amount of concurrent work is limited by the memory available to the server application.
</p>
<h3>Login</h3>
<p>
At the login page, you need to provide connection information to connect to a database.
Set the JDBC driver class of your database, the JDBC URL, user name and password.
If you are done, click [Connect].
</p><p>
You can save and reuse previously saved settings. The settings are stored in the
Application Properties file.
</p>
<h3>Error Messages</h3>
<p>
Error messages in are shown in red. You can show/hide the stack trace of the exception
by clicking on the message.
</p>
<h3>Adding Database Drivers</h3>
<p>
Additional database drivers can be registered by adding the Jar file location of the driver to the environment
variables H2DRIVERS or CLASSPATH. Example (Windows): To add the database driver library
C:\Programs\hsqldb\lib\hsqldb.jar, set the environment variable H2DRIVERS to
C:\Programs\hsqldb\lib\hsqldb.jar.
</p><p>
Multiple drivers can be set; each entry needs to be separated with a ';' (Windows) or ':' (other operating systems).
Spaces in the path names are supported. The settings must not be quoted.
</p>
<h3>Using the Application</h3>
<p>
The application has three main panels, the toolbar on top, the tree on the left and the query / result panel on the right.
The database objects (for example, tables) are listed on the left panel.
Type in a SQL command on the query panel and click 'Run'. The result of the command appears just below the command.
</p>
<h3>Inserting Table Names or Column Names</h3>
<p>
The table name and column names can be inserted in the script by clicking them in the tree.
If you click on a table while the query is empty, a 'SELECT * FROM ...' is added as well.
While typing a query, the table that was used is automatically expanded in the tree.
For, example if you type 'SELECT * FROM TEST T WHERE T.' then the table TEST is automatically expanded in the tree.
</p>
<h3>Disconnecting and Stopping the Application</h3>
<p>
On the browser, click 'Disconnect' on the toolbar panel. You will be logged out of the database.
However, the server is still running and ready to accept new sessions.
</p><p>
To stop the server, right click on the system tray icon and select [Exit].
If you don't have the icon (because you started it in another way),
press [Ctrl]+[C] on the console where the server was started (Windows),
or close the console window.
</p>
<br /><a name="console_settings"></a>
<h2>Settings of the H2 Console</h2>
<p>
The settings of the H2 Console are stored in a configuration file
called <code>.h2.server.properties</code> in you user home directory.
For Windows installations, the user home directory is usually <code>C:\Documents and Settings\[username]</code>.
The configuration file contains the settings of the application and is automatically created when the H2 Console is first started.
</p>
<br /><a name="connecting_using_jdbc"></a>
<h2>Connecting to a Database using JDBC</h2>
<p>
To connect to a database, a Java application first needs to load the database driver,
and then get a connection. A simple way to do that is using the following code:
</p>
<pre>
import java.sql.*;
public class Test {
public static void main(String[] a)
throws Exception {
Class.forName("org.h2.Driver");
Connection conn = DriverManager.
getConnection("jdbc:h2:~/test", "sa", "");
// add application code here
}
}
</pre>
<p>
This code first loads the driver (<code>Class.forName()</code>)
and then opens a connection (using <code>DriverManager.getConnection()</code>).
The driver name is <code>"org.h2.Driver"</code> in every case.
The database URL always needs to start with <code>jdbc:h2:</code>
to be recognized by this database. The second parameter in the <code>getConnection()</code> call
is the user name ('sa' for System Administrator in this example). The third parameter is the password.
Please note that in this database, user names are not case sensitive, but passwords are case sensitive.
</p>
<br /><a name="creating_new_databases"></a>
<h2>Creating New Databases</h2>
<p>
By default, if the database specified in the URL does not yet exist, a new (empty)
database is created automatically. The user that created the database automatically becomes
the administrator of this database.
</p>
<br /><a name="using_server"></a>
<h2>Using the Server</h2>
<p>
H2 currently supports three servers: a Web Server, a TCP Server and an ODBC Server.
The servers can be started in different ways.
</p>
<h3>Starting the Server from Command Line</h3>
<p>
To start the Server from the command line with the default settings, run
</p>
<pre>
java org.h2.tools.Server
</pre>
<p>
This will start the Server with the default options. To get the list of options and default values, run
</p>
<pre>
java org.h2.tools.Server -?
</pre>
<p>
There are options available to use different ports, and start or not start
parts of the Server and so on. For details, see the API documentation of the Server tool.
</p>
<h3>Connecting to the TCP Server</h3>
<p>
To remotely connect to a database using the TCP server, use the following driver and database URL:
</p>
<ul>
<li>JDBC driver class: org.h2.Driver
</li><li>Database URL: jdbc:h2:tcp://localhost/~/test
</li></ul>
<p>
For details about the database URL, see also in Features.
</p>
<h3>Starting the Server within an Application</h3>
<p>
It is also possible to start and stop a Server from within an application. Sample code:
</p>
<pre>
import org.h2.tools.Server;
...
// start the TCP Server
Server server = Server.createTcpServer(args).start();
...
// stop the TCP Server
server.stop();
</pre>
<h3>Stopping a TCP Server from Another Process</h3>
<p>
The TCP Server can be stopped from another process.
To stop the server from the command line, run:
</p>
<pre>
java org.h2.tools.Server -tcpShutdown tcp://localhost:9092
</pre>
<p>
To stop the server from a user application, use the following code:
</p>
<pre>
org.h2.tools.Server.shutdownTcpServer("tcp://localhost:9094");
</pre>
<p>
This function will call System.exit on the server.
This function should be called after all connections to the databases are closed
to avoid recovery when the databases are opened the next time.
To stop remote server, remote connections must be enabled on the server.
</p>
<br /><a name="using_hibernate"></a>
<h2>Using Hibernate</h2>
<p>
This database supports Hibernate version 3.1 and newer. You can use the HSQLDB Dialect,
or the native H2 Dialect. Unfortunately the H2 Dialect included in Hibernate is buggy. A
<a href="http://opensource.atlassian.com/projects/hibernate/browse/HHH-3401">patch
for Hibernate</a> has been submitted. The dialect for the newest version of Hibernate
is also available at src/tools/org/hibernate/dialect/H2Dialect.java.txt.
You can rename it to H2Dialect.java and include this as a patch in your application.
</p>
<br /><a name="using_toplink"></a>
<h2>Using TopLink and Glassfish</h2>
<p>
To use H2 with Glassfish (or Sun AS), set the Datasource Classname to
<code>org.h2.jdbcx.JdbcDataSource</code>. You can set this in the GUI
at Application Server - Resources - JDBC - Connection Pools,
or by editing the file <code>sun-resources.xml</code>: at element
<code>jdbc-connection-pool</code>, set the attribute
<code>datasource-classname</code> to <code>org.h2.jdbcx.JdbcDataSource</code>.
</p>
<p>
The H2 database is compatible with HSQLDB and PostgreSQL.
To take advantage of H2 specific features, use the <code>H2Platform</code>.
The source code of this platform is included in H2 at
<code>src/tools/oracle/toplink/essentials/platform/database/DatabasePlatform.java.txt</code>.
You will need to copy this file to your application, and rename it to .java.
To enable it, change the following setting in persistence.xml:
</p>
<pre>
<property
name="toplink.target-database"
value="oracle.toplink.essentials.platform.database.H2Platform"/>
</pre>
<p>
In old versions of Glassfish, the property name is <code>toplink.platform.class.name</code>.
</p>
<br /><a name="web_applications"></a>
<h2>Using Databases in Web Applications</h2>
<p>
There are multiple ways to access a database from within web
applications. Here are some examples if you use Tomcat or JBoss.
</p>
<h3>Embedded Mode</h3>
<p>
The (currently) simplest solution is to use the database in the
embedded mode, that means open a connection in your application when
it starts (a good solution is using a Servlet Listener, see below), or
when a session starts. A database can be accessed from multiple
sessions and applications at the same time, as long as they run in the
same process. Most Servlet Containers (for example Tomcat) are just
using one process, so this is not a problem (unless you run Tomcat in
clustered mode). Tomcat uses multiple threads and multiple
classloaders. If multiple applications access the same database at the
same time, you need to put the database jar in the shared/lib or
server/lib directory. It is a good idea to open the database when the
web application starts, and close it when the web application stops.
If using multiple applications, only one (any) of them needs to do
that. In the application, an idea is to use one connection per
Session, or even one connection per request (action). Those
connections should be closed after use if possible (but it's not that
bad if they don't get closed).
</p>
<h3>Server Mode</h3>
<p>
The server mode is similar, but it allows you to run the server in another process.
</p>
<h3>Using a Servlet Listener to Start and Stop a Database</h3>
<p>
Add the h2*.jar file your web application, and
add the following snippet to your web.xml file (after context-param and before filter):
</p>
<pre>
<listener>
<listener-class>org.h2.server.web.DbStarter</listener-class>
</listener>
</pre>
<p>
For details on how to access the database, see the file DbStarter.java.
By default the DbStarter listener opens an embedded connection using the database URL jdbc:h2:~/test
and user name and password 'sa'. If you want to use it within your servlet, you can access it like this:
</p>
<pre>
Connection conn = getServletContext().getAttribute("connection");
</pre>
<p>
The DbStarter can also start the TCP server, however this is disabled by default.
To enable it, use the parameter db.tcpServer in the file web.xml. Here is the complete list of options.
These options are set just after the display-name and description tag, but before any listener and filter tags:
</p>
<pre>
<context-param>
<param-name>db.url</param-name>
<param-value>jdbc:h2:~/test</param-value>
</context-param>
<context-param>
<param-name>db.user</param-name>
<param-value>sa</param-value>
</context-param>
<context-param>
<param-name>db.password</param-name>
<param-value>sa</param-value>
</context-param>
<context-param>
<param-name>db.tcpServer</param-name>
<param-value>-tcpAllowOthers</param-value>
</context-param>
</pre>
<p>
When the web application is stopped, the database connection will be closed automatically.
If the server is started within the DbStarter, it will also be stopped automatically when the
web application is stopped.
</p>
<h3>Using the H2 Console Servlet</h3>
<p>
The H2 Console is a standalone application and includes its own web server, but it can be
used as a servlet as well. To do that, include the the h2 jar file in your application, and
add the following configuration to your web.xml:
</p>
<pre>
<servlet>
<servlet-name>H2Console</servlet-name>
<servlet-class>org.h2.server.web.WebServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>H2Console</servlet-name>
<url-pattern>/console/*</url-pattern>
</servlet-mapping>
</pre>
<p>
For details, see also <code>src/tools/WEB-INF/web.xml</code>.
</p>
<p>
To create a web application that creates just the H2 Console, run the following command:
</p>
<pre>
build warConsole
</pre>
<br /><a name="csv"></a>
<h2>CSV (Comma Separated Values) Support</h2>
<p>
The CSV file support can be used inside the database using the functions CSVREAD and CSVWRITE,
and the CSV library can be used outside the database as a standalone tool.
</p>
<h3>Writing a CSV File from Within a Database</h3>
<p>
The built-in function CSVWRITE can be used to create a CSV file from a query.
Example:
</p>
<pre>
CREATE TABLE TEST(ID INT, NAME VARCHAR);
INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World');
CALL CSVWRITE('test.csv', 'SELECT * FROM TEST');
</pre>
<h3>Reading a CSV File from Within a Database</h3>
<p>
A CSV file can be read using the function CSVREAD. Example:
</p>
<pre>
SELECT * FROM CSVREAD('test.csv');
</pre>
<h3>Writing a CSV File from a Java Application</h3>
<p>
The CSV tool can be used in a Java application even when not using a database at all.
Example:
</p>
<pre>
import org.h2.tools.Csv;
import org.h2.tools.SimpleResultSet;
...
SimpleResultSet rs = new SimpleResultSet();
rs.addColumn("NAME", Types.VARCHAR, 255, 0);
rs.addColumn("EMAIL", Types.VARCHAR, 255, 0);
rs.addColumn("PHONE", Types.VARCHAR, 255, 0);
rs.addRow(new String[] { "Bob Meier", "bob.meier@abcde.abc", "+41123456789" });
rs.addRow(new String[] { "John Jones", "john.jones@abcde.abc", "+41976543210" });
Csv.getInstance().write("data/test.csv", rs, null);
</pre>
<h3>Reading a CSV File from a Java Application</h3>
<p>
It is possible to read a CSV file without opening a database.
Example:
</p>
<pre>
import org.h2.tools.Csv;
...
ResultSet rs = Csv.getInstance().read("data/test.csv", null, null);
ResultSetMetaData meta = rs.getMetaData();
while (rs.next()) {
for (int i = 0; i < meta.getColumnCount(); i++) {
System.out.println(meta.getColumnLabel(i + 1) + ": " + rs.getString(i + 1));
}
System.out.println();
}
rs.close();
</pre>
<br /><a name="upgrade_backup_restore"></a>
<h2>Upgrade, Backup, and Restore</h2>
<h3>Database Upgrade</h3>
<p>
The recommended way to upgrade from one version of the database engine to the next
version is to create a backup of the database (in the form of a SQL script) using the old engine,
and then execute the SQL script using the new engine.
</p>
<h3>Backup using the Script Tool</h3>
<p>
There are different ways to backup a database. For example, it is possible to copy the database files.
However, this is not recommended while the database is in use. Also, the database files are not human readable
and quite large. The recommended way to backup a database is to create a compressed SQL script file.
This can be done using the Script tool:
</p>
<pre>
java org.h2.tools.Script -url jdbc:h2:~/test -user sa -script test.zip -options compression zip
</pre>
<p>
It is also possible to use the SQL command SCRIPT to create the backup of the database.
For more information about the options, see the SQL command SCRIPT.
The backup can be done remotely, however the file will be created on the server side.
The built in FTP server could be used to retrieve the file from the server.
</p>
<h3>Restore from a Script</h3>
<p>
To restore a database from a SQL script file, you can use the RunScript tool:
</p>
<pre>
java org.h2.tools.RunScript -url jdbc:h2:~/test -user sa -script test.zip -options compression zip
</pre>
<p>
For more information about the options, see the SQL command RUNSCRIPT.
The restore can be done remotely, however the file needs to be on the server side.
The built in FTP server could be used to copy the file to the server.
It is also possible to use the SQL command RUNSCRIPT to execute a SQL script.
SQL script files may contain references to other script files, in the form of
RUNSCRIPT commands. However, when using the server mode, the references script files
need to be available on the server side.
</p>
<h3>Online Backup</h3>
<p>
The BACKUP SQL statement and the Backup tool both create a zip file
with all database files. However, the contents of this file are not human readable.
Other than the SCRIPT statement, the BACKUP statement does not lock the
database objects, and therefore does not block other users. The resulting
backup is transactionally consistent:
</p>
<pre>
BACKUP TO 'backup.zip'
</pre>
<p>
The Backup tool (org.h2.tools.Backup) can not be used to create a online backup;
the database must not be in use while running this program.
</p>
<br /><a name="command_line_tools"></a>
<h2>Command Line Tools</h2>
<p>
This database comes with a number of command line tools. To get more information about a tool,
start it with the parameter '-?', for example:
</p>
<pre>
java -cp h2*.jar org.h2.tools.Backup -?
</pre>
<p>
The command line tools are:
</p>
<ul><li><b>Backup</b> creates a backup of a database.
</li><li><b>ChangeFileEncryption</b> allows changing the file encryption password or algorithm of a database.
</li><li><b>Console</b> starts the browser based H2 Console.
</li><li><b>ConvertTraceFile</b> converts a .trace.db file to a Java application and SQL script.
</li><li><b>CreateCluster</b> creates a cluster from a standalone database.
</li><li><b>DeleteDbFiles</b> deletes all files belonging to a database.
</li><li><b>Script</b> allows converting a database to a SQL script for backup or migration.
</li><li><b>Recover</b> helps recovering a corrupted database.
</li><li><b>Restore</b> restores a backup of a database.
</li><li><b>RunScript</b> runs a SQL script against a database.
</li><li><b>Server</b> is used in the server mode to start a H2 server.
</li><li><b>Shell</b> is a command line database tool.
</li></ul>
<p>
The tools can also be called from an application by calling the main or another public methods.
For details, see the Javadoc documentation.
</p>
<br /><a name="open_office"></a>
<h2>Using OpenOffice Base</h2>
<p>
OpenOffice.org Base supports database access over the JDBC API. To connect to a H2 database
using OpenOffice Base, you first need to add the JDBC driver to OpenOffice.
The steps to connect to a H2 database are:
</p>
<ul><li>Start OpenOffice Writer, go to [Tools], [Options]
</li><li>Make sure you have selected a Java runtime environment in OpenOffice.org / Java
</li><li>Click [Class Path...], [Add Archive...]
</li><li>Select your h2 jar file (location is up to you, could be wherever you choose)
</li><li>Click [OK] (as much as needed), stop OpenOffice (including the Quickstarter)
</li><li>Start OpenOffice Base
</li><li>Connect to an existing database; select JDBC; [Next]
</li><li>Example datasource URL: jdbc:h2:~/test
</li><li>JDBC driver class: org.h2.Driver
</li></ul>
<p>
Now you can access the database stored in the current users home directory.
</p>
<p>
To use H2 in NeoOffice (OpenOffice without X11):
</p>
<ul><li>In NeoOffice, go to [NeoOffice], [Preferences]
</li><li>Look for the page under [NeoOffice], [Java]
</li><li>Click [Classpath], [Add Archive...]
</li><li>Select your h2 jar file (location is up to you, could be wherever you choose)
</li><li>Click [OK] (as much as needed), restart NeoOffice.
</li></ul>
<p>
Now, when creating a new database using the "Database Wizard":
</p>
<ul><li>Select "connect to existing database" and the type "jdbc". Click next.
</li><li>Enter your h2 database URL. The normal behavior of H2 is that a new db is created if it doesn't exist.
</li><li>Next step - up to you... you can just click finish and start working.
</li></ul>
<p>
Another solution to use H2 in NeoOffice is:
</p>
<ul><li>Package the h2 jar within an extension package
</li><li>Install it as a Java extension in NeoOffice
</li></ul>
<p>
This can be done by create it using the NetBeans OpenOffice plugin.
See also <a href="http://wiki.services.openoffice.org/wiki/Extensions_development_java">Extensions Development</a>.
</p>
<br /><a name="web_start"></a>
<h2>Java Web Start / JNLP</h2>
<p>
When using Java Web Start / JNLP (Java Network Launch Protocol), permissions tags must be set in the .jnlp file,
and the application .jar file must be signed. Otherwise, when trying to write to the file system, the following
exception will occur: java.security.AccessControlException: access denied (java.io.FilePermission ... read).
Example permission tags:
</p>
<pre>
<security>
<all-permissions/>
</security>
</pre>
<br /><a name="connection_pool"></a>
<h2>Using a Connection Pool</h2>
<p>
For many databases, opening a connection is slow, and it is a good idea to use a connection pool
to re-use connections. For H2 however opening a connection usually is fast if the database is already
open. Using a connection pool for H2 actually slows down the process a bit, except if
file encryption is used (in this case opening a connection is about half as fast as using
a connection pool). A simple connection pool is included in H2. It is based on the
<a href="http://www.source-code.biz/snippets/java/8.htm">Mini Connection Pool Manager</a>
from Christian d'Heureuse. There are other, more complex connection pools available, for example
<a href="http://jakarta.apache.org/commons/dbcp/">DBCP</a>. The build-in
connection pool is used as follows:
</p>
<pre>
// init
import org.h2.jdbcx.*;
...
JdbcDataSource ds = new JdbcDataSource();
ds.setURL("jdbc:h2:~/test");
ds.setUser("sa");
ds.setPassword("sa");
JdbcConnectionPool cp = JdbcConnectionPool.create(ds);
// use
Connection conn = cp.getConnection();
...
conn.close();
// dispose
cp.dispose();
</pre>
<br /><a name="fulltext"></a>
<h2>Fulltext Search</h2>
<p>
H2 supports Lucene full text search and native full text search implementation.
</p>
<h3>Using the Native Full Text Search</h3>
<p>
To initialize, call:
</p>
<pre>
CREATE ALIAS IF NOT EXISTS FT_INIT FOR "org.h2.fulltext.FullText.init";
CALL FT_INIT();
</pre>
<p>
You need to initialize it in each database where you want to use it.
Afterwards, you can create a full text index for a table using:
</p>
<pre>
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR);
INSERT INTO TEST VALUES(1, 'Hello World');
CALL FT_CREATE_INDEX('PUBLIC', 'TEST', NULL);
</pre>
<p>
PUBLIC is the schema, TEST is the table name. The list of column names (column separated) is optional,
in this case all columns are indexed. The index is updated in read time.
To search the index, use the following query:
</p>
<pre>
SELECT * FROM FT_SEARCH('Hello', 0, 0);
</pre>
<p>
This will produce a result set that contains the query needed to retrieve the data:
</p>
<p>
QUERY: "PUBLIC"."TEST" WHERE "ID"=1
</p>
<p>
To get the raw data, use <code>FT_SEARCH_DATA('Hello', 0, 0);</code>.
The result contains the columns SCHEMA (the schema name),
TABLE (the table name), COLUMNS (an array of column names), and KEYS
(an array of objects). To join a table, use a join as in:
<code>SELECT T.*FROM FT_SEARCH_DATA('Hello', 0, 0) FT, TEST T
WHERE FT.TABLE='TEST' AND T.ID= FT.KEYS[0];</code>
</p>
<p>
You can also call the index from within a Java application:
</p>
<pre>
org.h2.fulltext.FullText.search(conn, text, limit, offset);
org.h2.fulltext.FullText.searchData(conn, text, limit, offset);
</pre>
<h3>Using the Lucene Fulltext Search</h3>
<p>
To use the Lucene full text search, you need the Lucene library in the classpath.
How his is done depends on the application; if you use the H2 Console, you can add the Lucene
jar file to the environment variables H2DRIVERS or CLASSPATH.
To initialize the Lucene full text search in a database, call:
</p>
<pre>
CREATE ALIAS IF NOT EXISTS FTL_INIT FOR "org.h2.fulltext.FullTextLucene.init";
CALL FTL_INIT();
</pre>
<p>
You need to initialize it in each database where you want to use it.
Afterwards, you can create a full text index for a table using:
</p>
<pre>
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR);
INSERT INTO TEST VALUES(1, 'Hello World');
CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', NULL);
</pre>
<p>
PUBLIC is the schema, TEST is the table name. The list of column names (column separated) is optional,
in this case all columns are indexed. The index is updated in read time. To search the index,
use the following query:
</p>
<pre>
SELECT * FROM FTL_SEARCH('Hello', 0, 0);
</pre>
<p>
This will produce a result set that contains the query needed to retrieve the data:
</p>
<p>
QUERY: "PUBLIC"."TEST" WHERE "ID"=1
</p>
<p>
To get the raw data, use <code>FT_SEARCH_DATA('Hello', 0, 0);</code>.
The result contains the columns SCHEMA (the schema name),
TABLE (the table name), COLUMNS (an array of column names), and KEYS
(an array of objects). To join a table, use a join as in:
<code>SELECT T.*FROM FT_SEARCH_DATA('Hello', 0, 0) FT, TEST T
WHERE FT.TABLE='TEST' AND T.ID= FT.KEYS[0];</code>
</p>
<p>
You can also call the index from within a Java application:
</p>
<pre>
org.h2.fulltext.FullTextLucene.search(conn, text, limit, offset);
org.h2.fulltext.FullTextLucene.searchData(conn, text, limit, offset);
</pre>
<br /><a name="user_defined_variables"></a>
<h2>User-Defined Variables</h2>
<p>
This database supports user-defined variables. Variables start with @ and can be used wherever
expressions or parameters are used. Variables not persisted and session scoped, that means only visible for
the session where they are defined. A value is usually assigned using the SET command:
</p>
<pre>
SET @USER = 'Joe';
</pre>
<p>
It is also possible to change a value using the SET() method. This is useful in queries:
</p>
<pre>
SET @TOTAL = NULL;
SELECT X, SET(@TOTAL, IFNULL(@TOTAL, 1.) * X) F FROM SYSTEM_RANGE(1, 50);
</pre>
<p>
Variables that are not set evaluate to NULL. The data type of a user-defined variable is the data type
of the value assigned to it, that means it is not necessary (or possible) to declare variable names before using them.
There are no restrictions on the assigned values; large objects (LOBs) are supported as well.
</p>
<br /><a name="date_time"></a>
<h2>Date and Time</h2>
<p>
Date, time and timestamp values support ISO 8601 formatting, including time zone:
</p>
<pre>
CALL TIMESTAMP '2008-01-01 12:00:00+01:00';
</pre>
<p>
If the time zone is not set, the value is parsed using the current time zone setting of the system.
Date and time information is stored in H2 database files in GMT (Greenwich Mean Time).
If the database is opened using another system time zone, the date and time will change accordingly.
If you want to move a database from one time zone to the other and don't want this to happen,
you need to create a SQL script file using the SCRIPT command or Script tool, and then load
the database using the RUNSCRIPT command or the RunScript tool in the new time zone.
</p>
<br /><a name="spring"></a>
<h2>Using Spring</h2>
<p>
Use the following configuration to start and stop the H2 TCP Server using the Spring Framework:
</p>
<pre>
<bean id="org.h2.tools.Server"
class="org.h2.tools.Server"
factory-method="createTcpServer"
init-method="start"
destroy-method="stop">
<constructor-arg value="-tcp,-tcpAllowOthers,true,-tcpPort,8043" />
</bean>
</pre>
<p>
The "destroy-method" will help prevent exceptions on hot-redeployment or when restarting server.
</p>
<!-- [close] { --></div></td></tr></table><!-- } --><!-- analytics --></body></html>