sqlite保存数据模型

今天保存数据用到了sqlite,简单的使用了下

外部接口:public

@interface XBSQLiteTools : NSObject

//保存数据
+ (void)saveDataWithContact:(XBContact *)contact;


//取数据
+ (NSArray *)contactList;

//根据sql取得数据
+ (NSArray *)contactListWithSQL:(NSString *)sql;
//清空表中所有数据
+ (void)clearAllData;


@end

内部接口 : private

static sqlite3 *_db;
//这个方法在生命周期中只调用一次，所以作为创建数据库和创建表名最合适
+ (void)initialize
{
    
    NSString *path = [NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *filepath = [path stringByAppendingPathComponent:@"contacts.sqlite"];
    
    NSLog(@"%@",filepath);
    // 打开数据酷
    
    if(sqlite3_open(filepath.UTF8String, &_db) == SQLITE_OK)
    {
        NSLog(@"打开成功!");
    }
    else
    {
        NSLog(@"打开失败!");
    }

    NSString *sql = @"create table if not exists t_contact (id integer primary key autoincrement, name text, tel text);";
    
    char *error = NULL;
    
    sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &error);
    
    if(error)
    {
        NSLog(@"创建失败!");
    }
    else
    {
        NSLog(@"创建成功!");
    }
}
//除了查询外，其他操作都会调用这个内部借口  
+ (BOOL)execWithSql:(NSString *)sql
{
    BOOL flag;
    char *error = NULL;
    
    sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &error);
    
    if(error)
    {
        NSLog(@"%s",error);
        flag = NO;
    }
    else
    {
        flag = YES;
    }
    return flag;
}
+ (void)saveDataWithContact:(XBContact *)contact
{
    NSString *sql = [NSString stringWithFormat:@"insert into t_contact (name, tel) values ('%@','%@');",contact.name,contact.tel];
    //执行插入操作
     BOOL flag = [XBSQLiteTools execWithSql:sql];
    if(flag == YES)
    {
        //插入成功

    }
    else
    {
        //插入失败
    }
    
}

+ (NSArray *)contactList
{
    return [XBSQLiteTools contactListWithSQL:@"select * from t_contact"];
}

//查询操作，模糊查询等操作可通过这个外部接口
+ (NSArray *)contactListWithSQL:(NSString *)sql
{
    NSMutableArray *contactArray = [NSMutableArray array];
    
    //做查询操作
    sqlite3_stmt *stmt;
   if( sqlite3_prepare_v2(_db, sql.UTF8String, -1, &stmt, NULL) == SQLITE_OK)
   {
       //遍历结果集合
       while (sqlite3_step(stmt) == SQLITE_ROW) {
           //获取列的内容
           NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
           NSString *tel = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 2)];
           XBContact *contact = [XBContact contactWithName:name Tel:tel];
           [contactArray addObject:contact];
           
       }
   }
    return contactArray;
}
//清空表中的内容
+ (void)clearAllData
{
    NSString *sql = @"delete from t_contact;";
    [CGContactTool execWithSql:sql];
}