IOS Database Application 1: SQLite

 

 

 

There are many ways to save data, such as plist files, XML, or text files, but the efficiency is not high. SQLite provides local storage for efficient query and retrieval of big data.

SQLite is an open source library, written in C, that implements a self-contained SQL relational database engine. you can use SQLite to store large amounts of relational data. the developers of SQLite have optimized it for use on embedded devices like the iPhone and iPad.

Although the Core Data application programming interface (API) is also designed to store data on iOS, its primary purpose is to persist objects created by your application. SQLite excels when pre-loading your application with a large amount of data, whereas Core Data excels at managing data created on the device.

 

1. Create a database:
1. command-line

 

. TEXT,  INTEGER,  TEXT,  DOUBLE,  INTEGER,  INTEGER, . INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , . INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,  TEXT NOT NULL );

2. Use the SQLite Manager plug-in of Firefox browser

:

Http://code.google.com/p/sqlite-manager

2. Insert data:

2. Insert a row of data:

INSERT INTO . (,,,,, ,,,,,,, );

3. import data from a file to the database:

.separator  Product

Command:. import
Sqlite>. import file name table name
Note 1: Do not forget the Starting Point
NOTE 2: This statement cannot end with a semicolon. Non-SQL statements do not need to end with a semicolon.
NOTE 3: You need to check the default separator. It must be consistent. Otherwise, the sqlite field may be incorrectly divided.
Run the. show command to view the delimiter. If the Delimiter is inconsistent, modify it directly, for example:
Sqlite>. separator ","
Convert the separator into a comma.

4. Export:

Implementation Method: redirects the output to a file.
Command:. output
Sqlite>. output a.txt
Then, enter an SQL statement to query the data to be exported. After the query, the data is not displayed on the screen and is directly written to the file.
Enter
Sqlite>. output stdout
Redirects the output to the screen.
Example:
Export the data in tab_xx to the a.txt file.
Sqlite>. output a.txt
Sqlite> select * from tab_xx;

Display on the screen:

Sqlite>. output stdout

Sqlite> select * from tab_xx;

3. Using SQLite in iOS:
1. Data Modeling

2. Abstract The database operation API so that you can change the database later.

 <Foundation/Foundation.h> <sqlite3.h> - (NSMutableArray*- (- (

 sqlite3*-(     ((self =- (    NSString *path =     (sqlite3_open([path UTF8String], &database) ==, -(     (sqlite3_close(database) !=, - (NSMutableArray*    NSMutableArray *products =      *sql =  manufacturer.manufacturerid==country.countryid        sqlite3_stmt *     sqlResult = sqlite3_prepare_v2(database, sql, -, & ( sqlResult==         (sqlite3_step(statement) ==            Product  *product =             *name = ( *)sqlite3_column_text(statement,  *manufacturer = ( *)sqlite3_column_text(statement,  *details = ( *)sqlite3_column_text(statement,  *countryOfOrigin = ( *)sqlite3_column_text(statement,  *image = ( *)sqlite3_column_text(statement,             product.ID = sqlite3_column_int(statement, = (name) ? [NSString stringWithUTF8String:name] : = (manufacturer) ?= (details) ? [NSString stringWithUTF8String:details] : = sqlite3_column_double(statement, = sqlite3_column_int(statement, = (countryOfOrigin) ?= (image) ? [NSString stringWithUTF8String:image] : 

 

3,

 <sqlite3.h>***    dirPaths == [dirPaths objectAtIndex:    NSString *databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: *filemgr = ([filemgr fileExistsAtPath:databasePath] ==  *dbpath = (sqlite3_open(dbpath, &contactDB)== *  *sql_stmt =  (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg)!== = 

 

4. insert data into the database:

- (IBAction)SaveToDataBase:(*  *dbpath = (sqlite3_open(dbpath, &contactDB)==*insertSQL = [NSString stringWithFormat:%@\  *insert_stmt =-, & (sqlite3_step(statement)=== = = = = 

5. query the database:

- (IBAction)SearchFromDataBase:(  *dbpath =* (sqlite3_open(dbpath, &contactDB) ==*querySQL = [NSString stringWithFormat:%@\  *query_stmt = (sqlite3_prepare_v2(contactDB, query_stmt, -, &statement, NULL) == (sqlite3_step(statement) ==*addressField = [[NSString alloc] initWithUTF8String:(  *)sqlite3_column_text(statement, =*phoneField = [[NSString alloc] initWithUTF8String:(  *)sqlite3_column_text(statement, == = = = 

 

Usage:

        DBAccess *dbAccess =    self.products =    [dbAccess closeDatabase];

 

Tips:

1. Read files:

NSString *textFileContents = [NSString stringWithContentsOfFile:[[NSBundle mainBundle] pathForResource:encoding:NSUTF8StringEncoding error:& (fileContents ==　　NSLog(*lines = [textFileContents componentsSeparatedByString:]; 
NSLog(, [lines count] );