SAP学习笔记 - 开发13 - CAP 之 添加数据库支持（Sqlite）

上一章学习了CAP开发准备，添加Service。

SAP学习笔记 - 开发12 - CAP 之 开发准备，添加服务-CSDN博客

本章继续学习CAP开发 - 添加数据库支持（Sqlite）。

目录

1，数据库准备

- H2 内存数据库

 - Sqlite数据库

a），npm install sqlite3

b），安装 Sqlite Viewer

2，添加一个基于数据库表的Entity

3，创建和配置数据库文件

- cds deploy --to sqlite

- Sqlite View查看数据库

- SQLite3 Editor

- 在package.json 里面添加URL指定

- 配置Application.yml

- pom.xml 加Dependency

4，测试一下

- mvn spring-boot:run -D"spring-boot.run.profiles"=sqlite

- localhost:8080

- 用Postman测试

5，总结

---

下面是详细内容。

1，数据库准备

- H2 内存数据库

默认CAP是用的H2 内存数据库：

咱们上一篇文章当中，

SAP学习笔记 - 开发12 - CAP 之 开发准备，添加服务-CSDN博客

com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection conn0: url=jdbc:h2:mem:43a17619-9a82-4f03-a01f-0c2ec22faad1 user=SA

连schema-h2.sql 都一并生成好了：

有关H2数据库，好像有点儿意思，有空看看。

【SpringBoot新手篇】SpringBoot 集成H2数据库-CSDN博客

 - Sqlite数据库

咱们这里先使用Sqlite本地数据库，以后再弄弄Postgre，HANA Cloud啥的。

a），npm install sqlite3

进入到项目文件夹，然后安装Sqlite数据库。

如果没有package.json文件，还需要先运行 npm init -y 初期化项目。

应该是这个哈

b），安装 Sqlite Viewer

在Visual Studio Code直接打开Sqlite文件。

2，添加一个基于数据库表的Entity

- 在 db 文件夹下建 schema.cds 文件

entity DBEntities {key ID : Integer;title  : String(111);descr  : String(1111);
}

- 添加 data 文件

ID;title;descr
1001;Title1; Desc of test1
1002;Title2; Desc of test2

- 修改Service Defintion的cds文件

这里的 @cds.persistence.skip: true 这个annotation 的作用是告诉CAP不要创建DB表

service DemoService {@cds.persistence.skip: trueentity DemoEntity {key ID: Integer;title: String(111);descr: String(1111);}
}

3，创建和配置数据库文件

- cds deploy --to sqlite

PS C:\Users\abcta\CAPTest03> cds deploy --to sqlite
Debugger attached.
/> successfully deployed to db.sqlite Waiting for the debugger to disconnect...

这个 db.sqlite 文件就是 sqlite3 的数据库文件

后面因为路径问题，搞得我还挺晕菜的，这里直接把这个文件给放到srv 文件夹下面去

- Sqlite View查看数据库

直接在 VSCode里面查看是否建表成功

- SQLite3 Editor 

很有可能你会像我一样，想改下表啥的，那就得换个插件，我这里用的 SQLite3 Editor

- 在package.json 里面添加URL指定

  觉得名称有点儿怪，叫 xx.db更好些哈，咱这里默认生成了 .sqlite

  后面还因此出了错，网上有人说要改成 .db，因为我也改了别的东西了，但是我也没有太确定

  咱们这里都改成 sqlite.db 了。

  "cds": {"requires": {"db": {"kind": "sqlite","credentials": {"url":"sqlite.db"}}}}

- 配置Application.yml

AsIs：默认指向的是h2 数据库

---
spring:config.activate.on-profile: defaultsql.init.platform: h2
cds:data-source.auto-config.enabled: false

ToBe：配置指向Sqlite 数据库

---
spring:config:activate:on-profile: sqlitedatasource:url: jdbc:sqlite:sqlite.dbdriver-class-name: org.sqlite.JDBChikari:maximum-pool-size: 1

 

- pom.xml 加Dependency

这个是sqlite的JDBC驱动，加上这个，把H2 的那个给删掉

不然后面会出没有 sqlite-jdbc 驱动错误，然后还指向了H2

我总感觉这里好像还有些没弄明白似的，欢迎朋友们补充，搞得更清楚些

        <dependency><groupId>org.xerial</groupId><artifactId>sqlite-jdbc</artifactId><version>3.49.1.0</version></dependency>

都配置完了，运行一下看看

4，测试一下

- mvn spring-boot:run -D"spring-boot.run.profiles"=sqlite

PS C:\Users\abcta\CAPTest03> mvn spring-boot:run -D"spring-boot.run.profiles"=sqlite
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF-8
WARNING: A restricted method in java.lang.System has been called
WARNING: java.lang.System::load has been called by org.fusesource.jansi.internal.JansiLoader in an unnamed module (file:/E:/Maven/lib/jansi-2.4.1.jar)
WARNING: Use --enable-native-access=ALL-UNNAMED to avoid a warning for callers in this module
WARNING: Restricted methods will be blocked in a future release unless native access is enabledWARNING: A terminally deprecated method in sun.misc.Unsafe has been called
WARNING: sun.misc.Unsafe::objectFieldOffset has been called by com.google.common.util.concurrent.AbstractFuture$UnsafeAtomicHelper (file:/E:/Maven/lib/guava-33.2.1-jre.jar)
WARNING: Please consider reporting this to the maintainers of class com.google.common.util.concurrent.AbstractFuture$UnsafeAtomicHelper
WARNING: sun.misc.Unsafe::objectFieldOffset will be removed in a future release
[INFO] Scanning for projects...
[INFO] ------------------------------------------------------------------------
[INFO] Reactor Build Order:
[INFO] 
[INFO] CAPTest03 parent                                                   [pom]
[INFO] CAPTest03                                                          [jar]
[INFO] 
[INFO] ---------------------< customer:CAPTest03-parent >----------------------
[INFO] Building CAPTest03 parent 1.0.0-SNAPSHOT                           [1/2]
[INFO]   from pom.xml
[INFO] --------------------------------[ pom ]---------------------------------
[INFO] 
[INFO] >>> spring-boot:3.4.4:run (default-cli) > test-compile @ CAPTest03-parent >>>
[INFO] 
[INFO] --- enforcer:3.5.0:enforce (Project Structure Checks) @ CAPTest03-parent ---
[INFO] Rule 0: org.apache.maven.enforcer.rules.version.RequireMavenVersion passed
[INFO] Rule 1: org.apache.maven.enforcer.rules.version.RequireJavaVersion passed
[INFO] Rule 2: org.apache.maven.enforcer.rules.ReactorModuleConvergence passed
[INFO] 
[INFO] --- flatten:1.7.0:flatten (flatten) @ CAPTest03-parent ---
[INFO] Generating flattened POM of project customer:CAPTest03-parent:pom:1.0.0-SNAPSHOT...
[INFO] 
[INFO] <<< spring-boot:3.4.4:run (default-cli) < test-compile @ CAPTest03-parent <<<
[INFO] 
[INFO]
[INFO] --- spring-boot:3.4.4:run (default-cli) @ CAPTest03-parent ---
[INFO] 
[INFO] -------------------------< customer:CAPTest03 >-------------------------
[INFO] Building CAPTest03 1.0.0-SNAPSHOT                                  [2/2]
[INFO]   from srv\pom.xml
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] >>> spring-boot:3.4.4:run (default-cli) > test-compile @ CAPTest03 >>>
[INFO] 
[INFO] --- enforcer:3.5.0:enforce (Project Structure Checks) @ CAPTest03 ---
[INFO] 
[INFO] --- cds:3.9.1:install-node (cds.install-node) @ CAPTest03 ---
[INFO] InstallNodeMojo: Node.js v20.19.0 already installed.
[INFO] 
[INFO] --- cds:3.9.1:npm (cds.npm-ci) @ CAPTest03 ---
[INFO] NpmMojo: Using npm provided by goal install-node: C:\Users\abcta\.m2\repository\com\sap\cds\cds-maven-plugin\cache\unpacked\20.19.0\unpacked-20.19.0-win-x64.zip\node-v20.19.0-win-x64\npm.cmd
[INFO] NpmMojo: Identified C:\Users\abcta\CAPTest03 as reactor base directory.
[INFO] NpmMojo: Using directory containing a .cdsrc.json as working directory: C:\Users\abcta\CAPTest03
[INFO] NpmMojo: Executing [C:\Users\abcta\.m2\repository\com\sap\cds\cds-maven-plugin\cache\unpacked\20.19.0\unpacked-20.19.0-win-x64.zip\node-v20.19.0-win-x64\npm.cmd, ci] in working directory C:\Users\abcta\CAPTest03
[INFO] NpmMojo: npm warn deprecated inflight@1.0.6: This module is not supported, and leaks memory. Do not use it. Check out lru-cache if you want a good 
and tested way to coalesce async requests by a key value, which is much more comprehensive and powerful.
npm warn deprecated @npmcli/move-file@1.1.2: This functionality has been moved to @npmcli/fs
npm warn deprecated npmlog@6.0.2: This package is no longer supported.
npm warn deprecated rimraf@3.0.2: Rimraf versions prior to v4 are no longer supported
npm warn deprecated glob@7.2.3: Glob versions prior to v9 are no longer supported
npm warn deprecated are-we-there-yet@3.0.1: This package is no longer supported.
npm warn deprecated gauge@4.0.4: This package is no longer supported.added 382 packages, and audited 383 packages in 1m53 packages are looking for fundingrun `npm fund` for detailsfound 0 vulnerabilities[INFO]
[INFO] --- cds:3.9.1:resolve (cds.resolve) @ CAPTest03 ---
[INFO] 
[INFO] --- cds:3.9.1:cds (cds.build) @ CAPTest03 ---
[INFO] CdsMojo: Using npx provided by goal install-node: C:\Users\abcta\.m2\repository\com\sap\cds\cds-maven-plugin\cache\unpacked\20.19.0\unpacked-20.19.0-win-x64.zip\node-v20.19.0-win-x64\npx.cmd
[INFO] CdsMojo: Using directory containing a .cdsrc.json as working directory: C:\Users\abcta\CAPTest03
[INFO] CdsMojo: Executing [C:\Users\abcta\.m2\repository\com\sap\cds\cds-maven-plugin\cache\unpacked\20.19.0\unpacked-20.19.0-win-x64.zip\node-v20.19.0-win-x64\npx.cmd, -c, cds version] in working directory C:\Users\abcta\CAPTest03
[INFO] CdsMojo: Using configured working directory: C:\Users\abcta\CAPTest03
[INFO] CdsMojo: Executing [C:\Users\abcta\.m2\repository\com\sap\cds\cds-maven-plugin\cache\unpacked\20.19.0\unpacked-20.19.0-win-x64.zip\node-v20.19.0-win-x64\npx.cmd, -c, cds build --for java] in working directory C:\Users\abcta\CAPTest03
[INFO] CdsMojo: building project with {versions: { cds: '8.9.2', compiler: '5.9.2', dk: '8.9.3' },target: '.',tasks: [{ src: 'srv', for: 'java', options: { model: [ 'db', 'srv' ] } }]
}
writing generation log to [C:\Users\abcta\CAPTest03\srv\target\cds-build.log]done > wrote output to:srv\src\main\resources\edmx\csn.jsonsrv\src\main\resources\edmx\odata\v4\DemoService.xmlbuild completed in 1990 ms
[INFO] CdsMojo: Executing [C:\Users\abcta\.m2\repository\com\sap\cds\cds-maven-plugin\cache\unpacked\20.19.0\unpacked-20.19.0-win-x64.zip\node-v20.19.0-win-x64\npx.cmd, -c, cds deploy --to h2 --with-mocks --dry --out \"C:\Users\abcta\CAPTest03\srv/src/main/resources/schema-h2.sql\"] in working directory C:\Users\abcta\CAPTest03
[INFO] CdsMojo: 
[INFO]
[INFO] --- cds:3.9.1:generate (cds.generate) @ CAPTest03 ---
[INFO] GenerateMojo: Adding code output directory C:\Users\abcta\CAPTest03\srv\src\gen\java to compile source directories.
[INFO] GenerateMojo: Adding directory C:\Users\abcta\CAPTest03\srv\src\gen\resources to resources.
[INFO] GenerateMojo: Using C:\Users\abcta\CAPTest03\srv\src\main\resources\edmx\csn.json to generate Java classes into C:\Users\abcta\CAPTest03\srv\src\gen.
[INFO] GenerateMojo: Using module srv: groupId=customer, artifactId=CAPTest03, version=1.0.0-SNAPSHOT
[INFO] GenerateMojo: Class generation finished successfully
[INFO]
[INFO] --- resources:3.3.1:resources (default-resources) @ CAPTest03 ---
[INFO] Copying 4 resources from src\main\resources to target\classes
[INFO] Copying 1 resource from src\gen\resources to target\classes
[INFO] 
[INFO] --- flatten:1.7.0:flatten (flatten) @ CAPTest03 ---
[INFO] Generating flattened POM of project customer:CAPTest03:jar:1.0.0-SNAPSHOT...
[INFO] 
[INFO] --- compiler:3.14.0:compile (default-compile) @ CAPTest03 ---
[INFO] Nothing to compile - all classes are up to date.
[INFO]
[INFO] --- resources:3.3.1:testResources (default-testResources) @ CAPTest03 ---
[INFO] skip non existing resourceDirectory C:\Users\abcta\CAPTest03\srv\src\test\resources
[INFO] 
[INFO] --- compiler:3.14.0:testCompile (default-testCompile) @ CAPTest03 ---
[INFO] No sources to compile
[INFO]
[INFO] <<< spring-boot:3.4.4:run (default-cli) < test-compile @ CAPTest03 <<<
[INFO]
[INFO] 
[INFO] --- spring-boot:3.4.4:run (default-cli) @ CAPTest03 ---
[INFO] Attaching agents: []
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF-8.   ____          _            __ _ _/\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \\\/  ___)| |_)| | | | | || (_| |  ) ) ) )'  |____| .__|_| |_|_| |_\__, | / / / /=========|_|==============|___/=/_/_/_/:: Spring Boot ::                (v3.4.4)2025-05-19T12:40:33.192+09:00  INFO 792 --- [  restartedMain] customer.captest03.Application           : Starting Application using Java 24.0.1 with PID 792 (C:\Users\abcta\CAPTest03\srv\target\classes started by abcta in C:\Users\abcta\CAPTest03\srv)
2025-05-19T12:40:33.196+09:00  INFO 792 --- [  restartedMain] customer.captest03.Application           : The following 1 profile is active: "sqlite"
2025-05-19T12:40:33.300+09:00  INFO 792 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
2025-05-19T12:40:33.301+09:00  INFO 792 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
2025-05-19T12:40:34.331+09:00  INFO 792 --- [  restartedMain] c.s.c.services.impl.utils.CdsModelUtils  : Loaded CDS model from CSN resource path 'edmx/csn.json'
2025-05-19T12:40:34.407+09:00  INFO 792 --- [  restartedMain] c.s.c.services.impl.ServiceCatalogImpl   : Registered service OutboxService$InMemory
2025-05-19T12:40:34.413+09:00  INFO 792 --- [  restartedMain] c.s.c.services.impl.ServiceCatalogImpl   : Registered service ApplicationLifecycleService$Default
2025-05-19T12:40:34.419+09:00  INFO 792 --- [  restartedMain] c.s.c.services.impl.ServiceCatalogImpl   : Registered service AuthorizationService$Default
2025-05-19T12:40:34.425+09:00  INFO 792 --- [  restartedMain] c.s.c.services.impl.ServiceCatalogImpl   : Registered service TenantProviderService$Default 
2025-05-19T12:40:34.430+09:00  INFO 792 --- [  restartedMain] c.s.c.services.impl.ServiceCatalogImpl   : Registered service DeploymentService$Default     
2025-05-19T12:40:34.450+09:00  INFO 792 --- [  restartedMain] c.s.c.services.impl.ServiceCatalogImpl   : Registered service AuditLogService$Default
2025-05-19T12:40:34.498+09:00  INFO 792 --- [  restartedMain] c.s.c.services.impl.ServiceCatalogImpl   : Registered service DemoService
2025-05-19T12:40:34.499+09:00  INFO 792 --- [  restartedMain] .s.c.r.CdsRuntimeBeanDefinitionRegistrar : Found 'spring.datasource.url' configuration: Auto-configuration of DataSource beans is disabled.
2025-05-19T12:40:35.943+09:00  INFO 792 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port 8080 (http)
2025-05-19T12:40:35.988+09:00  INFO 792 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2025-05-19T12:40:35.992+09:00  INFO 792 --- [  restartedMain] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.39]
2025-05-19T12:40:36.145+09:00  INFO 792 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2025-05-19T12:40:36.147+09:00  INFO 792 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2844 ms
2025-05-19T12:40:36.265+09:00  INFO 792 --- [  restartedMain] c.s.c.f.s.c.adapter.AdapterBeanFactory   : Servlet CdsODataV4Servlet mapped to /odata/v4
2025-05-19T12:40:36.276+09:00  INFO 792 --- [  restartedMain] c.s.c.f.s.c.adapter.AdapterBeanFactory   : Servlet IndexPageServlet mapped to /
2025-05-19T12:40:36.281+09:00  INFO 792 --- [  restartedMain] c.s.c.f.s.c.adapter.AdapterBeanFactory   : Servlet CdsFioriPreviewServlet mapped to /$fiori-preview
2025-05-19T12:40:36.777+09:00  INFO 792 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
WARNING: A restricted method in java.lang.System has been called
WARNING: java.lang.System::load has been called by org.sqlite.SQLiteJDBCLoader in an unnamed module (file:/C:/Users/abcta/.m2/repository/org/xerial/sqlite-jdbc/3.49.1.0/sqlite-jdbc-3.49.1.0.jar)
WARNING: Use --enable-native-access=ALL-UNNAMED to avoid a warning for callers in this module
WARNING: Restricted methods will be blocked in a future release unless native access is enabled2025-05-19T12:40:36.964+09:00  INFO 792 --- [  restartedMain] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.sqlite.jdbc4.JDBC4Connection@5085b2c8
2025-05-19T12:40:36.968+09:00  INFO 792 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2025-05-19T12:40:37.014+09:00  INFO 792 --- [  restartedMain] c.s.c.services.impl.ServiceCatalogImpl   : Registered service PersistenceService$Default
2025-05-19T12:40:37.171+09:00  INFO 792 --- [  restartedMain] .s.c.a.o.v.m.p.EdmxProviderConfiguration : Initialized Default EDMX V4 Provider
2025-05-19T12:40:37.174+09:00  INFO 792 --- [  restartedMain] .s.c.a.o.v.m.p.EdmxProviderConfiguration : Initialized Default EDMX I18n Provider
2025-05-19T12:40:37.298+09:00  INFO 792 --- [  restartedMain] c.sap.cds.services.impl.utils.BuildInfo  : git.commit.id: bc6c83d24e9a3ad327d29c7d3ea52ba9cc4cad25
2025-05-19T12:40:37.300+09:00  INFO 792 --- [  restartedMain] c.sap.cds.services.impl.utils.BuildInfo  : maven.version: 3.9.1
2025-05-19T12:40:37.460+09:00  INFO 792 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2025-05-19T12:40:37.511+09:00  INFO 792 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port 8080 (http) with context path '/'
2025-05-19T12:40:37.529+09:00  INFO 792 --- [  restartedMain] customer.captest03.Application           : Started Application in 5.26 seconds (process running for 6.436)
2025-05-19T12:40:46.618+09:00  INFO 792 --- [nio-8080-exec-2] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2025-05-19T12:40:46.619+09:00  INFO 792 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2025-05-19T12:40:46.621+09:00  INFO 792 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet        : Completed initialization in 1 ms
2025-05-19T12:40:46.701+09:00  INFO 792 --- [nio-8080-exec-2] c.s.c.a.odata.v4.metadata.MetadataInfo   : Loading OData V4 metadata for service 'DemoService'

- localhost:8080

- 用Postman测试

好像能插进来的样子哈

打开数据库，好像也确实进来了

5，总结

- 默认是支持 H2 In-Memory数据库，我这里是改成了Sqlite数据库

- 在 db 文件夹下，建schema.cds 和 data 文件，用 cds deploy 可以生成表和初期化数据的

- 在Postman里面做测试的时候，它好像不认这个表，表名要加个"Service名_" 的前缀

  我也没时间搞了，估计是有什么设置的吧

- package.json 里面添加 cds指向Sqlite (正常应该是自动加的，也可能不需要，朋友们可以试试）

- Application.yml 里面添加指向Sqlite的配置，用以表明本Project用的是Sqlite，不是默认的H2

以上就是本篇的全部内容。

更多SAP顾问业务知识请点击下面目录链接或东京老树根的博客主页

https://blog.csdn.net/shi_ly/category_12216766.html

东京老树根-CSDN博客