IOS Study Notes (16)-Database Operations (using FMDB)

The SQLite API originally generated in IOS is quite unfriendly to use and inconvenient to use. As a result, there is a series of SQLite API encapsulation library, such as fmdb, plausibledatabase, sqlitepersistentobjects, fmdb (https://github.com/ccgus/fmdb) is a simple, easy to use package library, this article briefly introduces the use of fmdb.

After the fmdb file is downloaded, you must import the following files in the project and use the libsqlite3.dylib dependency package.

Fmdb is compatible with both arc and non-arc projects and automatically adjusts the relevant Memory Management Code according to the project configuration.

Common fmdb classes:

Fmdatabase: A single SQLite database used to execute SQL statements.
Fmresultset: Execute to query an fmdatabase result set, which is similar to the android cursor.
Fmdatabasequeue: this class is used when multiple threads are used to execute queries and updates.

Create a database:

db = [FMDatabase databaseWithPath:database_path];

1. When the database file does not exist, fmdb creates one by itself.

2. If the input parameter is an empty string: @ "", fmdb will create the database in the temporary file directory. When the database is disconnected, the database file will be deleted.

3. If the input parameter is null, it creates a database in the memory. When the database is disconnected, the database file is deleted.

Open the database:

[db open]

Return bool type.

Close the database:

[db close]

Database addition, deletion, modification, and other operations:

In addition to the query operation, the executeupdate method is executed for all fmdb database operations. This method returns the bool type.

Take a look at the example:

Create a table:

if ([db open]) {        NSString *sqlCreateTable =  [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('%@' INTEGER PRIMARY KEY AUTOINCREMENT, '%@' TEXT, '%@' INTEGER, '%@' TEXT)",TABLENAME,ID,NAME,AGE,ADDRESS];        BOOL res = [db executeUpdate:sqlCreateTable];        if (!res) {            NSLog(@"error when creating db table");        } else {            NSLog(@"success to creating db table");        }        [db close];    }

Add data:

If ([db open]) {NSString * insertSql1 = [NSString stringWithFormat: @ "insert into '% @' ('% @', '% @', '% @') VALUES ('% @', '% @', '% @') ", TABLENAME, NAME, AGE, ADDRESS, @" Zhang San ", @" 13 ", @ "Jinan"]; BOOL res = [db executeUpdate: insertSql1]; NSString * insertSql2 = [NSString stringWithFormat: @ "insert into '% @' ('% @', '% @', '% @') VALUES ('% @', '% @', '% @') ", TABLENAME, NAME, AGE, ADDRESS, @ "Li Si", @ "12", @ "Jinan"]; BOO L res2 = [db executeUpdate: insertSql2]; if (! Res) {NSLog (@ "error when insert db table");} else {NSLog (@ "success to insert db table");} [db close];}

Modify data:

if ([db open]) {        NSString *updateSql = [NSString stringWithFormat:                               @"UPDATE '%@' SET '%@' = '%@' WHERE '%@' = '%@'",                               TABLENAME,   AGE,  @"15" ,AGE,  @"13"];        BOOL res = [db executeUpdate:updateSql];        if (!res) {            NSLog(@"error when update db table");        } else {            NSLog(@"success to update db table");        }        [db close];    }

Delete data:

If ([db open]) {NSString * deleteSql = [NSString stringWithFormat: @ "delete from % @ where % @ = '% @'", TABLENAME, NAME, @ "James"]; BOOL res = [db executeUpdate: deleteSql]; if (! Res) {NSLog (@ "error when delete db table");} else {NSLog (@ "success to delete db table");} [db close];}

Database query operations:

The query operation uses executeQuery and involves FMResultSet.

if ([db open]) {        NSString * sql = [NSString stringWithFormat:                          @"SELECT * FROM %@",TABLENAME];        FMResultSet * rs = [db executeQuery:sql];        while ([rs next]) {            int Id = [rs intForColumn:ID];            NSString * name = [rs stringForColumn:NAME];            NSString * age = [rs stringForColumn:AGE];            NSString * address = [rs stringForColumn:ADDRESS];            NSLog(@"id = %d, name = %@, age = %@  address = %@", Id, name, age, address);        }        [db close];    }

FMDB's FMResultSet provides multiple methods to obtain different types of data:

Multi-thread database operations:

If the application uses multiple threads to operate the database, you need to use FMDatabaseQueue to ensure thread security. An FMDatabase object cannot be used in multiple threads in an application to operate on the database. This will cause database data confusion. To ensure the security of multi-threaded database operations, FMDB uses FMDatabaseQueue. It is very simple to use FMDatabaseQueue. First, a database file address is used to initialize FMDatabaseQueue, and then a closure (block) can be set) passed in the inDatabase method. Operate the database in the closure, instead of directly managing the FMDatabase.

FMDatabaseQueue * queue = [FMDatabaseQueue queue: database_path]; q1 = dispatch_queue_create ("queue1", NULL); q2 = partition ("queue2", NULL); dispatch_async (q1, ^ {for (int I = 0; I <50; ++ I) {[queue inDatabase: ^ (FMDatabase * db2) {NSString * insertSql1 = [NSString stringWithFormat: @ "insert into '% @' ('% @', '% @', '% @') VALUES (?, ?, ?) ", TABLENAME, NAME, AGE, ADDRESS]; NSString * name = [NSString stringWithFormat: @" jack % d ", I]; NSString * age = [NSString stringWithFormat: @ "% d", 10 + I]; BOOL res = [db2 executeUpdate: insertSql1, name, age, @ "Jinan"]; if (! Res) {NSLog (@ "error to inster data: % @", name);} else {NSLog (@ "succ to inster data: % @", name) ;}}] ;}}); dispatch_async (q2, ^ {for (int I = 0; I <50; ++ I) {[queue inDatabase: ^ (FMDatabase * db2) {NSString * insertSql2 = [NSString stringWithFormat: @ "insert into '% @' ('% @', '% @', '% @') VALUES (?, ?, ?) ", TABLENAME, NAME, AGE, ADDRESS]; NSString * name = [NSString stringWithFormat: @" lilei % d ", I]; NSString * age = [NSString stringWithFormat: @ "% d", 10 + I]; BOOL res = [db2 executeUpdate: insertSql2, name, age, @ "Beijing"]; if (! Res) {NSLog (@ "error to inster data: % @", name);} else {NSLog (@ "succ to inster data: % @", name) ;}}] ;}});

Download demo

/**

* @ Author Zhang xingye * http://blog.csdn.net/xyz_lmn* iOS entry group: 83702688

* Android Development Group: 241395671

* My Sina Weibo:@ Zhang xingye TBOW* My mailbox: xy-zhang # 163.com( #-> @)*/

Introduction to third-party encapsulated library of SQLite on iOS

Https://github.com/ccgus/fmdb