关于SQLite的一些基本操作

这里写了一个单例处理基本的数据库操作，包含查询数据库表名、查询表字段名、查询表数据、开关数据库等操作

FMSQLiteManager.h 文件

#import <Foundation/Foundation.h>
#import <sqlite3.h>

@interface FMSQLiteManager : NSObject{
    sqlite3* db;
}

+ (FMSQLiteManager *)sharedManager;

- (BOOL) openSQLiteDBWithPath:(NSString *) filePath;
- (void) closeSQLiteDB;

- (NSArray *) queryTableFromDB;
- (NSArray *) queryWithSQL:(NSString *) sqlStr;
- (NSArray *) queryPragmaFromTable:(NSString *) table;

@end

FMSQLiteManager.m 文件

#import "FMSQLiteManager.h"

@implementation FMSQLiteManager

+ (FMSQLiteManager *)sharedManager
{
    static FMSQLiteManager *sharedSQLiteManagerInstance = nil;
    static dispatch_once_t predicate;
    dispatch_once(&predicate, ^{
        sharedSQLiteManagerInstance = [[self alloc] init];
    });
    return sharedSQLiteManagerInstance;
}

- (BOOL) openSQLiteDBWithPath:(NSString *) filePath {

    if (sqlite3_open([filePath UTF8String], &db)==SQLITE_OK) {
        return YES;
    }

    return NO;
}

- (void) closeSQLiteDB {
    sqlite3_close(db);
}

// 查询数据库所有表名
- (NSArray *) queryTableFromDB {

    sqlite3_stmt *statement;

    NSString* sqlStr = [NSString stringWithFormat:@"select * from sqlite_master where type='table' order by name"];
    const char* sql = [sqlStr UTF8String];
    if (sqlite3_prepare_v2(db, sql, -1, &statement, nil)==SQLITE_OK) {
        NSLog(@"select ok.");
    }

    NSMutableArray* results = [NSMutableArray array];
    while (sqlite3_step(statement)==SQLITE_ROW) {
        int _id=sqlite3_column_int(statement, 0);
        char *name=(char *)sqlite3_column_text(statement, 1);
        NSLog(@"row>>id %i, name %s",_id,name);
        [results addObject:[NSString stringWithCString:name encoding:NSUTF8StringEncoding]];
    }

    sqlite3_finalize(statement);

    return results;
}

// 查询表所有属性字段名
- (NSArray *) queryPragmaFromTable:(NSString *) table {
    sqlite3_stmt *statement;

    NSString* sqlStr = [NSString stringWithFormat:@"PRAGMA table_info(%@)",table];
    const char* sql = [sqlStr UTF8String];
    if (sqlite3_prepare_v2(db, sql, -1, &statement, nil)==SQLITE_OK) {
        NSLog(@"select ok.");
    }

    NSMutableArray* results = [NSMutableArray array];
    while (sqlite3_step(statement)==SQLITE_ROW) {

        char *nameData = (char *)sqlite3_column_text(statement, 1);
        NSLog(@"columnName:%s",nameData);

        [results addObject:[NSString stringWithCString:nameData encoding:NSUTF8StringEncoding]];
    }

    sqlite3_finalize(statement);

    return results;
}

// 查询表数据
- (NSArray *) queryWithSQL:(NSString *) sqlStr {

    sqlite3_stmt *statement;

    const char* sql = [sqlStr UTF8String];
    if (sqlite3_prepare_v2(db, sql, -1, &statement, nil)==SQLITE_OK) {
        NSLog(@"select ok.");
    }

    NSMutableArray* results = [NSMutableArray array];
    while (sqlite3_step(statement)==SQLITE_ROW) {

        int count = sqlite3_column_count(statement);
        NSMutableDictionary* info = [NSMutableDictionary dictionary];
        for (int i = 0; i < count; i ++) {
            char *result =(char *)sqlite3_column_text(statement, i);
            NSString* value = nil;
            if (result != NULL) {
                value = [NSString stringWithCString:result encoding:NSUTF8StringEncoding];
            }

            const char *columnName = sqlite3_column_name(statement, i);
            NSString* key = [NSString stringWithUTF8String:columnName];

            if (value == nil) {
                value = @"";
            }

            [info setObject:value forKey:key];
        }

        [results addObject:info];
    }

    sqlite3_finalize(statement);

    return results;
}

@end