c-tree数据库(c-treeACE)(8)：开发篇之二：简单封装c-tree SQL API

在一个项目或者一个产品涉及到多种数据库的情况下，对各种数据库的API进行抽象封装出一个统一的接口，是一个比较好的方式。

这里简单的给出一个封装了c-tree SQL API功能的C++库。

主要包括4个类：Environment, Connection, Statement, ResultSet.  以下贴出代码：

 

/*
 * Environment.hpp
 */

#ifndef ENVIRONMENT_HPP_
#define ENVIRONMENT_HPP_
#include <vector>
#include "Connection.hpp"

using namespace std;

class Environment {
public:
    static bool staticConstructor();
    static Environment* createEnvironment(size_t maxConn=32);

    static void terminateEnvironment();
    Environment(size_t maxConn=32);
    ~Environment();
    Connection* createConnection(const string user, const string password, const string server, const string dbName);
    void terminateConnection(Connection *);
    void getConnection(size_t connHandle);
private:
    static Environment* env;
    static size_t maxConn;
    static bool staticInit;
    size_t envType;
    size_t connectionNumber;
};

#endif /* ENVIRONMENT_HPP_ */

 

 

/*
 * Environment.cpp
 */

#include "Environment.hpp"

size_t Environment::maxConn=0;
Environment* Environment::env = NULL;
bool Environment::staticInit = false;

bool Environment::staticConstructor(){
    if(staticInit)
        return true;
    Environment::env = new Environment();
    staticInit= true;
    return true;
}

Environment::Environment(size_t maxconn): envType(0), connectionNumber(0){
    Environment::maxConn = maxconn;
}

Environment::~Environment() {

}

Environment* Environment::createEnvironment(size_t maxconn){
    staticConstructor();
    Environment::env->maxConn = maxconn;
    return Environment::env;
}

void Environment::terminateEnvironment(){
    delete Environment::env;
}

Connection *Environment::createConnection(const std::string user, const std::string password, const std::string server, const std::string dbName){
    if(connectionNumber >= Environment::env->maxConn)
        return NULL;
    Connection *conn = new Connection(user, password, server, dbName);
    conn->connect();
    if(!conn->isConnected()){
        delete conn;
        throw bad_exception();
    }
    connectionNumber++;
    return conn;
}

void Environment::terminateConnection(Connection *conn){
    if(conn == NULL)
        return;
    conn->terminate();
    delete conn;
    conn=NULL;
    connectionNumber--;
}

 

/*
 * Connection.hpp
 */

#ifndef CONNECTION_H_
#define CONNECTION_H_
#include <string>
#include <vector>
#include "Statement.hpp"

using namespace std;
class Connection {
public:
    Connection(const string user, const string password, const string server, const string dbName);
    ~Connection();
    bool connect();
    Statement* createStatment();
    bool isConnected();
    void terminate();
    void closeStatment(Statement *stmt);
private:
    string user;
    string password;
    string server;
    string dbName;
    bool connected;

    pCTSQLCONN hConn;
    size_t statementNumber;
    size_t currStatementIndex;
    vector<Statement *> statements;

};

#endif /* CONNECTION_H_ */

/*
 * Connection.cpp
 */
#include "Connection.hpp"

Connection::Connection(const string user, const string password, const string server, const string dbName) {
    this->user = user;
    this->password = password;
    this->server = server;
    this->dbName = dbName;
    this->statementNumber = 0 ;
    this->currStatementIndex =0;
    this->statements.clear();
    hConn = NULL;
}

Connection::~Connection() {

}

bool Connection::connect()
{
    hConn = ctsqlNewConnection();
    if(hConn == NULL)
        return false;

    CTSQLRET rc=ctsqlConnect(hConn, (CTSQLCHAR*)dbName.c_str(), (CTSQLCHAR*)user.c_str(), (CTSQLCHAR*)password.c_str());
    if(rc != CTSQLRET_OK)
    {
        rc = ctsqlGetError (hConn);
        printf("SQL ERROR: [%d] - %s/n", rc, ctsqlGetErrorMessage(hConn));
        return false;
    }
    this->connected = true;

    if ((rc = ctsqlSetAutoCommit(hConn, CTSQL_FALSE)) != CTSQLRET_OK)
    {
        printf("SQL ERROR: [%d] - %s/n", rc, ctsqlGetErrorMessage(hConn));
        return false;
    }
    return true;
}

bool Connection::isConnected(){
    return this->connected;
}

void Connection::terminate(){
    if( hConn != NULL){
        if(connected){
            ctsqlDisconnect(hConn);
            connected = false;
        }
        ctsqlFreeConnection(hConn);
        hConn=NULL;
    }
}

Statement* Connection::createStatment(){
    Statement *stmt = new Statement(this->hConn);
    this->statements.push_back(stmt);
    this->statementNumber++;
    return stmt;
}

void Connection::closeStatment(Statement *stmt){
    stmt->close();
}

 

/*
 * ResultSet.hpp
 */

#ifndef RESULTSET_HPP_
#define RESULTSET_HPP_
#include <iostream>
#include <string>
#include <stdio.h>
#include <stdint.h>
#include "ctsqlapi.h"

using namespace std;
class ResultSet {
public:
    ResultSet(pCTSQLCURSOR hCursor);
    ResultSet();
    ~ResultSet();
    bool next();
    void close();
    uint64_t getRecordCount();
    string getFieldAsString(size_t idx);
    string getFieldAsDatetime(size_t idx);
    int64_t getFieldAsInt64(size_t idx);
private:
    pCTSQLCURSOR hCursor;

};

#endif /* RESULTSET_HPP_ */

/*
 * ResultSet.cpp
 */

#include "ResultSet.hpp"

ResultSet::ResultSet(pCTSQLCURSOR hCursor) {
    this->hCursor = hCursor;
}

ResultSet::ResultSet() {
    this->hCursor = NULL;
}

ResultSet::~ResultSet() {

}

void ResultSet::close(){
    ctsqlFreeCursor(hCursor);
    hCursor = NULL;
}

bool ResultSet::next(){
    if( ctsqlNext(hCursor) == CTSQLRET_OK)
        return true;
    else
        return false;
}

string ResultSet::getFieldAsString(size_t idx){
    size_t nLen = ctsqlGetColumnLength(hCursor, idx);
    char *p = new char[nLen+1];
    ctsqlGetChar(hCursor, idx, p);
    string str(p);
    delete[] p;
    return str;
}

string ResultSet::getFieldAsDatetime(size_t idx){
    SQLTIMESTAMP ts;
    ctsqlGetTimeStamp(hCursor, idx, &ts);
    char sTS[20];
    sprintf(sTS,"%4d-%02d-%02d %02d:%02d:%2d",
      ts.ts_date.year, ts.ts_date.month, ts.ts_date.day, ts.ts_time.hours, ts.ts_time.mins, ts.ts_time.secs);
    string str(sTS);
    return str;
}

int64_t ResultSet::getFieldAsInt64(size_t idx){
 BIGINT bi;
    ctsqlGetBigInt(hCursor, idx, &bi);
    int64_t lvalue = bi.hl;
    lvalue = lvalue<<32|bi.ll;
    return lvalue;
}

uint64_t ResultSet::getRecordCount(){
    uint64_t nRecords=0;

 ....
    return nRecords;
}

/*
 * Statement.hpp
 */

#ifndef STATEMENT_HPP_
#define STATEMENT_HPP_
#include <string>
#include "ResultSet.hpp"

using namespace std;
class Statement {
public:
    Statement(pCTSQLCONN hConn);
    ~Statement();
    void prepare(const string s);
    bool execute();
    bool execute(const string s);
    void executeBatch();
    void executeBatch(const string s);
    ResultSet *executeQuery();
    ResultSet *executeQuery(const string s);
    void setSQL(const string s);
    string getSQL();
    ResultSet* getResultSet();
    void closeResultSet(ResultSet *rs);
    void close();
    uint32_t rowsAffected();
private:
    string sSQL;
    pCTSQLCMD hCmd;
    pCTSQLCURSOR hCursor;
    pCTSQLCONN hConn;

};

#endif /* STATEMENT_HPP_ */

/*
 * Statement.cpp
 */

#include "Statement.hpp"

Statement::Statement(pCTSQLCONN hConn) {
    this->hConn = hConn;
    hCmd = ctsqlNewCommand(hConn);

}

Statement::~Statement() {

}

void Statement::setSQL(const string s){
    sSQL = s;
}

string Statement::getSQL(){
     return sSQL;
}

bool Statement::execute(const string sSQL){
    CTSQLRET rc;
    rc = ctsqlExecuteDirect(hCmd, (CTSQLCHAR*)sSQL.c_str());
    if (rc != CTSQLRET_OK){
        printf("SQL ERROR: [%d] - %s/n", rc, ctsqlGetErrorMessage(hConn));
        return false;
    }
    return true;
}

bool Statement::execute(){
 return execute(sSQL);
}

ResultSet *Statement::executeQuery(){
    CTSQLRET       rc;
    rc = ctsqlPrepare(hCmd, (CTSQLCHAR*)sSQL.c_str());
    if (rc != CTSQLRET_OK){
        printf("SQL ERROR: [%d] - %s/n", rc, ctsqlGetErrorMessage(hConn));
        return NULL;
    }
    rc = ctsqlExecute(hCmd, &hCursor);
    if (rc != CTSQLRET_OK){
        printf("SQL ERROR: [%d] - %s/n", rc, ctsqlGetErrorMessage(hConn));
        return NULL;
    }
    ResultSet *rs = new ResultSet(hCursor);

    return rs;
}

void Statement::close(){
    if(hCmd)
        ctsqlFreeCommand(hCmd);
    hCmd = NULL;
}

void Statement::closeResultSet(ResultSet *rs){
    rs->close();
}

uint32_t Statement::rowsAffected(){
    return ctsqlAffectedRows(hCmd);
}

再提供一个统一的头文件, 应用该库的时候，只要包含该头文件。

/*
 * CtreeDBI.hpp
 */

#ifndef CTREEDBI_HPP_
#define CTREEDBI_HPP_

#include "Environment.hpp"
#include "Connection.hpp"
#include "Statement.hpp"
#include "ResultSet.hpp"

#endif /* CTREEDBI_HPP_ */

 

 

测试程序：

/*
 * main.cpp
 */
#include "CtreeDBI.hpp"
#include <sys/time.h>
#define RECORDS 100
#define REFRESH (RECORDS/10)

int main()
{
    struct timeval tb,te;
    struct timezone tz;

 Environment *env = Environment::createEnvironment(2);

    Connection *conn=NULL;
    conn = env->createConnection("admin","ADMIN","FAIRCOMS","ctreesql");

    Statement *stmt = conn->createStatment();

    stmt->setSQL("create table t1 (" /
      "attributea varchar(10)," /
      "attributeb varchar(20)," /
      "attributec timestamp," /
      "attributed date," /
      "attributee bigint)" );

    if(!stmt->execute())
     cout<<"Execute create error"<<endl;

    gettimeofday(&tb, &tz);
    for(int i=0;i<RECORDS;i++){
  stmt->setSQL("insert into t1 values(" /
    "'melbourne', 'FAIRCOMS', '2010-12-15 23:58:59', '2010-12-24', 9223372036854775807)");
  if(!stmt->execute())
   cout<<"Execute insert error"<<endl;
  if(i%REFRESH ==0)
   cout<<"Inserted "<<i+1<<" records."<<endl;
    }
    gettimeofday(&te, &tz);
    cout<<"Insert Time:"<< (te.tv_sec-tb.tv_sec)*1000.0+(te.tv_usec-tb.tv_usec)/1000.0 <<" ms."<<endl;

    gettimeofday(&tb, &tz);
    if(!stmt->execute("update t1 set attributeb='FAIRCOMS'"))
        cout<<"Execute update error"<<endl;
    cout<<"rowsAffected="<<stmt->rowsAffected()<<endl;
    gettimeofday(&te, &tz);
    cout<<"Update Time:"<< (te.tv_sec-tb.tv_sec)*1000.0+(te.tv_usec-tb.tv_usec)/1000.0 <<" ms."<<endl;

    gettimeofday(&tb, &tz);
    stmt->setSQL("select * from t1");
    ResultSet *rs = stmt->executeQuery();
    int n=0;
    while(rs->next()){
        if(n % REFRESH == 0) {
            cout<<rs->getFieldAsString(0)<<endl;
            cout<<rs->getFieldAsString(1)<<endl;
            cout<<rs->getFieldAsDatetime(2)<<endl;
            cout<<rs->getFieldAsInt64(3)<<endl;
        }
        n++;
    }
    gettimeofday(&te, &tz);
    cout<<"Select Time:"<< (te.tv_sec-tb.tv_sec)*1000.0+(te.tv_usec-tb.tv_usec)/1000.0 <<" ms."<<endl;

    stmt->closeResultSet(rs);
    conn->closeStatment(stmt);
    env->terminateConnection(conn);
    Environment::terminateEnvironment();
}