在 iOS 上使用和访问现有的 SQLite 数据库

Use and Access Existing SQLite Database on iOS

提问by muttley91

I have a fully populated database in SQLite that I'd like to use in my new app. It's rather large, so I'd like to avoid changing it to another format if possible. How can I use this database in such a way that it ships with my app?

我在 SQLite 中有一个完全填充的数据库，我想在我的新应用程序中使用它。它相当大，所以如果可能的话，我想避免将其更改为另一种格式。我如何以我的应用程序附带的方式使用此数据库？

EDIT: If I just drop the file into my Supported Files directory, for example, how can I access it? How do I reference it?

编辑：例如，如果我只是将文件放入支持的文件目录中，我该如何访问它？我如何引用它？

回答by icodebuster

SQLite database interaction can be made simple and clean by using FBDB Framework. FMDB is an Objective-C wrapper for the SQLite C interface.

SQLite 数据库交互可以通过使用FBDB Framework. FMDB 是 SQLite C 接口的 Objective-C 包装器。

Reference worth reading:

值得一读的参考：

FMDB Framework Docs

FMDB 框架文档

Sample Project With Storyboard

带有故事板的示例项目

Initial Setup

最初设定

Add the SQLite DBlike any other file in your application's bundle then copy the database to documents directory using the following code then use the database from the documents directory

SQLite DB在应用程序包中添加任何其他文件，然后使用以下代码将数据库复制到文档目录，然后使用文档目录中的数据库

1. First download the FMDB framework
2. Extract the framework now copy all the file from src/fmdbfolder (not the src/sampleor src/extrafolders).
3. Click your project in the left column of Xcode.
4. Click the main target in the middle column.
5. Click the “Build Phases” tab.
6. Expand the arrow next to “Link Binary With Libraries”.
7. Click the “+” button.
8. Search for libsqlite3.0.dylib and double click it.

1. 首先下载FMDB框架
2. 提取框架现在从src/fmdb文件夹（不是src/sample或src/extra文件夹）复制所有文件。
3. 在 Xcode 的左栏中单击您的项目。
4. 单击中间列中的主要目标。
5. 单击“构建阶段”选项卡。
6. 展开“Link Binary With Libraries”旁边的箭头。
7. 单击“+”按钮。
8. 搜索 libsqlite3.0.dylib 并双击它。

Copying your existing databaseinto app's documentin didFinishLaunchingWithOptions:and maintain the database path through out the application.

将您的existing database输入复制到应用程序app's document中didFinishLaunchingWithOptions:并在整个应用程序中维护数据库路径。

In your AppDelegate add the following code.

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

AppDelegate.m

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

YourViewController.m

Select Query

选择查询

#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];   
}

Insert Query

插入查询

#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];
}

Update Query

更新查询

- (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];
}

Delete Query

删除查询

#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];
}

Addition Functionality

附加功能

Getting the row count

获取行数

Make sure to include the FMDatabaseAdditions.hfile to use intForQuery:.

确保包含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];
}

回答by Lithu T.V

Add the Sqlite DB like any other file in your application bundle

像应用程序包中的任何其他文件一样添加 Sqlite DB

Copy it to documents directory via code and use it .The purpose of this is that updating content in sqlite is possible in Documents directory only

通过代码将其复制到文档目录并使用它。这样做的目的是更新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;
}

回答by Indrajeet

This following methods will help you to handle database

以下方法将帮助您处理数据库

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
                    {
                        str=@"";
                    }
                    [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

以上 swift 中的方法会写成如下

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
}

回答by Hasya

Using swift, singleton class and FMDB. you can use below code to achieve it very easily.

使用 swift、单例类和 FMDB。您可以使用下面的代码很容易地实现它。

Download example

下载示例

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

    }
}

Have a happy coding.

祝你编码愉快。

回答by Brainstorm Technolabs

To copy .sqlite file in directory...

要复制目录中的 .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");
}

To select Data From Database...

要从数据库中选择数据...

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);

}