Poco库使用:操作SQLite数据库

Sqlite是在工程项目中使用比较多的小型数据库。由于其非常小巧且占用资源低，深受开发者喜欢。这里就介绍一下如何通过Poco框架操作SQLite数据库。这里假设你已经完整编译了Poco库，如果还没有编译好，可以下载我已经编译好的Poco库。下载地址:

VS2017编译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;
}

数据库访问类，采用线程安全的单例模式，可以在多线程的场景下使用。