POCO C++ 應用：MySQL 連接池實戰

需求

使用 POCO 建立 MySQL 連接池，執行增刪改查，ANSI 風格代碼，Qt Creator 專案。

專案配置

QT += core network
QT -= gui
TARGET = poco_mysql
CONFIG += console
DEFINES += CHARTDIR_HIDE_OBSOLETE _CRT_SECURE_NO_WARNINGS
INCLUDEPATH += /usr/local/include/Poco -I /usr/include/mysql
LIBS += -L/usr/local/lib -lPocoData -lPocoDataMySQL -lPocoDataSQLite -lPocoCrypto -lPocoUtil -lPocoFoundation -L /usr/lib64/mysql
SOURCES += mysql.cpp

核心代碼

#include <Poco/Data/MySQL/Connector.h>
#include <Poco/Data/SessionPool.h>
#include <Poco/Data/Session.h>
#include <Poco/Data/Statement.h>
#include <Poco/Data/RecordSet.h>
#include <Poco/DateTime.h>
#include <iostream>
#include <vector>
#include <string>

using namespace Poco::Data::Keywords;
using namespace Poco::Data;
using Poco::Data::Session;
using Poco::Data::MySQL::ConnectionException;
using Poco::Data::MySQL::StatementException;
using Poco::NotFoundException;
using Poco::DateTime;
using Poco::Data::RecordSet;

int main() {
    MySQL::Connector::registerConnector();
    std::string connStr = "host=localhost;port=3306;user=root;password=19810311;db=smart;compress=true;auto-reconnect=true";
    SessionPool pool(MySQL::Connector::KEY, connStr, 1, 32, 10); // min=1, max=32, idle=10s
    Session ses(pool.get());
    if (ses.isConnected()) std::cout << "*** Connected to (" << connStr << ")" << std::endl;

    ses << "DROP TABLE IF EXISTS ddjj", now;
    ses << "CREATE TABLE ddjj(name VARCHAR(20), sex VARCHAR(20))", now;

    DateTime bd(1980,4,1), ld(1982,5,9);
    ses << "INSERT INTO ddjj VALUES('Bart Simpson', ?)", use(bd), now;
    ses << "INSERT INTO ddjj VALUES('Lisa Simpson', ?)", use(ld), now;

    std::vector<std::string> names;
    ses << "SELECT * FROM ddjj WHERE name LIKE 'Bart Simpson'", into(names), now;
    for (auto &n : names) std::cout << "*** tables: " << n << std::endl;

    Statement select(ses);
    select << "SELECT * FROM ddjj";
    select.execute();
    RecordSet rs(select);
    for (std::size_t col = 0; col < rs.columnCount(); ++col) std::cout << rs.columnName(col) << std::endl;
    bool more = rs.moveFirst();
    while (more) {
        for (std::size_t col = 0; col < rs.columnCount(); ++col)
            std::cout << rs[col].convert<std::string>() << " ";
        std::cout << std::endl;
        more = rs.moveNext();
    }
    ses.close();
    MySQL::Connector::unregisterConnector();
    return 0;
}

輸出結果

*** Connected to (host=localhost;port=3306;user=root;password=19810311;db=smart;compress=true;auto-reconnect=true)
information_schema
mysql
performance_schema
smart
*** tables: Bart Simpson
name
sex
Bart Simpson 1980-04-01 00:00:00
Lisa Simpson 1982-05-09 00:00:00

關鍵點

• SessionPool 管理連接生命週期，Session 從池獲取連接。
• use() 綁定參數，防止 SQL 注入。
• into() 直接映射查詢結果到容器。
• RecordSet 提供游標式逐行讀取。