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) operations on the fly using embedded HSQLDB.

Sometimes we need to work with an in memory database when we 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 we 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.

Spring supports XML as well as Programmatic configuration of beans but, here we will use XML based metadata configuration.

If you already have an idea on how to create a maven project in Eclipse will be great otherwise I will tell you here how to create a maven project in Eclipse.
Prerequisites

The following things are required in order to run the application

Eclipse Kepler
JDK 1.8
Have maven 3 installed and configured
Spring dependencies in pom.xml

Now we will see the below steps how to create a maven based spring project in Eclipse

Step 1. Create a standalone maven project in Eclipse

Go to File -> New -> Other. On popup window under Maven select Maven Project. Then click on Next. Select the workspace location – either default or browse the location. Click on Next. Now in next window select the row as highlighted from the below list of archtypes and click on Next button.

maven-arctype-quickstart

Now enter the required fields (Group Id, Artifact Id) as shown below

Group Id : com.roytuts
Artifact Id : spring-hsqldb

Step 2. Modify the pom.xml file as shown below.

<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-hsqldb</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>spring-hsqldb</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <jdk.version>1.8</jdk.version>
        <junit.version>4.11</junit.version>
        <hsqldb.version>2.2.9</hsqldb.version>
        <spring.version>4.1.5.RELEASE</spring.version>
    </properties>

    <dependencies>
        <!-- Spring framework -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- hsqldb -->
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>${hsqldb.version}</version>
        </dependency>

        <!-- junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>${jdk.version}</source>
                    <target>${jdk.version}</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Step 3. If you see JRE System Library[J2SE-1.4] then change the version by below process

Do right-click on the project and go to Build -> Configure build path, under Libraries tab click on JRE System Library[J2SE-1.4], click on Edit button and select the appropriate jdk 1.8 from the next window. Click on Finish then Ok.

Step 4. Create src/main/resources folder for putting the resource files.

Do right-click on the project and go New -> Source Folder. Give Folder name: as src/main/resources and click on Finish button.

Step 5. Create an XML configuration file under src/main/resources.

Do right-click on src/main/resources in the project and go New -> file. Give File name: as applicationContext.xml and click on Finish button.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context  http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/jdbc
      http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">

    <!-- use annotation based property in classes -->
    <context:annotation-config />

    <!-- embedded hsql database -->
    <!-- note here the jdbc namespace -->
    <jdbc:embedded-database id="dataSource" type="HSQL">
        <jdbc:script location="classpath:schema.sql" />
        <!-- optional, holds initial data to be inserted into the table -->
        <jdbc:script location="classpath:test-data.sql" />
    </jdbc:embedded-database>

    <!-- create jdbctemplate using dataSource -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- data access layer -->
    <bean id="customerDao" class="com.roytuts.spring.hsqldb.dao.CustomerDaoImpl" />
    <!-- business layer -->
    <bean id="customerService" class="com.roytuts.spring.hsqldb.service.CustomerServiceImpl">
        <property name="customerDao" ref="customerDao" />
    </bean>

</beans>

Step 6. Defines the data persistence methods and the class that implements that interface and performs the actual persistence.

package com.roytuts.spring.hsqldb.dao;

import java.util.List;

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

public interface CustomerDao {

    Customer findByCustomerId(long customerId);

    List<Customer> findAllCustomers();

    void saveCustomer(Customer customer);

    void updateCustomer(Customer customer);

    void deleteCustomer(long customerId);

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

import static com.roytuts.spring.hsqldb.utils.Constants.SQL_DELETE_CUSTOMER;
import static com.roytuts.spring.hsqldb.utils.Constants.SQL_FIND_ALL_CUSTOMERS;
import static com.roytuts.spring.hsqldb.utils.Constants.SQL_FIND_BY_CUSTOMER_ID;
import static com.roytuts.spring.hsqldb.utils.Constants.SQL_NEW_CUSTOMER;
import static com.roytuts.spring.hsqldb.utils.Constants.SQL_UPDATE_CUSTOMER;

import java.util.List;

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

import com.roytuts.spring.hsqldb.mapper.CustomerMapper;
import com.roytuts.spring.hsqldb.model.Customer;

public class CustomerDaoImpl implements CustomerDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

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

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

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

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

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

}

Step 7. Business layer: the service interface and implementation of the service interface. 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.hsqldb.service;

import java.util.List;

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

public interface CustomerService {

    Customer findByCustomerId(long customerId);

    List<Customer> findAllCustomers();

    void saveCustomer(Customer customer);

    void updateCustomer(Customer customer);

    void deleteCustomer(long customerId);

}
package com.roytuts.spring.hsqldb.service;

import java.util.List;

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

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

@Service
public class CustomerServiceImpl implements CustomerService {

    @Autowired
    private CustomerDao customerDao;

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

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

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

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

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

    public CustomerDao getCustomerDao() {
        return customerDao;
    }

    public void setCustomerDao(CustomerDao customerDao) {
        this.customerDao = customerDao;
    }

}

Step 8. Create a POJO class for Customer

package com.roytuts.spring.hsqldb.model;

public class Customer {

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

    public Long getCustomerId() {
        return customerId;
    }

    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }

    public String getCustomerName() {
        return customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    public String getCustomerAddress() {
        return customerAddress;
    }

    public void setCustomerAddress(String customerAddress) {
        this.customerAddress = customerAddress;
    }

    public String getCustomerCity() {
        return customerCity;
    }

    public void setCustomerCity(String customerCity) {
        this.customerCity = customerCity;
    }

    public String getCustomerState() {
        return customerState;
    }

    public void setCustomerState(String customerState) {
        this.customerState = customerState;
    }

    public String getCustomerZip() {
        return customerZip;
    }

    public void setCustomerZip(String customerZip) {
        this.customerZip = customerZip;
    }

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

}

Step 9. Create a Row Mapper between Customer object and table row-column

package com.roytuts.spring.hsqldb.mapper;

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

import org.springframework.jdbc.core.RowMapper;

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

public class CustomerMapper 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;
    }

}

Step 10. Create a Constants class

package com.roytuts.spring.hsqldb.utils;

public class Constants {

    private Constants() {
    }

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

}

Step 11. Create schema.sql file and put it under src/main/resources

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
);

Step 12. Create a test-data.sql file and put it under src/main/resources

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')

Step 13. Create Junit class and put it under src/test/java

package com.roytuts.spring.hsqldb.test;

import java.util.List;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

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

public class TestSpringHsqldb {

    private CustomerService customerService;

    @Before
    public void setUp() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext(
                "classpath:applicationContext.xml");
        customerService = applicationContext.getBean("customerService",
                CustomerService.class);
    }

    @Test
    public void testCRUD() {
        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();
        for (Customer customer : customers) {
            System.out.println(customer);
        }

        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();
        for (Customer customerAdd : customersAfterAddition) {
            System.out.println(customerAdd);
        }

        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();
        for (Customer customerDel : customersAfterDeletion) {
            System.out.println(customerDel);
        }
    }

}

Step 14. Run the above Junit class and see the below output in the console

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]

That’s all. Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

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