H2 Database Cheatsheet

H2 Database is a lightweight, open-source, and fast relational database engine written in Java. It is known for its ease of use and compatibility with various platforms. Whether you are a beginner or an experienced developer, having a cheatsheet for H2 Database can be immensely helpful. This cheatsheet provides a quick reference guide to essential commands and code snippets for working with H2 Database.

1. Installation

# Maven Dependency
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.200</version> <!-- Use the latest version -->
</dependency>

2. Connection

// JDBC URL
String url = "jdbc:h2:~/test"; // Embedded mode

// Connect to the database
Connection connection = DriverManager.getConnection(url, "sa", "");

3. Creating a Database

-- Create a new database
CREATE DATABASE IF NOT EXISTS dbname;

4. Tables

-- Create a table
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    salary DOUBLE
);

5. Data Types

• INT: Integer
• VARCHAR(n): Variable-length character string
• DOUBLE: Double-precision floating-point

6. Inserting Data

-- Insert data into the table
INSERT INTO employees (id, name, salary)
VALUES (1, 'John Doe', 50000.00);

7. Querying Data

-- Select all rows from the table
SELECT * FROM employees;

-- Select specific columns
SELECT id, name FROM employees;

-- Filter data
SELECT * FROM employees WHERE salary > 50000;

8. Updating and Deleting Data

-- Update data
UPDATE employees SET salary = 55000 WHERE id = 1;

-- Delete a row
DELETE FROM employees WHERE id = 1;

9. Indexes

-- Create an index
CREATE INDEX idx_salary ON employees(salary);

10. Transactions

// Begin a transaction
connection.setAutoCommit(false);

// Commit the transaction
connection.commit();

// Rollback the transaction
connection.rollback();

11. Stored Procedures

-- Create a stored procedure
CREATE ALIAS IF NOT EXISTS myProcedure AS $$
String myProcedure() {
    return "Hello, World!";
}
$$;

-- Call the stored procedure
CALL myProcedure();

12. Backup and Restore

-- Backup
BACKUP TO '/path/to/backup.zip';

-- Restore
RESTORE FROM '/path/to/backup.zip';

This H2 Database cheatsheet serves as a handy reference for developers working with H2 Database. Whether you’re creating databases, manipulating data, or managing transactions, these snippets provide a quick and easy way to perform common tasks. Bookmark this cheatsheet for quick access to essential H2 Database commands in your development journey.

FAQ