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
- Introduction
- Setting Up HSQLDB
- JDBC Driver and Dependencies
- Connecting to HSQLDB
- CRUD Operations
- Create
- Read
- Update
- Delete
- 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.USERandPASSWORD: 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 thestudentstable.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 thestudentstable.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 thestudentstable.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 thestudentstable.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 thestudentstable.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