IOS database SQLite

IOS database SQLite
SQLite is a lightweight embedded database. Android and iOS use SQLite databases. Its features: it occupies very low resources. In embedded devices, it may only require several hundred KB of memory. It processes faster than MySQL and PostgreSQL, two famous databases. It is a C language framework with strong cross-platform performance.

To use SQLite3 in iOS, You need to import libsqlite3 library in Xcode

The following describes how to complete database operations using SQL statements:

Create table: create table Name (Field 1, Field 2 ,......, Field n, [Table-level constraints]) [TYPE = table type]; insert record: insert into Table Name (Field 1 ,......, Field n) values (value 1 ,......, Value n); delete record: delete from table name where condition expression; Modify record: update table name set field name 1 = value 1 ,......, Field name n = value n where condition expression; view record: select Field 1 ,......, Field n from table name where condition expression;

1. Open the database
The C language functions used are as follows:

/* Open the database */int sqlite3_open (const char * filename,/* database path (UTF-8) */sqlite3 ** pDb/* Returned Database handle */);

2. query the database
C language functions used:

/* Execute the SQL statement with returned results */int sqlite3_prepare_v2 (sqlite3 * db,/* database handle */const char * zSql,/* SQL statement (UTF-8) */int nByte,/* maximum SQL statement length.-1 indicates the maximum length supported by SQL */sqlite3_stmt ** ppStmt, /* returned query result */const char ** pzTail/* returned Failure Information */);

3. insert data
C language functions used:

/* Execute the SQL statement with returned results */int sqlite3_prepare (sqlite3 * db,/* database handle */const char * zSql,/* SQL statement (UTF-8) */int nByte,/* maximum SQL statement length.-1 indicates the maximum length supported by SQL */sqlite3_stmt ** ppStmt, /* returned query result */const char ** pzTail/* returned Failure Information */);

4. delete a database
C language functions used:

/* Execute the SQL statement not returned */int sqlite3_exec (sqlite3 * db,/* database handle */const char * SQL,/* SQL statement (UTF-8) */int (* callback) (void *, int, char **, char **),/* callback C function pointer */void * arg, /* The first parameter of the callback function */char ** errmsg/* returned error message */);

5. Shut down the database.
C language functions used:

/* Close the database */int sqlite3_close (sqlite3 * db );

The following is a small example:

Here, the request data is the user name and id of Weibo to define a model.

DataModel. h

#import 
  
   @interface DataModel : NSObject@property (nonatomic, copy) NSString *screen_name;@property (nonatomic, copy) NSString *avatar_hd;- (instancetype)initWithDictionary:(NSArray *)dictionary;@end
  

DataModel. m

# Import "DataModel. h "@ implementation DataModel-(instancetype) initWithDictionary :( NSDictionary *) dictionary {if (self = [super init]) {// It is too troublesome to write multiple attributes one by one, // complete the statement in one sentence: [self setValuesForKeysWithDictionary: dictionary];} return self;} // generally, the request data is returned, which contains a lot of data and must be written one by one, if one request is incorrect, an error is returned. // if you cannot use it again, write this method-(void) setValue :( id) value forUndefinedKey :( NSString *) key {// print key NSLog (@ "% @", key);} @ end

When defining a DataBase-like processing DataBase

DataBase. h

# Import
   
    
/** Import DataModel */# import "DataModel. h "@ interface DataBase: NSObject/** open DataBase */+ (void) openDB;/** query */+ (NSArray *) find; /** insert */+ (BOOL) insertModel :( DataModel *) dataModel; +/** Delete */+ (BOOL) deleteModel :( DataModel *) dataModel; @ end
   

DataBase. m

# Import "DataBase. h" // import # import
    
     
@ Implementation DataBase // create a DataBase object static sqlite3 * db; # pragma mark-Open DataBase + (void) openDB {// move the imported database to the Documents folder. // first, create a database and import the database to the project. "This step is required." // obtain the database path // here, WeiBo is the name of the database to be created (do not make a mistake) NSString * originPath = [[NSBundle mainBundle] pathForResource: @ "WeiBo" ofType: @ "sqlite"]; // The path in the Documents folder and then splice NSString * targetPath = [[NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex: 0] stringByAppendingPathComponent: @ "collection. sqlite"]; NSFileManager * manager = [NSFileManager defaultManager]; if (! [Manager fileExistsAtPath: targetPath]) {[manager moveItemAtPath: originPath toPath: targetPath error: nil];} char * filename = (char *) [targetPath UTF8String]; // open the database int result = sqlite3_open (filename, & db); if (result = SQLITE_ OK) {NSLog (@ "database enabled successfully ");} else {NSLog (@ "failed to enable database") ;}# pragma mark- + (NSArray *) find {NSMutableArray * mArray = [NSMutableArray array]; // open the database [self openDB]; // declare Statement sqlite3_stmt * stmt = nil; // query table // here, weiBo is the name of the table under the database creation (do not make a mistake) const char * SQL = "select * from weiBo "; // convert SQL text into a statement object int result = sqlite3_prepare_v2 (db, SQL,-1, & stmt, nil); if (result = SQLITE_ OK) {NSLog (@ "prepared for query succeeded"); while (sqlite3_step (stmt) = SQLITE_ROW) {// extract data const char * screen_name = (const char *) sqlite3_column_text (stmt, 0); const char * avatar_hd = (const char *) sqlite3_co Lumn_text (stmt, 1); DataModel * dataModel = [[DataModel alloc] init]; dataModel. screen_name = [NSString stringWithCString: screen_name encoding: 4]; dataModel. struct = [NSString stringWithCString: incluencoding: 4]; // "NSNumber type" [NSNumber numberWithInteger: [[NSString stringWithCString: deal_id encoding: 4] integerValue]; [mArray addObject: dataModel] ;}}return mArray ;}# pragma mark-plug + (BOO L) insertModel :( DataModel *) dataModel {// open the database [self openDB]; sqlite3_stmt * stmt = nil; // here, weiBo is the name of the table under the database creation (do not make a mistake) const char * SQL = "insert into weiBo (screen_name, avatar_hd) values (?, ?) "; // Convert the SQL text into a statement object int result = sqlite3_prepare (db, SQL,-1, & stmt, nil); if (result = SQLITE_ OK) {// Insert the data to be inserted into the database sqlite3_bind_text (stmt, 1, [dataModel. screen_name UTF8String],-1, nil); sqlite3_bind_text (stmt, 2, [dataModel. avatar_hd UTF8String],-1, nil); // [NSNumber] sqlite3_bind_text (stmt, 1, [[NSString stringWithFormat: @ "% @", model. comment_num] UTF8String],-1, nil); if (sqlite3_step (stmt) = SQLITE_DONE) {flag = YES ;}} return flag ;} # pragma mark-delete + (BOOL) deleteModel :( DataModel *) dataModel {BOOL flag = NO; // open the database [self openDB]; // here, weiBo is the name of the table under the database creation (do not make a mistake) NSString * SQL = [NSString stringWithFormat: @ "delete from weiBo where screen_name = % @", dataModel. screen_name]; int result = sqlite3_exec (db, SQL. UTF8String, NULL, NULL, nil); if (result = SQLITE_ OK) {NSLog (@ "deleted successfully"); flag = YES ;} else {NSLog (@ "failed to delete");} return flag ;}@ end
    

Create a database

(Firefox browser)
1. Download SQLite Manager

2. There are several ways to create a database (I will not discuss it here). I am using the database in Firefox.
Open your browser and choose tools> SQLite Manager.

3. Click the SQLite Manager page.

4. Create a database

5. Drag the desktop to the project.

6. Create a database table (the fields in the table can be understood as attributes)

7. After the table is created successfully