iOS Development Database-fmdb

Objective

Fmdb is the OC of the C language API, the use of more object-oriented, save a lot of trouble, redundant C language code, compared to Apple's own core data framework, more lightweight and flexible, provide multi-thread security database operation method, effectively prevent data confusion Fmdb is also compatible with both arc and non-ARC projects and automatically adjusts the associated memory management code according to the project configuration at compile time.

How to use

Fmdb has three main classes

• The fmdatabase represents a separate SQLite database. The command used to perform sqlite.
• Fmresultset represents the result set after Fmdatabase executes the query
• Fmdatabasequeue You should use this class if you want to execute multiple queries or updates in multiple threads. This is thread-safe.

Database creation

To create a Fmdatabase object by specifying the SQLite database file path

Fmdatabase *db = [Fmdatabase Databasewithpath:path]; if (! [DB Open]) {  NSLog (@ " database open failed! ");}

When you create a Fmdatabase object, the parameter is the SQLite database file path. The path can be one of the following three:

1, the specific file path

• The file path does not need to be real, and is created automatically if it does not exist.

2, empty string @ ""

• Indicates that an empty database is created in the temp directory, and the database file is deleted when the fmdatabase link is closed.

3, NULL

• An in-memory staging database is created and the database is destroyed when the Fmdatabase connection is closed

Open Database

The database must be open before interacting with the database. If the resource or permissions are insufficient to open or create the database, it will cause open failure, return bool type

if (! [DB Open]) {            [db release];            return ;     }

Perform the update

• All commands that are not select commands are treated as updates. This includes CREATE, UPDATE, Insert,alter,commit, BEGIN, DETACH, DELETE, DROP, END, EXPLAIN, VACUUM, and REPLACE (etc.).
  Simply put, a command that does not start with a select is an update command.
• Performs an update that returns a bool value. Yes indicates successful execution, otherwise it indicates that there are errors. You can call the-lasterrormessage and-lasterrorcode methods to get more information.

To perform an update using the Executeupdate: method

-(BOOL) executeupdate: (nsstring*) SQL, ... -(BOOL) Executeupdatewithformat: (nsstring*) format, ... -(BOOL) Executeupdate: (nsstring*) SQL Withargumentsinarray: (Nsarray *) arguments

Example

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

Execute Query

The Select command is the query, and the method to execute the query begins with-excutequery.

Query method

-(Fmresultset *) ExecuteQuery: (nsstring*) SQL, ... -(Fmresultset *) Executequerywithformat: (nsstring*) format, ... -(Fmresultset *) ExecuteQuery: (NSString *) SQL Withargumentsinarray: (Nsarray *) arguments

When the query is executed, the error returns nil if the Fmresultset object is successfully returned. Equivalent to performing an update, supports the use of the nserror** parameter. You can also use-lasterrorcode and-lasterrormessage to learn about error messages.

In order to traverse the query results, you can use the while loop. You also need to know how to jump to the next record. Using Fmdb, it's easy to implement, like this:

Fmresultset *s = [db executeQuery:@ "select * from myTable"];     while ([s next]) {       //retrieve values for each record   }

You must always call-[fmresultset next] before you access the query return value, even you only want a record:

Fmresultset *s = [db executeQuery:@ "Select COUNT (*) from myTable"];    if ([s next]) {        int totalcount = [s Intforcolumnindex:0];   }

Fmresultset provides a number of ways to get the values in the desired format:

IntForColumn:longForColumn:longLongIntForColumn:boolForColumn:doubleForColumn:stringForColumn:dataForColumn: DataNoCopyForColumn:UTF8StringForColumnIndex:objectForColumn:

These methods also include the method {Type}forcolumnindex, where the parameter is the index of the column of the query result set.
You do not need to call [Fmresultset Close] to close the result set, which is automatically closed when a new result set is generated, or when its database is closed.

Close the database

When you are finished using the database, you should-close to close the database connection to release the resources that SQLite uses.
[DB close];

Using Fmdatabasequeue and Thread safety

It is unwise to use one Fmdatabase instance in multiple threads at the same time. Now you can create a Fmdatabase object for each thread. Do not let multiple threads share the same instance, it cannot be used concurrently in multiple threads. If this happens, the bad things often happen, the program crashes from time to time, or reports anomalies, or meteors fall from the sky and hit your Mac Pro. In short, it collapsed. Therefore, do not initialize the Fmdatabase object and then use it in multiple threads. Please use Fmdatabasequeue, it is your friend and will help you. Here's how to use it:
Create a queue first

FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath];

This uses

[Queue indatabase:^ (Fmdatabase *db) {[DB executeupdate:@"INSERT into MyTable VALUES (?)", [NSNumber Numberwithint:1]]; [DB executeupdate:@"INSERT into MyTable VALUES (?)", [NSNumber Numberwithint:2]]; [DB executeupdate:@"INSERT into MyTable VALUES (?)", [NSNumber Numberwithint:3]]; Fmresultset*rs = [db executeQuery:@"select * from foo"];  while([Rs next]) {...} }];

Like this, it's easy to wrap a simple task into a transaction.

[Queue intransaction:^ (Fmdatabase *db, BOOL *rollback) {[DB executeupdate:@"INSERT into MyTable VALUES (?)", [NSNumber Numberwithint:1]]; [DB executeupdate:@"INSERT into MyTable VALUES (?)", [NSNumber Numberwithint:2]]; [DB executeupdate:@"INSERT into MyTable VALUES (?)", [NSNumber Numberwithint:3]]; if(whoopssomethingwronghappened) {*rollback = YES;return; }           // etc...[DB executeupdate:@"INSERT into MyTable VALUES (?)", [NSNumber Numberwithint:4]]; }];

Fmdatabasequeue will run in a serialized queue block. So if you call Fmdatabasequeue's method from multiple threads at the same time, they will execute the instructions they receive. So that queries and updates do not affect each other, each one is happy.

code Example

FMDBDataBase.h

//FMDBDataBase.h#import<Foundation/Foundation.h>@interfaceFmdbdatabase:nsobject//how to get a singleton for a database management object+ (Fmdbdatabase *) shareddatabase;//Close the database- (void) closeDataBase;//emptying the database-(BOOL) deletedatabase;//Insert a new record into the Search history table-(BOOL) Insertsearchtext: (NSString *) SearchText;//queries whether the database contains the current search record-(BOOL) Isexistsearchtext: (NSString *) SearchText;//get all Search Records-(Nsmutablearray *) Getallsearchtext;//Delete all search Records-(BOOL) deleteallsearchtext;@end

Fmdbdatabase.m

#import "FMDBDataBase.h"#import "FMDB.h"@interfacefmdbdatabase () @property (nonatomic, strong) Fmdatabase*fmdatabase;@end@implementationFmdbdatabase+ (Fmdbdatabase *) shareddatabase{StaticFmdbdatabase *fmdbdatabase =Nil; Staticdispatch_once_t Oncetoken; Dispatch_once (&oncetoken, ^{fmdbdatabase=[[Fmdbdatabase alloc] init];    }); returnfmdbdatabase;}-(instancetype) init{if(self =[Super Init]) {_fmdatabase=[Fmdatabase databasewithpath:[self Getdatabasepath]; //NULL if database open failure is returned        if(![_fmdatabase Open]) {            returnNil; }    }    //If the database opens successfully creating the table//Create a Search history tableNSString *searchhistorysql =@"CREATE TABLE IF not EXISTS t_history (t_id integer PRIMARY KEY autoincrement, searchtext text not NULL, age integer Not NULL)"; BOOL Issuc=[_fmdatabase Executeupdate:searchhistorysql]; if(ISSUC) {NSLog (@"Create success! "); }    returnSelf ;}-(NSString *) getdatabasepath{//1. Get the path to the database fileNSString *doc =[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) lastobject];//nsstring *doc = [Nshomedirectory () stringbyappendingpathcomponent:@ "Documents"];NSString *filename = [Doc stringbyappendingpathcomponent:@"Fmdb.sqlite"]; returnFileName;}//emptying the database-(BOOL) deletedatabase{nsstring*sql =@"DELETE from T_history"; BOOL Issuc=[_fmdatabase Executeupdate:sql]; if(ISSUC) {NSLog (@"Delete succeeded"); returnYES; } NSLog (@"Delete Failed"); returnNO;}//Close the database- (void) closedatabase{if(_fmdatabase) {[_fmdatabase close]; }}//queries whether the database contains the current search record-(BOOL) Isexistsearchtext: (NSString *) searchtext{NSString*sql =@"SELECT * from T_history"; Fmresultset*results =[_fmdatabase Executequery:sql];  while(results.next) {if([SearchText isequaltostring:[results stringforcolumn:@"SearchText"]]) {            returnYES; }    }    returnNO;}//insert a new record in the Search record table-(BOOL) Insertsearchtext: (NSString *) searchtext{if(!searchtext | | [SearchText isequaltostring:@""] ||[self isexistsearchtext:searchtext]) {NSLog (@"the data is empty or already exists"); returnNO; } nsstring*sql =@"INSERT into T_history (SearchText) VALUES (?)"; //executeupdate: Indeterminate parameters are used to occupy the positionBOOL ISINSERTSUC =[_fmdatabase Executeupdate:sql, SearchText]; if(ISINSERTSUC) {NSLog (@"%@ Insert Success", SearchText); returnYES; } NSLog (@"%@ Insert Failed", SearchText); returnNO;}//get all the search records-(Nsmutablearray *) getallsearchtext{NSString*sql =@"SELECT * from T_history ORDER BY t_id Desc"; //save an array of all dataNsmutablearray *searchtests =[Nsmutablearray array]; Fmresultset*results =[_fmdatabase Executequery:sql];  while(results.next) {NSString*result = [Results stringforcolumn:@"SearchText"];    [Searchtests Addobject:result]; }    returnsearchtests;}//Delete all search Records-(BOOL) deleteallsearchtext{nsstring*sql =@"DELETE from T_history"; BOOL Isdeletesuc=[_fmdatabase Executeupdate:sql]; if(ISDELETESUC) {NSLog (@"Delete succeeded"); returnYES; }    returnNO;}@end

Attention

• If the ID is set to gradual and is set to autogrow, then after deleting the data in the table, reinsert the new data, the ID number is not starting from 0, but the previous ID is numbered.

Reference articles

• Fmdb Official use of documents (translation)
• iOS Development Database Chapter-fmdb Brief Introduction

Welcome to read the previous article:

• Archiving of iOS data stores
• List of preferences and attributes for iOS data storage

Ios-ant-bang Mutual Community 426981364
iOS Technology group 461069757 Welcome to join!

iOS Development Database-fmdb