Third-party iOS learning 47-FMDB, ios47-fmdb

Third-party iOS learning 47-FMDB, ios47-fmdb

After CocoaPods is installed, the FMDB third-party can be integrated into the project according to CocoaPods instructions. For details, refer to the installation and use of third-party CocoaPods in blog iOS study 46 (general method)

1. FMDB Overview 1> Overview

• The SQLite API originally generated in iOS requires C functions for data storage. As a result, a series of libraries that encapsulate SQLite APIs, such as FMDB, PlausibleDatabase, and SQLitePersistentObjects.

• FMDB is a simple and easy-to-use package library. Therefore, we recommend that you use a third-party framework, FMDB, which is an encapsulation of the libsqlite3 framework. The procedure is similar to that of SQLite, And it processes concurrent multi-threaded operations, therefore, it is thread-safe.

2> Advantages and Disadvantages of FMDB

• Advantages:

It is thread-safe to process multi-threaded concurrent operations;

The C language API of SQLite is encapsulated in OC mode, which is more convenient to use;

FMDB is a lightweight framework with flexible usage.

• Disadvantages:

Because it is encapsulated in the OC language and can only be used during iOS development, there are limitations in implementing cross-platform operations.

3> important FMDB classes

• FMDatabase: An FMDatabase object represents a separate SQLite database used to execute SQL statements.

• FMResultSet: The result set after the query is executed using FMDatabase.

• FMDatabaseQueue: used to execute multiple queries or updates in multiple threads. It is thread-safe.

4> FMDB usage steps

• Step 1: Use CocoaPods to integrate a third party into a project

• Step 2: import the libsqlite3.0 framework and the header file FMDatabase. h

• Step 3: code implementation. Similar to the procedure of using SQLite, create a database path, obtain the database path, open the database, add, delete, modify, and query the database, and close the database.

2. Create databases and data tables in FMDB

• Step 1: Obtain the path of the database file

When creating an FMDatabase object, the parameter is the path of the SQLite database file. This path can be one of the following three methods:

① File path. This file path does not need to exist. If it does not exist, it is automatically created;

② Empty string (@""). An empty database will be created in the temporary directory. When the FMDatabase connection is closed, the file will also be deleted;

③ NULL. An internal database will be created. Similarly, when the FMDatabase connection is closed, the data will be destroyed.

We generally use the first method to obtain the path of the database file. The specific instance code is as follows:

　　 NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];    self.filePath = [documentPath stringByAppendingPathComponent:@"student.sqlite"];    NSLog(@"filePath = %@", self.filePath);

• Step 2: Use the path to initialize the FMDB object

The initialization method used:

　　 + (instancetype)databaseWithPath:(NSString*)aPath

Instance code:

// Step 4: Use the path to initialize the FMDB object self. database = [FMDatabase databaseWithPath: self. filePath];

• Step 3: Open the database before interacting with the database.

If you have insufficient permissions or resources, you cannot open or create a database.

// Execute the statement if (self. database. open) {// create table} only when the database is opened}

• Step 4: Create a table

The method used to execute SQL statements:

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

The return value of this method is a BOOL value. We can determine whether the SQL statement is successfully executed based on the return value.

Instance code:

If (self. database. open) {// table creation statement NSString * createSql = @ "create table if not exists t_student (id integer primary key autoincrement not null, name text not null, age integer not null, sax text not null) "; // execute the table creation statement to create the data table BOOL result = [self. database executeUpdate: createSql]; // determines whether the table is successfully created. if (result) {NSLog (@ "table created successfully ");} else {NSLog (@ "failed to create table ");}}

• Step 5: Shut down the database

Instance code:

// Step 5: close the database [self. database close];

3. Added, deleted, modified, and queried FMDB. 1> FMDB-update execution

All commands that are not the SELECT command are regarded as updates. This includes CREAT, UPDATE, INSERT, ALTER, BEGIN, COMMIT, DETACH, DELETE, DROP, END, EXPLAIN, VACUUM, REPLACE, and so on.

Simply put, any command not starting with SELECT is an update command.

Execute the update and return a BOOL value. YES indicates that the execution is successful; otherwise, an error occurs. You can call the-lastErrorMessage and-lastErrorCode methods to obtain more information.

2> how to execute the update command

• ExecuteUpdate: used for uncertain parameters? To placeholder (the following parameter must be an OC object; it indicates that the statement is over or can be left empty)

Add, delete, and modify code instances:

// Add (insert) Data BOOL result = [self. database executeUpdate: @ "insert into t_student (name, age, sax) values (?, ?, ?) ", @" Xiaoming ", @ 12, @" male "]; // update data BOOL result = [self. database executeUpdate: @" update t_student set name =? Where name =? ", @" Xiaoming ", @" James "]; // delete data BOOL result = [self. database executeUpdate: @" delete from t_student where name =? ", @" Xiaoming "];

• ExecuteUpdateWithFormat: uncertain parameters include % @, % d, and so on (parameters are of the original data type, and execution statements are case insensitive)

Add, delete, and modify code instances:

// Add (insert) Data BOOL result = [self. database executeUpdateWithFormat: @ "insert into t_student (name, age, sax) values (% @, % I, % @);", @ "xiaoming", @ 69, @ "male"]; // update data BOOL result = [self. database executeUpdateWithFormat: @ "update t_student set name = % @ where name = % @", @ "xiaoming", @ "James"]; // delete data BOOL result = [self. database executeUpdateWithFormat: @ "delete from t_student where name = % @", @ "xiaoming"];

• ExecuteUpdate: withArgumentsInArray: array. Use arrays directly.

Add, delete, and modify code instances:

// Add (insert) data [self. database executeUpdate: @ "insert into t_student (name, age, sax) values (?, ?, ?); "WithArgumentsInArray: @ [@" xiaoming ", @ 12, @" male "]; // update data [self. database executeUpdate: @" update t_student set name =? Where name = ?; "WithArgumentsInArray: @ [@" xiaoming ", @" James "]; // delete data [self. database executeUpdate: @" delete from t_student where name = ?; "WithArgumentsInArray: @ [@" xiaoming "];

You can choose one of the above methods based on your habits and needs.

3> FMDB-Data Query ① Overview

The SELECT command is the query, and the method for executing the query starts with-excuteQuery.

If the FMResultSet object is returned successfully when the query is executed, the nil is returned incorrectly. The NSError parameter can be used as the update.

You can also use-lastErrorCode and-lastErrorMessage to obtain the error message.

② FMResultSet

FMResultSet provides many methods to obtain the information of corresponding fields:

IntForColumn:, longForColumn:, longLongIntForColumn:, boolForColumn:, doubleForColumn:, stringForColumn:, dataForColumn:, dataNoCopyForColumn:, UTF8StringForColumnIndex:, objectForColumn:

③ Execute the query statement

Query the entire table

// FMResultSet * resultSet = [self. database executeQuery: @ "select * from t_student"];

Query by condition

// Query FMResultSet * resultSet = [self. db executeQuery: @ "select * from t_student where id <?; ", @ 14];

④ Traversal result set

    while (resultSet.next) {        NSInteger ID = [resultSet intForColumn:@"id"];        NSString *name = [resultSet objectForColumnName:@"name"];        NSInteger age = [resultSet intForColumn:@"age"];        NSString *sax = [resultSet objectForColumnName:@"sax"];                NSLog(@"id = %ld name = %@, age = %ld, sax = %@", ID, name, age, sax);    }

4> complete instance code

# Import "ViewController. h "// Step 1: Introduce the framework and the supported class libraries (libsqlite3.0.tbd) # import <FMDB. h> @ interface ViewController () // declare the database object @ property (nonatomic, strong) FMDatabase * database; // declare the storage path @ property (nonatomic, strong) NSString * filePath; @ end @ implementation ViewController-(void) viewDidLoad {[super viewDidLoad]; // Do any additional setup after loading the view, typically from a nib. [self createTabe];} # HPA Gma mark-create table-(void) createTabe {// Step 1: create an SQL statement NSString * createSql = @ "create table if not exists t_student (id integer primary key autoincrement not null, name text not null, age integer not null, sax text not null) "; // Step 2: Find the storage path NSString * documentPath = [NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex: 0]; // NSLog (@ "document = % @", documentPath); s Elf. filePath = [documentPath stringByAppendingPathComponent: @ "student. sqlite "]; NSLog (@" filePath = % @ ", self. filePath); // Step 3: Use the path to initialize the FMDB object self. database = [FMDatabase databaseWithPath: self. filePath]; // Step 4: perform related operations on the database // The statement if (self. database. open) {BOOL result = [self. database executeUpdate: createSql]; if (result) {NSLog (@ "table created successfully");} else {NSLog (@ "table created failed ");}} // Step 5: Close the database [sel F. database close] ;}# pragma mark-insert-(IBAction) insertIntoAction :( id) sender {// Step 1: Open the database [self. database open]; // Step 2: perform related operations NSArray * nameArray = @ [@ "MBBoy", @ "BoomSky", @ "James"]; for (NSString * name in nameArray) {BOOL result = [self. database executeUpdate: @ "insert into t_student (name, age, sax) values (?, ?, ?) ", Name, @ 69, @" male "]; // integer data cannot be used here. An object type data must be used, such as NSNumber and NSString... [self. database executeUpdate: @ "insert into t_student (name, age, sax) VALUES (?, ?, ?); "WithArgumentsInArray: @ [@" xiaoming ", @ 12, @" male "]; if (result) {NSLog (@" inserted successfully ");} else {NSLog (@ "insertion failed") ;}} [self. database close]; // update data // delete data // Add (insert) Data} # pragma mark-Update-(IBAction) updateAction :( id) sender {[self. database open]; BOOL result = [self. database executeUpdate: @ "update t_student set name =? Where name =? ", @" Xiaoming ", @" James "]; if (result) {NSLog (@" updated successfully ") ;}else {NSLog (@" update failed ");} [self. database close] ;}# pragma mark-delete-(IBAction) deleteAction :( id) sender {[self. database open]; BOOL result = [self. database executeUpdate: @ "delete from t_student where name =? ", @" MBBoy "]; if (result) {NSLog (@" deleted successfully ");} else {NSLog (@" failed to delete ");} [self. database close] ;}# pragma mark-query-(IBAction) selectAction :( id) sender {[self. database open]; // The class FMResultSet * resultSet = [self. database executeQuery: @ "select * from t_student"]; // traverses the expected result content while (resultSet. next) {NSInteger ID = [resultSet intForColumn: @ "id"]; NSString * name = [resultSet objectForColumnName: @ "name"]; NSInteger age = [resultSet intForColumn: @ "age"]; NSString * sax = [resultSet objectForColumnName: @ "sax"]; NSLog (@ "id = % ld name = % @, age = % ld, sax = % @ ", ID, name, age, sax);} [self. database close];} @ end

4. multi-threaded FMDB operations 1> Overview

• 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 (for example, updating and searching the database using two threads at the same time ).

• When multiple threads update the same resource and cause data competition, use the waiting queue (wait until the processing of the current execution ends ).

• Queue-based addition is a common method for FMDB.

• FMDB does not support simultaneous operations by multiple threads. Generally, related operations are performed in serial mode.

2> Create operation queue

Initialization Method Used

+ (instancetype)databaseQueueWithPath:(NSString*)aPath

3> package the operation in the Operation queue

Packaging Method

- (void)inTransaction:(void (^)(FMDatabase *db, BOOL *rollback))block

Add a serial queue to a Block

4> instance code

# Pragma mark-add many students in the form of a queue-(IBAction) insertManyStudent :( id) sender {// Adding students in the form of a queue is a common method of adding students to FMDB // FMDB does not support simultaneous operations by multiple threads. Generally, related operations are implemented in serial mode [self. database open]; // Step 1: Create an operation queue FMDatabaseQueue * queue = [FMDatabaseQueue databaseQueueWithPath: self. filePath]; // ID: whether the operation is successful _ block BOOL isSucceed = YES; // Step 2: package the operation in the Operation queue NSString * insertSql = @ "insert into t_studen (name, age, sax) values (?, ?, ?) "; [Queue inTransaction: ^ (FMDatabase * db, BOOL * rollback) {// serial queue isSucceed & = [db executeUpdate: insertSql, @" Old Wang next door ", @ 38, @ "male"]; isSucceed & = [db executeUpdate: insertSql, @ "Black", @ 18, @ "female"]; isSucceed & = [db executeUpdate: insertSql, @ "-1", @ 23, @ "male"]; if (! IsSucceed) {// The rollback parameter returned by the block is processed (a Boolean pointer) * rollback = YES; return ;} else {NSLog (@ "added successfully as a queue") ;}}]; [self. database close];}