Java H2 Database Tutorial

Author:
In this tutorial, we will learn how to connect to the H2 database using Java JDBC. H2 is an open-source, lightweight, fast, and reliable database management system. It can be embedded in Java applications or run in a client-server mode.

Table of Contents

  1. Introduction
  2. Setting Up the H2 Database
  3. JDBC Driver and Dependencies
  4. Connecting to H2 Database
  5. CRUD Operations
    • Create
    • Read
    • Update
    • Delete
  6. Conclusion

1. Introduction

H2 is a powerful, fast, and lightweight database that is easy to embed in Java applications. It supports standard SQL and JDBC, making it an excellent choice for development, testing, and lightweight applications.

2. Setting Up the H2 Database

Download the H2 database from the official website and follow the installation instructions.

You can start the H2 database in server mode by running the following command:

java -jar h2*.jar

This will start the H2 database and open the web console at http://localhost:8082.

3. JDBC Driver and Dependencies

To connect to H2 from Java, you need the H2 JDBC driver. Add the following dependency to your pom.xml if you are using Maven:

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

4. Connecting to the H2 Database

Let's write a simple Java program to connect to the H2 database.

Example: Connect to H2 Database

package com.example.h2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class H2DatabaseConnection {

    private static final String JDBC_URL = "jdbc:h2:~/test;AUTO_SERVER=TRUE";
    private static final String USER = "sa";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD)) {
            System.out.println("Connected to H2 database successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5. CRUD Operations

5.1 Create Table

Let's create a table named products.

package com.example.h2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTableExample {

    private static final String JDBC_URL = "jdbc:h2:~/test;AUTO_SERVER=TRUE";
    private static final String USER = "sa";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        String createTableSQL = "CREATE TABLE IF NOT EXISTS products (" +
                                "id INT AUTO_INCREMENT PRIMARY KEY, " +
                                "name VARCHAR(255), " +
                                "price DECIMAL(10, 2))";

        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
             Statement statement = connection.createStatement()) {
            statement.execute(createTableSQL);
            System.out.println("Table 'products' created successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5.2 Insert Record

package com.example.h2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertRecordExample {

    private static final String JDBC_URL = "jdbc:h2:~/test;AUTO_SERVER=TRUE";
    private static final String USER = "sa";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        String insertSQL = "INSERT INTO products (name, price) VALUES (?, ?)";

        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
             PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
            preparedStatement.setString(1, "Laptop");
            preparedStatement.setBigDecimal(2, new java.math.BigDecimal("799.99"));
            preparedStatement.executeUpdate();
            System.out.println("Record inserted successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5.3 Read Records

package com.example.h2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ReadRecordsExample {

    private static final String JDBC_URL = "jdbc:h2:~/test;AUTO_SERVER=TRUE";
    private static final String USER = "sa";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        String selectSQL = "SELECT * FROM products";

        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(selectSQL)) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                java.math.BigDecimal price = resultSet.getBigDecimal("price");
                System.out.println("ID: " + id + ", Name: " + name + ", Price: " + price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5.4 Update Record

package com.example.h2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateRecordExample {

    private static final String JDBC_URL = "jdbc:h2:~/test;AUTO_SERVER=TRUE";
    private static final String USER = "sa";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        String updateSQL = "UPDATE products SET price = ? WHERE name = ?";

        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
             PreparedStatement preparedStatement = connection.prepareStatement(updateSQL)) {
            preparedStatement.setBigDecimal(1, new java.math.BigDecimal("899.99"));
            preparedStatement.setString(2, "Laptop");
            preparedStatement.executeUpdate();
            System.out.println("Record updated successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5.5 Delete Record

package com.example.h2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DeleteRecordExample {

    private static final String JDBC_URL = "jdbc:h2:~/test;AUTO_SERVER=TRUE";
    private static final String USER = "sa";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        String deleteSQL = "DELETE FROM products WHERE name = ?";

        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
             PreparedStatement preparedStatement = connection.prepareStatement(deleteSQL)) {
            preparedStatement.setString(1, "Laptop");
            preparedStatement.executeUpdate();
            System.out.println("Record deleted successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6. Conclusion

In this tutorial, we learned how to connect to the H2 database using Java JDBC. We also covered basic CRUD operations: create, read, update, and delete. H2 is a lightweight and fast database that is easy to use with Java applications. Using the techniques shown in this tutorial, you can efficiently manage your data with the H2 database.

Comments

Post a Comment

Leave Comment