iphone开发之SQLite使用详解

原文：http://blog.csdn.net/shang_515/article/details/7537889

SQLite是一个开源的嵌入式关系数据库，它在2000年由D. Richard Hipp发布，它的减少应用程序管理数据的开销，SQLite可移植性好，很容易使用，很小，高效而且可靠。
SQLite嵌入到使用它的应用程序中，它们共用相同的进程空间，而不是单独的一个进程。从外部看，它并不像一个RDBMS，但在进程内部，它却是完整的，自包含的数据库引擎。
嵌入式数据库的一大好处就是在你的程序内部不需要网络配置，也不需要管理。因为客户端和服务器在同一进程空间运行。SQLite 的数据库权限只依赖于文件系统，没有用户帐户的概念。SQLite 有数据库级锁定，没有网络服务器。它需要的内存，其它开销很小，适合用于嵌入式设备。你需要做的仅仅是把它正确的编译到你的程序。

ios下使用sqlite首先导入SQLite3.0的lib库。然后包含头文件#import <sqlite3.h>

下面的代码主要对常用的数据库操作如查询、插入、删除、更新等进行封装，方便以后使用。

头文件代码如下：

[cpp] view plain copy print ?

1. #import <Foundation/Foundation.h>  
2. #import <sqlite3.h>  
3. #import "User.h"  
4.   
5. #define kFileName @"database.sqlite"  
6.   
7. @interface SQLite3Util : NSObject{  
8.     sqlite3_stmt *stmt;  
9.     sqlite3 *database;  
10. }  
11.   
12. - (NSString *)dataFilePath;  
13. - (int)getCountOfDB;  
14. - (BOOL)insertOrUpdateUser:(NSString *)sql;  
15. - (BOOL)deleteUser:(NSInteger)userId;  
16. - (NSMutableArray *)getUsers;  
17. - (User *)getUser:(NSInteger)userId;  
18. - (BOOL)openDatabase;  
19. - (void)closeDatabase;  
20. @end  

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

#define kFileName @"database.sqlite"

@interface SQLite3Util : NSObject{
    sqlite3_stmt *stmt;
    sqlite3 *database;
}

- (NSString *)dataFilePath;
- (int)getCountOfDB;
- (BOOL)insertOrUpdateUser:(NSString *)sql;
- (BOOL)deleteUser:(NSInteger)userId;
- (NSMutableArray *)getUsers;
- (User *)getUser:(NSInteger)userId;
- (BOOL)openDatabase;
- (void)closeDatabase;
@end

实现如下：

[cpp] view plain copy print ?

1. #import "SQLite3Util.h"  
2. @implementation SQLite3Util  
3.   
4.   
5. //Return Database path  
6. - (NSString *)dataFilePath{  
7.     NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);  
8.     NSString *documentsDirectory = [paths objectAtIndex:0];  
9.     return [documentsDirectory stringByAppendingPathComponent:kFileName];  
10. }  
11.   
12. //Insert or update data  
13. - (BOOL)insertOrUpdateUser:(NSString *)sql{  
14.     if ([self openDatabase]) {  
15.         if (sqlite3_exec(database, [sql UTF8String], nil, &stmt, nil) != SQLITE_OK) {  
16.             NSLog(@"Insert or update is failed!");  
17.             return NO;  
18.         }else{  
19. //            sqlite3_finalize(stmt);  
20.             NSLog(@"Insert or update successfully!");  
21.             return YES;  
22.         }  
23.         sqlite3_close(database);  
24.           
25.     }  
26.     return NO;  
27. }  
28.   
29. //Delete a user from database  
30. - (BOOL)deleteQuestion:(NSInteger)userId{  
31.     NSString *sql = [NSString stringWithFormat:@"Delete FROM User WHERE id = %i", userId];  
32.     if ([self openDatabase]) {  
33.         if (sqlite3_exec(database, [sql UTF8String], nil, &stmt, nil) != SQLITE_OK) {  
34.             NSLog(@"Delete data is failed!");  
35.             return NO;  
36.         }else{  
37.             sqlite3_finalize(stmt);  
38.             NSLog(@"Delete data successfully!");  
39.             return YES;  
40.         }  
41.         sqlite3_close(database);  
42.     }  
43.     return NO;  
44. }  
45. //Get users  
46. - (NSMutableArray *)getUsers{  
47.     NSMutableArray *users = [[NSMutableArray alloc] init];  
48.     NSString *sql = [NSString stringWithFormat:@"SELECT * FROM User];  
49.     if ([self openDatabase]) {  
50.         if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {  
51. //            NSLog(@"SQL is Prepared!");  
52.             while (sqlite3_step(stmt) == SQLITE_ROW) {  
53.                 User *user = [[Question alloc] init];  
54.                 char *name = (char *)sqlite3_column_text(stmt, 0);  
55.                 [user setName:[NSString stringWithUTF8String:name]];  
56.                 char *index = (char *)sqlite3_column_text(stmt, 1);  
57.                 [user setId:[[NSString stringWithUTF8String:index] intValue]];  
58.                 [users addObject: user];  
59.             }  
60.             sqlite3_finalize(stmt);  
61.         }  
62.     }  
63.     sqlite3_close(database);  
64.     return users;  
65. }  
66.   
67. //Get count of the users  
68. - (int)getCountOfDB{  
69.     int count = 0;  
70.     NSString *sql = [NSString stringWithFormat:@"SELECT * FROM User"];   
71.     if ([self openDatabase]) {  
72.         if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {  
73.             while (sqlite3_step(stmt) == SQLITE_ROW) {  
74.                 count ++;  
75.             }  
76.             sqlite3_finalize(stmt);  
77.         }  
78.     }  
79.     return count;  
80. }  
81.   
82. - (User *)getUser:(NSInteger)userId{  
83.     User *user = [[User alloc] init];  
84.     NSString *sql = [NSString stringWithFormat:@"SELECT * FROM User WHERE id = %i", userId];  
85. //    NSLog(@"sql = %@", sql);  
86.     if ([self openDatabase]) {  
87.         if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {  
88.             NSLog(@"SQL is Prepared!");  
89.             while (sqlite3_step(stmt) == SQLITE_ROW) {  
90.                 char *name = (char *)sqlite3_column_text(stmt, 0);  
91.                 [user setName:[NSString stringWithUTF8String:name]];  
92.                 [user setId:userId];  
93.             }  
94.             sqlite3_finalize(stmt);  
95.         }  
96.     }  
97.     return user;  
98. }  
99.   
100. - (BOOL)openDatabase{  
101.     if (sqlite3_open([[self dataFilePath] UTF8String], &database) == SQLITE_OK) {  
102.         NSLog(@"SQL is Open!");  
103.         return YES;  
104.     }  
105.     return NO;  
106. }  
107.   
108. - (void)closeDatabase{  
109.     sqlite3_close(database);  
110. }  
111. @end  

#import "SQLite3Util.h"
@implementation SQLite3Util


//Return Database path
- (NSString *)dataFilePath{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    return [documentsDirectory stringByAppendingPathComponent:kFileName];
}

//Insert or update data
- (BOOL)insertOrUpdateUser:(NSString *)sql{
    if ([self openDatabase]) {
        if (sqlite3_exec(database, [sql UTF8String], nil, &stmt, nil) != SQLITE_OK) {
            NSLog(@"Insert or update is failed!");
            return NO;
        }else{
//            sqlite3_finalize(stmt);
            NSLog(@"Insert or update successfully!");
            return YES;
        }
        sqlite3_close(database);
        
    }
    return NO;
}

//Delete a user from database
- (BOOL)deleteQuestion:(NSInteger)userId{
    NSString *sql = [NSString stringWithFormat:@"Delete FROM User WHERE id = %i", userId];
    if ([self openDatabase]) {
        if (sqlite3_exec(database, [sql UTF8String], nil, &stmt, nil) != SQLITE_OK) {
            NSLog(@"Delete data is failed!");
            return NO;
        }else{
            sqlite3_finalize(stmt);
            NSLog(@"Delete data successfully!");
            return YES;
        }
        sqlite3_close(database);
    }
    return NO;
}
//Get users
- (NSMutableArray *)getUsers{
    NSMutableArray *users = [[NSMutableArray alloc] init];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM User];
    if ([self openDatabase]) {
        if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {
//            NSLog(@"SQL is Prepared!");
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                User *user = [[Question alloc] init];
                char *name = (char *)sqlite3_column_text(stmt, 0);
                [user setName:[NSString stringWithUTF8String:name]];
                char *index = (char *)sqlite3_column_text(stmt, 1);
                [user setId:[[NSString stringWithUTF8String:index] intValue]];
                [users addObject: user];
            }
            sqlite3_finalize(stmt);
        }
    }
    sqlite3_close(database);
    return users;
}

//Get count of the users
- (int)getCountOfDB{
    int count = 0;
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM User"]; 
    if ([self openDatabase]) {
        if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                count ++;
            }
            sqlite3_finalize(stmt);
        }
    }
    return count;
}

- (User *)getUser:(NSInteger)userId{
    User *user = [[User alloc] init];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM User WHERE id = %i", userId];
//    NSLog(@"sql = %@", sql);
    if ([self openDatabase]) {
        if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {
            NSLog(@"SQL is Prepared!");
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                char *name = (char *)sqlite3_column_text(stmt, 0);
                [user setName:[NSString stringWithUTF8String:name]];
                [user setId:userId];
            }
            sqlite3_finalize(stmt);
        }
    }
    return user;
}

- (BOOL)openDatabase{
    if (sqlite3_open([[self dataFilePath] UTF8String], &database) == SQLITE_OK) {
        NSLog(@"SQL is Open!");
        return YES;
    }
    return NO;
}

- (void)closeDatabase{
    sqlite3_close(database);
}
@end

在以后的代码中直接调用即可，如查询一个用户：

[cpp] view plain copy print ?

1. SQlite3Util *sqlUtil = [[SQlite3Util alloc] init];  
2. NSIngeter userId = 15;  
3. User *user = [sqlUitl getUser:userId];  

SQlite3Util *sqlUtil = [[SQlite3Util alloc] init];
NSIngeter userId = 15;
User *user = [sqlUitl getUser:userId];

有问题请留言，大家一起交流学习！

说明：转载请注明出处！