Java HSQLDB Tutorial - Create, Read, Update and Delete JDBC Examples

Author:
In this tutorial, we will learn how to perform CRUD (Create, Read, Update, and Delete) operations using Java with HSQLDB (HyperSQL Database). HSQLDB is a lightweight, 100% Java SQL database engine that supports a wide range of SQL standards. It is perfect for embedded database applications, testing, and development.

Table of Contents

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

1. Introduction

HSQLDB is a relational database management system written in Java. It can be embedded in Java applications or run in server mode. This tutorial will cover how to use JDBC to connect to HSQLDB and perform CRUD operations.

2. Setting Up HSQLDB

Download HSQLDB from the official website and extract the files to your preferred location. HSQLDB can run in various modes, including in-memory and server modes.

3. JDBC Driver and Dependencies

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

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.5.2</version>
</dependency>

4. Connecting to HSQLDB

Let's write a simple Java program to connect to HSQLDB.

Example: Connect to HSQLDB

package com.example.hsqldb;

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

public class HSQLDBConnection {

    private static final String JDBC_URL = "jdbc:hsqldb:mem:testdb";
    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 HSQLDB database successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation:

  • JDBC_URL: The JDBC URL for the HSQLDB in-memory database.
  • USER and PASSWORD: The default user and password for HSQLDB.
  • DriverManager.getConnection(JDBC_URL, USER, PASSWORD): Establishes a connection to the HSQLDB database.

5. CRUD Operations

5.1 Create Table

Let's create a table named students.

package com.example.hsqldb;

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:hsqldb:mem:testdb";
    private static final String USER = "SA";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        String createTableSQL = "CREATE TABLE IF NOT EXISTS students (" +
                                "id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " +
                                "name VARCHAR(255), " +
                                "email VARCHAR(255))";

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

Explanation:

  • createTableSQL: The SQL statement to create the students table.
  • statement.execute(createTableSQL): Executes the SQL statement to create the table.

5.2 Insert Record

package com.example.hsqldb;

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:hsqldb:mem:testdb";
    private static final String USER = "SA";
    private static final String PASSWORD = "";

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

        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
             PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
            preparedStatement.setString(1, "Arjun");
            preparedStatement.setString(2, "arjun@example.com");
            preparedStatement.executeUpdate();
            System.out.println("Record inserted successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation:

  • insertSQL: The SQL statement to insert a record into the students table.
  • preparedStatement.setString(1, "Arjun"): Sets the value for the first parameter in the SQL statement.
  • preparedStatement.executeUpdate(): Executes the SQL statement to insert the record.

5.3 Read Records

package com.example.hsqldb;

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:hsqldb:mem:testdb";
    private static final String USER = "SA";
    private static final String PASSWORD = "";

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

        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");
                String email = resultSet.getString("email");
                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation:

  • selectSQL: The SQL statement to select all records from the students table.
  • statement.executeQuery(selectSQL): Executes the SQL statement and returns the result set.
  • resultSet.next(): Moves the cursor to the next row in the result set.

5.4 Update Record

package com.example.hsqldb;

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:hsqldb:mem:testdb";
    private static final String USER = "SA";
    private static final String PASSWORD = "";

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

        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
             PreparedStatement preparedStatement = connection.prepareStatement(updateSQL)) {
            preparedStatement.setString(1, "arjun_new@example.com");
            preparedStatement.setString(2, "Arjun");
            preparedStatement.executeUpdate();
            System.out.println("Record updated successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation:

  • updateSQL: The SQL statement to update a record in the students table.
  • preparedStatement.setString(1, "arjun_new@example.com"): Sets the value for the first parameter in the SQL statement.
  • preparedStatement.executeUpdate(): Executes the SQL statement to update the record.

5.5 Delete Record

package com.example.hsqldb;

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:hsqldb:mem:testdb";
    private static final String USER = "SA";
    private static final String PASSWORD = "";

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

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

Explanation:

  • deleteSQL: The SQL statement to delete a record from the students table.
  • preparedStatement.setString(1, "Arjun"): Sets the value for the first parameter in the SQL statement.
  • preparedStatement.executeUpdate(): Executes the SQL statement to delete the record.

6. Conclusion

In this tutorial, we learned how to connect to the HSQLDB database using Java JDBC. We also covered basic CRUD operations: create, read, update, and delete. HSQLDB 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 HSQLDB database.

Comments

Post a Comment

Leave Comment