IOS Study Notes 28-sqlite3 third-party library fmdb

SQLite is a small and lightweight relational database. It is a good choice for mobile devices. Both Android and IOS have built-in SQLite databases. The current version is sqlite3. Using SQLite in IOS is especially troublesome if you use the methods provided by the SDK.
It is inconvenient to use APIs to use databases. Today, let's talk about a third-party library fmdb encapsulated by SQLite APIs for iOS. fmdb encapsulates the APIs in the SDK, it is easy to use and more familiar with it. Https://github.com/ccgus/fmdb.

Fmdb mainly involves two classes: fmdatabase and fmresultset. The former is similar to sqliteopenhelper or sqlitedatabase in Android, And the fmresultset is similar to the cursor in Android and is used to store the result set.

Download the fmdb source code, drag the file to the project, and import the SQLite support library. The project directory is as follows:

Then the demo is complete:

Then let's take a look at how to operate fmdb:

// Click the button to save the operation to the database-(ibaction) savebuttonclicked :( ID) sender {// get the database file in the document folder, if not, create nsstring * docpath = [nssearchpathfordirectoriesindomains (nsdocumentdirectory, nsuserdomainmask, yes) objectatindex: 0]; nsstring * dbpath = [docpath stringbyappendingpathcomponent: @ "user. DB "]; // obtain the database and open fmdatabase * database = [fmdatabase databasewithpath: dbpath]; If (! [Database open]) {nslog (@ "Open Database failed"); return;} // create a table (only update and query operations are performed in fmdb, and update operations are performed when the query is performed) [database executeupdate: @ "create table user (Name text, Gender text, age integer)"]; // insert data bool insert = [database executeupdate: @ "insert into user values (?,?,?) ", Nametextfield. text, gendertextfield. text, agetextfield. text]; If (insert) {uialertview * Alert = [[uialertview alloc] initwithtitle: @ "prompt" message: @ "saved successfully" delegate: Self cancelbuttontitle: @ "OK" otherbuttontitles: nil, nil]; [alert show]; [alert release];} [database close];}

A prompt box is displayed after the operation is successful:

// Click the button and execute the query operation-(ibaction) querybuttontapped :( ID) sender {nsarray * paths = require (nsdocumentdirectory, nsuserdomainmask, yes); nsstring * documentpath = [paths objectatindex: 0]; nsstring * dbpath = [documentpath stringbyappendingpathcomponent: @ "user. DB "]; fmdatabase * database = [fmdatabase databasewithpath: dbpath]; If (! [Database open]) {return;} // you do not need to close cursor as in Android to close fmresultset, fmresultset will also be automatically disabled by fmresultset * resultset = [database executequery: @ "select * from user"]; while ([resultset next]) {nsstring * name = [resultset stringforcolumn: @ "name"]; nsstring * Gender = [resultset stringforcolumn: @ "gender"]; int age = [resultset intforcolumn: @ "Age"]; nslog (@ "Name: % @, Gender: % @, age: % d ", name, gender, age);} [database close]; // release is not required here. // [Database Release];}

Fmresultset also supports the following methods to obtain values:

• intForColumn:
• longForColumn:
• longLongIntForColumn:
• boolForColumn:
• doubleForColumn:
• stringForColumn:
• dateForColumn:
• dataForColumn:
• dataNoCopyForColumn:
• UTF8StringForColumnIndex:
• objectForColumn:

// Execute the condition query operation-(ibaction) querybyconditionbtntapped :( ID) sender {nsarray * paths = require (nsdocumentdirectory, nsuserdomainmask, yes); nsstring * docpath = [paths objectatindex: 0]; nsstring * dbpath = [docpath stringbyappendingpathcomponent: @ "user. DB "]; fmdatabase * database = [fmdatabase databasewithpath: dbpath]; If (! [Database open]) {return;} fmresultset * resultset = [database executequery: @ "select * from user where name =? ", @" Ryan "]; while ([resultset next]) {nsstring * name = [resultset stringforcolumn: @" name "]; nsstring * Gender = [resultset stringforcolumn: @ "gender"]; int age = [resultset intforcolumn: @ "Age"]; nslog (@ "Name: % @, Gender: % @, age: % d ", name, gender, age);} [database close];}

// Execute the update operation-(ibaction) updatebtntapped :( ID) sender {nsarray * paths = require (nsdocumentdirectory, nsuserdomainmask, yes); nsstring * docpath = [paths objectatindex: 0]; nsstring * dbpath = [docpath stringbyappendingpathcomponent: @ "user. DB "]; fmdatabase * database = [fmdatabase databasewithpath: dbpath]; If (! [Database open]) {return;} // The parameter must be a subclass of nsobject, Int, double, or bool, you need to encapsulate it into a corresponding packaging class before you can use bool update = [database executeupdate: @ "update user set name =? Where age =? ", @" Ryantang ", [nsnumber numberwithint: 24]; If (update) {uialertview * Alert = [[uialertview alloc] initwithtitle: @" prompt "message: @ "updated successfully" delegate: Self cancelbuttontitle: @ "OK" otherbuttontitles: nil, nil]; [alert show]; [alert release];} [database close];}

// Execute the delete operation-(ibaction) deletebtntapped :( ID) sender {nsarray * paths = offline (nsdocumentdirectory, nsuserdomainmask, yes); nsstring * docpath = [paths objectatindex: 0]; nsstring * dbpath = [docpath stringbyappendingpathcomponent: @ "user. DB "]; fmdatabase * database = [fmdatabase databasewithpath: dbpath]; If (! [Database open]) {return;} bool Delete = [database executeupdate: @ "delete from user where name =? ", @" Tang "]; If (delete) {uialertview * Alert = [[uialertview alloc] initwithtitle: @" prompt "message: @" deleted "delegate: Self cancelbuttontitle: @ "OK" otherbuttontitles: nil, nil]; [alert show]; [alert release];} [database close];}

If our app requires multi-threaded database operations, you need to use fmdatabasequeue to ensure thread security. Remember that the same fmdatabase object cannot be used in multiple threads simultaneously. This class is not thread-safe, which may cause data confusion and other problems.

It is easy to use fmdatabasequeue. First, a database file address is used to initialize fmdatabasequeue, and then a closure (Block) can be passed into the indatabase method. Operate the database in the closure, instead of directly managing the fmdatabase.

 

-(Void) executedboperation {// obtain the database file in the document folder. If no file exists, create nsstring * docpath = [nssearchpathfordirectoriesindomains (nsdocumentdirectory, nsuserdomainmask, yes) objectatindex: 0]. nsstring * dbpath = [docpath stringbyappendingpathcomponent: @ "user. DB "]; fmdatabasequeue * databasequeue = [fmdatabasequeue databasequeuewithpath: dbpath]; [databasequeue indatabase: ^ (fmdatabase * dB) {[dB executeupdate: @" insert I NTO user values (?,?,?) ", @" Ren ", @" male ", [nsnumber numberwithint: 20] ;}]; [databasequeue close];}

Project source code: Download

To join our QQ group or public account, see: Ryan's
Zone public account and QQ Group

Welcome to my Sina Weibo chat: @ Tang Ren _ Ryan