SpringBoot + Druid + Sqlite 文件数据库初体验
一次以外的机遇接触到了SQLite这样一个轻量型的嵌入式数据库组件，对于日常使用的大概都是传统RDBMS，大概当下更为流行NoSQL和大数据存储。对于这样一个自包含的，无服务器的，零配置的，事务性的 SQL 数据库引擎，主要是面向嵌入式大概终端类的使用场景，大概是说开箱即用、小型项目的场景。
[*]关于SQLite - 天下上装机量最多的数据库？
[*]关于Druid
[*]关于SpringBoot
[*]整合并查询
[*]创建DB文件
[*]初始化一个SpringBoot步伐
[*]导入Maven依赖
[*]配置服务毗连参数
[*]书写一个JPA步伐完成一次查询
关于SQLite - 天下上装机量最多的数据库？
[*]使用C语言开发，使得它小巧风雅而高效，·直接采用偏底层的语言，使用文件的逻辑，实现SQL数据库的逻辑；
[*]使用方：包罗但不限于 Python、Java、C# 等；
[*]无服务器的，零配置的，真的轻量；
[*]遵守ACID的关系型数据库管理体系，让了解Mysql等数据库的人极易上手；
[*]SQLite 是一个自包含的步伐如果你使用的是 Linux 大概 MacOS，那么 SQLite 很大概已经预装了，真的开箱即用；
:~$ sqlite3
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
它固然支持了增删改查以及事务等特性，但是它为什么是在嵌入式方向使用，而不是大范围使用呢，主要照旧不面向复杂的处置处罚逻辑、大数量的存储与查询，所以照旧要将对的东西用在对的方面。
关于Druid
Alibaba/Druid 想必各人肯定不陌生，一个国内使用率比力高的一个数据库毗连池，其他的还有：HikariCP, tomcat-jdbc, c3p0, dbcp，用的人多也就迭代和丰富了很多功能。
定位为：为监控而生的数据库毗连池，接入了一个高性能的数据库毗连池，还送了一套数据库利用的监控，肯定会让民气动。
它也黑白常自大的与其他数据库毗连池做了比对。
支持的数据库种类繁多，可以适配国内数据库。
关于SpringBoot
这个不用多说，Java开发者应该无人不知无人不晓。
整合并查询
创建DB文件
首先登录检察利用指南，必要记住命令都是"."开头，.help展示帮助指令，.open filename创建一个数据库， .quit 退出命令行
:~$ sqlite3
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
.help.auth ON|OFF Show authorizer callbacks.backup ?DB? FILE Backup DB (default "main") to FILE.bail on|off Stop after hitting an error.Default OFF.binary on|off Turn binary output on or off.Default OFF.cd DIRECTORY Change the working directory to DIRECTORY.changes on|off Show number of rows changed by SQL.check GLOB Fail if output since .testcase does not match.clone NEWDB Clone data into NEWDB from the existing database.databases List names and files of attached databases.dbconfig ?op? ?val? List or change sqlite3_db_config() options.dbinfo ?DB? Show status information about the database.dump ?TABLE? ... Render all database content as SQL.echo on|off Turn command echo on or off.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN.excel Display the output of next command in a spreadsheet.exit ?CODE? Exit this program with return-code CODE.expert EXPERIMENTAL. Suggest indexes for specified queries.fullschema ?--indent? Show schema and the content of sqlite_stat tables.headers on|off Turn display of headers on or off.help ?-all? ?PATTERN? Show help text for PATTERN.import FILE TABLE Import data from FILE into TABLE.imposter INDEX TABLE Create imposter table TABLE on index INDEX.indexes ?TABLE? Show names of indexes.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT.lint OPTIONS Report potential schema issues..log FILE|off Turn logging on or off.FILE can be stderr/stdout.mode MODE ?TABLE? Set output mode.nullvalue STRING Use STRING in place of NULL values.once (-e|-x|FILE) Output for the next SQL command only to FILE.open ?OPTIONS? ?FILE? Close existing database and reopen FILE.output ?FILE? Send output to FILE or stdout if FILE is omitted.parameter CMD ... Manage SQL parameter bindings.print STRING... Print literal STRING.progress N Invoke progress handler after every N opcodes.prompt MAIN CONTINUE Replace the standard prompts.quit Exit this program.read FILE Read input from FILE.restore ?DB? FILE Restore content of DB (default "main") from FILE.save FILE Write in-memory database into FILE.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off.schema ?PATTERN? Show the CREATE statements matching PATTERN.selftest ?OPTIONS? Run tests defined in the SELFTEST table.separator COL ?ROW? Change the column and row separators.session ?NAME? CMD ...Create or control sessions.sha3sum ... Compute a SHA3 hash of database content.shell CMD ARGS... Run CMD ARGS... in a system shell.show Show the current values for various settings.stats ?on|off? Show stats or turn stats on or off.system CMD ARGS... Run CMD ARGS... in a system shell.tables ?TABLE? List names of tables matching LIKE pattern TABLE.testcase NAME Begin redirecting output to 'testcase-out.txt'.timeout MS Try opening locked tables for MS milliseconds.timer on|off Turn SQL timer on or off.trace ?OPTIONS? Output each SQL statement as it is run.vfsinfo ?AUX? Information about the top-level VFS.vfslist List all available VFSes.vfsname ?AUX? Print the name of the VFS stack.width NUM1 NUM2 ... Set column widths for "column" modesqlite> 创建一个mydb
sqlite> .open mydb
sqlite> .databases
main: /Users/xxx/mydb
非常简朴地创建了一个DB文件
初始化一个SpringBoot步伐
使用start.spring.io 大概其他的初始化方式，建立一个简朴的SpringBoot项目。
导入Maven依赖
maven里面主要必要，springboot-jpa、druid、sqlite-jdbc、sqlite-dialect(RDBMS 方言)。
[*]springboot-jpa: 这个如果使用mybatis/mybatis-plus等ORM框架，都是可以的；
[*]druid: 这边使用druid-spring-boot-starter，主要为了便捷，单用druid自己手动去配置也是可以的；
[*]sqlite-jdbc：sqlite对于毗连的实现，必须；
[*]sqlite-dialect：是团结JPA所需的，对于Hibernate的方言，这边采用maven库中2023年更新的依赖包；
以上的配置，最关键的就是版本的依赖关系，Hibernete5?Hibernete6 + SpringBoot。
<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>sqlitedemo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sqlitedemo</name>
<description>sqlitedemo</description>
<properties>
<java.version>17</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.6.13</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-log4j2</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.42.0.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.19</version>
</dependency>
<dependency>
<groupId>com.github.gwenn</groupId>
<artifactId>sqlite-dialect</artifactId>
<version>0.1.4</version>
</dependency>
<!-- <dependency>-->
<!-- <groupId>com.zsoltfabok</groupId>-->
<!-- <artifactId>sqlite-dialect</artifactId>-->
<!-- <version>1.0</version>-->
<!-- </dependency>-->
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>17</source>
<target>17</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>${spring-boot.version}</version>
<configuration>
<mainClass>com.example.sqlitedemo.SqlitedemoApplication</mainClass>
<skip>true</skip>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
配置服务毗连参数
application.properties
server.port=8080
spring.application.name=sqlite-test
spring.datasource.druid.url=jdbc:sqlite::resource:db/sqlite.db
spring.datasource.druid.username=
spring.datasource.druid.password=
spring.datasource.druid.driver-class-name=org.sqlite.JDBC
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20
spring.datasource.druid.max-wait=60000
spring.datasource.druid.pool-prepared-statements=false
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=-1
spring.datasource.druid.validation-query=SELECT '1' from sqlite_master
spring.datasource.druid.validation-query-timeout=3
spring.datasource.druid.test-on-borrow=true
spring.datasource.druid.test-on-return=false
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.filters= stat
# WebStatFilter
spring.datasource.druid.web-stat-filter.enabled=false
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.web-stat-filter.session-stat-enable=true
spring.datasource.druid.web-stat-filter.session-stat-max-count=1000
spring.datasource.druid.web-stat-filter.principal-session-name=
spring.datasource.druid.web-stat-filter.principal-cookie-name=
spring.datasource.druid.web-stat-filter.profile-enable=
# StatViewServlet
spring.datasource.druid.stat-view-servlet.enabled=false
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=false
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=123456
spring.datasource.druid.stat-view-servlet.allow=
spring.datasource.druid.stat-view-servlet.deny=
# Spring
spring.datasource.druid.aop-patterns= com.*.service.*
# Dialect(com.github.gwenn.sqlite-dialect)
spring.jpa.database-platform=org.sqlite.hibernate.dialect.SQLiteDialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.naming.strategy=org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect=org.sqlite.hibernate.dialect.SQLiteDialect
spring.jpa.properties.hibernate.event.merge.entity_copy_observer=allow
spring.aop.proxy-target-class=true
注意这里配置的DB的所在，与你刚才新建的DB所在要一致。
书写一个JPA步伐完成一次查询
[*]通过命令行创建一个table
sqlite> CREATE TABLE user
...> (
...> id VARCHAR(8),
...> name VARCHAR(30)
...> );
sqlite>
[*]界说domain
@Entity
@Table(name = "user", schema = "main", catalog = "")
public class User {
private String id;
private String name;
private String age;
@Id
@Column(name = "id", nullable = true, length = 8)
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Basic
@Column(name = "name", nullable = true, length = 30)
public String getName() {
return stationId;
}
public void setName(String name) {
this.name = name;
}
}
[*]界说service
public interface UserService {
List<User> findList();
}
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserRepo userRepo;
@Override
public List<User> findList() {
return userRepo.findAllByNameNotNull();
}
}
[*]界说dao
public interface UserRepo extends JpaRepository<User,String> {
List<User> findAllByNameNotNull();
}
[*]测试
启动服务，书写接口测试
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ ____ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/___)| |_)| | | | | || (_| |) ) ) )
'|____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.6.13)
2024-05-10 10:29:41.354INFO 49697 --- [ main] c.e.sqlitedemo.SqlitedemoApplication : Starting SqlitedemoApplication using Java 17.0.8
2024-05-10 10:29:41.357INFO 49697 --- [ main] c.e.sqlitedemo.SqlitedemoApplication : No active profile set, falling back to 1 default profile: "default"
2024-05-10 10:29:42.630INFO 49697 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2024-05-10 10:29:42.736INFO 49697 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 81 ms. Found 1 JPA repository interfaces.
2024-05-10 10:29:43.141INFO 49697 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'com.alibaba.druid.spring.boot.autoconfigure.stat.DruidSpringAopConfiguration' of type is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2024-05-10 10:29:43.176INFO 49697 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.datasource.druid-com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties' of type is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2024-05-10 10:29:43.186INFO 49697 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'advisor' of type is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2024-05-10 10:29:43.570INFO 49697 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer: Tomcat initialized with port(s): 8081 (http)
2024-05-10 10:29:43.582INFO 49697 --- [ main] o.apache.catalina.core.StandardService : Starting service
2024-05-10 10:29:43.582INFO 49697 --- [ main] org.apache.catalina.core.StandardEngine: Starting Servlet engine:
2024-05-10 10:29:43.752INFO 49697 --- [ main] o.a.c.c.C...[/] : Initializing Spring embedded WebApplicationContext
2024-05-10 10:29:43.752INFO 49697 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2317 ms
2024-05-10 10:29:43.843INFO 49697 --- [ main] c.a.d.s.b.a.DruidDataSourceAutoConfigure : Init DruidDataSource
2024-05-10 10:29:44.533INFO 49697 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2024-05-10 10:29:44.750INFO 49697 --- [ main] o.hibernate.jpa.internal.util.LogHelper: HHH000204: Processing PersistenceUnitInfo
2024-05-10 10:29:44.873INFO 49697 --- [ main] org.hibernate.Version : HHH000412: Hibernate ORM core version 5.6.12.Final
2024-05-10 10:29:45.276INFO 49697 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
2024-05-10 10:29:45.563INFO 49697 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.sqlite.hibernate.dialect.SQLiteDialect
2024-05-10 10:29:45.667INFO 49697 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.sqlite.hibernate.dialect.SQLiteDialect
2024-05-10 10:29:46.753INFO 49697 --- [ main] o.h.e.t.j.p.i.JtaPlatformInitiator : HHH000490: Using JtaPlatform implementation:
2024-05-10 10:29:46.771INFO 49697 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2024-05-10 10:29:47.403WARN 49697 --- [ main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2024-05-10 10:29:47.746INFO 49697 --- [ main] o.s.b.a.w.s.WelcomePageHandlerMapping : Adding welcome page: class path resource
2024-05-10 10:29:48.028INFO 49697 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer: Tomcat started on port(s): 8081 (http) with context path ''
2024-05-10 10:29:48.040INFO 49697 --- [ main] c.e.sqlitedemo.SqlitedemoApplication : Started SqlitedemoApplication in 7.664 seconds (JVM running for 11.35)
2024-05-10 10:30:12.802INFO 49697 --- o.a.c.c.C...[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2024-05-10 10:30:12.802INFO 49697 --- o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2024-05-10 10:30:12.804INFO 49697 --- o.s.web.servlet.DispatcherServlet : Completed initialization in 2 ms
Hibernate: select user0_.id as u_1_0_, user0_.name as name2_0_ from user user0_ where user0_.name is not null
可以或许正常访问
以上SQLite初体验到此结束~~
如果喜欢我的文章的话，可以去GitHub上给一个免费的关注吗？
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]