poco框架库：数据库的基本⽤法
最近，⼀直在加强数据处理模块的功能，种种数据库中间件，感觉poco的还是⽐较好的，开发效率好，API接⼝清晰，下⾯，把最常⽤的基本步骤整理下来，供⼤家参考。
⼀ 基本步骤
a.注册会话类型
b. 创建会话(session)
c. 从DB中读写数据(神奇的into, use)
d. 使⽤statements
e. 使⽤容器（Collection） (数据，集合...)
f. 使⽤limit限定
g. 如何使⽤复杂的数据类型(如何将⼀个C++对象映射到数据库的表
⼆ 例程如下
例⼦以sqlite为主，其它数据库，只是建⽴会话参数不⼀样
2.1基本标准的数据库连接使⽤⽅法
#include "Poco/Data/Session.h"
#include "Poco/Data/SQLite/Connector.h"
#include <vector>
#include <iostream>
using namespace Poco::Data::Keywords;
using Poco::Data::Session;
using Poco::Data::Statement;
//保存查询结果
struct Person
{
std::string name;
std::string address;
int age;
};
int main(int argc, char** argv)
{
//注册会话类型
Poco::Data::SQLite::Connector::registerConnector();
// 建⽴会话
Session session("SQLite", "sample.db");
/
/ 如果有名为Person的表存在，删除
session << "DROP TABLE IF EXISTS Person", now;
// 创建表格
session << "CREATE TABLE Person (Name VARCHAR(30), Address VARCHAR, Age INTEGER(3))", now;
// 建⽴⼀个结构体变量
Person person =
{
"Bart Simpson",
"Springfield",
12
};
//创建执⾏命令的语句执⾏器
Statement insert(session);
insert << "INSERT INTO Person VALUES(?, ?, ?)",
use(person.name),
use(person.address),
use(person.age);
//插⼊纪录
//插⼊第⼆条纪录
person.name = "Lisa Simpson";
person.address = "Springfield";
person.age = 10;
// 建⽴查询执⾏器
Statement select(session);
select << "SELECT Name, Address, Age FROM Person",
into(person.name),
into(person.address),
into(person.age),
range(0, 1); // 每次放⼀条纪录
while (!select.done())
{
std::cout << person.name << " " << person.address << " " << person.age << std::endl; }
// 另⼀种，把查询结果放⼊到容器中
std::vector<std::string> names;
session << "SELECT Name FROM Person",
into(names),
now;
for (std::vector<std::string>::const_iterator it = names.begin(); it != d(); ++it) {
std::cout << *it << std::endl;
}
//退出会话注册器
Poco::Data::SQLite::Connector::unregisterConnector();
return 0;
}
2.2把查询结果信放⼊到纪录集中
#include "Poco/SharedPtr.h"
#include "Poco/DateTime.h"
#include "Poco/Data/SessionFactory.h"
#include "Poco/Data/Session.h"
#include "Poco/Data/RecordSet.h"
#include "Poco/Data/Column.h"
#include "Poco/Data/SQLite/Connector.h"
#include <iostream>
using namespace Poco::Data::Keywords;
using Poco::DateTime;
using Poco::Data::Session;
using Poco::Data::Statement;
using Poco::Data::RecordSet;
int main(int argc, char** argv)
{
Poco::Data::SQLite::Connector::registerConnector();
// 建⽴会话
Session session("SQLite", "sample.db");
/
/ 如果这个表存在，刚删除
session << "DROP TABLE IF EXISTS Person", now;
// 创建表格
session << "CREATE TABLE Person (Name VARCHAR(30), Address VARCHAR, Age INTEGER(3), Birthday DATE)", now;
// 插⼊两条纪录
DateTime bd(1980, 4, 1);
DateTime ld(1982, 5, 9);
session << "INSERT INTO Person VALUES('Bart Simpson', 'Springfield', 12, ?)", use(bd), now;
session << "INSERT INTO Person VALUES('Lisa Simpson', 'Springfield', 10, ?)", use(ld), now;
// 建⽴⼀个查询执⾏器
Statement select(session);
select << "SELECT * FROM Person";
// 创建⼀个纪录集
RecordSet rs(select);
std::size_t cols = rs.columnCount();
// 输出所有字段名称
for (std::size_t col = 0; col < cols; ++col)
{
std::cout << rs.columnName(col) << std::endl;
}
// 输出所有查询到内容
bool more = rs.moveFirst();
while (more)
{
for (std::size_t col = 0; col < cols; ++col)
{
std::cout << rs[col].convert<std::string>() << " ";
}
std::cout << std::endl;
more = rs.moveNext();
}
return 0;
}
2.3html表格的实现
具体不知道C++中实现这个，有什么⽤。
#include "Poco/SharedPtr.h"
#include "Poco/DateTime.h"
#include "Poco/Data/SessionFactory.h"
#include "Poco/Data/Session.h"
#include "Poco/Data/Statement.h"
#include "Poco/Data/RecordSet.h"
#include "Poco/Data/RowFormatter.h"
#include "Poco/Data/SQLite/Connector.h"
#include <iostream>
using namespace Poco::Data::Keywords;
using Poco::DateTime;
using Poco::Data::Session;
using Poco::Data::Statement;
using Poco::Data::RecordSet;
using Poco::Data::RowFormatter;
class HTMLTableFormatter : public RowFormatter
{
public:
HTMLTableFormatter()
{
std::ostringstream os;
os << "<TABLE border=\"1\" cellspacing=\"0\">" << std::endl;
setPrefix(os.str());
os.str("");
os << "</TABLE>" << std::endl;
setPostfix(os.str());
}
std::string& formatNames(const NameVecPtr pNames, std::string& formattedNames) {
std::ostringstream str;
str << "\t<TR>" << std::endl;
NameVec::const_iterator it = pNames->begin();
NameVec::const_iterator end = pNames->end();
for (; it != end; ++it) str << "\t\t<TH align=\"center\">" << *it << "</TH>" << std::endl; str << "\t</TR>" << std::endl;
return formattedNames = str.str();
}
std::string& formatValues(const ValueVec& vals, std::string& formattedValues)
{
std::ostringstream str;
str << "\t<TR>" << std::endl;
ValueVec::const_iterator it = vals.begin();
ValueVec::const_iterator end = d();
for (; it != end; ++it)
{
if (it->isNumeric())
str << "\t\t<TD align=\"right\">";
else
str << "\t\t<TD align=\"left\">";
str << it->convert<std::string>() << "</TD>" << std::endl;
}
str << "\t</TR>" << std::endl;
str << "\t</TR>" << std::endl;
return formattedValues = str.str();
}
};
int main(int argc, char** argv)
{
// register SQLite connector
Poco::Data::SQLite::Connector::registerConnector();
// create a session
Session session("SQLite", "sample.db");
// drop sample table, if it exists
session << "DROP TABLE IF EXISTS Simpsons", now;
// (re)create table
session << "CREATE TABLE Simpsons (Name VARCHAR(30), Address VARCHAR, Age INTEGER(3), Birthday DATE)", now;
// insert some rows
DateTime hd(1956, 3, 1);
session << "INSERT INTO Simpsons VALUES('Homer Simpson', 'Springfield', 42, ?)", use(hd), now;
hd.assign(1954, 10, 1);
session << "INSERT INTO Simpsons VALUES('Marge Simpson', 'Springfield', 38, ?)", use(hd), now;
hd.assign(1980, 4, 1);
session << "INSERT INTO Simpsons VALUES('Bart Simpson', 'Springfield', 12, ?)", use(hd), now;
hd.assign(1982, 5, 9);
session << "INSERT INTO Simpsons VALUES('Lisa Simpson', 'Springfield', 10, ?)", use(hd), now;
// create a statement and print the column names and data as HTML table
HTMLTableFormatter tf;
Statement stmt = (session << "SELECT * FROM Simpsons", format(tf), now);
RecordSet rs(stmt);
std::cout << rs << std::endl;
// Note: The code above is divided into individual steps for clarity purpose.
// The four lines can be reduced to the following single line:
std::cout << RecordSet(session, "SELECT * FROM Simpsons", HTMLTableFormatter());
// simple formatting example (uses the default SimpleRowFormatter provided by framework)
std::cout << std::endl << "Simple formatting:" << std::endl << std::endl;
exists的用法std::cout << RecordSet(session, "SELECT * FROM Simpsons");
return 0;
}
2.4把结果放⼊到数组中
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论