首頁>Program>source

我在SQLite中有一个完全填充的資料庫,希望在新應用程式中使用.它很大,所以我希望避免將其更改為另一種格式.如何使用该資料庫隨我的應用一起提供的方式?

編輯:例如,如果仅將檔案放入"支援的檔案"目錄中,如何訪問它? 如何引用它?

最新回復
  • 5月前
    1 #

    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/samplesrc/extra 檔案夹)。

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

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

    點击"構建階段"標簽。

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

    單击" +"按钮。

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

    Copying your 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];
    }
    
    附加功能

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

  • 5月前
    2 #

    像在應用程式捆绑包中的任何其他檔案一樣添加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;
    }
    

  • 5月前
    3 #

    使用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
        }
    }
    

    編碼愉快。

  • 5月前
    4 #

    要將.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);
    }
    

  • 5月前
    5 #

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

    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
    }
    

  • javascript:將表情符號列表替換為其圖像
  • 如何使用Google Maps android API v2获取"我的位置更改"事件?