poco的sqlite数据库

一、注册注销数据库连接

        我这里对外这个数据库操作类是单例，所有我这里直接把数据库注销和注册放在析构和构造函数里面了

SqliteDatabase::SqliteDatabase()
{
	//注册连接
	Poco::Data::SQLite::Connector::registerConnector();
}
SqliteDatabase::~SqliteDatabase()
{
	//注销连接
	Poco::Data::SQLite::Connector::unregisterConnector();
}

二、添加一条新的数据

        CREATE TABLE IF NOT EXISTS PATENTINFO 这里是假如没有找到数据库表就直接创建一张

        

bool SqliteDatabase::addNewPatentInfo(PatentInfo& info, std::string& strErro)
{
	try 
	{
		// 创建数据库会话  
		Session session(Poco::Data::SQLite::Connector::KEY, "PatentInfos.db");
		session << "CREATE TABLE IF NOT EXISTS PATENTINFO (CertificateNumber VARCHAR PRIMARY KEY NOT NULL, RightsOwner VARCHAR(30) NOT NULL, PatentName VARCHAR NOT NULL, Note VARCHAR, FilePath VARCHAR, StartTime INTEGER(64) NOT NULL, EndTime INTEGER(64) NOT NULL, PatentStatu INTEGER(8) NOT NULL, MainClass INTEGER(8) NOT NULL, SubClass INTEGER(8) NOT NULL)", now;

		if (isPersonInfoExist(info.strCertificateNumber))
		{
			return false;
		}


		//采用绑定的形式防止SQL注入
		Statement stmt = (session << "INSERT INTO PATENTINFO VALUES(:CertificateNumber, :RightsOwner, :PatentName, :Note, \
			:FilePath, :StartTime, :EndTime, :PatentStatu, :MainClass, :SubClass)",
			bind(info.strCertificateNumber), bind(info.strRightsOwner), bind(info.strPatentName), bind(info.strNote), bind(info.strFilePath),
			bind(info.nStartTime), bind(info.nEndTime), bind(info.ePatentStatu), bind(info.eMainClass), bind(info.eSubClass));

		return (stmt.execute() > 0);
	}
	catch (const Poco::Data::SQLite::SQLiteException& ex) {
		// 捕获 SQLite 异常并输出错误信息  
		strErro = "SQLite Error: " + std::string(ex.what())+ "  Error Code:"+ std::to_string(ex.code());
		return false;
	}
	catch (const Poco::Exception& ex) {
		// 捕获其他 Poco 异常  
		strErro="Poco Error: " + std::string(ex.what());
		return false;
	}
	catch (const std::exception& ex) {
		// 捕获标准异常  
		strErro = "Standard Error: " + std::string(ex.what());
		return false;
	}
}

二 删除主键的一条数据

        这里是支持sql语句的，如果CertificateNumber不是主键的话，就是删除CertificateNumber为strCertificateNumber的所有数据

bool SqliteDatabase::deletePatentInfo(const std::string& strCertificateNumber, std::string& strErro)
{
	try
	{
		Session session(Poco::Data::SQLite::Connector::KEY, "PatentInfos.db");
		Statement stmt = (session << "DELETE FROM PATENTINFO WHERE CertificateNumber = ?", bind(strCertificateNumber));
		if (stmt.execute() == 1)
		{
			return true;
		}
		else
		{
			strErro = "not find CertificateNumber";
			return false;
		}
		
	}
	catch (const Poco::Data::SQLite::SQLiteException& ex) {
		// 捕获 SQLite 异常并输出错误信息  
		strErro = "SQLite Error: " + std::string(ex.what()) + "  Error Code:" + std::to_string(ex.code());
		return false;
	}
	catch (const Poco::Exception& ex) {
		// 捕获其他 Poco 异常  
		strErro = "Poco Error: " + std::string(ex.what());
		return false;
	}
	catch (const std::exception& ex) {
		// 捕获标准异常  
		strErro = "Standard Error: " + std::string(ex.what());
		return false;
	}
}

三、修改一条数据，根据CertificateNumber

bool SqliteDatabase::updatePatentInfo(PatentInfo& info, std::string& strErro)
{
	try
	{
		Session session(Poco::Data::SQLite::Connector::KEY, "PatentInfos.db");
		Statement stmt = (session << "UPDATE PATENTINFO SET RIGHTSOWNER = :RightsOwner, PATENTNAME = :PatentName, NOTE = :Note, FILEPATH = :FilePath  \
			, STARTTIME = :StartTime, ENDTIME = :EndTime, PATENTSTATU = :PatentStatu, MAINCLASS = :MainClass, SUBCLASS = :SubClass\
			WHERE CertificateNumber = :CertificateNumber",
			bind(info.strRightsOwner), bind(info.strPatentName), bind(info.strNote), bind(info.strFilePath), bind(info.nStartTime)
			, bind(info.nEndTime), bind(info.ePatentStatu), bind(info.eMainClass), bind(info.eSubClass));
		return (stmt.execute() == 1);
	}
	catch (const Poco::Data::SQLite::SQLiteException& ex) {
		// 捕获 SQLite 异常并输出错误信息  
		strErro = "SQLite Error: " + std::string(ex.what()) + "  Error Code:" + std::to_string(ex.code());
		return false;
	}
	catch (const Poco::Exception& ex) {
		// 捕获其他 Poco 异常  
		strErro = "Poco Error: " + std::string(ex.what());
		return false;
	}
	catch (const std::exception& ex) {
		// 捕获标准异常  
		strErro = "Standard Error: " + std::string(ex.what());
		return false;
	}
}

四、查询主键为info.strCertificateNumber的一条数据

bool SqliteDatabase::searchPatentInfo(PatentInfo& info, std::string& strErro)
{
	Session session(Poco::Data::SQLite::Connector::KEY, "PatentInfos.db");


	time_t nStartTime = 0;
	time_t nEndTime = 0;
	uint8_t nPatentStatu = STATU_UNKNOWN;
	uint8_t nMainClass = MAIN_CLASS_UNKNO;
	uint8_t nSubClass = SUB_CLASS_UNKNOWN;
	std::string strRightsOwner;
	std::string strPatentName;
	std::string strNote;
	std::string strFilePath;


	Statement stmt = (session << "SELECT RightsOwner, PatentName, Note, FilePath, StartTime, EndTime, PatentStatu,MainClass, SubClass FROM PERSONINFO WHERE CERTIFICATENUMBER = :CertificateNumber",
		into(strRightsOwner), into(strPatentName), into(strNote), into(strFilePath), into(nStartTime), into(nEndTime),
		into(nPatentStatu), into(nMainClass), into(nSubClass), bind(info.strCertificateNumber));
	if (stmt.execute() == 1)
	{
		strErro = "not find data";
		info.strRightsOwner = strRightsOwner;
		info.strPatentName = strPatentName;
		info.strNote = strNote;
		info.strFilePath = strFilePath;
		info.nStartTime = nStartTime;
		info.nEndTime = nEndTime;
		info.ePatentStatu = (PatentStatus)nPatentStatu;
		info.eMainClass = (MainClass)nMainClass;
		info.eSubClass = (SubClass)nSubClass;
		return true;
	}
	return false;
}

五、查询所有数据

        这里需要注意一下数据的转换，很容易引起异常

bool SqliteDatabase::getAllPatentInfo( std::vector<PatentInfo>& vector, std::string& strErro)
{

	try
	{
		Session session(Poco::Data::SQLite::Connector::KEY, "PatentInfos.db");

		// 执行查询并获取结果  
		RecordSet rs(session, "SELECT CertificateNumber,RightsOwner, PatentName, Note, FilePath, StartTime, EndTime, PatentStatu,MainClass, SubClass FROM PATENTINFO");
		rs.moveFirst();
		for (size_t i = 0; i < rs.rowCount(); i++)
		{
			PatentInfo stu;
			stu.strCertificateNumber = rs["CertificateNumber"].toString();
			stu.strRightsOwner = rs["RightsOwner"].toString();
			stu.strPatentName = rs["PatentName"].toString();
			stu.strNote = rs["Note"].toString();
			stu.strFilePath = rs["FilePath"].toString();
			stu.nStartTime= rs["StartTime"].convert<__int64>();
			stu.nEndTime = rs["EndTime"].convert<__int64>();
			stu.ePatentStatu = static_cast<PatentStatus>(rs["PatentStatu"].convert<__int8>());
			stu.eMainClass = static_cast<MainClass>(rs["MainClass"].convert<__int8>());
			stu.eSubClass = static_cast<SubClass>(rs["SubClass"].convert<__int8>());
			vector.push_back(stu);
			rs.moveNext();
		}
		if (0 < vector.size())
			return true;
		else
			return false;
	}
	catch (const Poco::Exception& ex)
	{
		std::cerr << "Error: " << ex.displayText() << std::endl;
		return true;
	}
}