Example of spring DataSourceInitializer

0

In this article, I am going to show you how to create schema and test data as you load the application context. I will also show you how to cleanup the database as you close the context.

Example of DataSourceInitializer

We can use spring’s org.springframework.jdbc.datasource.init.DataSourceInitializer bean to populate the database as you build the application context and cleanup the database when you destroy the context.

DataSourceInitializer implements InitializingBean and DisposableBean which means callback method afterPropertiesSet will be called by the BeanFactory during the initialization phase and callback method destroy will be called during the closing phase of the context. DataSourceInitializer executes the database scripts configured in both these lifecycle methods. One set of scripts will be called in afterPropertiesSet to populate the database and the other set of scripts will be called in destroy to cleanup the database.

Dependencies of DataSourceInitializer

DataSourceInitializer depends on bean DatabasePopulator for the execution of the database scripts. Instead of executing the scripts itself, it delegates the task to DatabasePopulator. DataSourceInitializer has two member variables of type DatabasePopulator, one for the population of database and the other one for cleaning up the database. A DatabasePopulator bean can execute more than one script.

We will have to inject DataSource bean for the database connection as it needs to access the database for executing the scripts. In our example,  we will base our DataSource on spring provided org.springframework.jdbc.datasource.DriverManagerDataSource. We are using mysql database so our driver class will be com.mysql.jdbc.Driver.

In the example I am going to show, I will create an employee table and some test data.

db-schema.sql:

CREATE TABLE `employee` (
  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

db-test-data.sql:

insert into employee(id, name) values (1, "Sam");
insert into employee(id, name) values (2, "John");

I will have another script to drop employee table  during the closure of the context.
drop-schema.sql:

drop table if exists `employee`;

DatabasePopulator is just an interface, we will be using spring’s org.springframework.jdbc.datasource.init.ResourceDatabasePopulator for the actual implementation.

We will now modify applicationContext.xml to configure the beans. Since DataSourceInitializer depends on dataSource, databasePopulator and databaseCleaner, we will have to configure the DataSource as well as the DatabasePopulators.

applicationContext.xml:

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

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/test" />
		<property name="username" value="root" />
		<property name="password" value="admin" />
	</bean>
	
	<bean id="dbInit" class="org.springframework.jdbc.datasource.init.ResourceDatabasePopulator">
		<property name="scripts">
			<list>
				<value>db-schema.sql</value>
				<value>db-test-data.sql</value>
			</list>
		</property>
		<property name="continueOnError" value="true"/>
	</bean>
	
	<bean id="dbCleanup" class="org.springframework.jdbc.datasource.init.ResourceDatabasePopulator">
		<property name="scripts">
			<list>
				<value>drop-schema.sql</value>
			</list>
		</property>
	</bean>
	
	<bean id="startupScripts" class="org.springframework.jdbc.datasource.init.DataSourceInitializer">
		<property name="dataSource"><ref bean="dataSource"/></property>
		<property name="databasePopulator"><ref bean="dbInit"/></property>
		<property name="databaseCleaner"><ref bean="dbCleanup"/></property>
	</bean>
</beans>

Run the example

The example first creates an ApplicationContext. Since we have configured the population of database, we expect that it should have created employee table and the sample data. In order to make sure that the employee data is inserted into the database, we run a select query on the employee table. Next, for each row retrieved we create an Employee bean and add it to a list. Once all the rows are processed we print the employee list object. Finally, we close the context. This should drop the employee table. We can confirm this by re-running the example. It should run without any errors.

DatabaseInit.java:

package com.javarticles.spring.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class DatabaseInit {
    public static void main(String[] args) throws SQLException {
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        DataSource dataSource = (DataSource) context.getBean("dataSource");
        Connection conn = dataSource.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM EMPLOYEE");
        ResultSet rs = ps.executeQuery();
        List empList = new ArrayList();
        while(rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            Employee emp = new Employee();
            emp.setId(id);
            emp.setName(name);
            empList.add(emp);            
        }
        System.out.println(empList);
        ((ConfigurableApplicationContext) context).close();
    }
}

Output:

[1:Sam, 2:John]

jdbc:initialize-database element

Instead of configuring DataSourceInitializer, you can use spring’s custom element jdbc:initialize-database element where jdbcnamespace xmlns:jdbc is set to http://www.springframework.org/schema/jdbc. You just need to point attribute data-source to the DataSource bean and configure the scripts using the child element jdbc:script and location attribute. For example, we can achieve the same result using the below configuration:

 	<jdbc:initialize-database data-source="dataSource" enabled="true">
		<jdbc:script location="classpath:db-schema.sql" />
		<jdbc:script location="classpath:db-test-data.sql" />
	</jdbc:initialize-database>

Download Source Code

In this article, we have seen an example of DataSourceInitializer of how one can use it to populate database during the creation of ApplicationContext and cleanup the database during its closure.
You can download the source code here: jdbc-schema-init.zip

Share.

Leave A Reply