Poco访问数据库（测试例子MySQl）

/*步骤： 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，支持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; } 时间：2016 8 25 版本：1.7.4（ALL） 作者：黄林 参考链接：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; } #if 0 //此类用于像string一样使用类 namespace Poco { namespace Data { template <> class TypeHandler<class Person> { public: static std::size_t size() { return 3; // we handle three columns of the Table! } static void bind(std::size_t pos, const Person& obj, AbstractBinder* pBinder) { poco_assert_dbg(pBinder != 0); // the table is defined as Person (FirstName VARCHAR(30), lastName VARCHAR, SocialSecNr INTEGER(3)) // Note that we advance pos by the number of columns the datatype uses! For string/int this is one. TypeHandler<std::string>::bind(pos++, obj.getFirstName(), pBinder); TypeHandler<std::string>::bind(pos++, obj.getLastName(), pBinder); TypeHandler<Poco::UInt64>::bind(pos++, obj.getSocialSecNr(), pBinder); } static void prepare(std::size_t pos, const Person& obj, AbstractPreparation* pPrepare) { poco_assert_dbg(pBinder != 0); // the table is defined as Person (FirstName VARCHAR(30), lastName VARCHAR, SocialSecNr INTEGER(3)) // Note that we advance pos by the number of columns the datatype uses! For string/int this is one. TypeHandler<std::string>::prepare(pos++, obj.getFirstName(), pPrepare); TypeHandler<std::string>::prepare(pos++, obj.getLastName(), pPrepare); TypeHandler<Poco::UInt64>::prepare(pos++, obj.getSocialSecNr(), pPrepare); } static void extract(std::size_t pos, Person& obj, const Person& defVal, AbstractExtractor* pExt) /// obj will contain the result, defVal contains values we should use when one column is NULL { poco_assert_dbg(pExt != 0); std::string firstName;