iOS-SQLite3和FMDB使用

1 SQLite3

1> SQLite的语法

管理工具：Navicat
 
数据类型
{
  text     字段
  integer  数字
  real     浮点
  bolb     二进制  可以完整保存数据，例如照片
}
  
ddl(结构定义 CREATE ALTER DROP)
{
  
CREATE
CREATE TABLE 表的名字（每个字段的定义 字段名 数据类型...）
CREATE TABLE t_cjb(xiaoming text NOT NULL,
                   yuwen integer NOT NULL)
   
  
}
dml(数据操作 INSERT DELETE UPDATE SELECT)
{
INSERT
INSERT INTO 表名（字段列表） values(值的列表)
INSERT INTO studnet(id,name) VALUES (3,'linf') integer 可以不用''
 
UPDATE
UPDATE 表名 SET 字段名=值...WHERE 条件
UPDATE studnet SET telnum = '13100000000' WHERE name = 'xiaoming'
 
DELETE
DELETE FROM 表名 WHERE 条件
DELETE FROM studnet WHERE name = 'xiaoming'
 
SELECT
SELECT 字段列表 FROM 表名 WHERE 条件 ORDER BY 字段名
SELECT telnum (AS 电话号码) FROM studnet WHERE name = 'xiaoming'
SELECT * FROM studnet WHERE name = 'xiaoming'       //显示所有列
SELECT * FROM studnet WHERE name LIKE '张%'          //模糊查询,like
SELECT * FROM studnet WHERE age>20 AND age<30        //显示所有列,*
SELECT * FROM studnet WHERE age>20 ORDER BY age      //排序，大于20的年龄从小到大排
SELECT * FROM studnet WHERE age>20 ORDER BY age DESC//排序，大于20的年龄从大到小排
 
统计函数(数据类型为intege real)
SELECT count(*) FROM studnet WHERE age>20 AND age<30 //求个数
SELECT sum(age) FROM studnet                         //求总数
SELECT avg(age) FROM studnet                         //求平均数
SELECT *,yuwen+shuxue+yingyu AS sum FROM student    //各个列总和

在iOS中使用SQLite3，首先要添加库文件libsqlite3.dylib和导入主头文件

2>创建数据库和创表

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

    // 打开数据库(连接数据库)

    NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"shops.sqlite"];

    // 如果数据库文件不存在, 系统会自动创建文件自动初始化数据库

    int status = sqlite3_open(filename.UTF8String, &_db);

    if (status == SQLITE_OK) { // 打开成功

        NSLog(@"打开数据库成功");

         

        // 创表

        const char *sql = "CREATE TABLE IF NOT EXISTS t_shop (id integer PRIMARY KEY, name text NOT NULL, price real);";

        char *errmsg = NULL;

        sqlite3_exec(self.db, sql, NULL, NULL, &errmsg);

        if (errmsg) {

            NSLog(@"创表失败--%s", errmsg);

        }

    } else { // 打开失败

        NSLog(@"打开数据库失败");

    }

3>CUD

    sqlite3_exec()可以执行任何SQL语句，比如创表、更新、插入和删除操作。但是一般不用它执行查询语句，因为它不会返回查询到的数据

?

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

    //插入数据

    NSString *sql = [NSString stringWithFormat:@"INSERT INTO t_shop(name, price) VALUES ('%@', %f);", self.nameField.text, self.priceField.text.doubleValue];

    sqlite3_exec(self.db, sql.UTF8String, NULL, NULL, NULL);

     

    //带占位符插入数据

    char *sql = "insert into t_person(name, age) values(?, ?);";

    sqlite3_stmt *stmt = NULL; // stmt是用来取出查询结果的

    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) { //-1是自动计算sql语句的长度

        sqlite3_bind_text(stmt, 1, "母鸡", -1, NULL);

        sqlite3_bind_int(stmt, 2, 27);

    }

    if (sqlite3_step(stmt) != SQLITE_DONE) {

        NSLog(@"插入数据错误");

    }

    sqlite3_finalize(stmt);

 

代码解析：

sqlite3_prepare_v2()返回值等于SQLITE_OK，说明SQL语句已经准备成功，没有语法问题

 

   sqlite3_bind_text()：大部分绑定函数都只有3个参数

第1个参数是sqlite3_stmt *类型

第2个参数指占位符的位置，第一个占位符的位置是1，不是0

第3个参数指占位符要绑定的值

第4个参数指在第3个参数中所传递数据的长度，对于C字符串，可以传递-1代替字符串的长度

第5个参数是一个可选的函数回调，一般用于在语句执行后完成内存清理工作

4>查询数据

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

//

    sqlite3_step()返回SQLITE_ROW代表遍历到一条新记录

    sqlite3_column_*()用于获取每个字段对应的值，第2个参数是字段的索引，从0开始

    

    const char *sql = "SELECT name,price FROM t_shop;";

    // stmt是用来取出查询结果的

    sqlite3_stmt *stmt = NULL;

    // 准备

    int status = sqlite3_prepare_v2(self.db, sql, -1, &stmt, NULL);

    if (status == SQLITE_OK) { // 准备成功 -- SQL语句正确

        while (sqlite3_step(stmt) == SQLITE_ROW) { // 成功取出一条数据

            const char *name = (const char *)sqlite3_column_text(stmt, 0);

            const char *price = (const char *)sqlite3_column_text(stmt, 1);

             

            HMShop *shop = [[HMShop alloc] init];

            shop.name = [NSString stringWithUTF8String:name];

            shop.price = [NSString stringWithUTF8String:price];

            [self.shops addObject:shop];

        }

    }

5>配合UISearchBar的模糊查询

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

#pragma mark - UISearchBarDelegate

- (void)searchBar:(UISearchBar *)searchBar textDidChange:(NSString *)searchText

{

    [self.shops removeAllObjects];

     

    NSString *sql = [NSString stringWithFormat:@"SELECT name,price FROM t_shop WHERE name LIKE '%%%@%%' OR  price LIKE '%%%@%%' ;", searchText, searchText];

    // stmt是用来取出查询结果的

    sqlite3_stmt *stmt = NULL;

    // 准备

    int status = sqlite3_prepare_v2(self.db, sql.UTF8String, -1, &stmt, NULL);

    if (status == SQLITE_OK) { // 准备成功 -- SQL语句正确

        while (sqlite3_step(stmt) == SQLITE_ROW) { // 成功取出一条数据

            const char *name = (const char *)sqlite3_column_text(stmt, 0);

            const char *price = (const char *)sqlite3_column_text(stmt, 1);

             

            HMShop *shop = [[HMShop alloc] init];

            shop.name = [NSString stringWithUTF8String:name];

            shop.price = [NSString stringWithUTF8String:price];

            [self.shops addObject:shop];

        }

    }

     

    [self.tableView reloadData];

}

2 FMDA

1>什么是FMDA

FMDB以OC的方式封装了SQLite的C语言API

FMDB的优点
使用起来更加面向对象，省去了很多麻烦、冗余的C语言代码
对比苹果自带的Core Data框架，更加轻量级和灵活
提供了多线程安全的数据库操作方法，有效地防止数据混乱

FMDB的github地址
https://github.com/ccgus/fmdb

2>FMDB有三个主要的类

1)FMDatabase
一个FMDatabase对象就代表一个单独的SQLite数据库
用来执行SQL语句

2)FMResultSet
使用FMDatabase执行查询后的结果集

3)FMDatabaseQueue
用于在多线程中执行多个查询或更新，它是线程安全的 

3>创建或打开数据库

?

1

2

3

4

FMDatabase *db = [FMDatabase databaseWithPath:path];

if (![db open]) {

    NSLog(@"数据库打开失败！");

}

空字符串@"" 会在临时目录创建一个空的数据库。当FMDatabase连接关闭时，数据库文件也被删除
nil会创建一个内存中临时数据库，当FMDatabase连接关闭时，数据库会被销毁

4>执行更新

在FMDB中，除查询以外的所有操作，都称为“更新”
create、drop、insert、update、delete等       

使用executeUpdate:方法执行更新

?

1

2

3

- (BOOL)executeUpdate:(NSString*)sql, ...

- (BOOL)executeUpdateWithFormat:(NSString*)format, ...

- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments

示例  FMDB中SQL语句字符串不需要加''

?

1

2

3

4

5

6

7

8

9

10

11

//插入数据

        [db executeUpdate:@"insert into test (a, b, c, d, e) values (?, ?, ?, ?, ?)" ,

            @"hi'", // look!  I put in a ', and I'm not escaping it!

            [NSString stringWithFormat:@"number %d", i],

            [NSNumber numberWithInt:i],

            [NSDate date],

            [NSNumber numberWithFloat:2.2f]];

    }

     

//更新数据

[db executeUpdate:@"UPDATE t_student SET age = ? WHERE name = ?;", @20, @"Jack"]

5>执行查询

查询方法

?

1

2

3

- (FMResultSet *)executeQuery:(NSString*)sql, ...

- (FMResultSet *)executeQueryWithFormat:(NSString*)format, ...

- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)arguments

示例.遍历结果集

?

1

2

3

4

5

6

7

8

FMResultSet *rs = [db executeQuery:@"SELECT * FROM t_student"];

 

// 遍历结果集

while ([rs next]) {

    NSString *name = [rs stringForColumn:@"name"];

    int age = [rs intForColumn:@"age"];

    double score = [rs doubleForColumn:@"score"];

}

6>多线程异步执行多次数据操作

FMDatabase这个类是线程不安全的，如果在多个线程中同时使用一个FMDatabase实例，会造成数据混乱等问题
为了保证线程安全，FMDB提供方便快捷的FMDatabaseQueue类

1)FMDatabaseQueue的创建

?

1

FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:path];

2)简单使用

?

1

2

3

4

5

6

7

8

9

10

[queue inDatabase:^(FMDatabase *db) {

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jack"];

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Rose"];

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jim"];

     

    FMResultSet *rs = [db executeQuery:@"select * from t_student"];

    while ([rs next]) {

        // …

    }

}];

3)使用事务

?

1

2

3

4

5

6

7

8

9

10

11

12

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jack"];

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Rose"];

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jim"];

     

    FMResultSet *rs = [db executeQuery:@"select * from t_student"];

    while ([rs next]) {

        // …

    }

}];

事务回滚

*rollback = YES;