FMDB使用

FMDBManager.h

#import <Foundation/Foundation.h>
#import "FMDatabase.h"

@interface FMDBManager : NSObject
{
    NSString *dataBasePath;
    FMDatabase * fmdbDataBase;//数据库对象
    BOOL isFMDBopen;
}

+ (FMDBManager *)sharedFMDB;

// 创建表
-(void)createTable;

// 插入记录
-(void)insertTable;

// 获取所有记录
-(NSMutableArray *)getList;

//删除某人所有信息
-(BOOL)deleteUser;


@end

FMDBManager.m

#import "FMDBManager.h"
#import "userModel.h"

#define FMDB_NAME @"test.sqlite3"

static FMDBManager *_sharedFMDB = nil;

@implementation FMDBManager

//共享实例
+(FMDBManager *)sharedFMDB
{
    
    if (_sharedFMDB == nil) {
        _sharedFMDB = [[FMDBManager alloc]init];
    }
    return _sharedFMDB;
}

-(id)init
{
    self = [super init];
    if (self) {
        // Custom initialization
        [self openDataBase];
    }
    return self;
}

-(void)openDataBase
{
    // NSDocumentDirectory (Documents目录下面) NSLibraryDirectory(Library目录下面)
    NSArray *libPaths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
    dataBasePath=[[libPaths objectAtIndex:0] stringByAppendingPathComponent:FMDB_NAME];
    
    // 创建数据库
    fmdbDataBase = [FMDatabase databaseWithPath:dataBasePath];
    if ([fmdbDataBase open]) {
        isFMDBopen = YES;
    } else {
        isFMDBopen = NO;
        NSLog(@"error when open db");
    }
    
}

// 判断是否存在表
- (BOOL) isTableOK:(NSString *)tableName
{
    FMResultSet *rs = [fmdbDataBase executeQuery:@"select count(*) as 'count' from sqlite_master where type ='table' and name = ?", tableName];
    while ([rs next])
    {
        // just print out what we've got in a number of formats.
        NSInteger count = [rs intForColumn:@"count"];
        
        if (0 == count)
        {
            NSLog(@"表不存在");
            return NO;
        }
        else
        {
            NSLog(@"表已存在");
            return YES;
        }
    }
    
    return NO;
}

// 创建表
-(void)createTable
{
    NSString *strCreatetable = [NSString stringWithFormat:@"CREATE TABLE 'user10086' ('userID' INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL, 'userName' text, 'passWord' text)"];
    
    if (![self isTableOK:[NSString stringWithFormat:@"%@",@"user10086"]])
    {
        BOOL res = [fmdbDataBase executeUpdate:strCreatetable];
        if (!res)
        {
            NSLog(@"error when creating db table");
        }
        else
        {
            NSLog(@"succ to creating db table");
        }
    }
    
}

// 插入数据
-(void)insertTable
{

    NSString *strInserttable = [NSString stringWithFormat:@"insert into user10086 (userName, passWord) values(?, ?) "];
    BOOL res = [fmdbDataBase executeUpdate:strInserttable,@"testUser",@"123456"];
  
    if (!res) {
        NSLog(@"insert error");
    } else {
        NSLog(@"insert success");
    }
}

// 获取所有记录
-(NSMutableArray *)getList
{
    NSMutableArray *chatList = [[NSMutableArray alloc] init];
    
    NSString *sqlQuery = [NSString stringWithFormat:@"select * from user10086"];
    FMResultSet * rs = [fmdbDataBase executeQuery:sqlQuery];
    while ([rs next]) {
        
        userModel *user = [[userModel alloc] init];
        
        user.userId = [NSString stringWithFormat:@"%d",[rs intForColumn:@"userID"]];
        user.userName = [rs stringForColumn:@"userName"];
        user.passWord = [rs stringForColumn:@"passWord"];
        
        [chatList addObject:user];
    }
    
    return chatList;
}

//删除某一条记录
-(BOOL)deleteUser
{
    NSString *sqlDelete = [NSString stringWithFormat:@"delete from user10086 where userID=%d", 1];
    
    return [fmdbDataBase executeUpdate:sqlDelete];
}


@end

// 使用
  [[FMDBManager sharedFMDB]createTable];
  [[FMDBManager sharedFMDB]insertTable];