tutorial.html 19.7 KB
Newer Older
1 2 3 4 5
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<!-- 
Copyright 2004-2006 H2 Group. Licensed under the H2 License, Version 1.0 (http://h2database.com/html/license.html). 
Initial Developer: H2 Group
-->
6
<html><head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" /><title>
7
Tutorial
8
</title><link rel="stylesheet" type="text/css" href="stylesheet.css" />
9 10 11 12 13 14
<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">
15
    Starting and Using the H2 Console</a><br />
16
<a href="#connecting_using_jdbc">
17
    Connecting to a Database using JDBC</a><br />
18
<a href="#creating_new_databases">
19
    Creating New Databases</a><br />
20
<a href="#using_server">
21
    Using the Server</a><br />    
22
<a href="#using_hibernate">
23
    Using Hibernate</a><br />
24
<a href="#web_applications">
25
    Using Databases in Web Applications</a><br />
26
<a href="#csv">
27
    CSV (Comma Separated Values) Support</a><br />
28
<a href="#upgrade_backup_restore">
29
    Upgrade, Backup, and Restore</a><br />
30
<a href="#open_office">
31
    Using OpenOffice Base</a><br />
32

33
<br /><a name="tutorial_starting_h2_console"></a>
34
<h2>Starting and Using the H2 Console</h2>
35
<p>
36 37
This 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.
38
</p>
39
<img src="console.png" alt="Web Browser - H2 Console Server - H2 Database" />
40
<p>
41 42
This is a client / server application, so both a server and a client are required to run it.
</p><p>
43
Depending on your platform and environment, there are multiple ways to start the application:
44 45
</p>
<table><tr><th>OS</th><th>Java</th><th>Start</th></tr>
46 47 48 49
<tr>
  <td>Windows</td>
  <td>1.4 or 1.5</td>
  <td>
50
      Click [Start], [All Programs], [H2], and [H2 Console]<br />
51
      If this worked correctly, an icon will be added to the system tray:
52
    <img src="h2.png" alt="[H2 icon]" /><br />
53 54 55 56 57 58 59 60 61
    If you don't get 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 (URL: <a href="http://localhost:8082" target="_blank">http://localhost:8082</a>).
  </td>
</tr>
<tr>
  <td>Windows</td>
  <td>1.4 or 1.5</td>
  <td>
62
      Open a file browser, navigate to h2/bin, and double click on h2.bat.<br />
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
      If this worked correctly, an icon will be added to the system tray.
    If there is a problem, you will see the error message on the console window.
    A browser window will open
    and point to the Login page (URL: <a href="http://localhost:8082" target="_blank">http://localhost:8082</a>).
  </td>
</tr>
<tr>
  <td>Any</td>
  <td>1.4 or 1.5</td>
  <td>
      Open a console window, navigate to the directory 'h2/lib' and type:
<pre>
java -cp h2.jar org.h2.tools.Server
</pre>
  </td>
</tr>
</table>

<h3>Firewall</h3>
82
<p>
83 84 85 86 87
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 database 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.
88
</p><p>
89 90 91
Please not that a small firewall is already built into the server. This mechanism by default does not
allow other computer to connect to the server. This can be changed in the Preferences
(Allow connections from other computers).
92
</p>
93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144

<h3>Native Version</h3>
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. 

<h3>Testing Java</h3>
To check the Java version you have installed, open a command prompt and type:
<pre>
java -version
</pre>
If you get an error message, you may need to add the Java binary directory to the path environment variable.

<h3>Error Message 'Port is in use'</h3>
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.

<h3>Using another Port</h3>
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.

<h3>Starting Successfully</h3>
If starting the server from a console window was successful, 
a new window will open and display the following text:
<pre>
H2 Server running on port 9092
Webserver running on https://localhost:8082/
</pre>
Don't click inside this window; otherwise you might block the application (if you have the Fast-Edit mode enabled).

<h3>Connecting to the Server using a Browser</h3>
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:
http://192.168.0.2:8082. If you enabled SSL on the server side, the URL needs to start with HTTPS.

<h3>Multiple Concurrent Sessions</h3>
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.

<h3>Application Properties</h3>
Starting the server will create a configuration file in you local home directory called <code>.h2.server.properties</code>.
For Windows installations, this file will be in the directory <code>C:\Documents and Settings\[username]</code>.
This file contains the settings of the application.

<h3>Login</h3>
145
<p>
146 147 148
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].
149
</p><p>
150 151
You can save and reuse previously saved settings. The settings are stored in the 
Application Properties file.
152
</p>
153 154 155 156 157 158

<h3>Error Messages</h3>
Error messages in are shown in red. You can show/hide the stack trace of the exception
by clicking on the message.

<h3>Adding Database Drivers</h3>
159
<p>
160 161 162
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 
163 164
C:\Programs\hsqldb\lib\hsqldb.jar.
</p><p>
165 166
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.
167
</p><p>
168
Only the Java version supports additional drivers (this feature is not supported by the Native version).
169
</p>
170 171 172 173 174 175 176 177 178 179 180 181 182

<h3>Using the Application</h3>
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.

<h3>Inserting Table Names or Column Names</h3>
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.

<h3>Disconnecting and Stopping the Application</h3>
183
<p>
184
On the browser, click 'Disconnect' on the toolbar panel. You will be logged out of the database.
185 186
However, the server is still running and ready to accept new sessions.
</p><p>
187 188 189 190
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.
191
</p>
192

193
<br /><a name="connecting_using_jdbc"></a>
194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216
<h2>Connecting to a Database using JDBC</h2>
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:
<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>
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.

217
<br /><a name="creating_new_databases"></a>
218 219 220 221
<h2>Creating New Databases</h2>
By default, if the database specified in the URL does not yet exist, a new (empty) 
database is created automatically.

222
<br /><a name="using_server"></a>
223 224 225 226 227 228 229 230 231 232
<h2>Using the Server</h2>
H2 currently supports three servers: a Web Server, a TCP Server and an ODBC Server.
The servers can be started in different ways.

<h3>Limitations of the Server</h3>
There currently are a few limitations when using the server or cluster mode:
<ul>
<li>Statement.cancel() is only supported in embedded mode.
    A connection can only execute one operation at a time in server or cluster mode,
    and is blocked until this operation is finished.
233
</li><li>CLOBs and BLOBs are sent to the server in one piece and not as a stream.
234
    That means those objects need to fit in memory when using the server or cluster mode.
235
</li></ul>
236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259

<h3>Starting from Command Line</h3>
To start the Server from the command line with the default settings, run
<pre>
java org.h2.tools.Server
</pre>
This will start the Server with the default options. To get the list of options, run
<pre>
java org.h2.tools.Server -?
</pre>
The native version can also be started in this way:
<pre>
h2-server -?
</pre>
There are options available to use a different ports, and start or not start 
parts of the Server and so on. For details, see the API documentation of the Server tool.

<h3>Starting within an Application</h3>
It is also possible to start and stop a Server from within an application. Sample code:
<pre>
import org.h2.tools.Server;
...
// start the TCP Server with SSL enabled
String[] args = new String[]{"-ssl", "true"};
260
Server server = Server.createTcpServer(args).start();
261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280
...
// stop the TCP Server
server.stop();
</pre>

<h3>Stopping a TCP Server from Another Process</h3>
The TCP Server can be stopped from another process. 
To stop the server from the command line, run:
<pre>
java org.h2.tools.Server -tcpShutdown tcp://localhost:9092
</pre>
To stop the server from a user application, use the following code:
<pre>
org.h2.tools.Server.shutdownTcpServer("tcp://localhost:9094");
</pre>
This function will call System.exit on the server.
This function should be called after all connection 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.

281
<br /><a name="using_hibernate"></a>
282 283 284
<h2>Using Hibernate</h2>
This database supports Hibernate version 3.1 and newer. You can use the HSQLDB Dialect, 
or the native H2 Dialect that is available in the file src/tools/org/h2/tools/hibernate/H2Dialect.txt.
285
The H2 dialect is included in newer version of Hibernate. For versions where the dialect is missing, you need to copy the file
286 287
into the folder src\org\hibernate\dialect (Hibernate 3.1), rename it to H2Dialect.java and re-compile hibernate.

288
<br /><a name="web_applications"></a>
289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358
<h2>Using Databases in Web Applications</h2>
There are multiple ways to access a database from within web
applications. Here are some examples if you use Tomcat or JBoss.

<h3>Embedded Mode</h3>
The (currently) most simple 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 applications 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).

<h3>Server Mode</h3>
The server mode is similar, but it allows you to run the server in another process. 

<h3>Using a Servlet Listener to Start and Stop a Database</h3>
Add the following to the web.xml file (after context-param and before filter):
<pre>
&lt;listener>
   &lt;listener-class>db.Starter&lt;/listener-class>
&lt;/listener>
</pre>

Add the following Starter class:
<pre>
package org.h2.tools.servlet;

import javax.servlet.*;
import java.sql.*;

public class DbStarter implements ServletContextListener {
    private Connection conn;

    public void contextInitialized(ServletContextEvent servletContextEvent) {
        try {
            Class.forName("org.h2.Driver");
            // You can also get the setting from a context-param in web.xml:
            // ServletContext servletContext = servletContextEvent.getServletContext();
            // String url = servletContext.getInitParameter("db.url");
            conn = DriverManager.getConnection("jdbc:h2:test", "sa", "");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public Connection getConnection() {
        return conn;
    }

    public void contextDestroyed(ServletContextEvent servletContextEvent) {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
</pre>

359
<br /><a name="csv"></a>
360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386
<h2>CSV (Comma Separated Values) Support</h2>
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.

<h3>Writing a CSV File from Within a Database</h3>
The built-in function CSVWRITE can be used to create a CSV file from a query.
Example:
<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>
A CSV file can be read using the function CSVREAD. Example:
<pre>
SELECT * FROM CSVREAD('test.csv');
</pre>

<h3>Writing a CSV File from a Java Application</h3>
The CSV tool can be used in a Java application even when not using a database at all.
Example:
<pre>
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);
387 388
rs.addRow(new String[]{"Bob Meier", "bob.meier@abcde.abc", "+41123456789"});
rs.addRow(new String[]{"John Jones", "john.jones@abcde.abc", "+41976543210"});
389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406
Csv.write("test.csv", rs, null);
</pre>

<h3>Reading a CSV File from a Java Application</h3>
It is possible to read a CSV file without opening a database.
Example:
<pre>
ResultSet rs = Csv.read("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();
</pre>

407
<br /><a name="upgrade_backup_restore"></a>
408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440
<h2>Upgrade, Backup, and Restore</h2>

<h3>Database Upgrade</h3>
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.

<h3>Backup</h3>
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 backup tool:
<pre>
java org.h2.tools.Backup -url jdbc:h2:test -user sa -script test.zip -options compression zip
</pre>
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.
It is also possible to use the SQL command SCRIPT to create the backup of the database.

<h3>Restore</h3>
To restore a database from a SQL script file, you can use the RunScript tool:
<pre>
java org.h2.tools.RunScript -url jdbc:h2:test -user sa -script test.zip -options compression zip
</pre>
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.

441
<br /><a name="open_office"></a>
442 443 444 445 446 447
<h2>Using OpenOffice Base</h2>
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:
<ul>
<li>Stop OpenOffice, including the autostart
448 449 450 451 452 453
</li><li>Copy h2.jar into the directory &lt;OpenOffice&gt;\program\classes
</li><li>Start OpenOffice Base
</li><li>Connect to an existing database, select JDBC, [Next]
</li><li>Example datasource URL: jdbc:h2:c:/temp/test
</li><li>JDBC driver class: org.h2.Driver
</li></ul>
454
Now you can access the database stored in the directory C:/temp.
455 456

</div></td></tr></table></body></html>