iOS数据持久化之FMDB的简单封装

Posted on   |   In

缘由

FMDB是iOS平台处理数据持久化的一个强大工具，可以帮助开发者高效地在客户端处理数据，但对于我等初级开发者来说其使用还是不够简单。因此，有必要对它进行封装，从而使其更易于使用，封装的方式因人而异，需要我们根据自己的代码习惯进行优化。我的封装也仅限于提供一种简单的思路，以便后来者参考。

如何实现

我的项目名为fmdbEncap,托管于Github。在我的代码中， 使用MZDatabaseManager创建SQLite数据库；在 MZSqliteDao中创建了基于FMDB的一系列方法用于数据库创建、建表，以及数据库的增、删、改、查。

文末会附上Github项目地址

如何使用

关于如何在你的项目中使用该封装，你只需在引入FMDB的前提下添加本项目中的FMDBEncapsulation文件夹和相关模型数据即可。

一些说明

为了使它简单实用，项目中包含fmdb_task和fmdb_category两个表用于举例说明，表中的参数也与代码中实例一一对应。SQLite数据库建立在Documents路径下，对数据库的一切操作基于模拟数据。我会尽量涵盖FMDB基本使用的相关细节，并将持续更新代码。

SQL 语句

SQL语句以宏定义的形式在配置文件中写入。

  • Table Name
1
2
3
4

#define     TABLE_TASK                   @"FMDB_TASK"

#define     TABLE_CATEGORY               @"FMDB_CATEGORY"
  • Create tables
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

#define     SQL_CREATE_TASK              @"CREATE TABLE IF NOT EXISTS FMDB_TASK(\

                                           TASK_ID integer PRIMARY KEY AUTOINCREMENT NOT NULL,\

                                           ROOM_ID int(11) NOT NULL DEFAULT 0,\

                                           TASK_INFO int(11),\

                                           TASK_NUMBER int(4) NOT NULL,\

                                           TASK_STATUS int(4) NOT NULL,\

                                           TASK_COORDINATE varchar(20),\

                                           TASK_BEDSIDE varchar(20)\

                                           );"



#define     SQL_CREATE_CATEGORY          @"CREATE TABLE IF NOT EXISTS FMDB_CATEGORY(\

                                           CATEGORY_ID integer PRIMARY KEY AUTOINCREMENT NOT NULL,\

                                           PARENT_ID int(11) NOT NULL,\

                                           LEVEL int(11),\

                                           CATEGORY_NAME nvarchar(45) NOT NULL,\

                                           CATEGORY_IMG varchar(100),\

                                           CATEGORY_RANK int(11) NOT NULL,\

                                           CATEGORY_TYPE int(4) NOT NULL,\

                                           CATEGORY_STATUS int(11) NOT NULL,\

                                           CATEGORY_REMARK nvarchar(100)\

                                           );"
  • Insert
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

#define     SQL_INSERT_TASK              @"INSERT INTO FMDB_TASK\

                                           ( ROOM_ID, TASK_INFO, TASK_NUMBER,\

                                           TASK_STATUS, TASK_COORDINATE, TASK_BEDSIDE)\

                                           VALUES ( '%d', '%d', '%d', '%d', '%@', '%@');"



#define     SQL_INSERT_CATEGORY          @"INSERT INTO FMDB_CATEGORY\

                                           ( PARENT_ID, LEVEL, CATEGORY_NAME, CATEGORY_IMG,\

                                           CATEGORY_RANK, CATEGORY_TYPE, CATEGORY_STATUS, CATEGORY_REMARK)\

                                           VALUES ( '%d', '%d', '%@', '%d', '%d', '%d', '%d', '%@');"



#define     SQL_INSERT_TASK_PARAMTER     @"INSERT INTO FMDB_TASK\

                                           ( ROOM_ID, TASK_INFO, TASK_NUMBER,\

                                           TASK_STATUS, TASK_COORDINATE, TASK_BEDSIDE)\

                                           VALUES (\

                                           :roomID, :taskInfo, :taskNumber, :taskStatus, :taskCoordinate, :taskBeside);"



#define     SQL_INSERT_CATEGORY_PARAMTER @"INSERT INTO FMDB_CATEGORY\

                                           ( PARENT_ID, LEVEL, CATEGORY_NAME, CATEGORY_IMG,\

                                           CATEGORY_RANK, CATEGORY_TYPE, CATEGORY_STATUS, CATEGORY_REMARK)\

                                           VALUES (\

                                           :prantID, :level, :categoryName, :categoryImage, :categoryRank, :categoryType, :categoryStatus, :categoryRemark);"
  • Update
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

#define     SQL_UPDATE_TASK              @"UPDATE FMDB_TASK\

                                           SET ROOM_ID = '%d', TASK_INFO = '%d', TASK_NUMBER = '%d',\

                                           TASK_STATUS = '%d', TASK_COORDINATE = '%@', TASK_BEDSIDE = '%@'\

                                           WHERE TASK_ID = '%d';"



#define     SQL_UPDATE_CATEGORY          @"UPDATE FMDB_CATEGORY\

                                           SET PARENT_ID = '%d', LEVEL = '%d', CATEGORY_NAME = '%@',\

                                           CATEGORY_IMG = '%d',CATEGORY_RANK = '%d', CATEGORY_TYPE = '%d',\

                                           CATEGORY_STATUS = '%d',CATEGORY_REMARK = '%@'\                                           WHERE CATEGORY_ID = '%d';"
  • Delete
1
2
3
4
5
6

#define     SQL_DELETE                   @"DELETE FROM %@ WHERE '%@'='%d';"



#define     SQL_DELETE_ALL               @"DELETE FROM %@"
  • Select
1
2
3
4
5
6

#define     SQL_SELECT_ALL               @"SELECT * FROM %@;"



#define     SQL_SELECT_PARAMTER          @"SELECT * FROM %@ WHERE %@ = '%d';"

举例说明

1.Create database

1
2

[[MZDatabaseManager sharedInstance] createDatabaseWithName:@"test"];

2.Create table

1
2
3
4
5
6
7
8

self.sqliteDao = [[MZSqliteDao alloc] init];

// create table

[self.sqliteDao createTable:TABLE_TASK withSQL:SQL_CREATE_TASK];

[self.sqliteDao createTable:TABLE_CATEGORY withSQL:SQL_CREATE_CATEGORY];

3.Insert 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

NSArray *arrayOfTasks = [MZTask taskMetedata];

    NSArray *arrayOfCategories = [MZCategory categoryMetedata];

    

      // task: SQL 语句拼接

    for (MZTask *task in arrayOfTasks) {

        NSString *SQL_InsertTaskData = [NSString stringWithFormat:SQL_INSERT_TASK,[task.roomID intValue],[task.taskInfo  intValue],[task.taskNumber  intValue],[task.taskStatus  intValue],task.taskCoordinate,task.taskBeside];

        BOOL success = [self.sqliteDao excuteSQL:SQL_InsertTaskData];

        if (success) {

            NSLog(@"insert success!");

        }

    }

      // category: 带参

    for (NSInteger i = 0; i < arrayOfCategories.count; i ++) {

        MZCategory *category = arrayOfCategories[i];

        NSDictionary *paramDict = @{@"prantID":category.parentID,

                                    @"level":category.level,

                                    @"categoryName":category.categoryName,

                                    @"categoryImage":category.categoryImg,

                                    @"categoryRank":category.categoryRank,

                                    @"categoryType":category.categoryType,

                                    @"categoryStatus":category.categoryStatus,

                                    @"categoryRemark":category.categoryRemark

                                    };

        BOOL success = [self.sqliteDao excuteSQL:SQL_INSERT_CATEGORY_PARAMTER withDicParameter:paramDict];

        if (success) {

            NSLog(@"%@ insert success!",paramDict);

        }

    }

4.Update

1
2
3
4

NSString *SQL_UpdateTaskData = [NSString stringWithFormat:SQL_UPDATE_TASK, 10, 10, 10, 10,@"la",@"la", 10];

[self.sqliteDao excuteSQL:SQL_UpdateTaskData];

5.Delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14

//NSString *SQL_DeleteAllTaskData = [NSString stringWithFormat:SQL_DELETE_ALL,TABLE_TASK];

// delete one record

    NSString *SQL_DeleteTaskData = [NSString stringWithFormat:SQL_DELETE,TABLE_TASK,@"TASK_ID",248];

    BOOL deleteSuccess = [self.sqliteDao excuteSQL:SQL_DeleteTaskData];

    if (deleteSuccess) {

        NSLog(@"delete success!");

    }

6.Select

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

NSString *SQL_SelectCategoryData = [NSString stringWithFormat:SQL_SELECT_ALL,TABLE_TASK];

    NSMutableArray *selectedTasks = [NSMutableArray array];

    // select *

    [self.sqliteDao excuteQuerySQL:SQL_SelectCategoryData resultBlock:^(FMResultSet *rsSet) {

        while ([rsSet next]) {

            NSMutableDictionary *dict = [[NSMutableDictionary alloc] init];

            [dict setValue:[rsSet objectForColumnName:@"TASK_ID"] forKey:@"TASK_ID"];

            [dict setValue:[rsSet objectForColumnName:@"ROOM_ID"] forKey:@"ROOM_ID"];

            [dict setValue:[rsSet objectForColumnName:@"TASK_INFO"] forKey:@"TASK_INFO"];

            [dict setValue:[rsSet objectForColumnName:@"TASK_NUMBER"] forKey:@"TASK_NUMBER"];

            [dict setValue:[rsSet objectForColumnName:@"TASK_STATUS"] forKey:@"TASK_STATUS"];

            [dict setValue:[rsSet objectForColumnName:@"TASK_COORDINATE"] forKey:@"TASK_COORDINATE"];

            [dict setValue:[rsSet objectForColumnName:@"TASK_BEDSIDE"] forKey:@"TASK_BEDSIDE"];

            [selectedTasks addObject:dict];

        }

    }];

    NSLog(@"selected all tasks:%@",selectedTasks);

    // select by ID

    NSString *SQL_SelectByID = [NSString stringWithFormat:SQL_SELECT_PARAMTER,TABLE_CATEGORY,@"CATEGORY_ID",6];

    [self.sqliteDao excuteQuerySQL:SQL_SelectByID resultBlock:^(FMResultSet *rsSet) {

        while ([rsSet next]) {

            // save as model data

            MZCategory *selectedCategory    = [MZCategory new];

            selectedCategory.categoryID     = [rsSet objectForColumnName:@"CATEGORY_ID"];

            selectedCategory.parentID       = [rsSet objectForColumnName:@"PARENT_ID"];

            selectedCategory.level          = [rsSet objectForColumnName:@"LEVEL"];

            selectedCategory.categoryName   = [rsSet objectForColumnName:@"CATEGORY_NAME"];

            selectedCategory.categoryImg    = [rsSet objectForColumnName:@"CATEGORY_IMG"];

            selectedCategory.categoryRank   = [rsSet objectForColumnName:@"CATEGORY_RANK"];

            selectedCategory.categoryType   = [rsSet objectForColumnName:@"CATEGORY_TYPE"];

            selectedCategory.categoryStatus = [rsSet objectForColumnName:@"CATEGORY_STATUS"];

            selectedCategory.categoryRemark = [rsSet objectForColumnName:@"CATEGORY_REMARK"];

            NSLog(@"select by categoryID:%d,parentID:%d,level:%d,categoryName:%@,categoryImg:%@,categoryRank:%d,categoryType:%d,categoryStatus:%d,categoryRemark:%@",[selectedCategory.categoryID intValue],[selectedCategory.parentID intValue],[selectedCategory.level intValue],selectedCategory.categoryName,selectedCategory.categoryImg,[selectedCategory.categoryRank intValue],[selectedCategory.categoryType intValue],[selectedCategory.categoryStatus intValue],selectedCategory.categoryRemark);

        }

    }];

说在后面

本人技术浅薄，对数据库的理解也不够深入透彻，在此写下微末经验，以期为后来之初学者提供些许便利，若有一人觉得此项目并非通篇胡言，也不枉我码字之劳累，最后奉上Github项目地址，如需参考不妨前往下载。