目标

在SpringBoot中集成内存数据库Sqlite.

为什么

像H2、hsqldb、derby、sqlite这样的内存数据库，小巧可爱，做小型服务端演示程序，非常好用。最大特点就是不需要你另外安装一个数据库。

操作步骤

1. 修改pom.xml文件

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

2. 修改项目配置文件application.yml

spring:
  datasource:
    username: hsp
    password: 123456
    url: jdbc:derby:blogDb;create=true
    driver-class-name: org.apache.derby.jdbc.EmbeddedDriver
    schema: classpath:schema.sql
    data: classpath:data.sql
    initialization-mode: always
    continue-on-error: true

3. 添加初始化数据文件

• 建表脚本：schema.sql

CREATE TABLE `blog` (
  `id` int AUTO_INCREMENT NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

• 导入数据脚本：data.sql

insert into blog(id,title) values(1,'花生皮编程博客');

4. 启动类：HspApplication

@MapperScan({"cn.hsp.blog"})
@SpringBootApplication
public class HspApplication {

	public static void main(String[] args) {
		SpringApplication.run(HspApplication.class, args);
	}

}

5. Controller类：BlogController

@RestController
@RequestMapping("/blog")
public class BlogController {

    @Autowired
    private BlogMapper blogMapper;

    @GetMapping(value="/query")
    public List<Blog> query()
    {
        return blogMapper.query();
    }
}

6. Mapper类：BlogMapper

@Repository
public interface BlogMapper {
    @Select(value = "select * from blog")
    List<Blog> query();
}

7. 数据bean：Blog

@Data
public class Blog {
    private int id;
    private String title;
}

工程截图

运行

运行HspApplication即可

效果

完整源代码

https://gitee.com/hspbc/springboot_memdb.git

关于我

厦门大学计算机专业|华为八年高级工程师
十年软件开发经验，5年编程培训教学经验
目前从事编程教学，软件开发指导，软件类毕业设计指导。
所有编程资料及开源项目见https://cxyxy.blog.csdn.net/article/details/120405881

集成内存数据库系列

SpringBoot集成内存数据库H2
SpringBoot集成内存数据库Derby
SpringBoot集成内存数据库hsqldb
SpringBoot集成内存数据库Sqlite

Sqlite3内存数据库

Sqlite3是一个小型的，嵌入式的数据库。它还具有一个特色的功能：即内存数据库模式（与Redis ,Memcached等不同）。 这篇文章使用 .Net 5.0 平台演示实现Sqlite3的内存数据库模式。

环境及依赖

我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客：

1. 开发环境**.Net 5.0**
2. VS2019
3. Nuget 包： Microsoft.Data.Sqlite

功能演示-内存模式

using Microsoft.Data.Sqlite;
public class MsSqliteTest
{
	string MemoryConnStr = "Data Source=InMemorySample;Mode=Memory;Cache=Shared";
	string FileConnStr = "Data Source=MsSqliteTest.db;Cache=Shared";
	SqliteConnection Conn;
	public bool CheckTableExists(SqliteConnection Conn, string tableName)
	{
		var comm = Conn.CreateCommand();
		comm.CommandText = $"Select Count(name) FROM sqlite_master WHERE type='table' AND name='{tableName}';";
		int rt = System.Convert.ToInt32(comm.ExecuteScalar());
		return rt > 0;
	}
	
	public bool OpenCreateTable(SqliteConnection Conn)
	{
		Conn.Open();
		if (CheckTableExists(Conn, "Users") == false)
		{
			var comm = Conn.CreateCommand();
			comm.CommandText = "Create Table Users (Id Integer PRIMARY KEY AUTOINCREMENT,  Name CHAR (2, 20) NOT NULL,  Age  INTEGER);";
			int rt = System.Convert.ToInt32(comm.ExecuteNonQuery());
			comm.Dispose();
			return rt > 0;
		}
		return true;
	}
	
	public int InsertTable(SqliteConnection Conn)
	{
		var comm = Conn.CreateCommand();
		comm.CommandText = "insert into Users (Name,Age) Values ('xiaoming1',25)";
		int rt = System.Convert.ToInt32(comm.ExecuteNonQuery());
		comm.Dispose();
		return rt;
	}
	
	public int SelectTableCount(SqliteConnection Conn)
	{
		var comm = Conn.CreateCommand();
		comm.CommandText = "select Count(ID) From Users";
		int rt = System.Convert.ToInt32(comm.ExecuteScalar());
		comm.Dispose();
		return rt;
	}
	
	public void MemTest()
	{
		Conn = new SqliteConnection(MemoryConnStr);
		OpenCreateTable(Conn);
		Console.WriteLine("Mem:Insert=" + System.Convert.ToString(InsertTable(Conn)));
		Console.WriteLine("Mem:Select=" + System.Convert.ToString(SelectTableCount(Conn)));
		Conn.Close();
		Conn.Dispose();
	}


}

功能演示-文件模式

	public void FileTest()
	{
		Conn = new SqliteConnection(FileConnStr);
		OpenCreateTable(Conn);
		Console.WriteLine("File:Insert=" + System.Convert.ToString(InsertTable(Conn)));
		Console.WriteLine("File:Select=" + System.Convert.ToString(SelectTableCount(Conn)));
		Conn.Close();
		Conn.Dispose();
	}

功能演示-文件加载到内存

	public void LoadFileToMem()
	{
		Conn = new SqliteConnection(FileConnStr);
		Conn.Open();
		var MemConn = new SqliteConnection(MemoryConnStr);
		MemConn.Open();
		Conn.BackupDatabase(MemConn);
		
		Console.WriteLine(SelectTableCount(Conn));
		Console.WriteLine(SelectTableCount(MemConn));
		
		Conn.Close();
		Conn.Dispose();
		
		MemConn.Close();
		MemConn.Dispose();
		
	}
	

功能演示-内存保存到文件

		/// <summary>
	/// 子查询第6到10条:共5条
	/// </summary>
	public void SQLiteSubSelectTest(SqliteConnection Conn, int startLine, int endLine)
	{
		// SQL Server
		// select top 10 * from users where id not in(select top 30 id from users order by id asc) order by id asc
		var comm = Conn.CreateCommand();
		var Count = endLine - startLine + 1;
		var NotInCount = startLine - 1;
		// Select ID from users where id not in(select ID from users order by id asc Limit 0,5) order by id asc Limit 0,5
		comm.CommandText = $"Select ID from Users Where ID not in(select ID from Users Limit 0,{NotInCount}) Limit 0,{Count}";
		var reader = comm.ExecuteReader();
		var readedLine = 0;
		while (reader.Read())
		{
			Console.WriteLine($"ID={reader.GetInt32(0)}");
			readedLine++;
		}
		Console.WriteLine($"SQLiteSubSelect Read Line={readedLine}");
		reader.Close();
		reader.DisposeAsync();
		comm.Dispose();
		
	}
	


	public void SaveMemToFileTest()
	{
		Conn = new SqliteConnection(FileConnStr);
		Conn.Open();
		var MemConn = new SqliteConnection(MemoryConnStr);
		MemConn.Open();
		Conn.BackupDatabase(MemConn); //Load File To Mem
		
		Console.WriteLine(SelectTableCount(Conn));
		Console.WriteLine(SelectTableCount(MemConn));
		
		
		Console.WriteLine(InsertTable(MemConn));
		Console.WriteLine(InsertTable(MemConn));
		Console.WriteLine(SelectTableCount(MemConn));
		
		MemConn.BackupDatabase(Conn); //Save Mem To File
		
		SQLiteSubSelectTest(MemConn, 6, 10);
		
		MemConn.Close();
		MemConn.Dispose();
		
		Conn.Close();
		Conn.Dispose();
	}
	
	public void AllTest()
	{
		MemTest();
		FileTest();
		LoadFileToMem();
		SaveMemToFileTest();
	}
	public void Main(string[] argv)
	{
		MsSqliteTest ms = new MsSqliteTest();
		// ms.MemTest()
		// ms.FileTest()
		// ms.LoadFileToMem()
		// ms.SaveMemToFileTest()
		ms.AllTest();
	}

执行结果-第N次执行

Mem:Insert=1
Mem:Select=1
File:Insert=1
File:Select=4
4
4
4
4
1
1
6
ID=6
SQLiteSubSelect Read Line=1

执行结果-第N+1次执行

Mem:Insert=1
Mem:Select=1
File:Insert=1
File:Select=7
7
7
7
7
1
1
9
ID=6
ID=7
ID=8
ID=9
SQLiteSubSelect Read Line=4