Posted in Spring JDBC

Embedded HSQL Database with Spring

Introduction

This tutorial will show you how you can work with embedded HSQLDB with Spring framework. This application will show you a CRUD (Create, Read, Update and Delete) operation using embedded HSQLDB.

Sometimes you need to work with an in memory database when you want to demonstrate certain database centric features of an application during development phase. Such situation may be when there is no access to real database server and you want to perform test on an application on the fly using database operations then this may be very helpful.

Spring supports many databases such as HSQL, H2, and Derby as default embedded databases but, we can also use an extensible third party API to plug in new embedded database and DataSource implementations.

Prerequisites

Java at least 8, Spring Boot 2.3.3, HSQLDB 2.5.1, Gradle 6.5.1, Maven 3.6.3

Project Setup

You can create either gradle or maven based project in your favorite IDE or tool. The name of the project is spring-embedded-hsqldb.

You can use the following build.gradle script for your project if it is a gradle based project:

buildscript {
	ext {
		springBootVersion = '2.3.3.RELEASE'
	}
	
    repositories {
    	mavenCentral()
    }
    
    dependencies {
    	classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

plugins {
    id 'java-library'
    id 'org.springframework.boot' version "${springBootVersion}"
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    mavenCentral()
}

dependencies {
	implementation("org.springframework.boot:spring-boot-starter:${springBootVersion}")
	implementation("org.springframework.boot:spring-boot-starter-jdbc:${springBootVersion}")
	implementation 'org.hsqldb:hsqldb:2.5.1'
}

You can use the following pom.xml file if it is a maven based project:

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>spring-embedded-hsqldb</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.3.3.RELEASE</version>
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>2.5.1</version>
		</dependency>
	</dependencies>

    <build>
        <plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>at least 8</source>
					<target>at least 8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Database Configuration

The following class configures database beans for creating datasource.

package com.roytuts.spring.embedded.hsqldb.config;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;

@Configuration
public class Config {

	@Bean
	public DataSource dataSource() {
		EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
		EmbeddedDatabase db = builder.setType(EmbeddedDatabaseType.HSQL).addScript("classpath:table.sql")
				.addScript("classpath:data.sql").build();
		return db;
	}

	@Bean
	public JdbcTemplate jdbcTemplate(DataSource dataSource) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		return jdbcTemplate;
	}

}

As I am using in-memory database, so I did not need to configure any database connection details. The default values will be used for connection details.

I am using SQL scripts to create a table and storing some sample data. The SQL scripts are kept under src/main/resources folder.

table.sql

DROP TABLE CUSTOMER IF EXISTS;
CREATE TABLE CUSTOMER (
    CUSTOMER_ID integer identity primary key,
    CUSTOMER_NAME varchar(50) not null,
    CUSTOMER_ADDRESS varchar(255),
    CUSTOMER_CITY varchar(50) not null,
    CUSTOMER_STATE varchar(50) not null,
    CUSTOMER_ZIP_POSTAL varchar(30) not null
);

data.sql

INSERT INTO CUSTOMER VALUES(1,'Sumit Ghosh','Garfa','Kolkata','West Bengal','700085')
INSERT INTO CUSTOMER VALUES(2,'Gourab Guha','Garia','Kolkata','West Bengal','700145')
INSERT INTO CUSTOMER VALUES(3,'Debina Guha','Kestopur','Kolkata','West Bengal','700185')
INSERT INTO CUSTOMER VALUES(4,'Debabrata Poddar','Birati','Kolkata','West Bengal','700285')
INSERT INTO CUSTOMER VALUES(5,'Amit Dharmale','Thane','Mumbai','Maharastra','400140')

Model Class

I have created a model class for mapping Java fields to database table columns.

package com.roytuts.spring.embedded.hsqldb.model;

public class Customer {

	private Long customerId;
	private String customerName;
	private String customerAddress;
	private String customerCity;
	private String customerState;
	private String customerZip;

	// getters and setters

	@Override
	public String toString() {
		return "[ Customer Id : " + customerId + ", Customer Name : " + customerName + ", Customer Address : "
				+ customerAddress + ", Customer City : " + customerCity + ", Customer State : " + customerState
				+ ", Customer Zip Code : " + customerZip + "]";
	}

}

Row Mapper

To actually map the above model class with database table columns, you need a row mapper class in JDBC API. If you are using JPA API then you can simply use annotation to map your fileds.

package com.roytuts.spring.embedded.hsqldb.row.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.roytuts.spring.embedded.hsqldb.model.Customer;

public class CustomerRowMapper implements RowMapper<Customer> {

	@Override
	public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
		Customer customer = new Customer();

		customer.setCustomerId(rs.getLong("CUSTOMER_ID"));
		customer.setCustomerName(rs.getString("CUSTOMER_NAME"));
		customer.setCustomerAddress(rs.getString("CUSTOMER_ADDRESS"));
		customer.setCustomerCity(rs.getString("CUSTOMER_CITY"));
		customer.setCustomerState(rs.getString("CUSTOMER_STATE"));
		customer.setCustomerZip(rs.getString("CUSTOMER_ZIP_POSTAL"));

		return customer;
	}

}

DAO Class

DAO layer is needed to interact and perform database operations. So here I am going to show you CRUD operations.

package com.roytuts.spring.embedded.hsqldb.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.roytuts.spring.embedded.hsqldb.model.Customer;
import com.roytuts.spring.embedded.hsqldb.row.mapper.CustomerRowMapper;

@Repository
public class CustomerDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	private static final String SQL_NEW_CUSTOMER = "INSERT INTO CUSTOMER(CUSTOMER_NAME, CUSTOMER_ADDRESS, CUSTOMER_CITY, CUSTOMER_STATE, CUSTOMER_ZIP_POSTAL) VALUES(?,?,?,?,?)";
	private static final String SQL_UPDATE_CUSTOMER = "UPDATE CUSTOMER SET CUSTOMER_NAME = ?, CUSTOMER_ADDRESS = ?, CUSTOMER_CITY = ?, CUSTOMER_STATE = ?, CUSTOMER_ZIP_POSTAL = ? WHERE CUSTOMER_ID = ?";
	private static final String SQL_DELETE_CUSTOMER = "DELETE FROM CUSTOMER WHERE CUSTOMER_ID = ?";
	private static final String SQL_FIND_ALL_CUSTOMERS = "SELECT * FROM CUSTOMER";
	private static final String SQL_FIND_BY_CUSTOMER_ID = "SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = ?";

	public Customer findByCustomerId(long customerId) {
		Customer customer = jdbcTemplate.queryForObject(SQL_FIND_BY_CUSTOMER_ID, new CustomerRowMapper(),
				new Object[] { customerId });
		return customer;
	}

	public List<Customer> findAllCustomers() {
		List<Customer> customers = jdbcTemplate.query(SQL_FIND_ALL_CUSTOMERS, new CustomerRowMapper());
		return customers;
	}

	public void saveCustomer(Customer customer) {
		jdbcTemplate.update(SQL_NEW_CUSTOMER, new Object[] { customer.getCustomerName(), customer.getCustomerAddress(),
				customer.getCustomerCity(), customer.getCustomerState(), customer.getCustomerZip() });
	}

	public void updateCustomer(Customer customer) {
		jdbcTemplate.update(SQL_UPDATE_CUSTOMER,
				new Object[] { customer.getCustomerName(), customer.getCustomerAddress(), customer.getCustomerCity(),
						customer.getCustomerState(), customer.getCustomerZip(), customer.getCustomerId() });
	}

	public void deleteCustomer(long customerId) {
		jdbcTemplate.update(SQL_DELETE_CUSTOMER, new Object[] { customerId });
	}

}

Service Layer

Business layer or the service is mainly used to perform business processing. The CustomerService is simply a pass-through to the data access layer, but it is added for two reasons:

  • It provides a layer of abstraction between the application and the data access layer.
  • It provides a place to add additional real business logic or business rules.
package com.roytuts.spring.embedded.hsqldb.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.roytuts.spring.embedded.hsqldb.dao.CustomerDao;
import com.roytuts.spring.embedded.hsqldb.model.Customer;

@Service
public class CustomerService {

	@Autowired
	private CustomerDao customerDao;

	public Customer findByCustomerId(long customerId) {
		return customerDao.findByCustomerId(customerId);
	}

	public List<Customer> findAllCustomers() {
		return customerDao.findAllCustomers();
	}

	public void saveCustomer(Customer customer) {
		customerDao.saveCustomer(customer);
	}

	public void updateCustomer(Customer customer) {
		customerDao.updateCustomer(customer);
	}

	public void deleteCustomer(long customerId) {
		customerDao.deleteCustomer(customerId);
	}

}

Main Class

A class with main method and @SpringBootApplication annotation is enough to deploy. Here I am implementing command line interface as I don’t want to create web app.

package com.roytuts.spring.embedded.hsqldb;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.roytuts.spring.embedded.hsqldb.model.Customer;
import com.roytuts.spring.embedded.hsqldb.service.CustomerService;

@SpringBootApplication
public class SpringEmbeddedHsqlDbApp implements CommandLineRunner {

	@Autowired
	private CustomerService customerService;

	public static void main(String[] args) {
		SpringApplication.run(SpringEmbeddedHsqlDbApp.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		System.out.println("Customer information with Customer Id = 2");
		Customer findCustomer = customerService.findByCustomerId(2);
		System.out.println(findCustomer);

		System.out.println();
		System.out.println("List all Customers");

		List<Customer> customers = customerService.findAllCustomers();
		customers.stream().forEach(c -> System.out.println(c));

		System.out.println();
		System.out.println("Add new Customer");
		Customer customer = new Customer();
		customer.setCustomerName("Soumitra Roy");
		customer.setCustomerAddress("Tiruvanmiyur");
		customer.setCustomerCity("Chennai");
		customer.setCustomerState("Tamil Nadu");
		customer.setCustomerZip("600020");
		customerService.saveCustomer(customer);

		System.out.println();
		System.out.println("New customer information");
		Customer cust = customerService.findByCustomerId(6);
		System.out.println(cust);

		System.out.println();
		System.out.println("After creating a new Customer, list all Customers");
		List<Customer> customersAfterAddition = customerService.findAllCustomers();
		customersAfterAddition.stream().forEach(c -> System.out.println(c));

		System.out.println();
		System.out.println("Update customer information for Customer Id = 6");
		Customer customerUpdate = customerService.findByCustomerId(6);
		customerUpdate.setCustomerAddress("Chingrihata");
		customerUpdate.setCustomerCity("Kolkata");
		customerUpdate.setCustomerState("West Bengal");
		customerUpdate.setCustomerZip("700105");
		customerService.updateCustomer(customerUpdate);

		System.out.println();
		System.out.println("After updating customer information for Customer Id = 6");
		Customer custUpdate = customerService.findByCustomerId(6);
		System.out.println(custUpdate);

		System.out.println();
		System.out.println("Delete customer information for Customer Id = 6");
		customerService.deleteCustomer(6);

		System.out.println();
		System.out.println("After deleting a Customer for Customer Id = 6, list all Customers");
		List<Customer> customersAfterDeletion = customerService.findAllCustomers();
		customersAfterDeletion.stream().forEach(c -> System.out.println(c));
	}

}

Running the Application

Executing the above code will give you the following output:

Customer information with Customer Id = 2
[ Customer Id : 2, Customer Name : Gourab Guha, Customer Address : Garia, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700145]

List all Customers
[ Customer Id : 1, Customer Name : Sumit Ghosh, Customer Address : Garfa, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700085]
[ Customer Id : 2, Customer Name : Gourab Guha, Customer Address : Garia, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700145]
[ Customer Id : 3, Customer Name : Debina Guha, Customer Address : Kestopur, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700185]
[ Customer Id : 4, Customer Name : Debabrata Poddar, Customer Address : Birati, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700285]
[ Customer Id : 5, Customer Name : Amit Dharmale, Customer Address : Thane, Customer City : Mumbai, Customer State : Maharastra, Customer Zip Code : 400140]

Add new Customer

New customer information
[ Customer Id : 6, Customer Name : Soumitra Roy, Customer Address : Tiruvanmiyur, Customer City : Chennai, Customer State : Tamil Nadu, Customer Zip Code : 600020]

After creating a new Customer, list all Customers
[ Customer Id : 1, Customer Name : Sumit Ghosh, Customer Address : Garfa, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700085]
[ Customer Id : 2, Customer Name : Gourab Guha, Customer Address : Garia, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700145]
[ Customer Id : 3, Customer Name : Debina Guha, Customer Address : Kestopur, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700185]
[ Customer Id : 4, Customer Name : Debabrata Poddar, Customer Address : Birati, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700285]
[ Customer Id : 5, Customer Name : Amit Dharmale, Customer Address : Thane, Customer City : Mumbai, Customer State : Maharastra, Customer Zip Code : 400140]
[ Customer Id : 6, Customer Name : Soumitra Roy, Customer Address : Tiruvanmiyur, Customer City : Chennai, Customer State : Tamil Nadu, Customer Zip Code : 600020]

Update customer information for Customer Id = 6

After updating customer information for Customer Id = 6
[ Customer Id : 6, Customer Name : Soumitra Roy, Customer Address : Chingrihata, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700105]

Delete customer information for Customer Id = 6

After deleting a Customer for Customer Id = 6, list all Customers
[ Customer Id : 1, Customer Name : Sumit Ghosh, Customer Address : Garfa, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700085]
[ Customer Id : 2, Customer Name : Gourab Guha, Customer Address : Garia, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700145]
[ Customer Id : 3, Customer Name : Debina Guha, Customer Address : Kestopur, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700185]
[ Customer Id : 4, Customer Name : Debabrata Poddar, Customer Address : Birati, Customer City : Kolkata, Customer State : West Bengal, Customer Zip Code : 700285]
[ Customer Id : 5, Customer Name : Amit Dharmale, Customer Address : Thane, Customer City : Mumbai, Customer State : Maharastra, Customer Zip Code : 400140]

Source Code

Download

That’s all. Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *