iOS資料庫應用一：SQLite

 

 

 

儲存資料的方式很多，plist files, XML, 或者 文字檔，但是效率不高。SQLite提供了在大資料中高效查詢、檢索的本機存放區功能。

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、command-line

 

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

2、使用Firefox瀏覽器的SQLite Manager 外掛程式

：

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

二，插入資料：

2、插入一行資料：

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

3、從檔案中匯入資料到資料庫：

.separator  Product

命令: .import
sqlite> .import 檔案名稱 表名
注1: 不要忘了開頭的點
注2: 這條語句不能用分號結束. 非SQL不需要分號結束.
注3: 需要查看預設的分隔字元separator. 必須一致. 如果不一致可能導致sqlite欄位分割錯誤.
        查看分隔字元使用命令  .show , 如果不一致可直接修改, 比如:
        sqlite>.separator ","
        將分隔字元轉為逗號.

4、匯出：

實現方式: 將輸出重新導向至檔案.
命令: .output
sqlite> .output a.txt
然後輸入sql語句, 查詢出要導的資料. 查詢後,資料不會顯示在螢幕上,而直接寫入檔案.
結束後,輸入
sqlite> .output stdout
將輸出重新導向至螢幕.
舉例: 
將 tab_xx 中的資料匯出到檔案a.txt
sqlite> .output a.txt
sqlite> select * from tab_xx;

在螢幕顯示：

sqlite> .output stdout

sqlite> select * from tab_xx;

 三、在iOS軟體中使用SQLite：
1、首先要對資料建模

2、抽象出操作資料庫的API，以便以後更換資料庫。

 <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、向資料庫插入資料：

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

5、查詢資料庫庫：

- (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, == = = = 

 

使用：

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

 

tips：

1、讀取檔案：

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