DOCSEARCHbyAlgolia
Submit Docs

H2 Database Documentation

repository·master·Indexed 26 days ago

https://github.com/h2database/h2database

H2 is a lightweight, high-performance Java SQL database supporting in-memory and disk-based storage. It is JDBC compliant and features Embedded and Server modes, multi-version concurrency control, full-text search, and encrypted databases. The documentation covers Maven integration, building and testing the JAR, and detailed API references for the Command and CommandInterface classes for executing SQL queries and updates.

Tokens
17K
Snippets
5
Records
130
Agent score
88%

What's inside H2 Database

  1. Overview of H2 Database features

    master

    H2 is a fast, open-source Java SQL database with a small footprint (approximately 2.5 MB jar file size). Key features include:

    • Modes: Supports both Embedded and Server modes, with options for disk-based or in-memory databases.
    • Concurrency: Provides transaction support and multi-version concurrency control.
    • Interface: Offers a JDBC API, an ODBC driver, and a browser-based Console application.
    • Capabilities: Includes full-text search and encrypted databases.
    • Platform: Pure Java implementation.

  2. Build the H2 database JAR using Maven

    master

    You can build the H2 database JAR using Maven. Note that JARs generated via Maven are larger than the official releases, do not include OSGi attributes, and have incomplete configuration for the native-image tool. For a JAR compatible with official builds, use the provided build scripts with the jar target instead.

    If Maven is installed, use mvn. If not, use the included Maven Wrapper (./mvnw or ./mvnw.cmd).

  5. Add H2 to a Maven project

    master

    To use H2 in a Java project managed by Maven, add the following dependency to your pom.xml file. Note that you should check the official H2 website for the most recent version number.

    <dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.4.240</version>
</dependency>

  6. Use MERGE INTO to update or insert data

    master

    The MERGE INTO statement allows you to update an existing row or insert a new one in a single operation. It requires a primary key or a specified set of keys to identify existing rows. If a match is found based on the keys, the row is updated; otherwise, a new row is inserted.

    Syntax Patterns:

    1. With explicit values: MERGE INTO table_name (column1, column2, ...) VALUES (val1, val2, ...), (val3, val4, ...)

    2. With a query (Select data): MERGE INTO table_name (column1, column2, ...) SELECT column1, column2, ... FROM source_table

    3. Specifying keys: If keys are not automatically determined by the primary key, you can specify them using the KEY clause: MERGE INTO table_name (column1, column2) KEY (key_column1, key_column2) VALUES (...)

  7. Use INSERT SQL command

    master

    The INSERT command is used to add new rows to a table. H2 supports standard SQL INSERT syntax, as well as MySQL-style and PostgreSQL-style extensions for handling duplicate keys.

    Supported Patterns

    1. Standard Insert Insert specific values into specified columns.

      INSERT INTO table_name (column1, column2) VALUES (value1, value2);

    2. Insert from Select Insert rows into a table based on the results of a query.

      INSERT INTO table_name (column1, column2) SELECT col1, col2 FROM other_table;

    3. MySQL-style: INSERT ... ON DUPLICATE KEY UPDATE If a row being inserted would cause a duplicate key error, perform an update instead.

      INSERT INTO table_name (id, val) VALUES (1, 'new') 
ON DUPLICATE KEY UPDATE val = 'updated';

    4. MySQL-style: INSERT IGNORE / PostgreSQL-style: ON CONFLICT DO NOTHING If a row being inserted would cause a duplicate key error, simply ignore the error and do nothing.

      INSERT IGNORE INTO table_name (id) VALUES (1);
-- or --
INSERT INTO table_name (id) VALUES (1) ON CONFLICT DO NOTHING;

  8. Use the MERGE INTO ... USING syntax

    master

    The MERGE INTO ... USING syntax allows you to synchronize data between a target table and a source table based on a join condition. This syntax is distinct from the MERGE INTO ... KEYS form.

    It follows this general structure:

    1. MERGE INTO target_table
    2. USING source_table
    3. ON condition
    4. WHEN MATCHED (optional clauses for UPDATE or DELETE)
    5. WHEN NOT MATCHED (optional clause for INSERT)

    This command is useful for performing bulk upserts (update or insert) or deletions based on the presence or absence of matching rows in a source dataset.