上一篇介绍了数据库的单例的创建.这一篇介绍如何使用.数据库中存放的是一个名为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