Simple use of FMDB, simple use of FMDB

Simple use of FMDB, simple use of FMDB
Common FMDB classes:

FMDatabase: A single SQLite database used to execute SQL statements.
FMResultSet: Execute to query an FMDatabase result set, which is similar to the android Cursor.
FMDatabaseQueue: this class is used when multiple threads are used to execute queries and updates.

Create a database:

Db = [FMDatabase databaseWithPath: database_path];

1. When the database file does not exist, fmdb creates one by itself.

2. If the input parameter is an empty string: @ "", fmdb will create the database in the temporary file directory. When the database is disconnected, the database file will be deleted.

3. If the input parameter is NULL, it creates a database in the memory. When the database is disconnected, the database file is deleted.

 

Open the database:

[Db open]

Return BOOL type.

Close the database:

[Db close]

Create a table:

If ([db open])

{

NSString * sqlCreateTable = [NSString stringWithFormat: @ "create table if not exists '% @' ('% @ 'integer primary key autoincrement,' % @ '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];

If (! Res ){

NSLog (@ "error when insert db table ");

} Else {

NSLog (@ "success to insert db table ");

}

[Db close];

}

Modify data:

If ([db open]) {

NSString * updateSql = [NSString stringWithFormat:

@ "UPDATE '% @ 'set' % @' % = '% @ 'where' % @' = '% @'",

TABLENAME, AGE, @ "15", 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, @ "James"];

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 multiple methods to obtain different types of data:

Multi-thread database operations:

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. To ensure the security of multi-threaded database operations, FMDB uses FMDatabaseQueue. It is very simple to use FMDatabaseQueue. First, a database file address is used to initialize FMDatabaseQueue, and then a closure (block) can be set) passed in the inDatabase method. Operate the database in the closure, instead of directly managing 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 <50; ++ 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 );

}

}];

}

});