# H2 Database
記憶體資料庫
### 1. pom.xml 設定
>begin with the h2 and spring-boot-starter-data-jpa dependencies:

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

### 2. application.properies 設定

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

### 3. 加入SQL檔
>add a data.sql file in src/main/resources:

CREATE SCHEMA IF NOT EXISTS assignment  AUTHORIZATION sa; 
 
DROP TABLE IF EXISTS course; 
DROP TABLE IF EXISTS course_student; 
DROP TABLE IF EXISTS grade; 
DROP TABLE IF EXISTS student; 
 
CREATE TABLE course ( 
  id         char(36) NOT NULL, 
  name       varchar(60) NOT NULL, 
  createDate datetime NOT NULL, 
  remark     varchar(100), 
  PRIMARY KEY (id)); 
CREATE TABLE course_student ( 
  courseid   char(36) NOT NULL, 
  studentid  char(36) NOT NULL, 
  gradelevel varchar(1) NOT NULL, 
  PRIMARY KEY (courseid, 
  studentid)); 
CREATE TABLE grade ( 
  level  varchar(1) NOT NULL, 
  remark varchar(100), 
  PRIMARY KEY (level)); 
CREATE TABLE student ( 
  id           char(36) NOT NULL, 
  name         varchar(60) NOT NULL, 
  birthday     datetime NOT NULL, 
  registerDate datetime NOT NULL, 
  remark       varchar(100), 
  PRIMARY KEY (id)); 
ALTER TABLE course_student ADD CONSTRAINT FKcourse_stu103792 FOREIGN KEY (gradelevel) REFERENCES grade (level); 
ALTER TABLE course_student ADD CONSTRAINT FKcourse_stu538834 FOREIGN KEY (studentid) REFERENCES student (id); 
ALTER TABLE course_student ADD CONSTRAINT FKcourse_stu246947 FOREIGN KEY (courseid) REFERENCES course (id);

>:::info
>Spring Boot will automatically pick up the data.sql and run it against our configured H2 database during application startup. This is a good way to seed the database for testing or other purposes
>:::
### 4. Accessing the H2 Console
>H2 database has an embedded GUI console for browsing the contents of a database and running SQL queries. By default, the H2 console is not enabled in Spring. So to enable it, we need to add the following property to application.properties:

spring.h2.console.enabled=true

>開啟瀏覽器輸入 http://localhost:8080/h2-console
>

### 5. H2 Console 安全設定
>Furthermore, we set spring.h2.console.settings.trace to false to prevent trace output and we can also disable remote access by setting spring.h2.console.settings.web-allow-others to false.

spring.h2.console.path=/h2-console
spring.h2.console.settings.trace=false
spring.h2.console.settings.web-allow-others=false

### 6. H2 DB 本地檔案資料庫設定
> 1. jdbc:h2:file:E:/data/H2
> 表示將初始化的資料和H2 Console控制檯執行的資料儲存到E盤下data/H2資料夾中，即使應用重啟，資料不會丟失。
> 2. jdbc:h2:~/testdatabase
> 這裡就需要說明一下\~這個符號在window作業系統下代表什麼意思了，在Window作業系統下，~這個符號代表的就是當前登入到作業系統的使用者對應的使用者目錄，所以testdatabase資料庫對應的檔案存放在登入到作業系統的使用者對應的使用者目錄當中，比如我當前是使用Administrator使用者登入作業系統的，所以在C:\Documents and Settings\Administrator.h2目錄中就可以找到test資料庫對應的資料庫檔案了
>
>:::info
>持久化本地的問題：由於本地已經存在表，而應用每次啟動都會建立表，導致下次啟動時會啟動報錯。除非手動注掉application.properties中新建表的配置，或則刪除本地對應目錄的檔案。
>:::
> 3. jdbc:h2:mem:soa_service_api、jdbc:h2:mem:~/.h2/url類似與這種配置的，表示將初始化和h2 console控制檯上操作的資料儲存在記憶體（mem-memory）
> :::info
> 儲存到記憶體的問題：由於每次重啟應用記憶體釋放掉後，對應的資料也會消失，當然初始化的表 初始化資料就都沒了。然後重啟會從data.sql中重新初始化資料，啟動正常。但是你通過h2 console操作的其他資料則全部丟失。解決辦法是把在h2 console新新增的介面地址配置到data.sql中。然後重新啟動才行。
>:::

資料來源:
[Spring Boot With H2 Database](https://www.baeldung.com/spring-boot-h2-database)
[springboot配置記憶體資料庫H2教程詳解](https://codertw.com/%E7%A8%8B%E5%BC%8F%E8%AA%9E%E8%A8%80/307164/)

###### tags: `Spring boot`