SpringBoot + Druid + Sqlite 文件数据库初体验 - ToB企服应用市场:ToB评测及商务社交产业平台

:~$ 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>

复制代码

:~$ 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>

复制代码

sqlite> .open mydb
sqlite> .databases
main: /Users/xxx/mydb

复制代码

<?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>
</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>

复制代码

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

复制代码

sqlite> CREATE TABLE user
...> (
...> id VARCHAR(8),
...> name VARCHAR(30)
...> );
sqlite>

复制代码

@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;
}
}

复制代码

public interface UserService {
List<User> findList();
}
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserRepo userRepo;
@Override
public List<User> findList() {
return userRepo.findAllByNameNotNull();
}
}

复制代码

public interface UserRepo extends JpaRepository<User,String> {
List<User> findAllByNameNotNull();
}

复制代码

. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.6.13)
2024-05-10 10:29:41.354 INFO 49697 --- [ main] c.e.sqlitedemo.SqlitedemoApplication : Starting SqlitedemoApplication using Java 17.0.8
2024-05-10 10:29:41.357 INFO 49697 --- [ main] c.e.sqlitedemo.SqlitedemoApplication : No active profile set, falling back to 1 default profile: "default"
2024-05-10 10:29:42.630 INFO 49697 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2024-05-10 10:29:42.736 INFO 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.141 INFO 49697 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'com.alibaba.druid.spring.boot.autoconfigure.stat.DruidSpringAopConfiguration' of type [com.alibaba.druid.spring.boot.autoconfigure.stat.DruidSpringAopConfiguration] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2024-05-10 10:29:43.176 INFO 49697 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.datasource.druid-com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties' of type [com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2024-05-10 10:29:43.186 INFO 49697 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'advisor' of type [org.springframework.aop.support.RegexpMethodPointcutAdvisor] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2024-05-10 10:29:43.570 INFO 49697 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8081 (http)
2024-05-10 10:29:43.582 INFO 49697 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2024-05-10 10:29:43.582 INFO 49697 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.68]
2024-05-10 10:29:43.752 INFO 49697 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2024-05-10 10:29:43.752 INFO 49697 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2317 ms
2024-05-10 10:29:43.843 INFO 49697 --- [ main] c.a.d.s.b.a.DruidDataSourceAutoConfigure : Init DruidDataSource
2024-05-10 10:29:44.533 INFO 49697 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2024-05-10 10:29:44.750 INFO 49697 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default]
2024-05-10 10:29:44.873 INFO 49697 --- [ main] org.hibernate.Version : HHH000412: Hibernate ORM core version 5.6.12.Final
2024-05-10 10:29:45.276 INFO 49697 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
2024-05-10 10:29:45.563 INFO 49697 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.sqlite.hibernate.dialect.SQLiteDialect
2024-05-10 10:29:45.667 INFO 49697 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.sqlite.hibernate.dialect.SQLiteDialect
2024-05-10 10:29:46.753 INFO 49697 --- [ main] o.h.e.t.j.p.i.JtaPlatformInitiator : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2024-05-10 10:29:46.771 INFO 49697 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2024-05-10 10:29:47.403 WARN 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.746 INFO 49697 --- [ main] o.s.b.a.w.s.WelcomePageHandlerMapping : Adding welcome page: class path resource [static/index.html]
2024-05-10 10:29:48.028 INFO 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.040 INFO 49697 --- [ main] c.e.sqlitedemo.SqlitedemoApplication : Started SqlitedemoApplication in 7.664 seconds (JVM running for 11.35)
2024-05-10 10:30:12.802 INFO 49697 --- [nio-8081-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2024-05-10 10:30:12.802 INFO 49697 --- [nio-8081-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2024-05-10 10:30:12.804 INFO 49697 --- [nio-8081-exec-1] 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

复制代码