Example Spring 4 JDBC – Embedded Database

This example presents the basic concept of using embedded database in Spring JDBC.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>javaspringexamples.springJDBC</groupId>
	<artifactId>springJdbcEmbeddedDbSupport</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<maven.compiler.source>1.8</maven.compiler.source>
		<maven.compiler.target>1.8</maven.compiler.target>
	</properties>

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

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.3.18.RELEASE</version>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<version>1.3.175</version>
		</dependency>

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.11</version>
			<scope>test</scope>
		</dependency>
	</dependencies>
</project>

package javaspringexamples.springJDBC.EmbeddedDbSupport;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {
	public static void main(String[] args) throws SQLException {
		ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath:conf.xml");
		DataSource dataSource = applicationContext.getBean("dataSource", DataSource.class);

		Connection connection = dataSource.getConnection();
		System.out.println(connection.isClosed());
		connection.close();
		System.out.println(connection.isClosed());
	}
}

<?xml version="1.0" encoding="UTF-8"?>
<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.xsd
    http://www.springframework.org/schema/jdbc 
    http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd">

	<jdbc:embedded-database id="dataSource"
		type="H2">
		<jdbc:script location="classpath:schema.sql" />
		<jdbc:script location="classpath:data.sql" />
	</jdbc:embedded-database>

</beans>

insert into USER (NAME, USER_NAME, LOCKED) values ('Mounir SAHRANI','javaspringexamples',false);

CREATE TABLE USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(50), USER_NAME VARCHAR(50), LOCKED BOOLEAN);

package javaspringexamples.springJDBC.EmbeddedDbSupport;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;

/**
 *
 * @author mounir.sahrani@gmail.com
 *
 */
public class EmbeddedDataSourceTest {
	private DataSource dataSource;

	@Before
	public void setUp() {
		dataSource = new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).addScript("classpath:schema.sql")
				.addScript("classpath:data.sql").build();
	}

	@Test
	public void testDataAccessLogic() throws SQLException {
		Connection connection = dataSource.getConnection();
		Assert.assertFalse(connection.isClosed());
		connection.close();
		Assert.assertTrue(connection.isClosed());
	}

	@After
	public void tearDown() {
		((EmbeddedDatabase) dataSource).shutdown();
	}
}

Get the sources of the example from the following GitHub url

Or Download a .zip file