MySQL Connector/C++接口實例

mysql的項目主頁有對MySQL Connector/C++的文檔解釋和具體實例，但是大家也知道，如果把那些具體實例的代碼只是生硬的套入項目工程中是萬萬不行的，因為項目安全性要求，需要對容錯，資源創建釋放問題嚴格關注，下面貼一個自己的方法函數，里面涵蓋了相關安全處理而且對調用存儲過程也有所涉及

bool CommonService::......(JSONNode& in, JSONNode& out)
{
    /=====校驗json傳入參數=====/
    ..........


/*=====解析json傳入參數，得到安裝碼或者id, 安裝時間， 硬盤序列號， ip，mac=====*/

..........

/*=====通過安裝碼找到數據庫中的對應記錄寫入傳入的值=====*/
/********數據庫操作**********/
//1.得到數據庫連接
Connection* con = G<ConnectionPool>().GetConnection();
if (con == NULL)
{
    LOG4CXX_ERROR(g_logger, "不能得到數據庫連接");
    out.push_back(JSONNode(RESULT, ACTION_FALSE));
    out.push_back(JSONNode(ERROR_MESSAGE, "不能得到數據庫連接"));
    return false;
}

int ret = 1;
PreparedStatement* prep_stmt = NULL;
ResultSet* res = NULL;

try
{
    con->setSchema(G<ConnectionPool>().GetDBName().c_str());

    //執行sql改變安裝狀態
    std::string sql_statement = "update tb_host set reg_date=?, sn=?, ip=?, mac=?, state=?, sync_state=? where reg_code =? and state=?";//要執行的sql語句
    //事務處理
    con->setAutoCommit(0);
    prep_stmt = con->prepareStatement(sql_statement.c_str());

    prep_stmt->setString(1, install_time.c_str());
    prep_stmt->setString(2, harddrive_sn.c_str());
    prep_stmt->setString(3, ip_address.c_str());
    prep_stmt->setString(4, mac_address.c_str());
    prep_stmt->setInt(5, HAS_INSTALL);
    prep_stmt->setInt(6, HAS_SYNC);
    prep_stmt->setString(7, install_code.c_str());
    prep_stmt->setInt(8, NO_INSTALL);

    if(prep_stmt->executeUpdate() == 0)
    {
        ret = 2;
        LOG4CXX_INFO(g_logger, ".....");
        out.push_back(JSONNode(ERROR_MESSAGE, "....."));
        goto Finally_handle;
    }
    //調用賦默認策略存儲過程
    std::string procedure = "CALL updateHostPolicyByModHost(?,?, @ret, @msg)";
    prep_stmt = con->prepareStatement(procedure.c_str());
    prep_stmt->setString(1, install_code.c_str());
    prep_stmt->setInt(2, 0);
    prep_stmt->execute();

    std::string query = "select @ret AS ret,@msg AS msg";
    prep_stmt = con->prepareStatement(query.c_str());
    res = prep_stmt->executeQuery();
    while(res->next())
    {
        if(res->getInt("ret") != 0)
        {
            LOG4CXX_ERROR(g_logger, "....." << res->getString("msg").c_str() << res->getInt("ret"));
            out.push_back(JSONNode(ERROR_MESSAGE, "....."));
            goto Finally_handle;
        }
    }
    con ->commit();

}
catch (SQLException& e)
{
    try
    {
        con->rollback();
    }
    catch (SQLException& e)
    {
        ret = 0;
        LOG4CXX_ERROR(g_logger, "數據庫異常" << e.what());
        goto Finally_handle;
    }

    ret = 0;
    LOG4CXX_ERROR(g_logger, "數據庫異常" << e.what());
    out.push_back(JSONNode(ERROR_MESSAGE, e.what()));
    goto Finally_handle;
}
catch (...)
{
    ret = 0;
    LOG4CXX_ERROR(g_logger, "其他錯誤");
    out.push_back(JSONNode(ERROR_MESSAGE, "其他錯誤"));
    goto Finally_handle;
}

Finally_handle:
DestorySql(res, prep_stmt);
//將連接釋放到連接池
G<ConnectionPool>().ReleaseConnection(con);

if (ret == 1)
{
    out.push_back(JSONNode(RESULT, ACTION_SUCCESS));
    return true;
}
else if (ret == 2)
{
    out.push_back(JSONNode(RESULT, ACTION_FALSE));
    return true;
}
else
{
    out.push_back(JSONNode(RESULT, ACTION_FALSE));
    return false;
}


}
/**/
/ 銷毀數據庫記錄集資源                                                 /
/**/
void CommonService::DestorySql(ResultSet res, PreparedStatement prep_stmt)
{
    if (res != NULL)
    {
        try
        {
            res ->close();
        }
        catch(SQLException& e)
        {
            LOG4CXX_ERROR(g_logger, "數據庫異常" << e.what());
        }
        delete res;
        res = NULL;
    }
    if (prep_stmt != NULL) {
        try
        {
            prep_stmt->close();
        }
        catch(SQLException& e)
        {
            LOG4CXX_ERROR(g_logger, "數據庫異常" << e.what());
        }
        delete prep_stmt;
        prep_stmt = NULL;
    }
}</pre>