FMDB How to use

Excellent third-party library, the Readme is also very good, understand the readme, will be used to bring a lot of convenience.

ARC and MRC

The use of ARC or MRC in a project has no effect on using FMDB, and FMDB automatically matches when compiling the project.

Use

There are three important classes in FMDB:

1. FMDatabase: Is a class that provides a SQLite database for executing SQL statements.
2. FMResultSet: A FMDatabase class that executes the results of a query in.
3. FMDatabaseQueue: Queries and updates the classes used in the database under multiple threads.

Database creation

FMDatabaseis created from a SQLite database file path, which can be one of three of the following:

1. The system path of a file. This file may not exist on the disk because it will be created automatically for you if it does not exist.
2. An empty string @"" . An empty database is created in a temporary location, and FMDatabase the database is deleted when the connection is closed.
3. NULL。 A database is created in memory and is destroyed when FMDatabase the connection is closed.

// 创建数据库示例FMDatabase *db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];

Open Database

The database must be open in order to interact with it. If there are not enough resources and permissions to open \ Create the database, the database will fail to open.

Database updates

A statement in an SQL statement SELECT can be called an update operation. Including,,,,,,,,,, CREATE UPDATE INSERT ALTER COMMIT BEGIN DETACH DROP END EXPLAIN VACUUM , REPLACE etc... Typically SELECT , a SQL statement that is not preceded by an UPDATE statement.

A value is returned after the UPDATE statement is executed, indicating that the BOOL YES execution of the UPDATE statement was successful, that NO the return indicates an error, -lastErrorMessage and that more error information can be obtained by calling and the -lastErrorCode method.

Database queries

-executeQuery...perform SELECT a database query operation by calling one of the methods to execute the statement.

After the query operation is performed, if the success returns an FMResultSet object, the reverse is returned nil . -lastErrorMessagethe and -lastErrorCode method can determine why the query failed.

In order to traverse the results of the query, a while() loop is required and then viewed by record. In FMDB, it can be implemented in the following simple way:

FMResultSet *s = [db executeQuery:@"SELECT * FROM myTable"];while ([s next]) { // 每条记录的检索值}

Even if you only need to get one data, you must also call before accessing the query results -[FMResultSet next] .

// 示例FMResultSet *s = [db executeQuery:@"SELECT COUNT(*) FROM myTable"];if ([s next]) {    int totalCount = [s intForColumnIndex:0];}

FMResultSetProvides a number of convenient ways to query data:

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

Each of these methods has a {type}ForColumnIndex: variant that is based on the location of the column to query the data.

In general, it FMResultSet is not necessary -close to manually, because the resulting collection (result set) is freed or the source database shuts down automatically.

Close the database

When the database is queried and the update operation is complete, you need to call -close FMDatabase the connection that closes the database.

// 示例[db close];

Transaction

FMDatabaseYou can start and commit a transaction by calling the method, or you can execute the Start \ End Transaction (begin\end transaction) statement.

Multi-statement and batch processing

FMDatabase-executeStatements:withResultBlock:multiple statements can be executed in a string by means of a method.

Example NSString *sql = @ "CreateTable Bulktest1 (IdInteger PrimaryKey AutoIncrement, XText); " "CreateTable Bulktest2 (IdInteger PrimaryKey AutoIncrement, YText); " "CreateTable Bulktest3 (IdInteger PrimaryKey AutoIncrement, ZText); " "Insertinto Bulktest1 (x)VALUES (' XXX '); "Insertinto Bulktest2 (y)VALUES (' YYY '); "Insertinto Bulktest3 (z)VALUES (select count (*) Span class= "Hljs-keyword" >as count from bulktest1; " "select count (*) as count from bulktest2; " "select count (*) as count from bulktest3; "; Success = [Self.db executestatements:sql withresultblock:^int (nsdictionary *dictionary) {Nsinteger count = [dictionary[ @ "Count"] integervalue]; Xctassertequal (count, 1, @ "expected one record for dictionary%@", dictionary); return 0;}];             

Data processing

When providing SQL statements to FMDB, you should not process any data before inserting, but should use standard SQLite's binding syntax.

// 示例INSERT INTO myTable VALUES (?, ?, ?)

?The question mark is a placeholder for the value that is about to be inserted in SQLite, and the method that FMDB executes the statement accepts multiple parameters (or a collection of parameters, for example, NSArray NSDictionary va_list ) that are correctly escaped.

You can also use named parameter syntax:

// 示例INSERT INTO myTable VALUES (:id, :name, :value)

These arguments must begin with a colon, and SQLite itself supports other characters, but the key inside the dictionary will not contain a colon in the key of your dictionary when it begins with a colon.

// 示例NSDictionary *argsDict = [NSDictionary dictionaryWithObjectsAndKeys:@"My Name", @"name", nil];[db executeUpdate:@"INSERT INTO myTable (name) VALUES (:name)" withParameterDictionary:argsDict];

Therefore, you should not write an error code similar to the following line:

// 错误示例[db executeUpdate:[NSString stringWithFormat:@"INSERT INTO myTable VALUES (%@)", @"this has \" lots of ‘ bizarre \" quotes ‘"]];

And it should be written like this:

// 正确示例[db executeUpdate:@"INSERT INTO myTable VALUES (?)", @"this has \" lots of ‘ bizarre \" quotes ‘"];

All arguments passed to -executeUpdate: the method must be objects. The following notation does not work and causes a crash:

// 错误示例[db executeUpdate:@"INSERT INTO myTable VALUES (?)", 42];

The correct way to insert a number is to wrap the number into an NSNumber object:

// 正确示例[db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:42]];

You can also use -execute*WithFormat: this method to convert a number to a string:

// 转换成字符串示例[db executeUpdateWithFormat:@"INSERT INTO myTable VALUES (@d)", 42];

-execute*WithFormat:These methods can be followed by a format string parameter, the following% percent sign format characters are recognizable: %@ , %c %s %d %D %i %u %U %hi %hu %qi %qu ,,,,,,,,,, ,,,,, %f %g %ld %lu %lld , %llu . Using other format characters can cause unpredictable problems. For some reason, you might need to use a character in your SQL statement % , which should be escaped with a percent sign %% .

Fmdatabasequeue Queue and Thread safety

Using Fmdatabase singleton in multiple threads is an extremely wrong idea, causing each thread to create a Fmdatabase object. Do not use a singleton across threads, or cross multiple threads at the same time, or it will crash or be abnormal.

Therefore, do not instantiate a fmdatabase singleton to use across threads.

Instead, using Fmdatabasequeue, here's how it's used:

First, create the queue.

// 创建 FMdatabaseQueue 示例FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath];

Then use this:

// 示例[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]) { ... }}];

It is also easy to put operations in a transaction, such as:

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

Fmdatabase block code blocks are run on a serial queue, and they still execute sequentially even if the Fmdatabasequeue method is called at the same time as multithreading. This method of querying and updating does not affect other, thread-safe.

Custom SQLite functions based on block

This is possible, examples can be viewed in the Main.m makeFunctionNamed: method.

Swift

FMDB can also be used in Swift projects, which requires the following steps:

1. Drag the FMDB .m and .h all the files into your project.
2. If Xcode prompts you to create a bridging file, you need to click Create. If there are no prompts, and there are no bridging files in the project, you need to add them manually. Click here to view the bridging file for more information
3. In the bridging file, add this line of code:#import "FMDB.h"
4. You can copy files from the Src/extra/swift Extension folder FMDatabaseVariadic.swift to your project, and you can use executeUpdate and executeQuery multi-parameter.

With these steps, you can write the Swift code using Fmdatabase.

ExampleLet Documentsfolder =Nssearchpathfordirectoriesindomains (.Documentdirectory,.Userdomainmask,True) [0]AsStringLet Path = Documentsfolder.stringbyappendingpathcomponent ("Test.sqlite")Let database =Fmdatabase (Path:path)If!database.open () {println"Unable to open database")ReturnIf!database.executeupdate ("CREATE table Test (x text, y text, z text)", Withargumentsinarray:Nil) {println"CREATE TABLE failed:\ (Database.lasterrormessage ())}If!database.executeupdate ("INSERT into Test (x, y, z) VALUES (?,?,?)", Withargumentsinarray: ["A","B","C"]) {println"Insert 1 Table failed:\ (Database.lasterrormessage ())}If!database.executeupdate ("INSERT into Test (x, y, z) VALUES (?,?,?)", Withargumentsinarray: ["E","F","G"]) {println"Insert 2 Table failed:\ (Database.lasterrormessage ())}if let rs = database.executequery ( "Select X, Y, z from Test", Withargumentsinarray: nil) {while Rs.next () {let x = Rs.stringforcolumn ( "x") let y = rs.stringforcolumn (" y ") let z = Rs.stringforcolumn ( "Z") println (  "x = \ (x); y = \ (y), z = \ (z) ")}} else {println ( "Select failed: \ ( Database.lasterrormessage ())}database.close ()          

This article is actually a simple translation of FMDB's README to facilitate the use of FMDB.

RELATED links:

FMDB How to use

FMDB How to use