iOS，SQLite

SQLIte是一个轻量级的数据库，占用内存小，在iOS开发中，如果要做一些缓存数据的处理，有时候就考虑要用到SQLIte了。

亲测有效。拿来直接用。

在写代码前注意加上libsqlite3.tdb

下面直接上代码：

<pre name="code" class="objc">#import "JYSqlite.h"
#import "sqlite3.h"
#import "YSPattern.h"
@interface JYSqlite()
{
    //声明一个sqlite3的数据库
    sqlite3 *db;
}
@end
@implementation JYSqlite
//返回数据库在文件夹中的全路径信息
-(NSString *)filePath
{
    NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentDir=[paths objectAtIndex:0];
    //NSLog(@"我看看路径%@",documentDir);
    return [documentDir stringByAppendingPathComponent:@"/patternTest.sqlite"];
}

//打开数据库的方法
-(void)openDB
{
    if(sqlite3_open([[self filePath]UTF8String], &db)!=SQLITE_OK)
    {
        sqlite3_close(db);
        //NSLog(@"数据库打开失败");
    }
   // NSLog(@"数据库打开成功");
}

//创建表
-(void)createTable
{
    //模式名称，模式描述，模式图片
    NSString *sql=@"create TABLE if not EXISTS patternTable(name NSString,desc NSString,img NSString,rValue NSString,gValue NSString,bValue NSString);";
    char *errorMesg=NULL;
    int result=sqlite3_exec(db, [sql UTF8String], NULL, NULL, &errorMesg);
    if(result==SQLITE_OK)
    {
        NSLog(@"创建模式表成功");
    }
    else
    {
        NSLog(@"创建模式表失败%s",errorMesg);
    }
}

//插入数据的方法
-(void)insertRecordIntoTableName:(NSString *)tableName
                      withField1:(NSString *)field1 field1Value:(NSString *)field1Value
                       andField2:(NSString *)field2 field2Value:(NSString *)field2Value
                       andField3:(NSString *)field3 field3Value:(NSString *)field3Value
                       andField4:(NSString *)field4 field4Value:(NSString *)field4Value
                       andField5:(NSString *)field5 field5Value:(NSString *)field5Value
                       andField6:(NSString *)field6 field6Value:(NSString *)field6Value
{
    NSString *sql=[NSString stringWithFormat:@"INSERT INTO '%@'('%@','%@','%@','%@','%@','%@') VALUES('%@','%@','%@','%@','%@','%@')",tableName,field1,field2,field3,field4,field5,field6,field1Value,field2Value,field3Value,field4Value,field5Value,field6Value];
    char *err;
    if(sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err)!=SQLITE_OK)
    {
        sqlite3_close(db);
        NSAssert(0,@"插入数据错误！");
    }
    
}

//删除数据
-(void)deleteRecordWithName:(NSString *)patternName
{
    sqlite3_stmt *stmt;
    NSString *sql = [NSString stringWithFormat:@"delete from patternTable where name = '%@'", patternName];
    
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK)
    {
        sqlite3_step(stmt);
        //觉的应加一个判断, 若有这一行则删除
        if (sqlite3_step(stmt) == SQLITE_DONE)
        {
            sqlite3_finalize(stmt);
            
        }
    }
}
//更新rgb
-(void)updateRecordByRGB:(NSString *)patternName rValue:(NSString *)rValue gValue:(NSString *)gValue bValue:(NSString *)bValue
{
    sqlite3_stmt *stmt = nil;
    NSString *sql = [NSString stringWithFormat:@"update patternTable set rValue = '%@',gValue='%@',bValue='%@'  where name = '%@'", rValue,gValue,bValue,patternName];
    
    NSLog(@"===%@",sql);
    
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL) == SQLITE_OK)
    {
        if (sqlite3_step(stmt) == SQLITE_ROW)
        {
            //觉的应加一个判断, 若有这一行则修改
            if (sqlite3_step(stmt) == SQLITE_DONE)
            {
                sqlite3_finalize(stmt);
            }
        }
    }
}

//更新模式名称,模式描述,背景图标识
-(void)updateRecordByOldPatternName:(NSString *)oldPatternName andNewPatternName:(NSString *)newPatternName andDesc:(NSString *)descLabel andImg:(NSString *)imgKey
{
    sqlite3_stmt *stmt = nil;
    NSString *sql = [NSString stringWithFormat:@"update patternTable set name = '%@',desc='%@',img='%@'  where name = '%@'", newPatternName,descLabel,imgKey,oldPatternName];
    
    //NSLog(@"===%@",sql);
    
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL) == SQLITE_OK)
    {
        if (sqlite3_step(stmt) == SQLITE_ROW)
        {
            //觉的应加一个判断, 若有这一行则修改
            if (sqlite3_step(stmt) == SQLITE_DONE)
            {
                sqlite3_finalize(stmt);
            }
        }
    }
    
}

//查询数据
-(void)getAllRecord
{
    //NSLog(@"***************查询所有数据********************");
    NSString *sql=@"SELECT * FROM patternTable";
    sqlite3_stmt *statement;
    
    if(sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil)==SQLITE_OK)
    {
        while (sqlite3_step(statement)==SQLITE_ROW)
        {
            char *name=(char *)sqlite3_column_text(statement, 0);
            NSString *patternName=[[NSString alloc]initWithUTF8String:name];
            
            char *desc=(char *)sqlite3_column_text(statement, 1);
            NSString *descLabel=[[NSString alloc]initWithUTF8String:desc];
            
            char *img=(char *)sqlite3_column_text(statement, 2);
            NSString *imgKey=[[NSString alloc]initWithUTF8String:img];
            
            char *r =(char *)sqlite3_column_text(statement, 3);
            NSString *rValue=[[NSString alloc]initWithUTF8String:r];
            
            char *g =(char *)sqlite3_column_text(statement, 4);
            NSString *gValue=[[NSString alloc]initWithUTF8String:g];
            
            char *b =(char *)sqlite3_column_text(statement, 5);
            NSString *bValue=[[NSString alloc]initWithUTF8String:b];
            
            YSPattern *pattern=[[YSPattern alloc]init];
            pattern.patternName=patternName;
            pattern.descLabel=descLabel;
            pattern.imgKey=imgKey;
            pattern.rValue=rValue;
            pattern.gValue=gValue;
            pattern.bValue=bValue;
            NSLog(@"====%@",pattern.patternName);
            
            [self.patterns addObject:pattern];
        }
       // NSLog(@"==%ld",self.patterns.count);
    }
}

@end