Database operations (using FMDB)

SQLite (http://www.sqlite.org/docs.html) is a lightweight relational database. The IOS SDK supports SQLite very early, and when used, it only needs to join Libsqlite3.dylib dependencies and introduce sqlite3.h header files. However, the native SQLite API is quite unfriendly in use and inconvenient when used. As a result, there is a series of libraries that encapsulate the SQLite API in the open source community, while Fmdb (Https://github.com/ccgus/fmdb) is a great person in the open source community.

Fmdb Use Example:

nsstring* Docsdir =[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) lastobject]; NSString* DBPath = [Docsdir stringbyappendingpathcomponent:@"User.sqlite"]; Fmdatabase* db =[Fmdatabase Databasewithpath:dbpath];//Open Database[DB open];//results after queryFmresultset *rs = [db executeQuery:@"SELECT * from people"]; while([Rs next]) {NSLog (@"%@ %@", [Rs stringforcolumn:@"FirstName"], [rs stringforcolumn:@"LastName"]);} [DB close];

The database code after using Fmdb is clear and more elegant than the native API. In addition, the Fmdb is compatible with both arc and non-ARC projects and automatically adjusts the associated memory management code according to the engineering configuration.

Instructions for use:

This instruction is mainly translated from Fmdb's GitHub project documentation: HTTPS://GITHUB.COM/CCGUS/FMDB

Introduction of related Documents

First clone the Fmdb from GitHub and copy the following files into your project:

FMDatabase.hFMDatabase.mFMDatabaseAdditions.hFMDatabaseAdditions.mFMDatabasePool.hFMDatabasePool.mFMDatabaseQueue.hFMData Basequeue.mfmresultset.hfmresultset.m

Set up a database

Creating a database requires only the following line, and when the file does not exist, Fmdb creates one yourself . If you pass in an empty string: @ "", Fmdb will create the database in the temporary file directory, and if the parameter you pass in is NULL, it will establish a database in memory.

Fmdatabase *db = [fmdatabase databasewithpath:@ "/tmp/tmp.db"];

Open Database

Using the following statement, if open fails, it may be insufficient permissions or insufficient resources . Usually after you open the operation, you need to call the Close method to close the database.

if (! [DB Open]) {    //    return ;} // some operation [DB close];

Perform an update operation

In addition to the select operation, the others are update operations. The update operation uses the following method, if there is an error, can be obtained in the error parameter.

[Fmdatabase ExecuteUpdate:error:withArgumentsInArray:orVAList:]

Perform a query operation

An example of a query operation is shown below. Note: You need to call the next method of Fmresultset even if the result of the operation is only one row.

 mresultset *s = [db Executequery:@ " select * From myTable   ];  while   ([s next] { // retrieve values for each record  }fmresultset  *s = [db Executequery:@ " select COUNT (*) from myTable  "  ];  if   ([s next] { int  totalcount = [s Intforcolu Mnindex:0   

Fmdb provides several ways to obtain different types of data:

IntForColumn:longForColumn:longLongIntForColumn:boolForColumn:doubleForColumn:stringForColumn:dateForColumn: DataForColumn:dataNoCopyForColumn:UTF8StringForColumnIndex:objectForColumn:

Typically, you do not need to close fmresultset because the Fmresultset is automatically closed when the associated database is closed.

Data parameters

Typically, you can follow standard SQL statements, using the? Represents a parameter that executes a statement, such as:

1	INSERT INTO myTable VALUES (?, ?, ?)

Then, we can call the executeupdate method to pass in the specific parameters of the referring generation, usually with variable-length parameters to be passed in, as follows:

12	NSString *sql = @"insert into User (name, password) values (?, ?)";[db executeUpdate:sql, user.name, user.password];

It should be noted here that the parameter must be a subclass of NSObject, so a basic type like Int,double,bool needs to be encapsulated into the corresponding wrapper class, as follows:

1234	Code class= "OBJC" >//error, 42 cannot be used as parameter [ db executeupdate:@ "INSERT into MyTable VALUES (?)" 42]; //correct, the 42 package into NSNumber class [db Executeupdate:@ "INSERT into MyTable VALUES (?)" [nsnumber numberwithint:42< Span class= "P" >];

Thread Safety

If our app requires a multi-threaded database, then we need to use Fmdatabasequeue to ensure thread safety. Remember that you cannot co-fmdatabase objects in multiple threads and use them concurrently in multiple threads, and that the class itself is not thread-safe, which can cause problems with data clutter.

Using Fmdatabasequeue is simple, first fmdatabasequeue with a database file address, and then you can pass a closure (block) into the Indatabase method. Operate the database in a closure without directly participating in the management of the fmdatabase.

1. Get the path to the database file

NSString *doc = [Nssearchpathfordirectoriesindomains (nsdocumentdirectory, Nsuserdomainmask, YES) lastObject];

NSString *filename = [Doc stringbyappendingpathcomponent:@ "Persons.sqlite"];

//created, preferably in a singleton class FileName: Database file addressFmdatabasequeue *queue =[Fmdatabasequeue databasequeuewithpath:filename];//Use[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]) {//... ..    }}];//If you want to support transactions[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]];}];

In order to see the data in SQLite, a good graphical interface of the database management program is essential. MySQL has phpmyadmin, so what about SQLite?

Some people use a plugin called SQLite manager in Firefox, and after installing this plugin, you can open a database file with the suffix named SQLite directly. SQLite Manager provides a graphical interface to perform data query or change operations. As shown in the following:

I am using: Navicat Premium

Summarize

Fmdb the SQLite API is very friendly encapsulation, easy to use, for those who use the pure SQLite API for database operations, you can consider migrating it to Fmdb based, which for the future development and maintenance of database-related functions, can improve a lot of efficiency.