Java JDBC Connection with H2 Database

In this tutorial, we will explore how to establish a JDBC connection with an H2 database in Java. H2 is an open-source in-memory database that is fast and lightweight, making it ideal for development and testing purposes.

Introduction

What is JDBC?

Java Database Connectivity (JDBC) is an API that enables Java applications to interact with databases. It provides methods to query and update data in a database, as well as to retrieve metadata about the database itself.

What is H2 Database?

H2 is an open-source, lightweight, fast in-memory database that can also be run in embedded mode. It is widely used for development, testing, and as an embedded database in applications.

Table of Contents

1. Setting Up the H2 Database
2. Adding H2 JDBC Driver to Your Project
3. Establishing a Database Connection
4. Executing SQL Queries
5. Retrieving Data
6. Closing the Connection
7. Conclusion

1. Setting Up the H2 Database

H2 can be used in embedded mode or as an in-memory database. In this tutorial, we will use the in-memory mode.

2. Adding H2 JDBC Driver to Your Project

To interact with an H2 database, you need to add the H2 JDBC driver to your project. If you are using Maven, add the following dependency to your pom.xml file:

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

3. Establishing a Database Connection

We will start by establishing a connection to the H2 database using JDBC.

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

public class JDBCH2Example {
    private static final String URL = "jdbc:h2:mem:jdbc_example";
    private static final String USER = "sa";
    private static final String PASSWORD = "";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void main(String[] args) {
        try (Connection connection = getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the H2 database!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. Executing SQL Queries

We can use the Connection interface to create a Statement object and execute SQL queries. Here, we will create a products table and insert a record into it.

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

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

        String insertSQL = "INSERT INTO products (name, description, price) VALUES ('Laptop', 'Dell Inspiron', 75000.00)";

        try (Connection connection = JDBCH2Example.getConnection();
             Statement statement = connection.createStatement()) {

            // Create table
            statement.execute(createTableSQL);
            System.out.println("Table created successfully!");

            // Insert data
            int rowsInserted = statement.executeUpdate(insertSQL);
            System.out.println(rowsInserted + " row(s) inserted!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5. Retrieving Data

We can use the Connection interface to create a Statement object and execute a query to retrieve data from the products table.

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

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

        try (Connection connection = JDBCH2Example.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(selectSQL)) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String description = resultSet.getString("description");
                double price = resultSet.getDouble("price");

                System.out.println("ID: " + id + ", Name: " + name + ", Description: " + description + ", Price: " + price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6. Closing the Connection

Using the try-with-resources statement ensures that the connection is closed automatically. This is important to free up database resources.

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

public class CloseConnectionExample {
    public static void main(String[] args) {
        try (Connection connection = JDBCH2Example.getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the H2 database!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Conclusion

In this tutorial, we have covered the basics of using the JDBC Connection interface to interact with an H2 database. We demonstrated how to establish a connection, execute SQL queries, retrieve data, and close the connection using the try-with-resources statement. This guide should help you get started with JDBC and understand how to use the Connection interface effectively with H2.