spring boot应用中利用Embedded Database

  在应用开发过程中，利用内存数据库暂时代替实体数据库是很方便的一件事，既可以保证Juint的独立性，也能够让我们在开发前期方便的展示、模拟应用。
  本篇文章我们通过一个简单的应用程序一起来了解下H2在spring boot应用、以及在junit中如何使用（其他类似的数据库还有HSQL、Derby）
1.H2的特性

• 速度快、完全开源、支持JDBC API
• 同时提供内嵌和server两种模式
• 提供基于浏览器的控制台
• 非常轻量，jar只有1.5M左右

2.和其他数据库的对比

	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

3.在spring boot应用中引入H2，在pom.xml中追加如下依赖

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

4.完整的工程结构如下




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,进入页面控制台


点击connect按钮进入



可以看到创建的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创建数据库连接以及创建表，插入和查询功能