UE5 SQLite笔记

开发环境：

系统：Windows 10 64 bit
引擎：Unreal Engine 5.1.1
IDE：JetBrains Rider 2023.2.1
语言：C++
工具：DB Browser for SQLite

 SQLite数据类型：

    //INTEGER	TEXT	BLOB	REAL	NUMERIC
	/*
		integer 				--->整数，可以是1、2、3、4、6或8个字节，SQLite会根据数值大小自动调整。
		real 					--->实数(浮点数)，一律使用8个字节存储
		text 					--->文本，最大支持长度为1,000,000,000个字符的单个字符串
		blob 					--->二进制对象，最大支持长度为1,000,000,000个字节
		null 					--->没有值
		char(size)  			--->固定长度的字符串，size规定字符串的长度
		varchar(size) 			--->可变长度的字符串，size规定字符串的最大字符个数
	*/

启用插件：

添加插件模块引用："SQLiteCore","SQLiteSupport"

// Copyright Epic Games, Inc. All Rights Reserved.

using UnrealBuildTool;

public class PakFramework : ModuleRules
{
	public PakFramework(ReadOnlyTargetRules Target) : base(Target)
	{
		PCHUsage = PCHUsageMode.UseExplicitOrSharedPCHs;
	
		PublicDependencyModuleNames.AddRange(new string[] { "Core", "CoreUObject", "Engine", "InputCore", "UMG","SQLiteCore","SQLiteSupport"});

		// Uncomment if you are using Slate UI
		//PrivateDependencyModuleNames.AddRange(new string[] { "Slate", "SlateCore", "InputDevice"});
		
		// Uncomment if you are using online features
		// PrivateDependencyModuleNames.Add("OnlineSubsystem");

		// To include OnlineSubsystemSteam, add it to the plugins section in your uproject file with the Enabled attribute set to true
	}
}

示例代码：

【ASQLiteManager.h】

// Fill out your copyright notice in the Description page of Project Settings.

#pragma once

#include "CoreMinimal.h"
#include "GameFramework/Actor.h"
#include "SQLiteDatabaseConnection.h"
#include "SQLiteManager.generated.h"

UCLASS()
class PAKFRAMEWORK_API ASQLiteManager : public AActor
{
	GENERATED_BODY()

public:
	// Sets default values for this actor's properties
	ASQLiteManager();

protected:
	// Called when the game starts or when spawned
	virtual void BeginPlay() override;

public:
	// Called every frame
	virtual void Tick(float DeltaTime) override;
	virtual void EndPlay(const EEndPlayReason::Type EndPlayReason) override;

private:
	FSQLiteDatabaseConnection DBConnection;
	bool bIsOpened = false;
public:
	bool OpenDB(const FString& FileFullPath,const FString& UserName = TEXT(""),const FString& Password = TEXT(""));
	bool CloseDB();
	bool ExecSql(const TCHAR* InStatement);
	bool ExecSql(const TCHAR* InStatement, FDataBaseRecordSet*& RecordSet);
}

【ASQLiteManager.cpp】

// Fill out your copyright notice in the Description page of Project Settings.


#include "SQLiteManager.h"

#include "MyFramework/AOT/Runtime/CoreKits/LogKit.h"


// Sets default values
ASQLiteManager::ASQLiteManager()
{
	// Set this actor to call Tick() every frame.  You can turn this off to improve performance if you don't need it.
	PrimaryActorTick.bCanEverTick = true;
}

// Called when the game starts or when spawned
void ASQLiteManager::BeginPlay()
{
	Super::BeginPlay();

	const FString DBFileFullPath = TEXT("D:/_DB/DB_G005.db");
	if(OpenDB(DBFileFullPath))
	{
		/*
		//【1.创建数据表】
		if(ExecSql(TEXT("create table table_name(id integer,name text,age integer);") ))
		{
			ULogKit::I(__FUNCTION__,TEXT("成功生成表格!!!"));
		}
		else
		{
			ULogKit::E(__FUNCTION__,TEXT("生成表格失败!!!"));
		}
		*/

		/*
		//【2.检查数据表是否存在】
		FDataBaseRecordSet* ResultSet;
		if(ExecSql(TEXT("select * from sqlite_master where type = 'table' and name = 'table_name';"),ResultSet))
		{
			const int32 RecordCount = ResultSet->GetRecordCount();
			if(RecordCount > 0)
			{
				ULogKit::I(__FUNCTION__,TEXT("数据表 存在!!!"));
			}
			else
			{
				ULogKit::E(__FUNCTION__,TEXT("数据表 不存在 1 !!!"));
			}
			//【注意】
			//此处必须删除FDataBaseRecordSet对象，其为new方式生成对象-->[FSQLiteDatabaseConnection.cpp]-->RecordSet = new FSQLiteResultSet(MoveTemp(PreparedStatement));
			//否则CloseDB()会失败，退出程序时抛出异常："Destructor called while an SQLite database was still open. Did you forget to call Close?"
			delete ResultSet;
			ResultSet = nullptr;
		}
		else
		{
			ULogKit::E(__FUNCTION__,TEXT("数据表 不存在 2 !!!"));
		}
		*/
		
		/*
		//【3.插入表字段】
		if(ExecSql(TEXT("alter table table_name add column age2 integer;")))
		{
			ULogKit::I(__FUNCTION__,TEXT("插入表字段 成功!!!"));
		}
		else
		{
			ULogKit::E(__FUNCTION__,TEXT("插入表字段 失败!!!"));
		}
		*/
		
		/*
		//【4.删除表】
		if(ExecSql(TEXT("drop table table_name;")))
		{
			ULogKit::I(__FUNCTION__,TEXT("删除表 成功!!!"));
		}
		else
		{
			ULogKit::E(__FUNCTION__,TEXT("删除表 失败!!!"));
		}
		*/

		/*
		//【5.1.数据-增加行】
		if(ExecSql(TEXT("insert into table_name values(1,\"张三\",18,19);")))//不指定列字段
		{
			ULogKit::I(__FUNCTION__,TEXT("增加行 成功!!!"));
		}
		else
		{
			ULogKit::E(__FUNCTION__,TEXT("增加行 失败!!!"));
		}
		//【5.2.数据-增加行】
		if(ExecSql(TEXT("insert into table_name(name ,age) values(\"李四\",18);")))//指定列字段
		{
			ULogKit::I(__FUNCTION__,TEXT("增加行 成功!!!"));
		}
		else
		{
			ULogKit::E(__FUNCTION__,TEXT("增加行 失败!!!"));
		}
		*/
		
		/*
		//【6.数据-删除行】
		if(ExecSql(TEXT("delete from table_name where id=1;")))
		{
			ULogKit::I(__FUNCTION__,TEXT("删除行 成功!!!"));//若删除不存在项，也会返回true
		}
		else
		{
			ULogKit::E(__FUNCTION__,TEXT("删除行 失败!!!"));
		}
		*/

		/*
		//【7.数据-更新行】
		if(ExecSql(TEXT("update table_name set name=\"王五\",age=38 where id=1;")))
		{
			ULogKit::I(__FUNCTION__,TEXT("更新行 成功!!!"));//删除不存在项，也会返回true
		}
		else
		{
			ULogKit::E(__FUNCTION__,TEXT("更新行 失败!!!"));
		}
		*/

		
		//【8.数据-查询】
		FDataBaseRecordSet* ResultSet;
		if(DBConnection.Execute(TEXT("select * from tablename"),ResultSet))
		{
			FDataBaseRecordSet::TIterator itor(ResultSet);
			//通过get获得数据
			while (itor) {
				int32 Id = itor->GetInt(TEXT("id"));
				FString Name = itor->GetString(TEXT("name"));
				int32 Age = itor->GetInt(TEXT("age"));
				ULogKit::I(__FUNCTION__,
					TEXT("id = ")+FString::FromInt(Id)
					+TEXT(" ; name = ")+Name
					+TEXT(" ; age = ")+FString::FromInt(Age));
				++itor;
			}
			//【注意】
			//此处必须删除FDataBaseRecordSet对象，其为new方式生成对象-->[FSQLiteDatabaseConnection.cpp]-->RecordSet = new FSQLiteResultSet(MoveTemp(PreparedStatement));
			//否则CloseDB()会失败，退出程序时抛出异常："Destructor called while an SQLite database was still open. Did you forget to call Close?"
			delete ResultSet;
			ResultSet = nullptr;
		}
		
	}
	else
	{
		ULogKit::E(__FUNCTION__,TEXT("无法打开DB!!!"));
	}
}

// Called every frame
void ASQLiteManager::Tick(float DeltaTime)
{
	Super::Tick(DeltaTime);
}

void ASQLiteManager::EndPlay(const EEndPlayReason::Type EndPlayReason)
{
	Super::EndPlay(EndPlayReason);
	CloseDB();
}

bool ASQLiteManager::OpenDB(const FString& FileFullPath,const FString& UserName,const FString& Password)
{
	if(IsDatabaseExists(FileFullPath))
	{
		if(DBConnection.Open(*FileFullPath,nullptr,nullptr))
		{
			bIsOpened = true;
		}
		else
		{
			bIsOpened = false;
		}
	}
	else
	{
		bIsOpened = false;
	}
	return bIsOpened;
}

bool ASQLiteManager::CloseDB()
{
	if(bIsOpened)
	{
		DBConnection.Close();
		return true;
	}
	else
	{
		return false;
	}
}

bool ASQLiteManager::ExecSql(const TCHAR* InStatement)
{
	if(bIsOpened)
	{
		return DBConnection.Execute(InStatement);
	}
	else
	{
		return false;
	}
}

bool ASQLiteManager::ExecSql(const TCHAR* InStatement, FDataBaseRecordSet*& RecordSet)
{
	if(bIsOpened)
	{
		return DBConnection.Execute(InStatement,RecordSet);
	}
	else
	{
		return false;
	}
}