Fmdb with SQLite

Reprint to: http://www.linuxidc.com/Linux/2014-02/97020p16.htm

The native SQLite API for iOS is quite unfriendly in use and inconvenient when used. As a result, a series of libraries to encapsulate the SQLite API, such as FMDB, Plausibledatabase, sqlitepersistentobjects, etc., FMDB (https://github.com/ccgus/ Fmdb) is a concise, easy-to-use package library, this article briefly introduces the Fmdb.

After Fmdb downloads the file, the project must import the following files and use the Libsqlite3.dylib dependency package.

The Fmdb is compatible with both arc and non-ARC projects and automatically adjusts the associated memory management code according to the engineering configuration.

Fmdb Common classes:

Fmdatabase: A single SQLite database for executing SQL statements.

Fmresultset: Executes a query with a fmdatabase result set, which is similar to the cursor on Android.

Fmdatabasequeue: This class is used when multiple threads are executing queries and updates.

To create a database:

db = [Fmdatabase Databasewithpath:database_path];

1, when the database file does not exist, Fmdb will create a.

2, if you pass in a parameter is empty string: @ "", then Fmdb will create the database in the temporary file directory, the database is disconnected, the database file is deleted.

3. If you pass in a parameter that is NULL, it will establish a database in memory and the database file is deleted when the database is disconnected.

Open the database:

[DB Open]

Returns the bool type.

To close the database:

[DB Close]

Database additions and deletions and other operations:

In addition to the query operation, the FMDB database operation executes the Executeupdate method, which returns the bool type.

Take a look at the example:

To create a table:

if ([db Open]) {
NSString *sqlcreatetable = [NSString stringwithformat:@ "CREATE TABLE IF not EXISTS '%@ ' ('%@ ' INTEGER PRIMARY KEY AUTOINC Rement, '%@ ' text, '%@ ' INTEGER, '%@ ' text) ', tablename,id,name,age,address];
BOOL res = [db executeupdate:sqlcreatetable];
if (!res) {
NSLog (@ "error when creating DB table");
} else {
NSLog (@ "Success to creating DB table");
}
[DB close];

}

Add Data:

if ([db Open]) {
NSString *insertsql1= [NSString stringWithFormat:
@ "INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES ('%@ ', '%@ ', '%@ ')",
TABLENAME, NAME, age, ADDRESS, @ "Zhang San", @ "13", @ "Jinan"];
BOOL res = [db EXECUTEUPDATE:INSERTSQL1];
NSString *INSERTSQL2 = [NSString stringWithFormat:
@ "INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES ('%@ ', '%@ ', '%@ ')",
TABLENAME, NAME, age, ADDRESS, @ "John Doe", @ "12", @ "Jinan"];
BOOL res2 = [db EXECUTEUPDATE:INSERTSQL2];

if (!res) {
NSLog (@ "error when insert DB table");
} else {
NSLog (@ "Success to insert DB table");
}
[DB close];

}

To modify the data:

if ([db Open]) {
NSString *updatesql = [NSString stringWithFormat:
@ "UPDATE '%@ ' SET '%@ ' = '%@ ' WHERE '%@ ' = '%@ '",
TABLENAME, age, @ "All", age, @ "13"];
BOOL res = [db executeupdate:updatesql];
if (!res) {
NSLog (@ "error when update db table");
} else {
NSLog (@ "Success to update DB table");
}
[DB close];

}

Delete data:

if ([db Open]) {

NSString *deletesql = [NSString stringWithFormat:
@ "Delete from%@ where%@ = '%@ '",
TABLENAME, NAME, @ "Zhang San"];
BOOL res = [db executeupdate:deletesql];

if (!res) {
NSLog (@ "error when delete db table");
} else {
NSLog (@ "Success to delete db table");
}
[DB close];

}

Database Query Operations:
The query operation uses ExecuteQuery and involves Fmresultset.

if ([db Open]) {
NSString * sql = [NSString stringWithFormat:
@ "SELECT * from%@", TABLENAME];
Fmresultset * rs = [db Executequery:sql];
while ([Rs next]) {
int Id = [Rs intforcolumn:id];
NSString * name = [Rs Stringforcolumn:name];
NSString * age = [Rs stringforcolumn:age];
NSString * address = [Rs stringforcolumn:address];
NSLog (@ "id =%d, name =%@, age =%@ address =%@", ID, name, age, address);
}
[DB close];
}

Fmdb's Fmresultset provides several ways to get different types of data:

Multi-threaded database operations:

If you use a multi-threaded database in your application, you need to use Fmdatabasequeue to ensure thread safety. It is not common in the application to use a Fmdatabase object in multiple threads to manipulate the database, which can cause confusion in database data. For multi-threaded operation of database security, Fmdb uses Fmdatabasequeue, using Fmdatabasequeue is very simple, first with a database file address to the initial fmdatabasequeue, and then you can be a closure (block) Passed into the Indatabase method. Operate the database in a closure without directly participating in the management of the fmdatabase.

Fmdatabasequeue * queue = [Fmdatabasequeue Databasequeuewithpath:database_path];
dispatch_queue_t q1 = dispatch_queue_create ("queue1", NULL);
dispatch_queue_t q2 = dispatch_queue_create ("Queue2", NULL);

Dispatch_async (Q1, ^{
for (int i = 0; i < ++i) {
[Queue indatabase:^ (Fmdatabase *db2) {

NSString *insertsql1= [NSString stringWithFormat:
@ "INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES (?,?,?)",
TABLENAME, NAME, age, ADDRESS];

NSString * name = [NSString stringwithformat:@ "Jack%d", I];
NSString * age = [NSString stringwithformat:@ "%d", 10+i];


BOOL res = [DB2 EXECUTEUPDATE:INSERTSQL1, Name, age,@ "Jinan"];
if (!res) {
NSLog (@ "error to Inster data:%@", name);
} else {
NSLog (@ "SUCC to Inster Data:%@", name);
}
}];
}
});

Dispatch_async (Q2, ^{
for (int i = 0; i < ++i) {
[Queue indatabase:^ (Fmdatabase *db2) {
NSString *insertsql2= [NSString stringWithFormat:
@ "INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES (?,?,?)",
TABLENAME, NAME, age, ADDRESS];

NSString * name = [NSString stringwithformat:@ "Lilei%d", I];
NSString * age = [NSString stringwithformat:@ "%d", 10+i];

BOOL res = [DB2 executeupdate:insertsql2, Name, age,@ "Beijing"];
if (!res) {
NSLog (@ "error to Inster data:%@", name);
} else {
NSLog (@ "SUCC to Inster Data:%@", name);
}
}];
}
});

Demo download

Free in http://linux.linuxidc.com/

user name and password are www.linuxidc.com

specific download directory in /2014 information/February/21st/ios Learning Notes/Database operations (using FMDB)

Download method See http://www.linuxidc.com/Linux/2013-07/87684.htm

Fmdb with SQLite