1. 什么是H2内存数据库
H2是一个用Java开发的嵌入式数据库，它本身是一个类库，可以直接嵌入到项目中
官网地址：H2数据库
1.1. H2用途
- H2最大的用途在于可以同应用程序打包在一起发布，这样可以非常方便地少量结构化数据
- 用于单元测试，启动速度块，而且可以关闭持久化功能，每一个用例执行完随机还原到初始状态
- 作为缓存，作为NoSQL的一个补充。当某些场景下数据模型必须为关系型，可以把它当Memcached使，作为后端Mysql/Oracle的一个缓冲层，缓存一些不经常变化但需要频繁访问的数据，比如字电表、、权限表。不过这样系统架构就会比较复杂了。
1.2. H2的产品优势
- 纯Java编写，不受平台的限制
- 只有一个jar文件，适合作为嵌入式数据库使用
- H2提供了一个时份翻边的web控制台用于操作和管理数据库内容
- 功能完整，支持标准sql和jdbc
- 支持内嵌模式、服务器模式和集群
2. Springboot集成H2
2.1. 添加H2以及相关依赖
Copy
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | ```
com.h2database h2 runtime org.springframework.boot spring-boot-starter-data-jpa org.springframework.boot spring-boot-starter-web
| -------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
#### [](http://xiaoyuge.work/springboot-chapter3/index.html#2-2-%E9%85%8D%E7%BD%AEH2%E7%9B%B8%E5%85%B3%E5%8F%82%E6%95%B0 "2.2. 配置H2相关参数")2.2. 配置H2相关参数
Copy
| ```
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
``` | ```
spring: datasource: schema: classpath:db/schema.sql #指定数据表结构文件所在目录 data: classpath:db/data.sql #指定数据文件所在目录 driver-class-name: org.h2.Driver url: jdbc:h2:mem:test # 内存模式，数据不会持久化 #url:jdbc:h2:~/test 嵌入模式，数据文件存储在用户目录test开头的文件中 #url:jdbc:h2:tcp//localhost/〜/test 远程模式，访问远程的h2 数据库 username: sa #用户名 password: root #密码 platform: h2 h2: console: enabled: true path: /h2 #H2控制台访问路径 settings: web-allow-others: true jpa: hibernate: ddl-auto: update show-sql: true open-in-view: true
``` |
| ----------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
其中在`resources/db`下创建表结构schema.sql
Copy
| ```
1 2 3 4
``` | ```
create table if not exists sys_user( USER_ID int not null primary key auto_increment, USER_NAME varchar(200) );
``` |
| ---------------- | -------------------------------------------------------------------------------------------------------------------------------- |
以及数据文件 data.sql，作为初始化数据
Copy
| ```
1 2 3 4
``` | ```
-- 字段都是大写的 INSERT INTO SYS_USER (USER_ID, USER_NAME) VALUES (1, '小余哥'); INSERT INTO SYS_USER (USER_ID, USER_NAME) VALUES (2, '张三'); INSERT INTO SYS_USER (USER_ID, USER_NAME) VALUES (3, '李四');
``` |
| ---------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
#### [](http://xiaoyuge.work/springboot-chapter3/index.html#2-3-%E5%AE%9E%E4%BD%93%E5%85%B3%E8%81%94%E8%A1%A8 "2.3. 实体关联表")2.3. 实体关联表
给User添加@Entity注解和@Table注解
Copy
| ```
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
``` | ```
@Entity @Table(name="sys_user") @Proxy(lazy = false) public class User { @Id private int userId; /** * userName 对应数据库 USER_NAME * username 对应数据库 USERNAME */ @Column(name = "USER_NAME") private String userName; //----------省略getter/setter----- }
``` |
| ----------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
#### [](http://xiaoyuge.work/springboot-chapter3/index.html#2-4-%E5%88%9B%E5%BB%BADao%E7%BB%A7%E6%89%BFJpaRepository "2.4. 创建Dao继承JpaRepository")2.4. 创建Dao继承JpaRepository
Copy
| ```
1 2 3
``` | ```
@Repository public interface UserRepository extends JpaRepository<User, Integer> { }
``` |
| -------------- | --------------------------------------------------------------------------------------------- |
#### [](http://xiaoyuge.work/springboot-chapter3/index.html#2-5-%E8%AE%BF%E9%97%AEH2%E6%8E%A7%E5%88%B6%E5%8F%B0 "2.5. 访问H2控制台")2.5. 访问H2控制台
启动应用程序，通过访问H2控制台连接`localhost:8080/h2`，进入H2控制台页面
![]()
进入控制台后，可以查看到初始化的数据表`SYS_USER`以及数据
![]()
#### [](http://xiaoyuge.work/springboot-chapter3/index.html#2-6-%E5%88%9B%E5%BB%BA%E6%B5%8B%E8%AF%95%E7%B1%BB "2.6. 创建测试类")2.6. 创建测试类
Copy
| ```
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
``` | ```
@RunWith(SpringRunner.class) @SpringBootTest public class AppTest { @Autowired private UserRepository userRepository; @Test public void save(){ User user = new User(); user.setUserName("王五"); userRepository.save(user); //Hibernate: insert into sys_user (user_name, user_id) values (?, ?) } @Test public void getOne(){ User user = userRepository.getOne(1); System.out.println(user.toString()); //Hibernate: select user0_.user_id as user_id1_0_0_, user0_.user_name as user_nam2_0_0_ from sys_user user0_ where user0_.user_id=? //User{userId=1, userName='小余哥'} } @Test public void list(){ List<User> list = userRepository.findAll(); for (User user : list) { System.out.println(user.toString()); } //User{userId=1, userName='小余哥'} //User{userId=2, userName='张三'} //User{userId=3, userName='李四'} } }
``` |
| ----------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
