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 !!
Leave a Reply