萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle OCCI C++ 連線池與查詢封裝類實現:單例模式、事務控制、資源管理

設計目標

  • 單例模式管理 Environment 與 Connection 生命週期
  • 提供 TOcciQuery 封裝 Statement/ResultSet,支援事務、參數化查詢、自動資源釋放
  • 例外安全:建構/解構/執行全路徑 try-catch 包裹,轉拋 SQLException
  • 跨平台:Visual Studio 2005 / AIX xlC 驗證通過

核心類別架構

TOcciDatabase:連線池單例


#ifndef _OCCIDATABASE_H_
#define _OCCIDATABASE_H_

#include 
#include 
#include 

using namespace oracle::occi;
using namespace std;

namespace happyever {

class TOcciDatabase {
public:
    // 雙重檢查鎖定單例
    static TOcciDatabase* getInstance(const string& usr, const string& pwd, const string& db);
    static void destroyInstance();  // 程式結束時釋放
    
    int getConnectCount() const { return count_; }
    Connection* getConnection() { ++count_; return conn_; }
    
    ~TOcciDatabase();

protected:
    TOcciDatabase() = default;
    TOcciDatabase(const string& usr, const string& pwd, const string& db);

private:
    static TOcciDatabase* instance_;
    static int count_;
    static mutex mtx_;
    
    Environment* env_;
    Connection* conn_;
    
    // 禁止複製
    TOcciDatabase(const TOcciDatabase&) = delete;
    TOcciDatabase& operator=(const TOcciDatabase&) = delete;
};

class TOcciQuery {
private:
    Connection* conn_;
    Statement* stmt_;
    bool auto_commit_saved_;
    TOcciQuery() = default;
    
public:
    explicit TOcciQuery(Connection* conn) : conn_(conn), stmt_(nullptr), auto_commit_saved_(false) {}
    ~TOcciQuery() { close(); }
    
    // 事務控制
    void beginTransaction();
    void commit();
    void rollback();
    bool getAutoCommit() const;
    
    // 查詢與更新
    ResultSet* executeQuery(const string& sql);
    void executeUpdate(const string& sql);
    
    // 資源釋放
    void close(ResultSet* rs = nullptr);
    void close();
    
    // 禁止複製
    TOcciQuery(const TOcciQuery&) = delete;
    TOcciQuery& operator=(const TOcciQuery&) = delete;
};

} // namespace happyever
#endif

實作細節


// TOcciDatabase.cpp
namespace happyever {
    TOcciDatabase* TOcciDatabase::instance_ = nullptr;
    int TOcciDatabase::count_ = 0;
    mutex TOcciDatabase::mtx_;

    TOcciDatabase::TOcciDatabase(const string& usr, const string& pwd, const string& db) {
        try {
            env_ = Environment::createEnvironment(Environment::DEFAULT);
            conn_ = env_->createConnection(usr, pwd, db);
        } catch (SQLException& ex) {
            cerr << "OCCI Connect Error: " << ex.getErrorCode() << " - " << ex.getMessage() << endl;
            throw;
        }
    }

    TOcciDatabase::~TOcciDatabase() {
        try {
            if (conn_) env_->terminateConnection(conn_);
            if (env_) Environment::terminateEnvironment(env_);
        } catch (SQLException& ex) {
            cerr << "OCCI Cleanup Error: " << ex.getErrorCode() << " - " << ex.getMessage() << endl;
        }
    }

    TOcciDatabase* TOcciDatabase::getInstance(const string& usr, const string& pwd, const string& db) {
        if (!instance_) {
            lock_guard lock(mtx_);
            if (!instance_) instance_ = new TOcciDatabase(usr, pwd, db);
        }
        return instance_;
    }

    void TOcciDatabase::destroyInstance() {
        lock_guard lock(mtx_);
        delete instance_;
        instance_ = nullptr;
    }
}

// TOcciQuery.cpp
namespace happyever {
    void TOcciQuery::beginTransaction() {
        try {
            stmt_ = conn_->createStatement();
            auto_commit_saved_ = stmt_->getAutoCommit();
            stmt_->setAutoCommit(false);
        } catch (SQLException& ex) { /* log & throw */ }
    }

    void TOcciQuery::commit() {
        try {
            conn_->commit();
            if (stmt_) stmt_->setAutoCommit(auto_commit_saved_);
        } catch (SQLException& ex) { /* log & throw */ }
    }

    void TOcciQuery::rollback() {
        try {
            conn_->rollback();
            if (stmt_) stmt_->setAutoCommit(auto_commit_saved_);
        } catch (SQLException& ex) { /* log & throw */ }
    }

    ResultSet* TOcciQuery::executeQuery(const string& sql) {
        try {
            stmt_ = conn_->createStatement();
            return stmt_->executeQuery(sql);
        } catch (SQLException& ex) { /* log & throw */ return nullptr; }
    }

    void TOcciQuery::executeUpdate(const string& sql) {
        try {
            stmt_ = conn_->createStatement();
            stmt_->executeUpdate(sql);
        } catch (SQLException& ex) { /* log & throw */ }
    }

    void TOcciQuery::close(ResultSet* rs) {
        if (rs) { try { stmt_->closeResultSet(rs); } catch(...) {} }
        if (stmt_) { try { conn_->terminateStatement(stmt_); stmt_ = nullptr; } catch(...) {} }
    }

    void TOcciQuery::close() { close(nullptr); }
}

使用範例


// main.cpp
#include "TOcci.h"
#include 

int main() {
    using namespace happyever;
    
    try {
        // 取得連線(單例)
        TOcciQuery query(TOcciDatabase::getInstance("scott", "tiger", "orcl")->getConnection());
        
        // 單筆查詢
        ResultSet* rs = query.executeQuery("SELECT COUNT(*) FROM serv_value_total");
        if (rs->next()) {
            cout << "Count = " << rs->getInt(1) << endl;
        }
        query.close(rs);
        
        // 事務範例
        query.beginTransaction();
        query.executeUpdate("INSERT INTO log_table (msg) VALUES ('TXN START')");
        query.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
        query.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
        query.commit();
        
    } catch (SQLException& ex) {
        cerr << "Fatal: " << ex.getMessage() << endl;
        return 1;
    }
    
    TOcciDatabase::destroyInstance();
    return 0;
}

編譯鏈結說明

  • Windows (VS2005+):下載 Instant Client SDK (Windows x64),包含 oraocci12.lib、oraocci12.dll、headers,專案屬性加入 Include/Lib 路徑
  • Linux/AIX:$ORACLE_HOME/rdbms/public headers,$ORACLE_HOME/lib/libocci.so
  • CMake 範例:

find_package(OCCI REQUIRED)
target_link_libraries(MyApp ${OCCI_LIBRARIES})

遷移建議:現代 C++ 17/20

  • 使用 std::unique_ptr 管理 Statement/ResultSet (自定義 Deleter)
  • 連線池改用 ConnectionPool (OCCI 內建) 而非單例單連線
  • 參數化查詢:stmt->setString(1, val) 避免 SQL 注入
  • 非同步:搭配 std::future / co_await (C++20)
copyright © 萬盛學電腦網 all rights reserved