Configure HSQLDB in java web application

Here we will learn how to configure HSQLDB in Spring web application.

HSQLDB (HyperSQL DataBase) is the SQL relational db engine written in Java. It offers a small, fast multithreaded and transactional database engine with in-memory and disk-based tables and supports embedded and server modes. It includes tools such as a command line SQL tool and GUI query tools.

Current Release : 2.2.8 (Released January 2012)

Pre-Requisite :

JDK 1.6 (Separate download for JDK 1.5 is available)

hsqldb.jar (Included in hsqldb-2.2.8.zip. Can be downloaded from here )

Spring JDBC 3.1 Jar

How to configure in Spring :

To configure HSQLDB, we need to add  configuration details in applicationContext.xml with other bean information.

<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:jd="http://www.springframework.org/schema/jdbc"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
      http://www.springframework.org/schema/jdbc
      http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

 <!-- Below configuration has been added to enable in memory DB HSQLDB  -->
 <jd:embedded-database id="dataSource" type="HSQL">
	  <jd:script location="classpath:schema.sql"/>
	  <jd:script location="classpath:test-data.sql"/>
 </jd:embedded-database>

 

Here schema.sql contains the detail of DB that needs to be created by HSQLDB.

CREATE TABLE JBT_MEM(ID VARCHAR(5),PAN_NUMBER  VARCHAR(10),ADDRESS VARCHAR(40),CITY VARCHAR(35),STATE VARCHAR(2),PINCODE integer);

 

To populate the newly created DB test-data.sql script would be required.

insert into JBT_MEM values('20','AABBAABB','Address','NY','AB',23500);
insert into JBT_MEM values('20','BGDBCBDB','Address','City','State',23500);

Above created datasource can be used in combination of jdbctemplate and Hibernate. This datasource can directly be injected in dao layer to work with jdbctemplate.

 <bean id="SpringJdbcDao"  class="com.dao.SpringJdbcDaoImpl">
  <property name="dataSource" ref="dataSource"/>
 </bean>

To work with hibernate one more configuration would be required

 <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
     <property name="dataSource" ref="dataSource"/>
     <property name="hibernateProperties">
       <props>
             <prop key="hibernate.dialect">org.hsqldb.jdbcDriver</prop>
             <prop key="hibernate.show_sql">true</prop>
       </props>
     </property>
  <property name="packagesToScan" value="com.beans"></property>
  <property name="mappingResources" value="JBTMem.hbm.xml"></property>
 </bean>

 

This session factory can now be injected in Dao layer.

<bean id="SpringHibernateDao"  class="com.dao.SpringHibernateDaoImpl">
  <property name="sessionFactory" ref="sessionFactory"/>
 </bean>

 

By | 2012-07-12T03:15:29+00:00 July 12th, 2012|Spring|6 Comments

About the Author:

6 Comments

  1. Kaushal Pandya December 27, 2012 at 12:05 pm - Reply

    Thanks for sharing nice info . really helpful as struggled 1.5 days for it

    • admin December 27, 2012 at 4:39 pm - Reply

      Thanks for your kind words.

  2. Milan February 20, 2013 at 4:33 am - Reply

    I consider this amazing post , “Java Beginners Tutorial Setup data source for HSQLDB in spring”,
    pretty interesting and also the blog post ended up being a good read.
    I appreciate it-Britt

  3. janu April 22, 2013 at 5:49 pm - Reply

    Hi
    I would apprecite i the details regarding the creation of database,where to create the configurations and where the session factory is injected to DAO layer?

  4. InProgress December 2, 2013 at 11:24 pm - Reply

    Ty a lot for that node)

  5. ishika November 19, 2015 at 2:45 pm - Reply

    hello,
    i need guidance in the program where we check the username and password from the database (PostgreSQL) and then if correct then open the next page else show message “Incorrect username and password”.

Leave A Comment