上一篇介绍了数据库的单例的创建.这一篇介绍如何使用.数据库中存放的是一个名为class13的table.对象为Person类,这里就不再粘贴Person的代码了.这里新建了一个PersonManage类的单例,用来处理数据库里面的数据,以及方便其他类的调用.本段程序为PersonManage.h和.m的程序.第一段注释较为详细,涉及到一些C语言中的方法.参数的设置大多已经注释.另外需要注意的是增删减和普通的查询的区别.
#import "PersonManager.h" #import "DB.h" @implementation PersonManager static PersonManager *pm = nil; + (PersonManager *)sharedPersonManager { @synchronized(self){ if (pm == nil) { pm = [[PersonManager alloc] init]; } } return pm; } - (NSArray *)allPersons { //@"select * from class13" //打开数据库 sqlite3 *db = [DB open]; //stmt是 存放结果集 的对象。 sqlite3_stmt *stmt = nil; //第一个参数代表数据库对象,第二个参数代表需要执行的操作.字符串类型的,第三个代表字符串长度,一般我们都填-1,代表不限制长度,第四个代表用来存放结果的结果集,第五个参数表示查询玩之后做什么事情,NULL标示不做任何事情. int flag = sqlite3_prepare_v2(db, "select * from class13", -1, &stmt, NULL); NSMutableArray *persons = nil; if (flag == SQLITE_OK) {//如果SQL语句没问题,数据库也打开了,我们要从stmt中拿出结果(比如结果显示到tableview上等等) //创建一个数组,用来接收生成的Person类对象 persons = [NSMutableArray arrayWithCapacity:2]; //检查语句是否有问题.查询时候用SQLITE_ROW,检测是否还有下一条数据 while (sqlite3_step(stmt) == SQLITE_ROW) { //column是从0开始的,代表数据在数据库中一条数据的列数 int ID = sqlite3_column_int(stmt, 0); const unsigned char *name = sqlite3_column_text(stmt, 1); const unsigned char *sex = sqlite3_column_text(stmt, 2); const unsigned char *phone = sqlite3_column_text(stmt, 4); int age = sqlite3_column_int(stmt, 3); //数据库中的数据都是C语言类型的.需要转化成oc中的字符串类型 Person *p = [[Person alloc] initWithID:ID name:[NSString stringWithCString:(const char *)name encoding:NSUTF8StringEncoding] sex:[NSString stringWithCString:(const char *)sex encoding:NSUTF8StringEncoding] age:age phone:[NSString stringWithCString:(const char *)phone encoding:NSUTF8StringEncoding]]; [persons addObject:p]; } } sqlite3_finalize(stmt);//释放结果集占有的资源(内存) //[DB close];//此处没有关闭数据库,是想提高程序性能。避免任何一个小的操作都要 开关数据库。 return persons; } - (Person *)personByID:(int)ID { sqlite3 *db = [DB open]; sqlite3_stmt *stmt = nil; int flag = sqlite3_prepare_v2(db, "select * from class13 where id = ?", -1, &stmt, NULL); Person *p = nil; if (flag == SQLITE_OK) { //问号是从1开始的 用ID添补第一个?(问号) sqlite3_bind_int(stmt, 1, ID); if(sqlite3_step(stmt)==SQLITE_ROW) { int ID = sqlite3_column_int(stmt, 0); const unsigned char *name = sqlite3_column_text(stmt, 1); const unsigned char *sex = sqlite3_column_text(stmt, 2); const unsigned char *phone = sqlite3_column_text(stmt, 4); int age = sqlite3_column_int(stmt, 3); p = [[Person alloc] initWithID:ID name:[NSString stringWithCString:(const char *)name encoding:NSUTF8StringEncoding] sex:[NSString stringWithCString:(const char *)sex encoding:NSUTF8StringEncoding] age:age phone:[NSString stringWithCString:(const char *)phone encoding:NSUTF8StringEncoding]]; } } sqlite3_finalize(stmt); // [DB close] return p; } - (NSArray *)pesonsWithName:(NSString *)name { sqlite3 *db = [DB open]; sqlite3_stmt *stmt = nil; int flag = sqlite3_prepare_v2(db, "select * from class13 where name = ?", -1, &stmt, NULL); NSMutableArray *persons = nil; if (flag == SQLITE_OK) { sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL); persons = [NSMutableArray arrayWithCapacity:2]; while(sqlite3_step(stmt) == SQLITE_ROW) { int ID = sqlite3_column_int(stmt, 0); const unsigned char *name = sqlite3_column_text(stmt, 1); const unsigned char *sex = sqlite3_column_text(stmt, 2); const unsigned char *phone = sqlite3_column_text(stmt, 4); int age = sqlite3_column_int(stmt, 3); Person *p = [[Person alloc] initWithID:ID name:[NSString stringWithCString:(const char *)name encoding:NSUTF8StringEncoding] sex:[NSString stringWithCString:(const char *)sex encoding:NSUTF8StringEncoding] age:age phone:[NSString stringWithCString:(const char *)phone encoding:NSUTF8StringEncoding]]; [persons addObject:p]; } } sqlite3_finalize(stmt); // [DB close]; return persons; } - (void)addPerson:(Person *)p { sqlite3 *db = [DB open]; sqlite3_stmt *stmt = nil; int flag = sqlite3_prepare_v2(db, "insert into class13(name,sex,age,phone) values(?,?,?,?)", -1, &stmt, NULL); if (flag == SQLITE_OK) { sqlite3_bind_text(stmt, 1, [p.name UTF8String], -1, NULL); sqlite3_bind_text(stmt, 2, [p.sex UTF8String], -1, NULL); sqlite3_bind_int(stmt, 3, p.age); sqlite3_bind_text(stmt, 4, [p.phone UTF8String], -1, NULL); if(sqlite3_step(stmt)==SQLITE_DONE) { NSLog(@"插入成功"); }else{ NSLog(@"插入失败"); } } } - (void)updatePerson:(Person *)p { sqlite3 *db = [DB open]; sqlite3_stmt *stmt = nil; int flag = sqlite3_prepare_v2(db, "update class13 set name = ? , sex = ? , age = ? , phone = ? where id = ?", -1, &stmt, NULL); if (flag == SQLITE_OK) { sqlite3_bind_text(stmt, 1, [p.name UTF8String], -1, NULL); sqlite3_bind_text(stmt, 2, [p.sex UTF8String], -1, NULL); sqlite3_bind_text(stmt, 4, [p.phone UTF8String], -1, NULL); sqlite3_bind_int(stmt, 3, p.age); sqlite3_bind_int(stmt, 5, p.ID); if (sqlite3_step(stmt) == SQLITE_DONE) { NSLog(@"更新成功"); }else{ NSLog(@"更新失败"); } } } - (void)deletePerson:(Person *)p { sqlite3 *db = [DB open]; sqlite3_stmt *stmt = nil; int flag = sqlite3_prepare_v2(db, "delete from class13 where id = ?", -1, &stmt, NULL); if (flag == SQLITE_OK) { sqlite3_bind_int(stmt, 1, p.ID); if (sqlite3_step(stmt) == SQLITE_DONE) { NSLog(@"删除成功"); }else{ NSLog(@"删除失败"); } } } @end