小编典典

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

swift

我在SQLite中有一个完全填充的数据库,希望在新应用程序中使用。它相当大,因此,我希望尽可能避免将其更改为另一种格式。如何使用该数据库随我的应用一起提供的方式使用?

编辑:例如,如果我只是将文件拖放到“支持的文件”目录中,如何访问它?我该如何引用?


阅读 311

收藏
2020-07-07

共1个答案

小编典典

使用SQLite可以使SQLite数据库交互变得简单而干净FBDB Framework。FMDB是SQLite C接口的Objective-C包装器。

参考值得阅读:

FMDB框架文档

带情节提要的示例项目

最初设定

SQLite DB类似的文件添加到应用程序包中,然后使用以下代码将数据库复制到documents目录,然后
使用documents目录中的数据库

  1. 首先下载FMDB框架
  2. 现在提取框架,从src/fmdb文件夹(而不是src/samplesrc/extra文件夹)中复制所有文件。
  3. 单击您的项目在Xcode的左列。
  4. 单击中间列中的主要目标。
  5. 点击“构建阶段”标签。
  6. 展开“将二进制文件与库链接”旁边的箭头。
  7. 点击“ +”按钮。
  8. 搜索libsqlite3.0.dylib并双击它。

将您的文件复制existing database到整个应用程序中app's documentdidFinishLaunchingWithOptions:并在其中维护数据库路径。

在您的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];
}

附加功能

获取行数

确保包括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];
}
2020-07-07