iOS 开发之数据库(SQLite)

SQLite嵌入式数据库优点：
1.嵌入式数据库
2.支持事件,不需要配置,不需要安装,不需要管理员
3.支持大部分SQL92标准
4.完整的数据库保存在磁盘上面一个文件,同一个数据库文件可以在不同机器上面使用,最大支持数据库到2T
5.整个系统少于3万行,少于250KB的内存占用

开始使用SQLite： 
1.引入头文件
2.打开数据库
3.执行SQL命令
4.关闭数据库

以下代码展示整个数据库的 打开、关闭、增加、删除、查找、修改。

创建一个单例:

#import <foundation/foundation.h>@class Student;@interface DataBaseHandle : NSObject//创建一个单例+(DataBaseHandle *)shareDB; //获取Documents路径- (NSString *)documentsPath; //打开数据库- (void)openDB; //关闭数据库- (void)closeDB; //创建表- (void)createTable; //插入信息- (void)insertStudent:(Student *)stu; //修改信息- (void)updateMessage; //删除信息- (void)deleteMessage; //查找全部- (void)selectAllStudent; //条件查找(示例)- (void)selectWithSex:(NSString *)sex; @end

#import "DataBaseHandle.h"#import <sqlite3.h>#import "Student.h"//全局单利对象static DataBaseHandle *shareModle = nil;@implementation DataBaseHandle#pragma mark 实现单利+ (DataBaseHandle *)shareDB{    if (nil == shareModle)    {        shareModle = [[DataBaseHandle alloc] init];    }    return shareModle;} #pragma  mark 获取Documents路径- (NSString *)documentsPath{    NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];    return documentPath;} //在操作数据之前引入框架(sql...3.0)//声明一个数据库的对象static sqlite3 *db = nil;#pragma mark 打开数据库- (void)openDB{    //判断数据库是不是为空    if (nil == db)    {        //与documents路径拼接数据库存放路径(获得存放路径)        NSString *dbPath = [[self documentsPath] stringByAppendingString:@"/Student.sqlite"];        //根据路径打开数据库,如该路径下没有数据库,就自动创建一个数据库        //打开数据库(c语言中的语法)        int result = sqlite3_open(dbPath.UTF8String, &db);        //判断是否打开数据库成功        if (result == SQLITE_OK)        {            NSLog(@"数据库打开成功");        }        else        {            NSLog(@"数据库打开失败");        }    }} #pragma mark 关闭数据库- (void)closeDB{    int result = sqlite3_close(db);    if (result == SQLITE_OK)    {        //如果关闭成功,置为nill        db = nil;        NSLog(@"数据库关闭成功");    }    else    {        NSLog(@"数据库关闭失败");    }} #pragma mark 创建表- (void)createTable{    //要创建表,就要使用SQL语句.    //SQL语句在这是以字符串的形式存在的    NSString *createString = @"CREATE  TABLE  IF NOT EXISTS student (sid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , sname TEXT, ssex TEXT, sage TEXT)";    //执行SQL语句   int result = sqlite3_exec(db, createString.UTF8String, NULL, NULL, NULL);         if (result == SQLITE_OK)    {        NSLog(@"创建表成功");    }    else    {        NSLog(@"创建失败");    } } #pragma mark 插入信息- (void)insertStudent:(Student *)stu{    //准备插入语句    NSString *insertString = [NSString stringWithFormat:@"INSERT INTO student (sname,ssex,sage) VALUES ('%@','%@','%@')",stu.name,stu.sex,stu.age];    //执行语句    int result = sqlite3_exec(db, insertString.UTF8String, NULL, NULL, NULL);    if (result == SQLITE_OK)    {        NSLog(@"插入成功");    }    else    {        NSLog(@"插入失败");    }} #pragma mark 修改数据- (void)updateMessage{    //准备修改语句    NSString *undataString = @"UPDATE student SET ssex = '女' ,sname = '小梦' WHERE  sid = 10";    int result = sqlite3_exec(db, undataString.UTF8String, NULL, NULL, NULL);    if (result == SQLITE_OK)    {        NSLog(@"修改成功");    }    else    {        NSLog(@"修改失败");    }} #pragma  mark 删除数据- (void)deleteMessage{    //准备删除语句    NSString *deleteString = @"DELETE  FROM student WHERE sid = 1";    int result = sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL);    if (result == SQLITE_OK)    {        NSLog(@"delete OK");    }    else    {        NSLog(@"delete _NO");    }  } #pragma mark 查找全部- (void)selectAllStudent{    //准备查找语句    NSString *selectAll = @"SELECT *FROM student";    //创建伴随指针    sqlite3_stmt *stmt = nil;         //预备执行    int result = sqlite3_prepare(db, selectAll.UTF8String, -1, &stmt, NULL);    if (result == SQLITE_OK)    {        //在没有查找完成之前一直循环执行        while (sqlite3_step(stmt) == SQLITE_ROW)        {            //取出sid(第0列)            NSInteger sid = sqlite3_column_int(stmt, 0);            //取出sname(第1列)            NSString *sname = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];            //取出ssex(第2列)            NSString *ssex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];            //取出sage(第3列)            NSString *sage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];            NSLog(@"sid:%d sname:%@ ssex:%@ sage:%@",sid,sname,ssex,sage);         }        sqlite3_finalize(stmt);    }    else    {        //如果查找失败,结束伴随指针        sqlite3_finalize(stmt);        NSLog(@"查找失败");    }     }#pragma mark 条件查找- (void)selectWithSex:(NSString *)sex{    //准备查找语句    NSString *selecstString = @"SELECT * FROM student WHERE ssex = ?";    //创建伴随指针    sqlite3_stmt *stmt = nil;    //预执行     int result = sqlite3_prepare(db, selecstString.UTF8String, -1, &stmt, NULL);        if (result == SQLITE_OK)    {        //绑定?的值        //"1"代表第一个问号"?"和哪个参数绑定        sqlite3_bind_text(stmt, 1, sex.UTF8String, -1, NULL);        while (sqlite3_step(stmt) == SQLITE_ROW)        {            NSInteger sid = sqlite3_column_int(stmt, 0);            NSString *sname = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];            NSString *ssex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];            NSString *sage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];            NSLog(@"sid: %d sname: %@ ssex: %@ sage: %@",sid,sname,ssex,sage);        }    }    else    {        //关闭指针        sqlite3_finalize(stmt);        NSLog(@"查询失败");    }}@end

创建一个Student类

#import <foundation/foundation.h> @interface Student : NSObject@property(nonatomic,strong)NSString *name;@property(nonatomic,strong)NSString *age;@property(nonatomic,strong)NSString *sex;@end</foundation>

在 ViewController.m中 ViewDidLoad 调用方法

    //打印路径    NSLog(@"%@",[[DataBaseHandle shareDB] documentsPath]);    //打开数据库    [[DataBaseHandle shareDB] openDB];    //创建表    [[DataBaseHandle shareDB] createTable];         //插入信息//    Student *stu = [[Student alloc] init];//    stu.name = @"小露";//    stu.age = @"29";//    stu.sex = @"女";//    [[DataBaseHandle shareDB] insertStudent:stu];    //修改//    [[DataBaseHandle shareDB] updateMessage];    //删除//    [[DataBaseHandle shareDB] deleteMessage];    //查找全部信息//    [[DataBaseHandle shareDB] selectAllStudent];    //根据条件查找信息    [[DataBaseHandle shareDB] selectWithSex:@"女"];