QT       += core network
QT       -= gui
TARGET = poco_mysql
CONFIG   += console
CONFIG   -= app_bundle

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
#LIBS += -L/usr/local/lib -lPocoData  -lPocoDataSQLite   -lPocoFoundation  -lPocoCrypto   -lPocoUtil
SOURCES += \

#include "Poco/String.h"
#include "Poco/Format.h"
#include "Poco/Exception.h"
#include "Poco/Data/StatementImpl.h"
#include "Poco/Data/MySQL/Connector.h"
#include "Poco/Data/MySQL/MySQLException.h"
#include "Poco/Data/Session.h"
#include "Poco/Data/SessionPool.h"
#include "Poco/Data/SessionFactory.h"
#include "Poco/Data/LOB.h"
#include "Poco/Data/MySQL/MySQLStatementImpl.h"
#include "Poco/DateTime.h"
#include "Poco/Data/RecordSet.h"
#include "Poco/Data/Column.h"

#include 

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::Data::Statement;
using Poco::DateTime;
using Poco::Data::RecordSet;
//给出访问数据库的信息
std::string _dbConnString  = "host=localhost;port=3306;"
                                                "user=root;password=19810311;"
                                                "db=smart;"
                                                "compress=true;auto-reconnect=true";

int main(int argc, char** argv)
{
        MySQL::Connector::registerConnector();
        //与数据库建立一个连接池
        Poco::Data::SessionPool pool(MySQL::Connector::KEY, _dbConnString,1,32,10);
        //从数据库存连接池中获得一个数据库连接
        Poco::Data::Session ses(pool.get());
        //如果与数据库建立会话成功,输出连接信息
        if(ses.isConnected())
             std::cout << "*** Connected to " << '(' << _dbConnString << ')' << std::endl;
        //如果有为名ddjj的表,先删除,方便下面调试
        ses << "DROP TABLE IF EXISTS ddjj", now;

        //把查询结果存储到容器中
        std::vector names;
        ses << "show databases",into(names), now;
       //输出查询结果,此处列出所有数据库名称
        for (std::vector::const_iterator it = names.begin(); it != names.end(); ++it)
        {
            std::cout << *it << std::endl;
        }

        // 建立一个表,名为ddjj,字段名:name,sex
        ses << "create table ddjj(name VARCHAR(20),sex VARCHAR(20));", now;
       //实现数据纪录的插入
        DateTime bd(1980, 4, 1);
        DateTime 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 names1;
        ses << "select * from ddjj where name like 'Bart Simpson' ",
            into(names1),
            now;
        for (std::vector::const_iterator it = names1.begin(); it != names1.end(); ++it)
        {
           std::cout << "*** tables: " << *it << std::endl;
        }

        Statement select(ses);
        select << "SELECT * FROM ddjj";
        select.execute();

        //创建纪录集
        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::cout << std::endl;
            more = rs.moveNext();
        }

        ses.close();
        MySQL::Connector::unregisterConnector();
        return 0;
}