FMDB for iOS development

FMDB for iOS development
As a lightweight database, sqlite is widely used in many embedded devices because it occupies a small amount of memory. IOS SDK has started to support SQLite for a long time. We only need to add libsqlite3.dylib and introduce the header file sqlite3.h. However, because native sqlite APIs are not very friendly, therefore, it is generally encapsulated with an open source FMDB, which is the most popular. FMDB main Class 1. FMDatabase-represents a separate SQLite database. The command used to execute SQLite. 2. FMResultSet-indicates the result set after the FMDatabase executes the query. 3. FMDatabaseQueue-This class ensures thread safety when you perform operations in multiple threads. Create an FMDB Database: when creating an FMDatabase object, the parameter must be the path of the SQLite database file. This path can be one of the following three types: 1 .. file path. This file path does not need to be saved. If it does not exist, it is automatically created. 2. Empty string (@""). Indicates that a temporary database will be created in the temporary directory. When the FMDatabase link is closed, the file will also be deleted. 3. NULL. A memory database will be created. Similarly, when the FMDatabase connection is closed, the data will be destroyed. Memory Database: databases are usually stored on disks. However, we can also store the database in the memory. The advantage of the memory database is that the operation speed is faster. After all, the memory access time is lower than the access disk, but the memory database has the following defects: because the memory database is not persistent, the database will disappear immediately after it is shut down, and data will be lost if it is powered off or the program crashes. If read/write mutex processing is not supported, You need to manually add locks; cannot be accessed by other processes. Temporary Database: the temporary database is very similar to the memory database. The temporary database created by connecting the two databases is independent of each other. After the connection is closed, the temporary database will automatically disappear, the underlying files will also be deleted automatically. Although the disk file is created to store data information in the temporary database, the temporary database will usually reside in the memory like the memory database. The only difference is that, when the data volume in the temporary database is too large, SQLite writes some data in the temporary database to the disk file to ensure that more memory is available for other operations, memory databases always store data in the memory. Create a database: FMDatabase * db = [FMDatabase databaseWithPath: dbPath]; before performing database operations, you must open the database first. If the resources or permissions are insufficient, you cannot open or create a database, will cause opening failure. The following is an example of creating and opening a database: copy the code NSArray * paths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES); NSString * documentDirectory = [paths objectAtIndex: 0]; // dbPath: database path, which is stored in Document. NSString * dbPath = [documentDirectory stringByAppendingPathComponent: @ "MYTest. db "]; // create a database instance db here: if the path does not exist" MYTest. database file, sqlite will automatically create "MYTest. db "FMDatabase * db = [FMDatabase databaseWithPath: dbPath]; if (! [Db open]) {NSLog (@ "cocould not open db. "); return;} the copy code UPDATE operation is regarded as an UPDATE operation, including CREATE, UPDATE, INSERT, ALTER, COMMIT, BEGIN, DETACH, DELETE, DROP, END, EXPLAIN, VACUUM, and REPLACE. CREATE a TABLE: [db executeUpdate: @ "create table myTable (Name text, Age integer)"]; INSERT [db executeUpdate: @ "insert into myTable (Name, Age) VALUES (?,?) ", @" Jason ", [NSNumber numberWithInt: 20]; UPDATE [db executeUpdate: @" UPDATE myTable SET Name =? WHERE Name =? ", @" John ", @" jason "];. DELETE [db executeUpdate: @" delete from myTable WHERE Name =? ", @" Jason "]; The SELECT command for the query operation is query. 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. while loop: FMResultSet * s = [db executeQuery: @ "SELECT * FROM myTable"]; while ([s next]) {// extract information from each record} close the database when the database is used up, you should close the database connection to release the resources used by SQLite. [Db close]; Generally, you can use the following standard SQL statement? The execution statement parameters, such as insert into myTable VALUES (?, ?, ?) Then, we can call the executeUpdate method? The specified parameter is usually passed in using the variable length parameter, as follows: NSString * SQL = @ "insert into myTable (name, password) values (?, ?) "; [Db executeUpdate: SQL, user. name, user. password]; note that the parameter must be a subclass of NSObject, so the basic types such as int, double, and bool need to be encapsulated accordingly [db executeUpdate: @ "insert into myTable VALUES (?) ", [NSNumber numberWithInt: 42]; multi-threaded operations because the FMDatabase object itself is not thread-safe, FMDatabaseQueue must be used to perform related operations to avoid errors during multi-threaded operations. You only need to use a database file address to initialize the FMDatabaseQueue, and then input a block to the inDatabase. Even if multiple threads operate simultaneously, the queue ensures that these operations can be performed in sequence, ensure thread security. Create a queue: FMDatabaseQueue * queue = [FMDatabaseQueue databaseQueueWithPath: aPath]; usage: copy the Code [queue inDatabase: ^ (FMDatabase * db) {[db executeUpdate: @ "insert into myTable VALUES (?) ", [NSNumber numberWithInt: 1]; [db executeUpdate: @" insert into myTable VALUES (?) ", [NSNumber numberWithInt: 2]; FMResultSet * rs = [db executeQuery: @" select * from foo "]; while ([rs next]) {… }]; Copy the code. The transaction can be processed like this: copy the Code [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 (somethingWrongHappened) {* rollback = YES; return;}/etc... [Db executeUpdate: @ "insert into myTable VALUES (?) ", [NSNumber numberWithInt: 4];}];