[poco] 访问数据库

桃扇骨 2022-03-29 09:56 373阅读 0赞

From: [https://blog.csdn.net/hl2015222050145/article/details/52335422?utm\_source=blogxgwz6][https_blog.csdn.net_hl2015222050145_article_details_52335422_utm_source_blogxgwz6]

poco访问数据基本步骤：  
a. 创建会话(session)

b. 从DB中读写数据  
c. 使用statements

d. 使用容器（Collection） (数据，集合...)  
e. 使用limit限定  
f. 如何使用复杂的数据类型(如何将一个C++对象映射到数据库的表

创建会话：Session create(const std::string& connectorKey, const std::string& connectionString);  
参数1：SQLite；MySQL；或者ODBC，ODBC支持Oracle, SQLite, DB2, SQLServer和PostgreSQL。  
参数2：就是数据库文件的路径或者连接字符

基本操作（增删查改）：

增：

std::string aName("Peter");  
ses << "INSERT INTO 表名 VALUES(" << aName << ")", now;  
或者ses << "INSERT INTO 表名 VALUES(?)", use(aName), now;

查：

一般查找

std::string aName;  
ses << "SELECT NAME FROM FORENAME", into(aName), now; // aName的默认值为空字符串  
ses << "SELECT NAME FROM FORENAME", into(aName, "default"), now;

匹配查找 先into后use 且不能使用常量 如:4

std::string aName;  
std::string match("Peter")  
ses << "SELECT NAME FROM FORENAME WHERE NAME=?", into(aName), use(match), now;  
poco\_assert (aName == match);

多字节匹配查找

std::string firstName("Peter";  
std::string lastName("Junior");  
int age = 0;  
ses << "INSERT INTO PERSON VALUES (?, ?, ?)", use(firstName), use(lastName), use(age), now;  
ses << "SELECT (firstname, lastname, age) FROM Person", into(firstName), into(lastName), into(age), now;

into语句允许定义一个默认值,主要针对数据库查询里面包含空时候

ses << "SELECT (firstname, lastname, age) FROM Person", into(firstName), into(lastName), into(age, -1), now;  
  
改：  
session << "update Person set Address = ? WHERE Name= ?", use(addr),use(name),now;

使用Statements：

用于保存SQl语句，使得多次查询。

std::string aName("Peter");  
Statement stmt = ( ses << "INSERT INTO FORENAME VALUES(?)", use(aName) );//括号必须

或者  
Statement stmt(ses);  
stmt << "INSERT INTO FORENAME VALUES(?)", use(aName);  
eg：

std::string aName("Peter");  
Statement stmt = ( ses << "INSERT INTO FORENAME VALUES(?)", use(aName) );  
.  
.  
stmt.execute();  
poco\_assert (stmt.done());

容器使用：

默认支持以下容器类：  
a. vector(数组): 无特别要求  
b. set: 类型需要支持<操作符，注意：重复的key/value将忽略  
c. multiset: 需支持<操作符  
d. map: 需支持()操作符，并且按key返回对象，注意：重复的key/value将忽略  
e. multimap: 需支持()操作符，并按key返加对象.

std::vector<std::string> names;  
ses << "SELECT NAME FROM FORENAME", into(names), now;

Limit限定：针对查询结果的个数限定

std::vector<std::string> names;  
ses << "SELECT NAME FROM FORENAME", into(names), limit(50), now;  
以上代码将只返回50行的数据。（当然也可能什么都不返回）,并追加到 names这个容量中。如果想确保50行记录返回，需要设置limit的第二参数为true(默认为false):  
std::vector<std::string> names;  
ses << "SELECT NAME FROM FORENAME", into(names), limit(50, true), now;  
Statement stmt = (ses << "SELECT NAME FROM FORENAME", into(aName), lowerLimit(1), upperLimit(10));//限制范围  
Statement stmt = (ses << "SELECT NAME FROM FORENAME", into(aName), range(1,10));

类作为保存变量：这个就是将自定义类作为容器查询

namespace Poco  
\{  
namespace Data  
\{  
template <>  
class TypeHandler<class Person>  
\{  
.....  
\}  
\}  
\}

记录集(RecordSet)

a. 遍历数据表中所有的行与列  
b. 获取各列的元信息，比如名称，类型，长度等.

Statement select(session);  
select << "SELECT \* FROM Person";  
select.execute();  
RecordSet rs(select);

遍历结果：

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();  
\}

Tuples：在数据库中的列类型已知的情阅下，Poco::Tuple以及Tuple数组提供了更简便的方法获取数据。

typedef Poco::Tuple<std::string, std::string, int> Person;  
typedef std::vector<Person> People;  
  
People people;  
people.push\_back(Person("Bart Simpson", "Springfield", 12));  
people.push\_back(Person("Lisa Simpson", "Springfield", 10));  
  
Statement insert(session);  
insert << "INSERT INTO Person VALUES(?, ?, ?)",  
use(people), now;  
  
当然，tuple也可以用于查询:  
Statement select(session);  
select << "SELECT Name, Address, Age FROM Person", into(people), now;  
  
for (People::const\_iterator it = people.begin(); it != people.end(); ++it)  
\{  
std::cout << "Name: " << it->get<0>() <<  
", Address: " << it->get<1>() <<  
", Age: " << it->get<2>() <<std::endl;  
\}

参考链接：http://pocoproject.org/docs/00200-DataUserManual.html

测试代码：

\#include "Poco/Data/Session.h"  
\#include "Poco/Data/MySQL/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::MySQL::Connector::registerConnector();  
  
// 创建 session  
Session session("MySQL", "host=127.0.0.1;port=3306;db=demo;user=admin;password=admin;compress=true;auto-reconnect=true");  
  
// 删除已存在的表  
session << "DROP TABLE IF EXISTS Person", now;  
  
// 创建新表  
session << "CREATE TABLE Person (Name char(20),Address char(30) ,Age integer)", now;//varchar 会报错  
// 插入数据  
Person person =  
\{  
"Bart Simpson",  
"Springfield",  
12  
\};  
  
Statement insert(session);  
insert << "INSERT INTO Person VALUES(?, ?, ?)",  
use(person.name),  
use(person.address),  
use(person.age);  
  
insert.execute();  
  
person.name = "Lisa Simpson";  
person.address = "Springfield";  
person.age = 10;  
  
insert.execute();  
  
//查询数据  
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())  
\{  
select.execute();  
std::cout << person.name << " " << person.address << " " << person.age << std::endl;  
\}  
  
std::string addr = "hubeilichuan";  
std::string name = "Lisa Simpson";  
  
session << "update Person set Address = ? WHERE Name= ?", use(addr),use(name),now;  
// 另一种查询方式  
std::vector<std::string> names;  
session << "SELECT Address FROM Person",  
into(names),  
now;  
  
  
for (std::vector<std::string>::const\_iterator it = names.begin(); it != names.end(); ++it)  
\{  
std::cout << \*it << std::endl;  
\}  
getchar();  
return 0;  
\}

测试工程链接：http://download.csdn.net/detail/hl2015222050145/9614574

编译好的动态库链接：http://download.csdn.net/detail/hl2015222050145/9614205

[https_blog.csdn.net_hl2015222050145_article_details_52335422_utm_source_blogxgwz6]: https://blog.csdn.net/hl2015222050145/article/details/52335422?utm_source=blogxgwz6