Simple use of Sqlite in IOS apps

IOS appAboutSqliteUsage is what we will introduce in this article,SqliteIs embedded and lightweight SQLDatabase.SqliteIs implemented by c. It is widely used in most browsers that support html5, except ie), IOS, android, and small web application systems that are portable.

Preparation before using sqlite

UseSqliteYesIos appThe first time I faced c in development, including me. Because sqlite is written in c, objc can directly use c code. Before sqlite, the cocoa touch framework is generally used, which is based on objc.

First, add the following to the header file of the corresponding file:

 
 

  
  
#import "/usr/include/sqlite3.h" 
 
 

Add the required database to Frameworks. Otherwise, an error is returned:

 
 

  
  
Undefined symbols:   
  
  
  "_sqlite3_open", referenced from: 
 
 

The method for adding data to the database is:

Select sqlite Library:

Effect:

Then, there should be a member variable, such as my code:

 
 

  
  
@interface DetailViewController : UIViewController <UIPopoverControllerDelegate, UISplitViewControllerDelegate> {   
  
  
    UIPopoverController *popoverController;   
  
  
    UIToolbar *toolbar;   
  
  
    id detailItem;   
  
  
    UILabel *detailDescriptionLabel;   
  
  
    sqlite3 *database; 
 
 

Open Database

Sqlite databases are file databases and stored in file systems. Therefore, you need to know where the file is saved. For more information, see operations on the file in the iOS app. For example, save this article to the Documents directory. Code:

 
 

  
  
NSArray *documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory   
  
  
                                                   , NSUserDomainMask   
  
  
                                                   , YES);   
  
  
NSString *databaseFilePath=[[documentsPaths objectAtIndex:0] stringByAppendingPathComponent:@"mydb"];   
  
  
 
  
  
if (sqlite3_open([databaseFilePath UTF8String], &database)==SQLITE_OK) {   
  
  
    NSLog(@"open sqlite db ok.");   
  
  
} 
 
 

View the Documents directory through ssh and find that the mydb file has been created. The sqlite policy is to open the file if it exists, and create a file if it does not exist, that is, to create a database.

Note that the c syntax is used, and sqlite3_open refers to the database address.

Close Database

When the database is used up, close it, for example, when exiting the application:

 
 

  
  
- (void)viewDidUnload {   
  
  
    // Release any retained subviews of the main view.   
  
  
    // e.g. self.myOutlet = nil;   
  
  
   sqlite3_close(database);   
  
  
    self.popoverController = nil;   
  
  
} 
 
 

Table creation statement

If no table exists after the database is opened, create the table:

 
 

  
  
char *errorMsg;   
  
  
const char *createSql="create table if not exists persons (id integer primary key autoincrement,name text)";   
  
  
 
  
  
if (sqlite3_exec(database, createSql, NULL, NULL, &errorMsg)==SQLITE_OK) {   
  
  
    NSLog(@"create ok.");   
  
  
} 
 
 

Note that errorMsg transmits the address, because this function uses address reference to write error characters.

Insert records to a table

Similar to the table creation statement:

 
 

  
  
Const char * insertSql = "insert into persons (name) values ('zhang san ')";
  
  
If (sqlite3_exec (database, insertSql, NULL, NULL, & errorMsg) = SQLITE_ OK ){
  
  
NSLog (@ "insert OK .");
  
  
}
 
 

Handle error messages

If you use errorMsg in multiple places, you need to clear the string after each use, for example:

 
 

  
  
if (sqlite3_exec(database, createSql, NULL, NULL, &errorMsg)==SQLITE_OK) {   
  
  
    NSLog(@"create ok.");   
  
  
}else {   
  
  
    NSLog(@"error: %s",errorMsg);   
  
  
    sqlite3_free(errorMsg);   
  
  
} 
 
 

Query Result set

Statement is required for query of result sets:

 
 

  
  
const char *selectSql="select id,name from persons";   
  
  
sqlite3_stmt *statement;   
  
  
if (sqlite3_prepare_v2(database, selectSql, -1, &statement, nil)==SQLITE_OK) {   
  
  
    NSLog(@"select ok.");   
  
  
}   
  
  
 
  
  
while (sqlite3_step(statement)==SQLITE_ROW) {   
  
  
    int _id=sqlite3_column_int(statement, 0);   
  
  
    char *name=(char *)sqlite3_column_text(statement, 1);   
  
  
    NSLog(@"row>>id %i, name %s",_id,name);   
  
  
}   
  
  
 
  
  
sqlite3_finalize(statement); 
 
 

But there is a problem here, look at the printed log:

Garbled. Because the char type is used directly.

The solution is to replace char with nsstring:

 
 

  
  
while (sqlite3_step(statement)==SQLITE_ROW) {   
  
  
    int _id=sqlite3_column_int(statement, 0);   
  
  
    NSString *name=[[NSString alloc] initWithCString:(char *)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];   
  
  
    NSLog(@"row>>id %i, name %@",_id,name);   
  
  
} 
 
 

Char performs an explicit encoding when nsstring is generated. Solution:

Note:

WriteDatabase, There is no problem using char, writeDatabaseThe encoding is correct;

Extracted from the database. ascii decoding may be used by default, resulting in garbled characters.

Summary:IOS appAboutSqliteI hope this article will be helpful to you!