設計目標
- 單例模式管理
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)