DB.h
#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface DB : NSObject
// 打开数据库,并返回数据库的指针
+ (sqlite3 *)open;
// 关闭数据库
+ (void)close;
@end
DB.m
#import "DB.h"
#define KSQLFileName @"StudentManage.sqlite"
@implementation DB
static sqlite3 *db = nil;
// 实际开发中 对数据库的操作会比较多,未来避免不停的打开和关闭数据库, 我们把数据库的指针做成单例,如果单例有值,直接返回,如果单例没值,创建一个.
+ (sqlite3 *)open
{
if (db != nil) { // 如果数据库已经打开,无需再次打开
return db;
}
// documents文件夹路径
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
// 数据库文件 路径
NSString *sqlfilePath = [docPath stringByAppendingPathComponent:KSQLFileName];
NSFileManager *fm = [NSFileManager defaultManager];
if ([fm fileExistsAtPath:sqlfilePath] == NO) { // 如果给定的路径没有文件
// 从包里取出文件
NSString *bundleSQLFilePath = [[NSBundle mainBundle] pathForResource:KSQLFileName ofType:nil];
// 把包里的数据文件 拷贝到Documents下
[fm copyItemAtPath:bundleSQLFilePath toPath:sqlfilePath error:nil];
}
// 打开数据库
sqlite3_open([sqlfilePath UTF8String], &db);
return db;
}
+ (void)close
{
sqlite3_close(db);
db = nil;
}
@end
Person.h
#import <Foundation/Foundation.h>
@interface Person : NSObject
@property(nonatomic,assign) int ID;
@property(nonatomic,retain) NSString *name;
@property(nonatomic,retain) NSString *phone;
- (id)initWithID:(int)ID name:(NSString *)name phone:(NSString *)phone;
+ (id)personWithID:(int)ID name:(NSString *)name phone:(NSString *)phone;
@end
Person.m
#import "Person.h"
@implementation Person
- (id)initWithID:(int)ID name:(NSString *)name phone:(NSString *)phone
{
self = [super init];
if (self) {
self.ID = ID;
self.name = name;
self.phone = phone;
}
return self;
}
+ (id)personWithID:(int)ID name:(NSString *)name phone:(NSString *)phone
{
Person *person = [[[Person alloc] initWithID:ID name:name phone:phone] autorelease];
return person;
}
- (void)dealloc
{
[_phone release];
[_name release];
[super dealloc];
}
@end
PersonManager.h
#import <Foundation/Foundation.h>
#import "Person.h"
@interface PersonManager : NSObject
+ (PersonManager *)sharedPersonManager;
// 查询class13表中的全部信息
- (NSArray *)allPersons;
- (Person *)personByID:(int)ID;
- (NSArray *)personsWithName:(NSString *)name;
- (void)updatePerson:(Person *)p;
- (void)deletePerson:(Person *)p;
- (void)addPerson:(Person *)p;
@end
PersonManager.m
#import "PersonManager.h"
#import "DB.h"
static PersonManager *inst = nil;
@implementation PersonManager
+ (PersonManager *)sharedPersonManager
{
@synchronized(self){
if (inst == nil) {
inst = [[PersonManager alloc] init];
}
}
return inst;
}
- (NSArray *)allPersons
{
// 打开数据库
sqlite3 *db = [DB open];
// 拿到stmt stmt是 存放结果集 的对象
sqlite3_stmt *stmt = nil;
int flag = sqlite3_prepare_v2(db, "select *from class13", -1, &stmt, NULL);
// 使用结果集
NSMutableArray *persons = nil;
if (flag == SQLITE_OK) { // 如果SQL语句没有问题,数据库也打开了,我们要从stmt中拿出结果 (比如结果显示到tableview上等等)
persons = [NSMutableArray arrayWithCapacity:2];
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 *phone = sqlite3_column_text(stmt, 2);
Person *p = [[Person alloc] initWithID:ID name:[NSString stringWithCString:(const char *)name encoding:NSUTF8StringEncoding] 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, nil);
Person *p = nil;
if (flag == SQLITE_OK) {
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_text16(stmt, 1);
const unsigned char *phone = sqlite3_column_text16(stmt, 2);
p = [Person personWithID:ID name:[NSString stringWithCString:(const char *)name encoding:NSUTF8StringEncoding] phone:[NSString stringWithCString:(const char *)phone encoding:NSUTF8StringEncoding]];
}
}
sqlite3_finalize(stmt);
[DB close];
return p;
}
//
- (NSArray *)personsWithName:(NSString *)name
{
sqlite3 *db = [DB open];
sqlite3_stmt *stmt = nil;
int flag = sqlite3_prepare_v2(db, "select * from class13 where name = ?", -1, &stmt, nil);
NSMutableArray *persons = [NSMutableArray arrayWithCapacity:2];
if (flag == SQLITE_OK) {
sqlite3_bind_text(stmt, 1, [name UTF8String], -1, nil);
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 *phone = sqlite3_column_text(stmt, 2);
Person *p = [Person personWithID:ID name:[NSString stringWithCString:(const char *)name encoding:NSUTF8StringEncoding] phone:[NSString stringWithCString:(const char *)phone encoding:NSUTF8StringEncoding]];
[persons addObject:p];
}
}
sqlite3_finalize(stmt);
[DB close];
return persons;
}
//
- (void)updatePerson:(Person *)p
{
sqlite3 *db = [DB open];
sqlite3_stmt *stmt = nil;
int flag = sqlite3_prepare_v2(db, "update class13 set name = ?,phone = ? where ID = ?" , -1, &stmt, nil);
if (flag == SQLITE_OK) {
sqlite3_bind_text(stmt, 1, [p.name UTF8String], -1, nil);
sqlite3_bind_text(stmt, 2, [p.phone UTF8String] , -1, nil);
sqlite3_bind_int(stmt, 3, 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, nil);
if (flag == SQLITE_OK) {
sqlite3_bind_int(stmt, 1, p.ID);
if (sqlite3_step(stmt) == SQLITE_DONE) {
NSLog(@"删除成功");
}else{
NSLog(@"删除失败");
}
}
}
//
- (void)addPerson:(Person *)p
{
sqlite3 *db = [DB open];
sqlite3_stmt *stmt = nil;
int flag = sqlite3_prepare_v2(db, "insert into class13 (ID,name,phone) values(?,?,?)", -1, &stmt, nil);
if (flag == SQLITE_OK) {
sqlite3_bind_int(stmt, 1, p.ID);
sqlite3_bind_text(stmt, 2, [p.name UTF8String], -1, nil);
sqlite3_bind_text(stmt, 3, [p.phone UTF8String], -1, nil);
if (sqlite3_step(stmt) == SQLITE_DONE) {
NSLog(@"添加成功");
}else{
NSLog(@"添加失败");
}
}
}
@end