大约是一年多前开始接触OBJECT-C并进行iOS开发，一上来就被OBJECT里那种近似于“丑陋”的方法命名及其[]调用方式给“强暴”了一把，所以在后来的开发过程中，开始思考如何能使用C++方式来混编开发。经过了近半年多的代码积累和开发调试，开始有了这个系列的内容。本系列BLOG的主要方向是引导iOS开发者特别是之前用C#和C++的朋友，可以一步步搭建属于拥有.net风格的基本类库，并快速进行iOS应用的开发。不过前提是读者和开发者有一定的C++开发经验，以免遇到一些诡异问题时，能够快速找出解决方案。
好了，开始今天的内容吧！ 在XCODE开发时，可以很方便的引入SQLITE库来开发基于支持本地数据库的应用。但网上查到的大部分内容都只是介绍一个简单的示例而已。众所周知，微软的ADO.NET很好很强大，且已发展多年，其使用方式也很灵活多样。如果将其哪怕部分实现方式“移植”到IOS上，那即使是.NET新手也可以很快适应。在网上基于C++实现连接SQLITE的示例代码多如牛毛，但封装的却不甚理想。最后笔者在CODEPROJECT上终于挖出一个老项目，里面基本上定义了一些实现方式和框架且实现得短小精悍，且利于扩充，所以我就在其基础上，逐步加入了一些新的功能。所以就有了今天的内容。 在ADO.NET中定义了一些基本的数据库访问组件，比如DataSet, DataTable,DataRow,以及事务等。下面就来看一下用C++实现这些对象的方式。 首先是DataSet: typedef class CppSQLite3DB { public: CppSQLite3DB(); CppSQLite3DB( const char* szFile); virtual ~CppSQLite3DB(); void open( const char* szFile); void close(); bool tableExists( const char* szTable); int execDML( const char* szSQL); // 该方法为execNoQuery的封装 int execNoQuery( const char* szSQL); CppSQLite3Query execQuery( const char* szSQL); int execScalar( const char* szSQL); CppSQLite3Table getTable( const char* szSQL); CppSQLite3Statement compileStatement( const char* szSQL); sqlite_int64 lastRowId(); void interrupt() { sqlite3_interrupt(mpDB); } void setBusyTimeout( int nMillisecs); static const char* Version() { return SQLITE_VERSION; } public: CppSQLite3DB( const CppSQLite3DB& db); CppSQLite3DB& operator=( const CppSQLite3DB& db); sqlite3_stmt* compile( const char* szSQL); void checkDB(); sqlite3* mpDB; int mnBusyTimeoutMs; } DB;
typedef class CppSQLite3Table { public: CppSQLite3Table(); CppSQLite3Table( const CppSQLite3Table& rTable); CppSQLite3Table( char** paszResults, int nRows, int nCols); virtual ~CppSQLite3Table(); CppSQLite3Table& operator=( const CppSQLite3Table& rTable); int fieldsCount(); int rowsCount(); const char* fieldName( int nCol); const char* fieldValue( int nField); const char* operator[]( int nField); const char* fieldValue( const char* szField); const char* operator[]( const char* szField); int getIntField( int nField, int nNullValue= 0); int getIntField( const char* szField, int nNullValue= 0); double getFloatField( int nField, double fNullValue= 0.0); double getFloatField( const char* szField, double fNullValue= 0.0); const char* getStringField( int nField, const char* szNullValue= ""); const char* getStringField( const char* szField, const char* szNullValue= ""); bool fieldIsNull( int nField); bool fieldIsNull( const char* szField); void setRow( int nRow); const CppSQLite3TableRow getRow( int nRow); void finalize(); private: void checkResults(); int mnCols; int mnRows; int mnCurrentRow; char** mpaszResults; } Table; typedef class CppSQLite3TableRow { private: Table& inTable; public: const char* operator[]( int nField); const char* operator[]( const char* szField); CppSQLite3TableRow( Table& table):inTable(table){} virtual ~CppSQLite3TableRow( void) {}; } Row;
#ifndef DBHelper_h #define DBHelper_h #include " SQLiteHelper.h " // #include <ctime> #include <iostream> using namespace std; using namespace SQLiteWrapper; namespace SQLiteWrapper { class DBHelper { private: DBHelper() {} virtual ~DBHelper( void) {} public: static DB db; static DB loadDb() { DB database; { NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init]; NSArray *arr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory /* NSCachesDirectory */, NSUserDomainMask, YES); NSString *path = [arr objectAtIndex: 0]; path = [path stringByAppendingPathComponent: @" MySqlLitePath "]; // create directory for db if it not exists NSFileManager *fileManager = [[NSFileManager alloc] init]; BOOL isDirectory = NO; BOOL exists = [fileManager fileExistsAtPath:path isDirectory:&isDirectory]; if (!exists) { [fileManager createDirectoryAtPath:path withIntermediateDirectories:NO attributes:nil error:nil]; if (![fileManager fileExistsAtPath:path]) { [NSException raise: @" FailedToCreateDirectory " format: @" Failed to create a directory for the db at '%@' ",path]; } } [fileManager release]; // create db object NSString *dbfilePath = [path stringByAppendingPathComponent: @" Blogs "]; std:: string dbpathstr =[dbfilePath UTF8String]; const char *dbpath = dbpathstr.c_str(); // "/Users/MySqlLitePath/Blogs"; database.open(dbpath); [pool release]; } return database; } static bool tableExists( const char* szTable) { return db.tableExists(szTable); } static int execNoQuery( const char* szSQL) { return db.execDML(szSQL); } static int execNoQuery( const NSString* szSQL) { return db.execDML([szSQL UTF8String].c_str()); } static Query execQuery( const char* szSQL) { return db.execQuery(szSQL); } static Query execQuery( const NSString* szSQL) { return db.execQuery([szSQL UTF8String].c_str()); } static Query execScalar( const char* szSQL) { return db.execQuery(szSQL); } static int execScalar( const NSString* szSQL) { return db.execScalar([szSQL UTF8String].c_str()); } static Table getTable( const char* szSQL) { return db.getTable(szSQL); } static Table getTable( const NSString* szSQL) { return db.getTable([szSQL UTF8String].c_str()); } static Statement compileStatement( const char* szSQL) { return db.compileStatement(szSQL); } static Statement compileStatement( const NSString* szSQL) { return db.compileStatement([szSQL UTF8String].c_str()); } static sqlite_int64 lastRowId() { return db.lastRowId(); } static void setBusyTimeout( int nMillisecs) { db.setBusyTimeout(nMillisecs); } }; } DB DBHelper::db = DBHelper::loadDb(); // 在全局区进行对象初始化操作。
NSArray *arr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory /* NSCachesDirectory */, NSUserDomainMask, YES);
下面我们来看如何使用它，上DEMO, 呵呵。 判断当前数据库中是否存在某个TABLE,如存在则删除，之后创建新表:
if(DBHelper::tableExists( " emp ")){ DBHelper::execNoQuery( " drop table emp; "); } DBHelper::execNoQuery( " create table emp(empno int, empname char(20)); ");
接着向新表中接入数据并返回插入成功的条数，如下：
int nRows = DBHelper::execNoQuery( " insert into emp values (7, 'David Beckham'); "); cout << nRows << " rows inserted " << endl;
nRows = DBHelper::execNoQuery( " update emp set empname = 'Christiano Ronaldo' where empno = 7; "); cout << nRows << " rows updated " << endl; nRows = DBHelper::execNoQuery( " delete from emp where empno = 7; "); cout << nRows << " rows deleted " << endl;
int nRowsToCreate( 50000); cout << endl << " Transaction test, creating " << nRowsToCreate; cout << " rows please wait... " << endl; DBHelper::execNoQuery( " begin transaction; "); for ( int i = 0; i < nRowsToCreate; i++) { char buf[ 128]; sprintf(buf, " insert into emp values (%d, 'Empname%06d'); ", i, i); DBHelper::execNoQuery(buf); } DBHelper::execNoQuery( " commit transaction; ");
cout << DBHelper::execScalar( " select count(*) from emp; ") << " rows in emp table in ";
Buffer bufSQL; bufSQL.format( " insert into emp (empname) values (%Q); ", " He's bad "); cout << ( const char*)bufSQL << endl; DBHelper::execNoQuery(bufSQL); DBHelper::execNoQuery(bufSQL); bufSQL.format( " insert into emp (empname) values (%Q); ", NULL); cout << ( const char*)bufSQL << endl; DBHelper::execNoQuery(bufSQL);
Query q = DBHelper::execQuery( " select * from emp order by 1; "); for ( int fld = 0; fld < q.fieldsCount(); fld++){ cout << q.fieldName(fld) << " ( " << q.fieldDeclType(fld) << " )| "; } cout << endl; while (!q.eof()){ cout << q.fieldValue( 0) << " | " ; cout << q.fieldValue( 1) << " | " << endl; cout << q.fieldValue( " empno ") << " || " ; cout << q.fieldValue( " empname ") << " || " << endl; // 或使用[]索引，效果同q.fieldValue cout << q[ 0] << " | " ; cout << q[ 1] << " | " << endl; cout << q[ " empno "] << " || " ; cout << q[ " empname "] << " || " << endl; q.nextRow(); }
遍历数据集方式2：
Table t = DBHelper::getTable( " select * from emp order by 1; "); for ( int fld = 0; fld < t.fieldsCount(); fld++){ cout << t.fieldName(fld) << " | "; } for ( int row = 0; row < t.rowsCount(); row++){ Row r= t.getRow(row); cout << r[ " empno "] << " " << r[ " empname "] << " | "; cout << endl; }
cout << endl << " Transaction test, creating " << nRowsToCreate; cout << " rows please wait... " << endl; DBHelper::execNoQuery( " drop table emp; "); DBHelper::execNoQuery( " create table emp(empno int, empname char(20)); "); DBHelper::execNoQuery( " begin transaction; "); Statement stmt = DBHelper::compileStatement( " insert into emp values (?, ?); "); for ( int i = 0; i < nRowsToCreate; i++){ char buf[ 16]; sprintf(buf, " EmpName%06d ", i); stmt.bind( 1, i); stmt.bind( 2, buf); stmt.execDML(); stmt.reset(); } DBHelper::execNoQuery( " commit transaction; ");
原文链接:
作者: daizhj, 代震军
微博: http://weibo.com/daizhj Tags:ios, c++, sqlite