H2 Database Engine
Version 1.3.169 (2012-09-09)
1 of 176
Table of Contents
H2 Database Engine.............................................................................................................................................................1
Quickstart..........................................................................................................................................................................12
Embedding H2 in an Application.....................................................................................................................................12
The H2 Console Application...........................................................................................................................................12
Step-by-Step...........................................................................................................................................................12
Installation.........................................................................................................................................................12
Start the Console................................................................................................................................................12
Login.................................................................................................................................................................13
Sample..............................................................................................................................................................14
Execute..............................................................................................................................................................15
Disconnect.........................................................................................................................................................16
End....................................................................................................................................................................16
Installation.........................................................................................................................................................................17
Requirements...............................................................................................................................................................17
Database Engine......................................................................................................................................................17
H2 Console..............................................................................................................................................................17
Supported Platforms......................................................................................................................................................17
Installing the Software..................................................................................................................................................17
Directory Structure........................................................................................................................................................17
Tutorial..............................................................................................................................................................................18
Starting and Using the H2 Console.................................................................................................................................18
Firewall...................................................................................................................................................................19
Testing Java............................................................................................................................................................19
Error Message 'Port may be in use'...........................................................................................................................19
Using another Port...................................................................................................................................................19
Connecting to the Server using a Browser.................................................................................................................19
Multiple Concurrent Sessions....................................................................................................................................19
Login......................................................................................................................................................................20
Error Messages........................................................................................................................................................20
Adding Database Drivers..........................................................................................................................................20
Using the H2 Console...............................................................................................................................................20
Inserting Table Names or Column Names..................................................................................................................20
Disconnecting and Stopping the Application...............................................................................................................20
Special H2 Console Syntax.............................................................................................................................................20
Settings of the H2 Console............................................................................................................................................21
Connecting to a Database using JDBC............................................................................................................................21
Creating New Databases................................................................................................................................................22
Using the Server...........................................................................................................................................................22
Starting the Server Tool from Command Line............................................................................................................22
Connecting to the TCP Server...................................................................................................................................22
Starting the TCP Server within an Application............................................................................................................22
Stopping a TCP Server from Another Process.............................................................................................................22
Using Hibernate............................................................................................................................................................23
Using TopLink and Glassfish..........................................................................................................................................23
Using EclipseLink..........................................................................................................................................................23
Using Apache ActiveMQ.................................................................................................................................................23
Using H2 within NetBeans..............................................................................................................................................24
Using H2 with jOOQ......................................................................................................................................................24
Using Databases in Web Applications.............................................................................................................................24
Embedded Mode......................................................................................................................................................25
Server Mode............................................................................................................................................................25
Using a Servlet Listener to Start and Stop a Database................................................................................................25
Using the H2 Console Servlet....................................................................................................................................25
Android........................................................................................................................................................................26
CSV (Comma Separated Values) Support........................................................................................................................27
Reading a CSV File from Within a Database...............................................................................................................27
Importing Data from a CSV File................................................................................................................................27
Writing a CSV File from Within a Database................................................................................................................27
Writing a CSV File from a Java Application.................................................................................................................27
Reading a CSV File from a Java Application...............................................................................................................28
Upgrade, Backup, and Restore.......................................................................................................................................28
Database Upgrade...................................................................................................................................................28
Backup using the Script Tool....................................................................................................................................28
Restore from a Script...............................................................................................................................................28
Online Backup.........................................................................................................................................................28
Command Line Tools.....................................................................................................................................................29
2 of 176
The Shell Tool...............................................................................................................................................................29
Using OpenOffice Base..................................................................................................................................................30
Java Web Start / JNLP...................................................................................................................................................30
Using a Connection Pool................................................................................................................................................30
Fulltext Search..............................................................................................................................................................31
Using the Native Fulltext Search...............................................................................................................................31
Using the Lucene Fulltext Search..............................................................................................................................31
User-Defined Variables..................................................................................................................................................32
Date and Time..............................................................................................................................................................33
Using Spring.................................................................................................................................................................33
Using the TCP Server...............................................................................................................................................33
Error Code Incompatibility........................................................................................................................................33
Java Management Extension (JMX)................................................................................................................................34
Features............................................................................................................................................................................35
Feature List..................................................................................................................................................................35
Main Features..........................................................................................................................................................35
Additional Features..................................................................................................................................................35
SQL Support............................................................................................................................................................35
Security Features.....................................................................................................................................................36
Other Features and Tools.........................................................................................................................................36
Comparison to Other Database Engines..........................................................................................................................36
DaffodilDb and One$Db............................................................................................................................................37
McKoi......................................................................................................................................................................37
H2 in Use.....................................................................................................................................................................37
Connection Modes.........................................................................................................................................................37
Embedded Mode......................................................................................................................................................37
Server Mode............................................................................................................................................................38
Mixed Mode.............................................................................................................................................................38
Database URL Overview................................................................................................................................................39
Connecting to an Embedded (Local) Database................................................................................................................40
In-Memory Databases...................................................................................................................................................40
Database Files Encryption..............................................................................................................................................40
Creating a New Database with File Encryption...........................................................................................................40
Connecting to an Encrypted Database.......................................................................................................................40
Encrypting or Decrypting a Database........................................................................................................................41
Database File Locking....................................................................................................................................................41
Opening a Database Only if it Already Exists...................................................................................................................41
Closing a Database........................................................................................................................................................41
Delayed Database Closing........................................................................................................................................41
Don't Close a Database when the VM Exits................................................................................................................42
Execute SQL on Connection...........................................................................................................................................42
Ignore Unknown Settings..............................................................................................................................................42
Changing Other Settings when Opening a Connection.....................................................................................................42
Custom File Access Mode...............................................................................................................................................42
Multiple Connections.....................................................................................................................................................43
Opening Multiple Databases at the Same Time..........................................................................................................43
Multiple Connections to the Same Database: Client/Server.........................................................................................43
Multithreading Support.............................................................................................................................................43
Locking, Lock-Timeout, Deadlocks............................................................................................................................43
Avoiding Deadlocks..................................................................................................................................................44
Database File Layout.....................................................................................................................................................44
Moving and Renaming Database Files.......................................................................................................................44
Backup....................................................................................................................................................................44
Logging and Recovery...................................................................................................................................................44
Compatibility.................................................................................................................................................................44
Compatibility Modes.................................................................................................................................................45
DB2 Compatibility Mode...........................................................................................................................................45
Derby Compatibility Mode.........................................................................................................................................45
HSQLDB Compatibility Mode.....................................................................................................................................45
MS SQL Server Compatibility Mode...........................................................................................................................45
MySQL Compatibility Mode.......................................................................................................................................45
Oracle Compatibility Mode........................................................................................................................................46
PostgreSQL Compatibility Mode................................................................................................................................46
Auto-Reconnect............................................................................................................................................................46
Automatic Mixed Mode..................................................................................................................................................46
Page Size......................................................................................................................................................................47
Using the Trace Options................................................................................................................................................47
Trace Options..........................................................................................................................................................47
Setting the Maximum Size of the Trace File...............................................................................................................47
Java Code Generation..............................................................................................................................................48
Using Other Logging APIs..............................................................................................................................................48
3 of 176
Read Only Databases....................................................................................................................................................48
Read Only Databases in Zip or Jar File............................................................................................................................48
Opening a Corrupted Database.................................................................................................................................49
Computed Columns / Function Based Index....................................................................................................................49
Multi-Dimensional Indexes.............................................................................................................................................49
User-Defined Functions and Stored Procedures...............................................................................................................50
Referencing a Compiled Method...............................................................................................................................50
Declaring Functions as Source Code..........................................................................................................................50
Method Overloading.................................................................................................................................................51
Function Data Type Mapping....................................................................................................................................51
Functions That Require a Connection........................................................................................................................51
Functions Throwing an Exception..............................................................................................................................51
Functions Returning a Result Set..............................................................................................................................51
Using SimpleResultSet..............................................................................................................................................51
Using a Function as a Table......................................................................................................................................52
Pluggable or User-Defined Tables...................................................................................................................................52
Triggers........................................................................................................................................................................52
Compacting a Database.................................................................................................................................................53
Cache Settings..............................................................................................................................................................54
Performance......................................................................................................................................................................55
Performance Comparison...............................................................................................................................................55
Embedded...............................................................................................................................................................55
Client-Server...........................................................................................................................................................55
Benchmark Results and Comments...........................................................................................................................56
H2.....................................................................................................................................................................56
HSQLDB.............................................................................................................................................................56
Derby................................................................................................................................................................56
PostgreSQL........................................................................................................................................................56
MySQL...............................................................................................................................................................56
Firebird..............................................................................................................................................................56
Why Oracle / MS SQL Server / DB2 are Not Listed................................................................................................57
About this Benchmark..............................................................................................................................................57
How to Run........................................................................................................................................................57
Separate Process per Database...........................................................................................................................57
Number of Connections.......................................................................................................................................57
Real-World Tests................................................................................................................................................57
Comparing Embedded with Server Databases.......................................................................................................57
Test Platform.....................................................................................................................................................57
Multiple Runs.....................................................................................................................................................57
Memory Usage...................................................................................................................................................57
Delayed Operations............................................................................................................................................58
Transaction Commit / Durability..........................................................................................................................58
Using Prepared Statements.................................................................................................................................58
Currently Not Tested: Startup Time.....................................................................................................................58
PolePosition Benchmark.................................................................................................................................................58
Database Performance Tuning.......................................................................................................................................59
Keep Connections Open or Use a Connection Pool.....................................................................................................59
Use a Modern JVM...................................................................................................................................................59
Virus Scanners.........................................................................................................................................................59
Using the Trace Options...........................................................................................................................................59
Index Usage............................................................................................................................................................59
How Data is Stored Internally...................................................................................................................................59
Optimizer................................................................................................................................................................60
Expression Optimization...........................................................................................................................................60
COUNT(*) Optimization............................................................................................................................................60
Updating Optimizer Statistics / Column Selectivity......................................................................................................60
In-Memory (Hash) Indexes.......................................................................................................................................60
Use Prepared Statements.........................................................................................................................................61
Prepared Statements and IN(...)...............................................................................................................................61
Optimization Examples.............................................................................................................................................61
Cache Size and Type................................................................................................................................................61
Data Types..............................................................................................................................................................61
Sorted Insert Optimization........................................................................................................................................61
Using the Built-In Profiler..............................................................................................................................................61
Application Profiling.......................................................................................................................................................62
Analyze First............................................................................................................................................................62
Database Profiling.........................................................................................................................................................62
Statement Execution Plans............................................................................................................................................63
Displaying the Scan Count........................................................................................................................................63
Special Optimizations...............................................................................................................................................64
How Data is Stored and How Indexes Work....................................................................................................................64
4 of 176
Indexes...................................................................................................................................................................64
Using Multiple Indexes.............................................................................................................................................65
Fast Database Import....................................................................................................................................................66
Advanced...........................................................................................................................................................................67
Result Sets...................................................................................................................................................................67
Statements that Return a Result Set.........................................................................................................................67
Limiting the Number of Rows...................................................................................................................................67
Large Result Sets and External Sorting......................................................................................................................67
Large Objects...............................................................................................................................................................68
Storing and Reading Large Objects...........................................................................................................................68
When to use CLOB/BLOB..........................................................................................................................................68
Large Object Compression........................................................................................................................................68
Linked Tables...............................................................................................................................................................68
Updatable Views...........................................................................................................................................................68
Transaction Isolation.....................................................................................................................................................69
Table Level Locking.................................................................................................................................................69
Lock Timeout...........................................................................................................................................................69
Multi-Version Concurrency Control (MVCC).....................................................................................................................70
Clustering / High Availability..........................................................................................................................................70
Using the CreateCluster Tool....................................................................................................................................70
Detect Which Cluster Instances are Running.............................................................................................................71
Clustering Algorithm and Limitations.........................................................................................................................71
Two Phase Commit.......................................................................................................................................................71
Compatibility.................................................................................................................................................................72
Transaction Commit when Autocommit is On.............................................................................................................72
Keywords / Reserved Words.....................................................................................................................................72
Standards Compliance...................................................................................................................................................72
Supported Character Sets, Character Encoding, and Unicode......................................................................................72
Run as Windows Service................................................................................................................................................72
Install the Service....................................................................................................................................................72
Start the Service......................................................................................................................................................73
Connect to the H2 Console.......................................................................................................................................73
Stop the Service......................................................................................................................................................73
Uninstall the Service................................................................................................................................................73
Additional JDBC drivers............................................................................................................................................73
ODBC Driver.................................................................................................................................................................73
ODBC Installation....................................................................................................................................................73
Starting the Server...................................................................................................................................................73
ODBC Configuration.................................................................................................................................................74
PG Protocol Support Limitations................................................................................................................................74
Security Considerations............................................................................................................................................74
Using Microsoft Access.............................................................................................................................................74
Using H2 in Microsoft .NET............................................................................................................................................75
Using the ADO.NET API on .NET...............................................................................................................................75
Using the JDBC API on .NET.....................................................................................................................................75
ACID............................................................................................................................................................................75
Atomicity.................................................................................................................................................................75
Consistency.............................................................................................................................................................75
Isolation..................................................................................................................................................................76
Durability................................................................................................................................................................76
Durability Problems.......................................................................................................................................................76
Ways to (Not) Achieve Durability..............................................................................................................................76
Running the Durability Test......................................................................................................................................77
Using the Recover Tool.................................................................................................................................................77
File Locking Protocols....................................................................................................................................................77
File Locking Method 'File'..........................................................................................................................................77
File Locking Method 'Socket'.....................................................................................................................................78
File Locking Method 'FS'...........................................................................................................................................78
File Locking Method 'Serialized'......................................................................................................................................78
Using Passwords...........................................................................................................................................................79
Using Secure Passwords...........................................................................................................................................79
Passwords: Using Char Arrays instead of Strings........................................................................................................79
Passing the User Name and/or Password in the URL..................................................................................................79
Password Hash.............................................................................................................................................................79
Protection against SQL Injection....................................................................................................................................80
What is SQL Injection..............................................................................................................................................80
Disabling Literals.....................................................................................................................................................80
Using Constants.......................................................................................................................................................80
Using the ZERO() Function.......................................................................................................................................81
Protection against Remote Access..................................................................................................................................81
Restricting Class Loading and Usage..............................................................................................................................81
5 of 176
