在一个项目或者一个产品涉及到多种数据库的情况下,对各种数据库的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();
}