objective c:在iOS上使用和訪問現有的SQLite資料庫

SQLite資料庫互動可以通過使用 FBDB Framework變得簡單而干净 . FMDB是SQLite C介面的Objective-C包裝器。

值得一讀的參考书

FMDB框架文件

Sample Project With Storyboard

初始設置

添加 SQLite DB 像應用程式包中的任何其他檔案一樣，然後使用以下代碼將資料庫複製到documents目錄，然後 use the database from the documents directory

首先下載FMDB框架

提取框架，現在从 src/fmdb複製所有檔案 檔案夹（不是 src/sample 或 src/extra 檔案夹）。

在Xcode的左列中單击您的專案。

單击中間列中的主要目標。

點击"構建階段"標簽。

展開"將二进製檔案与庫鏈接"旁邊的箭頭。

單击" +"按钮。

搜尋libsqlite3.0.dylib並双击它。

Copying your existing database 进入 app's document 在 didFinishLaunchingWithOptions: 並在整个應用程式中維護資料庫路徑。

在您的AppDelegate中添加以下代碼。

AppDelegate.m

#import "AppDelegate.h"
@implementation AppDelegate
// Application Start
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
    // Function called to create a copy of the database if needed.
    [self createCopyOfDatabaseIfNeeded];
    return YES;
}
#pragma mark - Defined Functions
// Function to Create a writable copy of the bundled default database in the application Documents directory.
- (void)createCopyOfDatabaseIfNeeded {
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    // Database filename can have extension db/sqlite.
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *appDBPath = [documentsDirectory stringByAppendingPathComponent:@"database-name.sqlite"];
    success = [fileManager fileExistsAtPath:appDBPath];
    if (success) {
        return;
    }
    // The writable database does not exist, so copy the default to the appropriate location.
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"database-name.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:appDBPath error:&error];
    NSAssert(success, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}

YourViewController.m

選擇查詢

#import "FMDatabase.h"
- (void)getAllData {
    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];
    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSString *sqlSelectQuery = @"SELECT * FROM tablename";
    // Query result 
    FMResultSet *resultsWithNameLocation = [database executeQuery:sqlSelectQuery];
    while([resultsWithNameLocation next]) {
        NSString *strID = [NSString stringWithFormat:@"%d",[resultsWithNameLocation intForColumn:@"ID"]];
        NSString *strName = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"Name"]];
        NSString *strLoc = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"Location"]];
        // loading your data into the array, dictionaries.
        NSLog(@"ID = %d, Name = %@, Location = %@",strID, strName, strLoc);
    }
    [database close];   
}

插入查詢

#import "FMDatabase.h"
- (void)insertData {
    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];
    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];    
    NSString *insertQuery = [NSString stringWithFormat:@"INSERT INTO user VALUES ('%@', %d)", @"Jobin Kurian", 25];
    [database executeUpdate:insertQuery];   
    [database close];
}

更新查詢

- (void)updateDate {
    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"fmdb-sample.sqlite"];
    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];    
    NSString *insertQuery = [NSString stringWithFormat:@"UPDATE users SET age = '%@' WHERE username = '%@'", @"23", @"colin" ];
    [database executeUpdate:insertQuery];
    [database close];
}

删除查詢

#import "FMDatabase.h"
- (void)deleteData {
    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];
    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSString *deleteQuery = @"DELETE FROM user WHERE age = 25";
    [database executeUpdate:deleteQuery];   
    [database close];
}

附加功能

Getting the row count

確保包括 FMDatabaseAdditions.h 档案使用 intForQuery:

#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"
- (void)gettingRowCount {
    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];
    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSUInteger count = [database intForQuery:@"SELECT COUNT(field_name) FROM table_name"];
    [database close];
}

像在應用程式捆绑包中的任何其他檔案一樣添加Sqlite資料庫

通過代碼將其複製到文件目錄並使用。其目的是仅在Documents目錄中才可以更新sqlite中的內容

-(void) checkAndCreateDatabase
{
    // Check if the SQL database has already been saved to the users phone, if not then copy it over
    BOOL success;
    // Create a FileManager object, we will use this to check the status
    // of the database and to copy it over if required
    NSFileManager *fileManager = [NSFileManager defaultManager];
    // Check if the database has already been created in the users filesystem
    success = [fileManager fileExistsAtPath:_databasePath];
    // If the database already exists then return without doing anything
    if(success) return;
    // If not then proceed to copy the database from the application to the users filesystem
    // Get the path to the database in the application package
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:_databaseName];
    // Copy the database from the package to the users filesystem
    [fileManager copyItemAtPath:databasePathFromApp toPath:_databasePath error:nil];
}
- (id)init {
    if ((self = [super init]))
    {
        _databaseName = DB_NAME;
        NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *documentsDir = [documentPaths objectAtIndex:0];
        _databasePath = [documentsDir stringByAppendingPathComponent:_databaseName];
        if (sqlite3_open([[self dbPath] UTF8String], &_database) != SQLITE_OK)
        {
            [[[UIAlertView alloc]initWithTitle:@"Missing"
                                       message:@"Database file not found"
                                      delegate:nil
                             cancelButtonTitle:@"OK"
                             otherButtonTitles:nil, nil]show];
        }
    }
    return self;
}

使用swift，單例類和FMDB.您可以使用以下代碼轻松實現。

下載示例

import Foundation
class LocalDatabase: NSObject {

//sharedInstance
static let sharedInstance = LocalDatabase()

    func methodToCreateDatabase() -> NSURL? {
    let fileManager = NSFileManager.defaultManager()
    let urls = fileManager.URLsForDirectory(.DocumentDirectory, inDomains: .UserDomainMask)
    if let documentDirectory:NSURL = urls.first { // No use of as? NSURL because let urls returns array of NSURL
        // exclude cloud backup
        do {
            try documentDirectory.setResourceValue(true, forKey: NSURLIsExcludedFromBackupKey)
        } catch _{
            print("Failed to exclude backup")
        }
        // This is where the database should be in the documents directory
        let finalDatabaseURL = documentDirectory.URLByAppendingPathComponent("contact.db")
        if finalDatabaseURL.checkResourceIsReachableAndReturnError(nil) {
            // The file already exists, so just return the URL
            return finalDatabaseURL
        } else {
            // Copy the initial file from the application bundle to the documents directory
            if let bundleURL = NSBundle.mainBundle().URLForResource("contact", withExtension: "db") {
                do {
                    try fileManager.copyItemAtURL(bundleURL, toURL: finalDatabaseURL)
                } catch _ {
                    print("Couldn't copy file to final location!")
                }
            } else {
                print("Couldn't find initial database in the bundle!")
            }
        }
    } else {
        print("Couldn't get documents directory!")
    }
    return nil
}
    func methodToInsertUpdateDeleteData(strQuery : String) -> Bool
    {
       // print("%@",String(methodToCreateDatabase()!.absoluteString))
        let contactDB = FMDatabase(path: String(methodToCreateDatabase()!.absoluteString) )
        if contactDB.open() {
            let insertSQL = strQuery
            let result = contactDB.executeUpdate(insertSQL,
                withArgumentsInArray: nil)
            if !result {
                print("Failed to add contact")
                print("Error: \(contactDB.lastErrorMessage())")
                return false
            } else {
                print("Contact Added")
                return true
            }
        } else {
            print("Error: \(contactDB.lastErrorMessage())")
            return false
        }
    }
    func methodToSelectData(strQuery : String) -> NSMutableArray
    {
        let arryToReturn : NSMutableArray = []
        print("%@",String(methodToCreateDatabase()!.absoluteString))
        let contactDB = FMDatabase(path: String(methodToCreateDatabase()!.absoluteString) )
        if contactDB.open() {
            let querySQL = strQuery
            let results:FMResultSet? = contactDB.executeQuery(querySQL,
                withArgumentsInArray: nil)
            while results?.next() == true
            {
                arryToReturn.addObject(results!.resultDictionary())
            }
            // NSLog("%@", arryToReturn)
            if arryToReturn.count == 0
            {
                print("Record Not Found")
            }
            else
            {
                print("Record Found")
            }

            contactDB.close()
        } else {
            print("Error: \(contactDB.lastErrorMessage())")
        }
        return arryToReturn
    }
}

編碼愉快。

要將.sqlite檔案複製到目錄中...

BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
// Database filename can have extension db/sqlite.
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *databasePath = [documentsDirectory stringByAppendingPathComponent:@"MapView.sqlite"];
success = [fileManager fileExistsAtPath:databasePath];
//    if (success){
//        return;
//    }
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"MapView.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:databasePath error:&error];
if (!success) {
    //NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
else
{
    NSLog(@"Database created successfully");
}

要選擇資料庫中的資料...

const char *dbpath = [databasePath UTF8String];
sqlite3_stmt    *statement;
if (sqlite3_open(dbpath, &mapDB) == SQLITE_OK)
{
    NSString *querySQL = [NSString stringWithFormat: @"SELECT * FROM maplatlong"];
    const char *query_stmt = [querySQL UTF8String];
    if (sqlite3_prepare_v2(mapDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        while(sqlite3_step(statement) == SQLITE_ROW)
        {
            NSString *cityN = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
            NSString *lat = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
            NSString *longi = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];
            [cityName addObject:cityN];
            [latitude addObject:lat];
            [longitude addObject:longi];
        }
        sqlite3_finalize(statement);
                }
    sqlite3_close(mapDB);
}

以下方法將帮助您處理資料庫

Method for copy database in document directory if not exist

-(void)copyDatabase
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *insPath = [NSString stringWithFormat:@"Instamontage.sqlite"];
    destPath = [documentsDirectory stringByAppendingPathComponent:insPath];
    NSString *srcPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:insPath];
// NSLog(@"\n src %@ \n dest %@", srcPath, destPath);
   if (![[NSFileManager defaultManager] fileExistsAtPath:destPath])
   {
    NSError *error;
    NSLog(@"not exist");
    [[NSFileManager defaultManager] copyItemAtPath:srcPath toPath:destPath error:&error];
   }
   else
   {
      NSLog(@"exist");
   }
}

Method for insert/deleting/updating table

-(BOOL)dataManipulation: (NSString *)query
{
    BOOL result=NO;
   if (sqlite3_open([destPath UTF8String], &connectDatabase)==SQLITE_OK)
   {
      sqlite3_stmt *stmt;
      if (sqlite3_prepare_v2(connectDatabase, [query UTF8String], -1, &stmt, NULL)==SQLITE_OK)
    {
        sqlite3_step(stmt);
        result=YES;
    }
    sqlite3_finalize(stmt);
  }
  sqlite3_close(connectDatabase);
  return result;
}

Method for getting rows from table

-(NSMutableArray *)getData: (NSString *)query
{
    NSMutableArray *arrData=[[NSMutableArray alloc]init];
    if (sqlite3_open([destPath UTF8String],&connectDatabase)==SQLITE_OK)
    {
        sqlite3_stmt *stmt;
        const char *query_stmt = [query UTF8String];
        if (sqlite3_prepare_v2(connectDatabase,query_stmt, -1, &stmt, NULL)==SQLITE_OK)
        {
            while (sqlite3_step(stmt)==SQLITE_ROW)
            {
                NSMutableDictionary *dictResult=[[NSMutableDictionary alloc] init];
                for (int i=0;i<sqlite3_column_count(stmt);i++)
                {
                    NSString *str;
                    if (sqlite3_column_text(stmt,i)!=NULL)
                    {
                        str = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt,i)];
                    }
                    else
                    {
                        [email protected]"";
                    }
                    [dictResult setValue:str forKey:[NSString stringWithUTF8String:(char *)sqlite3_column_name(stmt,i)]];
                }
                [arrData addObject:dictResult];
            }
            sqlite3_finalize(stmt);
        }
        sqlite3_close(connectDatabase);
    }
    return arrData;
}

Above methods in swift will written as below

Method for copy database in document directory if not exist

func copyDatabaseToDocumentDirectory() {
    let directoryList = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)
    var documentDirectory = directoryList.first
    documentDirectory?.append("/DatabasePract1.sqlite")
    print(documentDirectory!)
    if !FileManager.default.fileExists(atPath: documentDirectory!) {
        let databaseBundlePath = Bundle.main.path(forResource: "DatabasePract1", ofType: "sqlite")
        do {
           try FileManager.default.copyItem(atPath: databaseBundlePath!, toPath: documentDirectory!)
            self.databasePath = documentDirectory
        } catch {
            print("Unable to copy database.")
        }
    } else {
        print("database exist")
        self.databasePath = documentDirectory
    }
}

Method for insert/deleting/updating table

func dataManipulation(query: String) -> Bool {
    var database: OpaquePointer?
    var result = false
    if (sqlite3_open(databasePath, &database) == SQLITE_OK) {
        var queryStatement: OpaquePointer?
        if (sqlite3_prepare_v2(database, query, -1, &queryStatement, nil) == SQLITE_OK) {
            sqlite3_step(queryStatement)
            result = true
        } else {
            let errmsg = String(cString: sqlite3_errmsg(database)!)
            print("error preparing insert: \(errmsg)")
        }
        sqlite3_finalize(queryStatement)
    }
    sqlite3_close(database)
    return result
}

Method for getting rows from table

func fetchData(_ query: String) -> [[String:Any]] {
    var database: OpaquePointer?
    var arrData: [[String:Any]] = []
    if (sqlite3_open(databasePath, &database) == SQLITE_OK) {
        var stmt:OpaquePointer?
        if sqlite3_prepare(database, query, -1, &stmt, nil) != SQLITE_OK{
            let errmsg = String(cString: sqlite3_errmsg(database)!)
            print("error preparing insert: \(errmsg)")
            return arrData
        } 
        while(sqlite3_step(stmt) == SQLITE_ROW) {
            var dictData: [String: Any] = [:]
            for i in 0..<sqlite3_column_count(stmt) {
                var strValue = ""
                if (sqlite3_column_text(stmt, i) != nil) {
                    strValue = String(cString: sqlite3_column_text(stmt, i))
                }
                let keyName = String(cString: sqlite3_column_name(stmt, i), encoding: .utf8)
                dictData[keyName!] = strValue
            }
            arrData.append(dictData)
        }
        sqlite3_close(database)
    }
    return arrData
}