Skip to content

H
h2database
切换分支/标签
查找文件
Normal viewHistoryPermalink
tutorial.html 33.7 KB
Newer Older
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
1 2 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Thomas Mueller's avatar
update copyright
84595889
 
Thomas Mueller committed
3 
Copyright 2004-2009 H2 Group. Multiple-Licensed under the H2 License, Version 1.0,
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 
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 />
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
56 57 
<br />
<h2 id="tutorial_starting_h2_console">Starting and Using the H2 Console</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
58 
<p>
Thomas Mueller's avatar
--no commit message
502d8756
 
Thomas Mueller committed
59 
The H2 Console application lets you access a SQL database using a browser interface.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
60 61 62 63 64 65 66 67 
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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
68 69 
<table>
<tr><th>OS</th><th>Start</th></tr>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
70 
<tr>
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
71 72 
    <td>Windows</td>
    <td>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
73 
    Click [Start], [All Programs], [H2], and [H2 Console (Command Line)]<br />
Thomas Mueller's avatar
prepare release
e7197fcc
 
Thomas Mueller committed
74 
    When using the Sun JDK 1.5, a window with the title 'H2 Console ' should appear.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
75 76 77 78 
    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).
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
79 
    A browser window should open and point to the Login page at http://localhost:8082 .
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
80 
    </td>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
81 82 
</tr>
<tr>
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
83 84 
    <td>Windows</td>
    <td>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
85 86 87 88 
    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).
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
89 
    </td>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
90 
</tr>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
91 92 93 94 95 96 97 
<tr>
    <td>Any</td>
    <td>
    Double click on the h2*.jar file.
    This only works if the .jar suffix is associated with java.
    </td>
</tr>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
98 
<tr>
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
99 100 101 
    <td>Any</td>
    <td>
    Open a console window, navigate to the directory 'h2/bin' and type:
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
102 
    <pre>java -cp h2*.jar org.h2.tools.Server</pre>
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
103 
    </td>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 
</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>Testing Java</h3>
<p>
Thomas Mueller's avatar
improve documentation
613ec072
 
Thomas Mueller committed
127 
To find out which version of Java is installed, open a command prompt and type:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
128 129 130 131 132 133 134 135 
</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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
136 
<h3>Error Message 'Port may be in use'</h3>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
137 138 139 
<p>
You can only start one instance of the H2 Console,
otherwise you will get the following error message:
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
140 
<code>The Web server could not be started. Possible cause: another server is already running...</code>.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 
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/&lt;username&gt;").
The relevant entry is webPort.
</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.
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
156 157 
JavaScript needs to be enabled.
If you started the server on the same computer as the browser, open the URL http://localhost:8082 .
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
158 
If you want to connect to the application from another computer, you need to provide the IP address of the server, for example:
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
159 160 
http://192.168.0.2:8082 .
If you enabled SSL on the server side, the URL needs to start with https:// .
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
161 162 163 164 165 166 167 168 169 170 171 172 173 174 
</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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
175 176 
You can save and reuse previously saved settings. The settings are stored in a properties file
(see <a href="#console_settings">Settings of the H2 Console</a>).
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
177 178 179 180 181 182 183 184 185 186 187 
</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
Thomas Mueller's avatar
Improved docs.
27d7b7d3
 
Thomas Mueller committed
188 
variables H2DRIVERS or CLASSPATH. Example (Windows): to add the database driver library
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
189 190 191 192 193 194 195 
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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
196 
<h3>Using the H2 Console</h3>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
197 
<p>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
198 
The H2 Console application has three main panels: the toolbar on top, the tree on the left, and the query / result panel on the right.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
199 200 201 202 203 204 205 206 207 208 209 210 211 212 
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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
213 
To log out of the database, click 'Disconnect' in the toolbar panel.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
214 215 216 
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].
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
217 218 219 
If you don't have the system tray icon,
navigate to [Preferences] and click [Shutdown],
press [Ctrl]+[C] in the console where the server was started (Windows),
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
220 221 222 
or close the console window.
</p>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
223 224 
<br />
<h2 id="console_settings">Settings of the H2 Console</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
225 226 227 228 229 230 231 
<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>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
232 233 
<br />
<h2 id="connecting_using_jdbc">Connecting to a Database using JDBC</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
234 235 236 237 238 239 240 
<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 {
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
241 242 243 244 245 246 
    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
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
247 
        conn.close();
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
248 
    }
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
249 250 251 
}
</pre>
<p>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
252 
This code first loads the driver (<code>Class.forName(...)</code>)
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
253 
and then opens a connection (using <code>DriverManager.getConnection()</code>).
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
254 
The driver name is <code>"org.h2.Driver"</code>.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
255 256 257 
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.
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
258 
In this database, user names are not case sensitive, but passwords are.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
259 260 
</p>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
261 262 
<br />
<h2 id="creating_new_databases">Creating New Databases</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
263 264 265 266 267 268 
<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>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
269 270 
<br />
<h2 id="using_server">Using the Server</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
271 
<p>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
272 273 274 
H2 currently supports three server: a web server (for the H2 Console),
a TCP server (for client/server connections) and an PG server (for PostgreSQL clients).
The servers can be started in different ways, one is using the server tool.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
275 276 
</p>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
277 
<h3>Starting the Server Tool from Command Line</h3>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
278 
<p>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
279 
To start the server tool from the command line with the default settings, run:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
280 281 
</p>
<pre>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
282 
java -cp h2*.jar org.h2.tools.Server
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
283 284 
</pre>
<p>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
285 
This will start the server tool with the default options. To get the list of options and default values, run:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
286 287 
</p>
<pre>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
288 
java -cp h2*.jar org.h2.tools.Server -?
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
289 290 
</pre>
<p>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
291 292 
There are options available to use other ports, and start or not start
parts. For details, see the API documentation of the server tool.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
293 294 295 296 297 298 299 300 301 302 303 304 305 306 
</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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
307 
<h3>Starting the TCP Server within an Application</h3>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
308 
<p>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
309 
Servers can also be started and stopped from within an application. Sample code:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
310 311 312 313 314 315 316 317 318 319 320 321 322 
</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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
323 
The TCP server can be stopped from another process.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
324 325 326 327 328 329 330 331 332 333 334 335 
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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
336 337 338 339 340 341 342 
This function will only stop the TCP server.
If other server were started in the same process, they will continue to run.
To avoid recovery when the databases are opened the next time,
all connections to the databases should be closed before calling this method.
To stop a remote server, remote connections must be enabled on the server.
Shutting down a TCP server can be protected using the option -tcpPassword
(the same password must be used to start and stop the TCP server).
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
343 344 
</p>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
345 346 
<br />
<h2 id="using_hibernate">Using Hibernate</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
347 348 349 350 351 352 353 354 355 
<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>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
356 357 
<br />
<h2 id="using_toplink">Using TopLink and Glassfish</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 
<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>
&lt;property
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
376 377 
    name="toplink.target-database"
    value="oracle.toplink.essentials.platform.database.H2Platform"/>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
378 379 380 381 382 
</pre>
<p>
In old versions of Glassfish, the property name is <code>toplink.platform.class.name</code>.
</p>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
383 384 
<br />
<h2 id="web_applications">Using Databases in Web Applications</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 
<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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
418 419 
Add the h2*.jar file to your web application, and
add the following snippet to your web.xml file (between the 'context-param' and the 'filter' section):
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
420 421 422 
</p>
<pre>
&lt;listener>
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
423 
    &lt;listener-class>org.h2.server.web.DbStarter&lt;/listener-class>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
424 425 426 
&lt;/listener>
</pre>
<p>
Thomas Mueller's avatar
--no commit message
b28e03e4
 
Thomas Mueller committed
427 
For details on how to access the database, see the file DbStarter.java.
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
428 429 430 
By default the DbStarter listener opens an embedded connection
using the database URL 'jdbc:h2:~/test', user name 'sa', and password 'sa'.
If you want to use this connection within your servlet, you can access as follows:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
431 
</p>
Thomas Mueller's avatar
--no commit message
b28e03e4
 
Thomas Mueller committed
432 433 434 
<pre>
Connection conn = getServletContext().getAttribute("connection");
</pre>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
435 
<p>
Thomas Mueller's avatar
--no commit message
b28e03e4
 
Thomas Mueller committed
436 437 
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.
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
438 
These options need to be placed between the 'description' tag and the 'listener' / 'filter' tags:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
439 440 441 
</p>
<pre>
&lt;context-param>
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
442 443 
    &lt;param-name>db.url&lt;/param-name>
    &lt;param-value>jdbc:h2:~/test&lt;/param-value>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
444 445 
&lt;/context-param>
&lt;context-param>
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
446 447 
    &lt;param-name>db.user&lt;/param-name>
    &lt;param-value>sa&lt;/param-value>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
448 449 
&lt;/context-param>
&lt;context-param>
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
450 451 
    &lt;param-name>db.password&lt;/param-name>
    &lt;param-value>sa&lt;/param-value>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
452 453 
&lt;/context-param>
&lt;context-param>
Thomas Mueller's avatar
indentation
d7b57e07
 
Thomas Mueller committed
454 455 
    &lt;param-name>db.tcpServer&lt;/param-name>
    &lt;param-value>-tcpAllowOthers&lt;/param-value>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
456 457 458 459 
&lt;/context-param>
</pre>
<p>
When the web application is stopped, the database connection will be closed automatically.
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
460 
If the TCP server is started within the DbStarter, it will also be stopped automatically.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 
</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>
&lt;servlet&gt;
    &lt;servlet-name&gt;H2Console&lt;/servlet-name&gt;
    &lt;servlet-class&gt;org.h2.server.web.WebServlet&lt;/servlet-class&gt;
    &lt;load-on-startup&gt;1&lt;/load-on-startup&gt;
&lt;/servlet&gt;
&lt;servlet-mapping&gt;
    &lt;servlet-name&gt;H2Console&lt;/servlet-name&gt;
    &lt;url-pattern&gt;/console/*&lt;/url-pattern&gt;
&lt;/servlet-mapping&gt;
</pre>
<p>
For details, see also <code>src/tools/WEB-INF/web.xml</code>.
</p>
<p>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
484 
To create a web application with just the H2 Console, run the following command:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
485 486 487 488 489 
</p>
<pre>
build warConsole
</pre>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
490 491 
<br />
<h2 id="csv">CSV (Comma Separated Values) Support</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
492 493 
<p>
The CSV file support can be used inside the database using the functions CSVREAD and CSVWRITE,
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
494 
or it can be used outside the database as a standalone tool.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 
</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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
522 
import java.sql.*;
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
523 524 
import org.h2.tools.Csv;
import org.h2.tools.SimpleResultSet;
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
525 526 527 528 529 
public class TestCsv {
    public static void main(String[] args) throws Exception {
        SimpleResultSet rs = new SimpleResultSet();
        rs.addColumn("NAME", Types.VARCHAR, 255, 0);
        rs.addColumn("EMAIL", Types.VARCHAR, 255, 0);
Thomas Mueller's avatar
Use varargs.
d7856843
 
Thomas Mueller committed
530 531 
        rs.addRow("Bob Meier", "bob.meier@abcde.abc");
        rs.addRow("John Jones", "john.jones@abcde.abc");
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
532 533 534 
        Csv.getInstance().write("data/test.csv", rs, null);
    }
}
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
535 536 537 538 539 540 541 542 
</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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
543 
import java.sql.*;
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
544 
import org.h2.tools.Csv;
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
545 546 547 548 549 550 551 552 553 554 555 556 557 558 
public class TestCsv {
    public static void main(String[] args) throws Exception {
        ResultSet rs = Csv.getInstance().
            read("data/test.csv", null, null);
        ResultSetMetaData meta = rs.getMetaData();
        while (rs.next()) {
            for (int i = 0; i &lt; meta.getColumnCount(); i++) {
                System.out.println(
                    meta.getColumnLabel(i + 1) + ": " +
                    rs.getString(i + 1));
            }
            System.out.println();
        }
        rs.close();
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
559 560 561 562 
    }
}
</pre>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
563 564 
<br />
<h2 id="upgrade_backup_restore">Upgrade, Backup, and Restore</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 

<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>
Thomas Mueller's avatar
--no commit message
180480ee
 
Thomas Mueller committed
622 623 624 625 626 
<p>
Creating a backup while the database is running is not supported, except
if the file systems support creating snapshots. The problem is that it can't
be guaranteed that the data is copied in the right order.
</p>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
627
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
628 629 
<br />
<h2 id="command_line_tools">Command Line Tools</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 
<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>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.
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
649 
</li><li><b>Script</b> allows converting a database to a SQL script for backup or migration.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
650 651 652 653 
</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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
654 
The tools can also be called from an application by calling the main or another public method.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
655 656 657 
For details, see the Javadoc documentation.
</p>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
658 659 
<br />
<h2 id="open_office">Using OpenOffice Base</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
660 661 662 663 664 665 666 667 668 669 670 
<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
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
671 
</li><li>Connect to an existing database; select [JDBC]; [Next]
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
672 673 674 675 676 677 678 679 680 681 682 
</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]
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
683 
</li><li>Click [Class Path], [Add Archive...]
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
684 685 686 687 
</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>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
688 
Now, when creating a new database using the "Database Wizard" :
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
689 
</p>
Thomas Mueller's avatar
Improve documentation
455544f2
 
Thomas Mueller committed
690 691 692 693 
<ul><li>Click [File], [New], [Database].
</li><li>Select [Connect to existing database] and the select [JDBC]. Click next.
</li><li>Example datasource URL: jdbc:h2:~/test
</li><li>JDBC driver class: org.h2.Driver
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
694 695 696 697 698 699 700 701 702 703 704 705 
</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>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
706 707 
<br />
<h2 id="web_start">Java Web Start / JNLP</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
708 709 710 711 712 713 714 715 716 717 718 719 
<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>
&lt;security>
    &lt;all-permissions/>
&lt;/security>
</pre>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
720 721 
<br />
<h2 id="connection_pool">Using a Connection Pool</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
722 
<p>
Thomas Mueller's avatar
--no commit message
0867a55f
 
Thomas Mueller committed
723 724 725 
For H2, opening a connection is fast if the database is already open.
Still, using a connection pool improves performance if you open and close connections a lot.
A simple connection pool is included in H2. It is based on the
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
726 
<a href="http://www.source-code.biz/snippets/java/8.htm">Mini Connection Pool Manager</a>
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
727 728 
from Christian d'Heureuse. There are other, more complex, open source connection pools available,
for example the <a href="http://jakarta.apache.org/commons/dbcp/">Apache Commons DBCP</a>.
Thomas Mueller's avatar
Improved connection pool documentation
33acc2da
 
Thomas Mueller committed
729 
For H2, it is about twice as faster to get a connection from the built-in connection pool than to get
Thomas Mueller's avatar
--no commit message
0867a55f
 
Thomas Mueller committed
730 
one using DriverManager.getConnection(). The build-in connection pool is used as follows:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
731 732 
</p>
<pre>
Thomas Mueller's avatar
Improved connection pool documentation
33acc2da
 
Thomas Mueller committed
733 734 735 736 737 738 739 740 741 742 743 744 745 746 
import java.sql.*;
import org.h2.jdbcx.JdbcConnectionPool;
public class Test {
    public static void main(String[] args) throws Exception {
        JdbcConnectionPool cp = JdbcConnectionPool.create(
            "jdbc:h2:~/test", "sa", "sa");
        for (int i = 0; i &lt; args.length; i++) {
            Connection conn = cp.getConnection();
            conn.createStatement().execute(args[i]);
            conn.close();
        }
        cp.dispose();
    }
}
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
747 748 
</pre>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
749 750 
<br />
<h2 id="fulltext">Fulltext Search</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
751 
<p>
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
752 753 754 
H2 includes two fulltext search implementations. One is using Apache Lucene,
and the other (the native implementation) stores the index data in special
tables in the database.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
755 756 
</p>
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
757 
<h3>Using the Native Fulltext Search</h3>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
758 759 760 761 762 763 764 765 766 
<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.
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
767 
Afterwards, you can create a fulltext index for a table using:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
768 769 770 771 772 773 774 775 
</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,
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
776 
in this case all columns are indexed. The index is updated in realtime.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
777 778 779 780 781 782 
To search the index, use the following query:
</p>
<pre>
SELECT * FROM FT_SEARCH('Hello', 0, 0);
</pre>
<p>
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
783 784 785 786 787 788 
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>
Thomas Mueller's avatar
--no commit message
f747f951
 
Thomas Mueller committed
789 790 791 792 
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:
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
793 
<code>SELECT T.* FROM FT_SEARCH_DATA('Hello', 0, 0) FT, TEST T
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
794 
WHERE FT.TABLE='TEST' AND T.ID=FT.KEYS[0];</code>
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
795 796 
</p>
<p>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
797 798 799 
You can also call the index from within a Java application:
</p>
<pre>
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
800 801 
org.h2.fulltext.FullText.search(conn, text, limit, offset);
org.h2.fulltext.FullText.searchData(conn, text, limit, offset);
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
802 803 804 805 806 
</pre>

<h3>Using the Lucene Fulltext Search</h3>
<p>
To use the Lucene full text search, you need the Lucene library in the classpath.
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
807 
How to do that depends on the application; if you use the H2 Console, you can add the Lucene
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
808 
jar file to the environment variables H2DRIVERS or CLASSPATH.
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
809 
To initialize the Lucene fulltext search in a database, call:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 
</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,
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
826 
in this case all columns are indexed. The index is updated in realtime. To search the index,
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
827 
use the following query:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
828 829 830 831 832 
</p>
<pre>
SELECT * FROM FTL_SEARCH('Hello', 0, 0);
</pre>
<p>
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
833 834 835 836 837 838 
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>
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
839 
To get the raw data, use <code>FTL_SEARCH_DATA('Hello', 0, 0);</code>.
Thomas Mueller's avatar
--no commit message
f747f951
 
Thomas Mueller committed
840 841 842 
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:
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
843 
<code>SELECT T.* FROM FTL_SEARCH_DATA('Hello', 0, 0) FT, TEST T
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
844 
WHERE FT.TABLE='TEST' AND T.ID=FT.KEYS[0];</code>
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
845 846 
</p>
<p>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
847 848 849 
You can also call the index from within a Java application:
</p>
<pre>
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
850 851 
org.h2.fulltext.FullTextLucene.search(conn, text, limit, offset);
org.h2.fulltext.FullTextLucene.searchData(conn, text, limit, offset);
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
852 853 
</pre>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
854 855 
<br />
<h2 id="user_defined_variables">User-Defined Variables</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
856 857 
<p>
This database supports user-defined variables. Variables start with @ and can be used wherever
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
858 859 
expressions or parameters are allowed. Variables are not persisted and session scoped, that means only visible
from within the session in which they are defined. A value is usually assigned using the SET command:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
860 861 862 863 864 
</p>
<pre>
SET @USER = 'Joe';
</pre>
<p>
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
865 
The value can also be changed using the SET() method. This is useful in queries:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
866 867 868 869 870 871 872 873 874 875 876 
</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>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
877 878 
<br />
<h2 id="date_time">Date and Time</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 
<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>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
894 895 
<br />
<h2 id="spring">Using Spring</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
896 
<p>
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
897 
Use the following configuration to start and stop the H2 TCP server using the Spring Framework:
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
898 899 
</p>
<pre>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
900 
&lt;bean id = "org.h2.tools.Server"
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
901 902 903 904 905 906 907 908 
            class="org.h2.tools.Server"
            factory-method="createTcpServer"
            init-method="start"
            destroy-method="stop"&gt;
    &lt;constructor-arg value="-tcp,-tcpAllowOthers,true,-tcpPort,8043" /&gt;
&lt;/bean&gt;
</pre>
<p>
Thomas Mueller's avatar
Improve documentation
df90fb37
 
Thomas Mueller committed
909 
The "destroy-method" will help prevent exceptions on hot-redeployment or when restarting the server.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
910 911 912 913 
</p>

<!-- [close] { --></div></td></tr></table><!-- } --><!-- analytics --></body></html>