spring boot应用中利用Embedded Database

在应用开发过程中，利用内存数据库暂时代替实体数据库是很方便的一件事，既可以保证Juint的独立性，也能够让我们在开发前期方便的展示、模拟应用。
本篇文章我们通过一个简单的应用程序一起来了解下H2在spring boot应用、以及在junit中如何使用（其他类似的数据库还有HSQL、Derby）
1.H2的特性
[list]
[*]速度快、完全开源、支持JDBC API
[*]同时提供内嵌和server两种模式
[*]提供基于浏览器的控制台
[*]非常轻量，jar只有1.5M左右
[/list]
2.和其他数据库的对比
[table]
||H2 | Derby| HSQLDB| MySQL| PostgreSQL|
|Pure Java| Yes| Yes| Yes| No| No|
|Memory Mode | Yes| Yes| Yes| No| No|
|Encrypted Database| Yes| Yes| Yes| No| No|
|ODBC Driver| Yes| No| No| Yes| Yes |
|Fulltext Search| Yes| No| No| Yes| Yes |
|Multi Version Concurrency| Yes |No| Yes| Yes| Yes |
|Footprint (jar/dll size)| ~1 MB| ~2 MB| ~1 MB| ~4 MB| ~6 MB|
[/table]
3.在spring boot应用中引入H2，在pom.xml中追加如下依赖

<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<scope>runtime</scope>
</dependency>

4.完整的工程结构如下

[img]http://dl2.iteye.com/upload/attachment/0130/4875/087a906a-1dff-3c62-b0cd-cd217e7ba09d.png[/img]


5.完整的pom.xml文件

<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>chengf.falcon</groupId>
	<artifactId>spring-boot-data-h2</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-starter-parent</artifactId>
				<version>2.0.2.RELEASE</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

6.启动类

/**
 * 
 */
package chengf.falcon.spring.data.h2;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

/**
 * @author: 作者： chengaofeng
 * @date: 创建时间：2018-07-20 16:02:57
 * @Description: TODO
 * @version V1.0
 */
@SpringBootApplication
@RestController
public class H2DataApp implements CommandLineRunner {

	public static void main(String[] args) {
		SpringApplication.run(H2DataApp.class, args);
	}

	@Autowired
	H2CommandProcessor processor;

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.springframework.boot.CommandLineRunner#run(java.lang.String[])
	 */
	@Override
	public void run(String... args) throws Exception {
		processor.create();

	}

	@RequestMapping("/insert")
	public void insert(@RequestParam String id) {
		processor.insert(new User(id, "xuefei", 27));
	}

	@RequestMapping("/query")
	public User query(@RequestParam String id) {
		return processor.query(id);
	}
}

实现CommandLineRunner在启动时创建好相应的表

7.数据库操作类

/**
 * 
 */
package chengf.falcon.spring.data.h2;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;

/**
 * @author: 作者： chengaofeng
 * @date: 创建时间：2018-07-20 15:58:47
 * @Description: TODO
 * @version V1.0
 */
@Component
public class H2CommandProcessor {

	@Autowired
	JdbcTemplate jdbcTemplate;

	public void create() {
		jdbcTemplate.execute("CREATE TABLE USER (ID VARCHAR2(128),NAME VARCHAR2(128),AGE NUMBER(3), SCORE NUMBER(12,4))");
	}

	public void insert(User user) {
		String sql = "INSERT INTO USER VALUES(?, ?, ?,?)";
		jdbcTemplate.update(sql, new Object[] { user.getId(), user.getName(), user.getAge(), 89.75d });
	}

	public User query(String id) {
		User user = new User();
		String sql = String.format("SELECT ID, NAME, AGE FROM USER WHERE ID = ?");
		jdbcTemplate.query(sql, new Object[] { id }, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				user.setId(rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setAge(rs.getInt("age"));
				for (int i = 0; i< rs.getMetaData().getColumnCount() ; i++) {
					System.out.println(rs.getMetaData().getColumnName(i));
					System.out.println(rs.getMetaData().getColumnTypeName(i));
					System.out.println(rs.getMetaData().getColumnType(i));
				}

			}
		});
		return user;
	}

}

8.对应的bean

/**
 * 
 */
package chengf.falcon.spring.data.h2;

/**
 * @author: 作者： chengaofeng
 * @date: 创建时间：2018-07-20 15:59:47
 * @Description: TODO
 * @version V1.0
 */
public class User {

	private String id;

	private String name;

	private int age;

	/**
	 * @param name
	 * @param age
	 */
	public User(String id, String name, int age) {
		this.id = id;
		this.name = name;
		this.age = age;
	}

	/**
	 * 
	 */
	public User() {
		// TODO Auto-generated constructor stub
	}

	/**
	 * @return the id
	 */
	public String getId() {
		return id;
	}

	/**
	 * @param id
	 *            the id to set
	 */
	public void setId(String id) {
		this.id = id;
	}

	/**
	 * @return the name
	 */
	public String getName() {
		return name;
	}

	/**
	 * @param name
	 *            the name to set
	 */
	public void setName(String name) {
		this.name = name;
	}

	/**
	 * @return the age
	 */
	public int getAge() {
		return age;
	}

	/**
	 * @param age
	 *            the age to set
	 */
	public void setAge(int age) {
		this.age = age;
	}

}

9.配置文件

spring.h2.console.enabled: true
spring.datasource.url: jdbc:h2:mem:testdb;MODE=Oracle;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE

10.启动后访问 http://localhsot:8080/h2-console,进入页面控制台

[img]http://dl2.iteye.com/upload/attachment/0130/4877/46e44fb5-a5aa-318b-99ff-97fd65922b55.png[/img]
点击connect按钮进入

[img]http://dl2.iteye.com/upload/attachment/0130/4879/5e683229-fd7f-3ddb-958d-04ade4dd1eb6.png[/img]

可以看到创建的USER表

11.Junit中使用H2

package chengf.falcon.spring.data.h2;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.h2.jdbcx.JdbcDataSource;
import org.junit.Assert;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;


/**
 * 
 */

/**
 * @author: 作者： chengaofeng
 * @date: 创建时间：2018-08-09 18:49:57
 * @Description: TODO
 * @version V1.0
 */
public class H2DoubleTest {

	JdbcTemplate jdbcTemplate;
	@Test
	public void test() {
		JdbcDataSource dd = new JdbcDataSource();
		dd.setUrl("jdbc:h2:mem:testdb;MODE=Oracle;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE");
		jdbcTemplate = new JdbcTemplate(dd);
		create();
		insert();
		User user = query("3");
		Assert.assertEquals("chengf", user.getName());
	}


	public void create() {
		jdbcTemplate.execute("CREATE TABLE USER (ID VARCHAR2(128),NAME VARCHAR2(128),AGE NUMBER(3), SCORE NUMBER(12,4))");
	}

	public void insert() {
		String sql = "INSERT INTO USER VALUES(?, ?, ?,?)";
		jdbcTemplate.update(sql, new Object[] { "3", "chengf", 20, 89.75d });
	}

	public User query(String id) {
		User user = new User();
		String sql = String.format("SELECT ID, NAME, AGE,SCORE FROM USER WHERE ID = ?");
		jdbcTemplate.query(sql, new Object[] { id }, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				user.setId(rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setAge(rs.getInt("age"));
				for (int i = 1; i<= rs.getMetaData().getColumnCount() ; i++) {
					System.out.print(rs.getMetaData().getColumnName(i));
					System.out.print(":");
					System.out.print(rs.getMetaData().getColumnTypeName(i));
					System.out.print(":");
					System.out.print(rs.getMetaData().getPrecision(i));
					System.out.print(".");
					System.out.println(rs.getMetaData().getScale(i));
//					System.out.println(rs.getMetaData().getColumnType(i));
				}

			}
		});
		return user;
	}
}

这个junit简单演示了直接利用h2创建数据库连接以及创建表，插入和查询功能