Using SQL Scripts with Spring JDBC + JPA + HSQLDB

In this tutorial, I am giving an example of using custom SQL scripts in spring application to initialize the database at startup of application with appropriate tables and data populated inside it. I am using HQLDB (in-memory database) for backend DB in this example.

Table of Contents

SQL Scripts
Include Spring JDBC dependency
Adding jdbc:initialize-database tag
Complete Configuration

SQL Scripts

Very first step is to have your SQL statements in one SQL file (saved with .sql extension). Place it inside resource folder so that it can be picked up from classpath in runtime.

tempDB.sql

insert into department (id, name) values (default, 'Human Resource');
insert into department (id, name) values (default, 'Finance');
insert into department (id, name) values (default, 'Information Technology');


insert into employee (id, department_id, email, firstName, lastName) values (default, 1, 'howtodoinjava@gmail.com', 'Lokesh', 'Gupta');
insert into employee (id, department_id, email, firstName, lastName) values (default, 2, 'testOne@gmail.com', 'Test', 'One');
insert into employee (id, department_id, email, firstName, lastName) values (default, 3, 'testTwo@gmail.com', 'Test', 'Two');
insert into employee (id, department_id, email, firstName, lastName) values (default, 3, 'testThree@gmail.com', 'Test', 'Three');
insert into employee (id, department_id, email, firstName, lastName) values (default, 3, 'testFour@gmail.com', 'Test', 'Four');

Include Spring JDBC dependency

Now add Spring JDBC dependencies in pom.xml file.

<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>4.1.4.RELEASE</version>
</dependency>

And add schema declaration in application-context.xml file.

<beans xmlns="http://www.springframework.org/schema/beans"
    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-3.0.xsd
    http://www.springframework.org/schema/jdbc/ http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd">

 </beans>

Adding jdbc:initialize-database tag

Now add the scripts, placed in classpath to project, to datasource using jdbc:initialize-database tag.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
  <property name="url" value="jdbc:hsqldb:mem:howtodoinjava" />
  <property name="username" value="sa" />
  <property name="password" value="" />
</bean>

<jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
    <jdbc:script location="classpath:scripts/tempDB.sql" />
</jdbc:initialize-database>

Great !! Now you are good to start your application. Spring will automatically detect the DB script file and execute it once datasource is ready.

Complete Configuration

Let’s look at complete configuration file for reference in future.

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

	<context:component-scan base-package="com.howtodoinjava.jpa.demo" />

	<bean id="entityManagerFactoryBean"	class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="persistenceUnitName" value="demoJPAUnit" />
		<property name="packagesToScan">
			<list>
				<value>com.howtodoinjava.jpa.demo.entity</value>
			</list>
		</property>
		<property name="jpaVendorAdapter">
			<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
		</property>
		<property name="jpaProperties">
			<props>
				<prop key="hibernate.archive.autodetection">class,hbm</prop>
				<prop key="hibernate.hbm2ddl.auto">create</prop>
				<prop key="hibernate.show_sql">true</prop>
				<prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
			</props>
		</property>
	</bean>

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
	  <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
	  <property name="url" value="jdbc:hsqldb:mem:howtodoinjava" />
	  <property name="username" value="sa" />
	  <property name="password" value="" />
	</bean>

	<jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
	    <jdbc:script location="classpath:scripts/tempDB.sql" />
	</jdbc:initialize-database>

	<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
		<property name="entityManagerFactory" ref="entityManagerFactoryBean" />
	</bean>

	<tx:annotation-driven />

</beans>

Let me know if you face any problem in adding SQL scripts through Spring JDBC using jdbc:initialize-database.

Happy Learning !!