当C++遇到IOS应用开发---SQLITE篇(三)

这里要注意的一点就是，在其静态方法loadDb()中，要使用Object-C中的NSAutoreleasePool 对象来“框住”数据库的加载逻辑代码，否则会在下面这一样产生内存泄露：

[cpp]
NSArray *arr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory/*NSCachesDirectory*/, NSUserDomainMask, YES);

下面我们来看如何使用它，上DEMO, 呵呵。

判断当前数据库中是否存在某个TABLE,如存在则删除，之后创建新表:
[cpp]
if(DBHelper::tableExists("emp")){
DBHelper::execNoQuery("drop table emp;");
}
DBHelper::execNoQuery("create table emp(empno int, empname char(20));");

接着向新表中接入数据并返回插入成功的条数，如下：
[cpp]
int nRows = DBHelper::execNoQuery("insert into emp values (7, 'David Beckham');");
cout << nRows << " rows inserted" << endl;

然后进行UPDATE,DELETE操作并返回操作的记录条数:
[cpp]
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;

基于事务属性进行批量操作，以提升性能。

[cpp]
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;");

进行select count操作：
[cpp]
cout << DBHelper::execScalar("select count(*) from emp;") << " rows in emp table in ";

使用Buffer进行SQL语句构造：

[cpp]
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);

遍历数据集方式1：

[cpp]
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：

[cpp]
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;
}

预编译Statements测试(使用场景不多)：

[cpp]
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;");

最后我们只要找一个相应的.m文件改成.mm后缀，将上面测试语句执行一下，就可以了。