在IOS应用开发中, SQL数据库的使用非常重要.在Iphone中存在沙盒机制, 应用程序只能操作沙盒中的文件.数据库本质上也是一个文件,所以它也存储在沙盒中.对数据库的操作一般有,创建数据库, 以及数据库的增删改查.
常用的SQL语句有
creat table ContactInfo (contactName text, contactPhoneNumber text, contactGender, tex)创建一个表格
insert into ContactInfo (contactName, contactPhoneNumber, contactGender) values ("Duke", "18610318291", "男")插入一条数据
update ContactInfo set contactPhoneNumber = "13546541385", contactName = "Douglas" where rowid = 1更新一条数据
delete from ContactInfo where rowid = 1删除一条数据
select *from ContactInfo查找所有记录
select * from ContactInfo where rowid = 3查找一条数据
select contactGender from ContactInfo where rowid = 3查找一个字段
select * from ContactInfo where contactName like "D%"模糊查询
select *from ContactInfo where contactName like"%k%"
下面就简单介绍一下数据库的增删改查
要使用数据库sqlite，首先将libsqlite3.0.dylib加到项目中.新建一个DataBase类继承与NSObject,导入头文件#import<sqlite3.h>, 实现打开,关闭数据库两个方法.
@implementation DataBase
staticsqlite3 *dataBasePointer = nil;
+ (sqlite3 *)openDataBase
{
if (dataBasePointer == nil) {
//获取沙盒中的documents文件夹路径
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
//生成数据库文件的存储路径
NSString *dataBaseFilePath = [documentPath stringByAppendingPathComponent:@"Contact.sqlite"];
//利用NSFileManager类的实例对象, 做拷贝文件的操作
NSFileManager *fileManager = [NSFileManagerdefaultManager];
//判断如果数据库文件存储路径中, 没有指定的.sqlite文件,在做文件拷贝的工作
if (![fileManager fileExistsAtPath:dataBaseFilePath]) {
//生成应用程序包中的原始sqlite文件路径
NSString *sqlOrignalFilePath = [[NSBundlemainBundle] pathForResource:@"Contact"ofType:@"sqlite"];
NSLog(@"包中的文件路径%@", sqlOrignalFilePath);
NSLog(@"沙盒中的文件路径%@", dataBaseFilePath);
//声明拷贝错误信息的指针
NSError *error = nil;
//执行拷贝文件操作, 将原始数据库文件拷贝到指定文件路径
[fileManager copyItemAtPath:sqlOrignalFilePath toPath:dataBaseFilePath error:&error];
if (error != nil) {
NSLog(@"%@", [error description]);
//如果拷贝出错, 则打印错误信息, 并返回nil
returnnil;
}
}
//一旦拷贝成功, 就通过沙盒路径下的sql文件执行打开数据库的操作
sqlite3_open([dataBaseFilePath UTF8String], &dataBasePointer);
}
returndataBasePointer;
}
//关闭数据库
+ (void)closeDataBase
{
if (dataBasePointer != nil) {
sqlite3_close(dataBasePointer);
dataBasePointer = nil;
}
}
@end
再创建一个类ContectManager继承与NSObject, 导入头文件DataBase.h, ContectInfo.h
@implementation ContactManager
+ (NSMutableArray *)queryAllContactInformations
{
//数据库操作
//第一步, 打开数据库
sqlite3 *dataBase = [DataBaseopenDataBase];
//第二步, 声明sqlite3_stmt指针
sqlite3_stmt *statment = nil;
//第三步, 通过sqlite3_prepare_v2函数, 检查SQL语句是否正确, 如果正确, 将结果存入statment指针, 如果错误返回错误信息, 该函数有五个参数,需要我们明确传入的有数据库指针, SQL语句字符数组和stament指针地址, 其他两个整型的bytes指定为-1, 最后一个参数默认为nil
int result = sqlite3_prepare_v2(dataBase, "select * from ContactInfo", -1, &statment, nil);
NSMutableArray *contactListArray = [NSMutableArrayarray];
//如果SQL语句正确
if (result == SQLITE_OK) {
//循环取出每一行记录
while (sqlite3_step(statment) == SQLITE_ROW) {
constunsignedchar *name = sqlite3_column_text(statment, 0);
constunsignedchar *phoneNumber = sqlite3_column_text(statment, 1);
constunsignedchar *gender = sqlite3_column_text(statment, 2);
//下面需要把C语言字符数组, 转成OCNSString类型的实例对象
NSString *contactName = [NSStringstringWithUTF8String:(constchar *)name];
NSString *contactPhoneNumber = [NSStringstringWithUTF8String:(constchar *)phoneNumber];
NSString *contactGender = [NSStringstringWithUTF8String:(constchar *)gender];
ContactInfo *oneInfo = [[ContactInfoalloc] initWithContactName:contactName phoneNumber:contactPhoneNumber gender:contactGender];
[contactListArray addObject:oneInfo];
[oneInfo release], oneInfo = nil;
}
}
//释放statment占用的资源
sqlite3_finalize(statment);
//关闭数据库
[DataBasecloseDataBase];
return contactListArray;
}
//插入一条联系人信息
+ (BOOL)insertContactInfo:(ContactInfo *)contact
{
//打开数据库
sqlite3 *dataBase = [DataBaseopenDataBase];
//声明slite3_stmt指针
sqlite3_stmt *statment = nil;
//准备SQL插入语句
int result = sqlite3_prepare_v2(dataBase, "insert into ContactInfo(contactName, contactPhoneNumber, contactGender)values(?,?,?)", -1, &statment, nil);
if (result == SQLITE_OK) {
//为SQL语句绑定参数
constchar *contactName = [contact.contactNameUTF8String];
//绑定第一个问号占位的数剧
sqlite3_bind_text(statment, 1, contactName, -1, nil);
//绑定第二个?占位的数据
constchar *contactPhoneNumber = [contact.contactPhoneNumberUTF8String];
sqlite3_bind_text(statment, 2, contactPhoneNumber, -1, nil);
//绑定第三个?占位的数据
constchar *contactGender = [contact.contactGenderUTF8String];
sqlite3_bind_text(statment, 3, contactGender, -1, nil);//绑定参数的函数第二个参数需要指明对哪个字段做绑定,对应的是第几个问号,从一开始计数
//执行插入操作
int flag = sqlite3_step(statment);
if (flag == SQLITE_DONE) {
//释放statment指针
sqlite3_finalize(statment);
//关闭数据库
[DataBasecloseDataBase];
returnYES;
}
}
//释放statment指针
sqlite3_finalize(statment);
//关闭数据库
[DataBasecloseDataBase];
returnNO;
}
//删除一条联系人信息
+ (void)deleteContactInfo:(ContactInfo *)contact
{
sqlite3 *dataBase = [DataBaseopenDataBase];
sqlite3_stmt *statment = nil;
NSString *sqlString = [NSStringstringWithFormat:@"delete from ContactInfo where contactPhoneNumber = ‘%@‘", contact.contactPhoneNumber];
int result = sqlite3_prepare_v2(dataBase, [sqlString UTF8String], -1, &statment, nil);
if (result == SQLITE_OK) {
sqlite3_step(statment);
sqlite3_finalize(statment);
[DataBasecloseDataBase];
}
}
//更新联系人信息
+ (void)updateContactInfo:(ContactInfo *)contact
{
sqlite3 *dataBase = [DataBaseopenDataBase];
sqlite3_stmt *statment = nil;
int result = sqlite3_prepare_v2(dataBase, "update ContactInfo set contactName = ?, contactPhoneNumber = ? where contactPhoneNumber = ?", -1, &statment, nil);
if (result == SQLITE_OK) {
constchar *name = [contact.contactNameUTF8String];
sqlite3_bind_text(statment, 1, name, -1, nil);
constchar *phoneNumber = [contact.contactPhoneNumberUTF8String];
sqlite3_bind_text(statment, 2, phoneNumber, -1, nil);
//sqlite3_exec(sqlite3 *, const char *sql, int (*callback)(void *, int, char **, char **), void *, char **errmsg);
sqlite3_step(statment);
sqlite3_finalize(statment);
[DataBasecloseDataBase];
}
}
@end