Embedded HSQL Database with Spring

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.

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.


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.

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.

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

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

Step 8. Create a POJO class for Customer

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

Step 10. Create a Constants class

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

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

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

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

That’s all. Thanks for reading.

Leave a Reply

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

Time limit is exhausted. Please reload CAPTCHA.