仓库代码地址：https://gitee.com/liudegui/DatabaseLayer
概述
- 需要用到MySQL数据库的业务时，我们有时候想快速搭建一个应用，此时可用临时SQLite代替MySQL做开发；等待业务稳定以后再切换成MySQL数据库，这也可以防止多人公用一份MySQL数据库时产生冲突。
- DatabaseLayer是一个支持Sqlite3和MySQL的数据库封装，支持跨平台（Window和Linux）。参考了CppSQLite和POCO中的数据库实现。
特点：
1、提供统一的Sqlite3和MySQL的操作接口
2、只对Sqlite3和MySQL的API进行简单封装，代码简单易懂
3、CppSqlite3部分没有用到stl，支持嵌入式平台使用；CppMySQL部分用到少量STL，支持跨平台。
4、不支持Unicode
5、具体事宜方法请参考demos中的例子
对外头文件DatabaseLayer.h
DatabaseLayer.h中通过C++宏启用MySQL或者SQLite，当然你也可以直接使用CppSQLite3.h或CppMySQL.h，这两个数据库的api尽量做到了统一。
#pragma once
#ifndef _CppDBLayer_H_
#define _CppDBLayer_H_
#define POCO_VERSION 0x01010001
#ifdef _SQLITE3_DB
class CppSQLite3Exception;
class CppSQLite3DB;
class CppSQLite3Query;
class CppSQLite3ResultSet;
class CppSQLite3Statement;
typedef CppSQLite3Exception CppDBException;
typedef CppSQLite3DB CppDB;
typedef CppSQLite3Query CppDBQuery;
typedef CppSQLite3ResultSet CppDBResultSet;
typedef CppSQLite3Statement CppDBStatement;
#endif
#ifdef _MySQL_DB
class CppMySQLException;
class CppMySQLDB;
class CppMySQLQuery;
class CppMySQLResultSet;
class CppMySQLStatement;
typedef CppMySQLException CppDBException;
typedef CppMySQLDB CppDB;
typedef CppMySQLQuery CppDBQuery;
typedef CppMySQLResultSet CppDBResultSet;
typedef CppMySQLStatement CppDBStatement;
#endif
class DatabaseException
{
public:
virtual const int errorCode() = 0;
virtual const char* errorMessage() = 0;
};
class DatabaseLayer
{
public:
virtual void open(const char* szFile) = 0;
virtual void close() = 0;
virtual bool tableExists(const char* szTable) = 0;
virtual int execDML(const char* szSQL) = 0;
virtual CppDBQuery execQuery(const char* szSQL) = 0;
virtual int execScalar(const char* szSQL, int nNullValue=0) = 0;
virtual void startTransaction() = 0;
virtual void commitTransaction() = 0;
virtual void rollback() = 0;
virtual bool isTransaction() = 0;
};
class DatabaseQuery
{
public:
virtual int numFields() = 0;
virtual int fieldIndex(const char* szField) = 0;
virtual const char* fieldName(int nCol) = 0;
virtual int fieldDataType(int nCol) = 0;
virtual const char* fieldValue(int nField) = 0;
virtual const char* fieldValue(const char* szField) = 0;
virtual bool fieldDataIsNull(int nField) = 0;
virtual bool eof() = 0;
virtual void nextRow() = 0;
virtual void clear() = 0;
virtual int getIntField(int nField, int nNullValue = 0) = 0;
virtual int getIntField(const char* szField, int nNullValue = 0) = 0;
virtual double getDoubleField(int nField, double fNullValue = 0.0) = 0;
virtual double getDoubleField(const char* szField, double fNullValue = 0.0) = 0;
virtual const char* getStringField(int nField, const char* szNullValue = "") = 0;
virtual const char* getStringField(const char* szField, const char* szNullValue = "") = 0;
};
class DatabaseResultSet
{
public:
virtual int numFields() = 0;
virtual unsigned long numRows() = 0;
virtual int FieldColIndex(const char* szField) = 0;
virtual const char* fieldName(int nCol) = 0;
virtual const char* fieldValue(int nField) = 0;
virtual const char* fieldValue(const char* szField) = 0;
virtual bool fieldDataIsNull(int nField) = 0;
virtual bool eof() = 0;
virtual void nextRow() = 0;
virtual int seekRow(unsigned long nRow) = 0;
virtual void clear() = 0;
};
class DatabaseStatement
{
public:
virtual int execDML() = 0;
virtual void bind(int nParam, const char* szValue) = 0;
virtual void bind(int nParam, const int nValue) = 0;
virtual void bind(int nParam, const double dwValue) = 0;
virtual void reset() = 0;
virtual void clear() = 0;
};
#if defined _SQLITE3_DB && !defined _CppSQLite3_H_
#include "CppSQLite3.h"
#endif
#if defined _MySQL_DB && !defined _CPPMYSQL_H_
#include "CppMySQL.h"
#endif
#ifdef _WIN32
#include "Platform_WIN32.h"
#endif
#endif
使用示例
示例代码演示了，如何在业务代码中在SQLite和MySQL之间快速切换数据
#include <stdlib.h>
#include <stdio.h>
#include <ctime>
#include <iostream>
#include "DatabaseLayer.h"
using namespace std;
#ifdef _SQLITE3_DB
const char* gszDB = "CppSQLite3Demo.db";
#endif
#ifdef _MySQL_DB
const char* gszDB = "CppMySQLDemo";
#endif
int main()
{
try
{
CppDB db;
int i, fld;
#ifdef _SQLITE3_DB
remove(gszDB);
#endif
#ifdef _MySQL_DB
db.connect("127.0.0.1", "root", "root");
db.dropDB(gszDB);
db.createDB(gszDB);
#endif
db.open(gszDB);
cout << endl << "emp table exists=" << (db.tableExists("emp") ? "TRUE":"FALSE") << endl;
cout << endl << "Creating emp table" << endl;
db.execDML("create table emp(empno int, empname char(20));");
cout << endl << "emp table exists=" << (db.tableExists("emp") ? "TRUE":"FALSE") << endl;
cout << endl << "= 测试DML,打印影响行 =" << endl;
int nRows = db.execDML("insert into emp values (6, '勒布朗·詹姆斯');");
cout << nRows << " rows inserted" << endl;
nRows = db.execDML("update emp set empname = 'LeBron James' where empno = 6;");
cout << nRows << " rows updated" << endl;
nRows = db.execDML("delete from emp where empno = 7;");
cout << nRows << " rows deleted" << endl;
nRows = db.execDML("delete from emp where empno = 6;");
cout << nRows << " rows deleted" << endl;
cout << endl << "= 测试事务和execScalar =" << endl;
int nRowsToCreate(25000);
cout << endl << "Transaction test, creating " << nRowsToCreate;
cout << " rows please wait..." << endl;
db.startTransaction();
for (i = 0; i < nRowsToCreate; i++)
{
char buf[128];
sprintf(buf, "insert into emp values (%d, 'Empname%06d');", i, i);
db.execDML(buf);
}
db.commitTransaction();
cout << db.execScalar("select count(*) from emp;") << " rows in emp table";
cout << endl << "= 重新创建表emp = " << endl;
db.execDML("drop table emp;");
#ifdef _SQLITE3_DB
db.execDML("create table emp(empno integer primary key, empname char(20));");
#endif
#ifdef _MySQL_DB
db.execDML("CREATE TABLE emp(empno int not null auto_increment, empname char(20) not null, primary key (empno)) ENGINE=InnoDB;");
#endif
cout << nRows << " rows deleted" << endl;
for (i = 0; i < 5; i++)
{
char buf[128];
sprintf(buf, "insert into emp (empname) values ('帅锅%02d');", i+1);
db.execDML(buf);
}
cout << endl << "= 测试CppDBQuery =" << endl;
CppDBQuery q = db.execQuery("select * from emp order by 1;");
cout <<endl<<"Num of fields: "<< q.numFields() << endl;
#ifdef _SQLITE3_DB
cout <<endl<<"Num of rows: "<< db.execScalar("select count(*) from emp") << endl<<endl;
#endif
#ifdef _MySQL_DB
cout <<endl<<"Num of rows: "<< q.numRows() << endl<<endl;
#endif
for (fld = 0; fld < q.numFields(); fld++)
{
cout << q.fieldName(fld) << "|";
}
cout << endl;
while(!q.eof())
{
cout << q.getStringField(0) << "|";
cout << q.getStringField(1) << "|" << endl;
q.nextRow();
}
cout << endl << "= 赋值构造函数测试 =" << endl;
q = db.execQuery("select empname from emp;");
cout <<endl<<"Num of fields: "<< q.numFields() << endl;
q.clear();
cout << endl << "= CppDBResultSet测试 =" << endl;
CppDBResultSet t = db.getResultSet("select * from emp order by 1;");
cout <<endl<<"Num of fields: "<< t.numFields() << endl;
cout <<endl<<"Num of rows: "<< t.numRows() << endl << endl;
for (fld = 0; fld < t.numFields(); fld++)
{
cout << t.fieldName(fld) << "|";
}
cout << endl;
int row;
for (row = 0; row < (int)t.numRows(); row++)
{
t.seekRow(row);
for (fld = 0; fld < t.numFields(); fld++)
{
cout << t.fieldValue(fld) << "|";
}
cout << endl;
}
cout << endl << "另一种显示ResultSet的方法" << endl;
t.seekRow(0);
while (!t.eof())
{
cout << t.fieldValue(0) << "|";
cout << t.fieldValue(1) << "|";
cout << endl;
t.nextRow();
}
t.clear();
cout << endl << "= 测试CppDBStatement = " << endl;
cout << " rows please wait..." << endl;
db.execDML("drop table emp;");
db.execDML("create table emp(empno int, empname char(20));");
db.startTransaction();
nRowsToCreate = 200;
cout << endl << "Creating with bind by number" << endl;
CppDBStatement stmt = db.compileStatement("insert into emp values (?, ?);");
for (i = 0; i < nRowsToCreate; i++)
{
char buf[16];
sprintf(buf, "EmpName%02d", i);
stmt.bind(1, i);
stmt.bind(2, buf);
stmt.execDML();
stmt.reset();
}
stmt.clear();
db.commitTransaction();
cout << db.execScalar("select count(*) from emp;") << " rows in emp table " << endl;
t = db.getResultSet("select * from emp limit 10;");
for (fld = 0; fld < t.numFields(); fld++)
{
cout << t.fieldName(fld) << "|";
}
cout << endl;
for (row = 0; row < (int)t.numRows(); row++)
{
t.seekRow(row);
for (fld = 0; fld < t.numFields(); fld++)
{
cout << t.fieldValue(fld) << "|";
}
cout << endl;
}
cout << endl << "End of tests" << endl;
}
catch (CppDBException& e)
{
cerr <<endl<< "Exception:"<<e.errorCode() << ":" << e.errorMessage() << endl;
}
char c(' ');
while (c != 'q' && c != 'Q')
{
cout << "Press q then enter to quit: ";
cin >> c;
}
return 0;
}
|