Oracle OCCI C++ 簡易封裝類實作

設計目標

• 單例管理 Environment 與 Connection。
• RAII 風格自動釋放 Statement、ResultSet。
• 支援交易控制。
• 異常統一處理。

核心代碼

// TOcci.h
#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);
  Connection* getConnection() { ++count_; return conn_; }
  int getConnectCount() const { return count_; }
  ~TOcciDatabase();

private:
  TOcciDatabase(const string& usr, const string& pwd, const string& db);
  static TOcciDatabase* instance_;
  static int count_;
  Environment* env_;
  Connection* conn_;
};

class TOcciQuery {
public:
  explicit TOcciQuery(Connection* conn) : conn_(conn), stmt_(nullptr), autoCommit_(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);

private:
  Connection* conn_;
  Statement* stmt_;
  bool autoCommit_;
};

} // namespace happyever
#endif

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

namespace happyever {

TOcciDatabase* TOcciDatabase::instance_ = nullptr;
int TOcciDatabase::count_ = 0;

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 << "Connection failed: " << ex.getMessage() << endl;
    throw;
  }
}

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

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

void TOcciQuery::beginTransaction() {
  if (!stmt_) stmt_ = conn_->createStatement();
  autoCommit_ = stmt_->getAutoCommit();
  stmt_->setAutoCommit(false);
}

void TOcciQuery::commit() {
  if (stmt_) {
    conn_->commit();
    stmt_->setAutoCommit(autoCommit_);
  }
}

void TOcciQuery::rollback() {
  if (stmt_) {
    conn_->rollback();
    stmt_->setAutoCommit(autoCommit_);
  }
}

bool TOcciQuery::getAutoCommit() const {
  return stmt_ ? stmt_->getAutoCommit() : false;
}

ResultSet* TOcciQuery::executeQuery(const string& sql) {
  if (!stmt_) stmt_ = conn_->createStatement();
  return stmt_->executeQuery(sql);
}

void TOcciQuery::executeUpdate(const string& sql) {
  if (!stmt_) stmt_ = conn_->createStatement();
  stmt_->executeUpdate(sql);
}

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

} // namespace happyever

使用範例

#include "TOcci.h"

int main() {
  using namespace happyever;
  
  TOcciQuery query(TOcciDatabase::getInstance("scott", "tiger", "orcl")->getConnection());
  
  string sql = "SELECT COUNT(*) FROM SERV_VALUE_TOTAL";
  ResultSet* rs = query.executeQuery(sql);
  
  while (rs->next()) {
    cout << "Count = " << rs->getInt(1) << endl;
  }
  
  query.close(rs);
  return 0;
}

編譯與連結

• 包含目錄：$ORACLE_HOME/rdbms/public
• 連結庫：oraocci19.lib (Windows) 或 libocci.so (Linux)
• VS2005+ 需下載對應版本 OCCI 庫。

改進方向

• 連線池支援（ConnectionPool）。
• PreparedStatement 參數綁定封裝。
• 執行緒安全的單例（Double-Checked Locking）。
• 支援 BLOB/CLOB 流式讀寫。