Tutorial

Starting and Using the H2 Console
Connecting to a Database using JDBC
Creating New Databases
Using the Server
Using Hibernate
Using Databases in Web Applications
CSV (Comma Separated Values) Support
Upgrade, Backup, and Restore

Starting and Using the H2 Console

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.

This is a client / server application, so both a server and a client are required to run it.

Depending on your platform and environment, there are multiple ways to start the application:

OS	Java	Start
Windows	1.4 or 1.5	Click [Start], [All Programs], [H2], and [H2 Console] If this worked correctly, an icon will be added to the system tray: 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: http://localhost:8082).
Windows	1.4 or 1.5	Open a file browser, navigate to h2/bin, and double click on h2.bat. 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: http://localhost:8082).
Any	1.4 or 1.5	Open a console window, navigate to the directory 'h2/lib' and type: java -cp h2.jar org.h2.tools.Server

Firewall

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.

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).

Native Version

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.

Testing Java

To check the Java version you have installed, open a command prompt and type:

java -version

If you get an error message, you may need to add the Java binary directory to the path environment variable.

Error Message 'Port is in use'

You can only start one instance of the H2 Console, otherwise you will get the following error message: Port is in use, maybe another ... server already running on.... 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.

Using another Port

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.

Starting Successfully

If starting the server from a console window was successful, a new window will open and display the following text:

H2 Server running on port 9092
Webserver running on https://localhost:8082/

Don't click inside this window; otherwise you might block the application (if you have the Fast-Edit mode enabled).

Connecting to the Server using a Browser

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.

Multiple Concurrent Sessions

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.

Application Properties

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

Login

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].

You can save and reuse previously saved settings. The settings are stored in the Application Properties file.

Error Messages

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

Adding Database Drivers

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.

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.

Only the Java version supports additional drivers (this feature is not supported by the Native version).

Using the Application

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.

Inserting Table Names or Column Names

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.

Disconnecting and Stopping the Application

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.

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.

Connecting to a Database using JDBC

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:

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
  }
}

This code first loads the driver (Class.forName()) and then opens a connection (using DriverManager.getConnection()). The driver name is "org.h2.Driver" in every case. The database URL always needs to start with jdbc:h2: to be recognized by this database. The second parameter in the getConnection() 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.

Creating New Databases

By default, if the database specified in the URL does not yet exist, a new (empty) database is created automatically.

Using the Server

H2 currently supports three servers: a Web Server, a TCP Server and an ODBC Server. The servers can be started in different ways.

Limitations of the Server

There currently are a few limitations when using the server or cluster mode:

• 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.
• CLOBs and BLOBs are sent to the server in one piece and not as a stream. That means those objects need to fit in memory when using the server or cluster mode.

Starting from Command Line

To start the Server from the command line with the default settings, run

java org.h2.tools.Server

This will start the Server with the default options. To get the list of options, run

java org.h2.tools.Server -?

The native version can also be started in this way:

h2-server -?

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.

Starting within an Application

It is also possible to start and stop a Server from within an application. Sample code:

import org.h2.tools.Server;
...
// start the TCP Server with SSL enabled
String[] args = new String[]{"-ssl", "true"};
Server server = Server.startTcpServer(args);
...
// stop the TCP Server
server.stop();

Stopping a TCP Server from Another Process

The TCP Server can be stopped from another process. To stop the server from the command line, run:

java org.h2.tools.Server -tcpShutdown tcp://localhost:9092

To stop the server from a user application, use the following code:

org.h2.tools.Server.shutdownTcpServer("tcp://localhost:9094");

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.

Using Hibernate

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. This dialect will be integrated into Hibernate, but until this is done you need to copy the file into the folder src\org\hibernate\dialect (Hibernate 3.1), rename it to H2Dialect.java and re-compile hibernate.

Using Databases in Web Applications

There are multiple ways to access a database from within web applications. Here are some examples if you use Tomcat or JBoss.

Embedded Mode

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).

Server Mode

The server mode is similar, but it allows you to run the server in another process.

Using a Servlet Listener to Start and Stop a Database

Add the following to the web.xml file (after context-param and before filter):

<listener>
   <listener-class>db.Starter</listener-class>
</listener>

Add the following Starter class:

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();
        }
    }
}

CSV (Comma Separated Values) Support

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.

Writing a CSV File from Within a Database

The built-in function CSVWRITE can be used to create a CSV file from a query. Example:

CREATE TABLE TEST(ID INT, NAME VARCHAR);
INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World');
CALL CSVWRITE('test.csv', 'SELECT * FROM TEST');

Reading a CSV File from Within a Database

A CSV file can be read using the function CSVREAD. Example:

SELECT * FROM CSVREAD('test.csv');

Writing a CSV File from a Java Application

The CSV tool can be used in a Java application even when not using a database at all. Example:

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.fgh", "+41123456789"});
rs.addRow(new String[]{"John Jones", "johnjones@abcde.fgh", "+41976543210"});
Csv.write("test.csv", rs, null);

Reading a CSV File from a Java Application

It is possible to read a CSV file without opening a database. Example:

ResultSet rs = Csv.read("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();

Upgrade, Backup, and Restore

Database Upgrade

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.

Backup

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:

java org.h2.tools.Backup -url jdbc:h2:test -user sa -script test.zip -options compression zip

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.

Restore

To restore a database from a SQL script file, you can use the RunScript tool:

java org.h2.tools.RunScript -url jdbc:h2:test -user sa -script test.zip -options compression zip

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.