IOS Learning Notes (16)--Detailed database operations (using FMDB) _ios

The SQLite API, which is native to iOS, is quite unfriendly to use and inconvenient when used. As a result, there is a series of libraries that encapsulate the SQLite APIs, such as Fmdb, Plausibledatabase, Sqlitepersistentobjects, Fmdb (https://github.com/ccgus/ Fmdb) is a concise, easy-to-use packaging Library, this article briefly introduces the use of Fmdb.

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

Fmdb is also compatible with ARC and non-ARC engineering and automatically adjusts the associated memory management code according to the project configuration.

Fmdb Common class:

1. Fmdatabase: A single SQLite database for executing SQL statements.
2. Fmresultset: Execute query for a fmdatabase result set, similar to Android's cursor.
3. Fmdatabasequeue: This class is used when multiple threads are executing queries and updates.

To create a database:

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

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

3, if you pass in the parameter is NULL, it will create a database in memory, the database disconnected, the database file was deleted.

To open a database:

Returns the bool type.

To close the database:

Database additions and deletions and other operations:

In addition to query operations, the FMDB database operation performs 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 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, @ "John", @ "13", @ "Jinan"]; 
    BOOL res = [db EXECUTEUPDATE:INSERTSQL1]; 
    NSString *INSERTSQL2 = [NSString stringWithFormat: 
                @ "INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES ('%@ ', '%@ ', '%@ ')", 
   tablename, NAME, age, Address, @ "Dick", @ "12", @ "Jinan"]; 
    BOOL res2 = [db EXECUTEUPDATE:INSERTSQL2]; 
     
    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, @" The Age,  @ ""; 
    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%@ = '%@ '", 
                TABLE Name, name, @ "John"]; 
    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:

Database multithreaded operations:

If you use a multithreaded database in your application, you need to use Fmdatabasequeue to keep the thread safe. It is not common in applications to use a Fmdatabase object to manipulate databases in multiple threads, which can cause confusion in database data. For multithreading to operate the database security, Fmdb used the Fmdatabasequeue, the use of Fmdatabasequeue is very simple, first with a database file address to the beginning of fmdatabasequeue, and then you can put a closure (block) The incoming Indatabase method. Operate the database in closures, not directly involved in fmdatabase management.

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 < 50; ++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); 
    } 
      }]; 

 } 
  });

SOURCE Download: DEMO

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.