iPhone開發之SQLite使用詳解

SQLite是一個開源的嵌入式關系數據庫，它在2000年由D. Richard Hipp發布，它的減少應用程序管理數據的開銷，SQLite可移植性好，很容易使用，很小，高效而且可靠。

SQLite嵌入到使用它的應用程序中，它們共用相同的進程空間，而不是單獨的一個進程。從外部看，它並不像一個RDBMS，但在進程內部，它卻是完整的，自包含的數據庫引擎。

嵌入式數據庫的一大好處就是在你的程序內部不需要網絡配置，也不需要管理。因為客戶端和服務器在同一進程空間運行。SQLite 的數據庫權限只依賴於文件系統，沒有用戶帳戶的概念。SQLite 有數據庫級鎖定，沒有網絡服務器。它需要的內存，其它開銷很小，適合用於嵌入式設備。你需要做的僅僅是把它正確的編譯到你的程序。

ios下使用sqlite首先導入SQLite3.0的lib庫。然後包含頭文件#import <sqlite3.h>

下面的代碼主要對常用的數據庫操作如查詢、插入、刪除、更新等進行封裝，方便以後使用。

頭文件代碼如下：

1. #import <Foundation/Foundation.h>
2. #import <sqlite3.h>
3. #import "User.h"
5. #define kFileName @"database.sqlite"
7. @interface SQLite3Util : NSObject{
8. sqlite3_stmt *stmt;
9. sqlite3 *database;
10. }
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

實現如下：

1. #import "SQLite3Util.h"
2. @implementation SQLite3Util
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. }
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);
25. }
26. return NO;
27. }
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. }
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. }
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. }
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. }
108. - (void)closeDatabase{
109. sqlite3_close(database);
110. }
111. @end

在以後的代碼中直接調用即可，如查詢一個用戶：

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