Sqlite是在工程项目中使用比较多的小型数据库。由于其非常小巧且占用资源低,深受开发者喜欢。这里就介绍一下如何通过Poco框架操作SQLite数据库。这里假设你已经完整编译了Poco库,如果还没有编译好,可以下载我已经编译好的Poco库。下载地址:
首先在工程中引入依赖库PocoDataSQLite.lib,同时包含对应的头文件目录。完成对应的路径添加之后,我们就可以使用Poco库来读写SQLite数据库中的数据了。
在开始使用数据库和结束使用数据库的时候,我们需要注册和注销对应的数据库连接。这里我们采用RAII的模式在构造和析构的时候处理连接,对应的实现如下所示:
SqliteDatabase::SqliteDatabase()
{
//注册连接
Poco::Data::SQLite::Connector::registerConnector();
}
SqliteDatabase::~SqliteDatabase()
{
//注销连接
Poco::Data::SQLite::Connector::unregisterConnector();
}
完成连接的注册之后,我们就可以建立会话来操作数据库中的内容了,创建会话的方式如下所示:
//@1指定数据库的类型为SQLite
//@2指定数据库的名称为test.db
Session session(Poco::Data::SQLite::Connector::KEY, "test.db");
//拼接SQL语句
Statement stmt = (session << "SELECT * from TABLENAME");
//执行对应的SQL
stmt.stmt.execute();
完成上面的配置之后我们就可以通过SQL语句来对数据库中的内容进行处理了。下面以一个简单的数据体为例介绍一下如何通过Poco库实现对数据表的增删改查操作。数据体主要包含五个字段: 姓名,年龄,性别,身高,身份ID。对应的实现如下:
class Person {
public:
std::string mName;//姓名
int mAge; //年龄
std::string mSex; //性别
int mHeight; //身高
std::string mId; //身份ID
};
然后通过SQLite数据库访问类,实现数据的增删改查操作,对应的实现如下:
//SqliteDatabase.h
#ifndef _SQLITE_DATA_BASE_H_
#define _SQLITE_DATA_BASE_H_
#include "Poco\Data\SQLite\SQLite.h"
#include "Poco\Data\Session.h"
#include "Poco\Mutex.h"
#include "Poco\Process.h"
//添加需要的的类
using namespace Poco;
using Poco::FastMutex;
using Poco::ProcessHandle;
using Poco::Data::Session;
using Poco::Data::Keywords::now;
using Poco::Data::Keywords::use;
using Poco::Data::Keywords::bind;
using Poco::Data::Keywords::into;
using Poco::Data::Statement;
//数据结构体
class Person {
public:
std::string mName;//姓名
int mAge; //年龄
std::string mSex; //性别
int mHeight; //身高
std::string mId; //身份ID
};
class SqliteDatabase
{
public:
static SqliteDatabase* Instance();
private:
SqliteDatabase();
public:
~SqliteDatabase();
//增加用户
bool addNewPerson(Person& info);
//删除用户
bool deletePersonByID(const std::string& uuid);
//修改用户信息
bool updatePersonByID(const std::string& uuid, Person& info);
//查找用户信息
bool searchPersonByID(std::string uuid, Person& info);
//判断某个用户信息是否存在
bool isPersonInfoExist(const std::string& uuid);
private:
static SqliteDatabase* mInstance;
static FastMutex mutex;
};
#endif
//SqliteDatabase.cpp
#include "SqliteDatabase.h"
#include "Poco\Data\SQLite\Connector.h"
SqliteDatabase* SqliteDatabase::mInstance = nullptr;
FastMutex SqliteDatabase::mutex;
//单例模式,doublecheck,线程安全
SqliteDatabase * SqliteDatabase::Instance()
{
if (mInstance == nullptr)
{
mutex.lock();
if (mInstance == nullptr)
{
mInstance = new SqliteDatabase();
}
mutex.unlock();
}
return mInstance;
}
SqliteDatabase::SqliteDatabase()
{
Poco::Data::SQLite::Connector::registerConnector();
}
SqliteDatabase::~SqliteDatabase()
{
Poco::Data::SQLite::Connector::unregisterConnector();
}
bool SqliteDatabase::addNewPerson(Person & info)
{
Session session(Poco::Data::SQLite::Connector::KEY, "test.db");
session << "CREATE TABLE IF NOT EXISTS PERSONINFO (UUID VARCHAR(64), NAME VARCHAR(64), AGE INTEGER(3), SEX VARCHAR(64), HEIGHT INTEGER(3))", now;
if (isPersonInfoExist(info.mId))
{
return false;
}
//采用绑定的形式防止SQL注入
Statement stmt = (session << "INSERT INTO PERSONINFO VALUES(:uuid, :name, :age, :sex, :height)",
bind(info.mId), bind(info.mName), bind(info.mAge), bind(info.mSex), bind(info.mHeight));
return (stmt.execute() > 0);
}
bool SqliteDatabase::deletePersonByID(const std::string & uuid)
{
Session session(Poco::Data::SQLite::Connector::KEY, "test.db");
Statement stmt = (session << "DELETE FROM PERSONINFO WHERE UUID = :uuid", bind(uuid.c_str()));
return (stmt.execute() == 1);
}
bool SqliteDatabase::updatePersonByID(const std::string & uuid, Person & info)
{
Session session(Poco::Data::SQLite::Connector::KEY, "test.db");
Statement stmt = (session << "UPDATE PERSONINFO SET NAME = :name, AGE = :age, SEX = :sex, HEIGHT = :height WHERE UUID = :uuid",
bind(info.mName), bind(info.mAge), bind(info.mSex), bind(info.mHeight), bind(uuid));
return (stmt.execute() == 1);
}
bool SqliteDatabase::searchPersonByID(std::string uuid, Person & info)
{
Session session(Poco::Data::SQLite::Connector::KEY, "test.db");
int age = 0;
int height = 0;
std::string name;
std::string sex;
Statement stmt = (session << "SELECT NAME, AGE, SEX, HEIGHT FROM PERSONINFO WHERE UUID = :uuid",
into(name), into(age), into(sex), into(height), bind(uuid));
if (stmt.execute() == 1)
{
info.mAge = age;
info.mName = name;
info.mSex = sex;
info.mHeight = height;
info.mId = uuid;
return true;
}
return false;
}
bool SqliteDatabase::isPersonInfoExist(const std::string & uuid)
{
Session session(Poco::Data::SQLite::Connector::KEY, "test.db");
Statement stmt = (session << "SELECT * from PERSONINFO WHERE UUID = :uuid", bind(uuid));
if (stmt.execute() == 1)
{
return true;
}
return false;
}
数据库访问类,采用线程安全的单例模式,可以在多线程的场景下使用。