How to use existing SQLite database in swift?
First add libsqlite3.dylib to your Xcode project (in project settings/Build Phases/Link Binary with Libraries), then use something like fmdb, it makes dealing with SQLite a lot easier. It's written in Objective-C but can be used in a Swift project, too.
Then you could write a DatabaseManager
class, for example...
import Foundation;
class DatabaseManager {
private let dbFileName = "database.db"
private var database:FMDatabase!
init() {
openDatabase()
}
func openDatabase() {
let resourcePath = NSBundle.mainBundle().resourceURL!.absoluteString
let dbPath = resourcePath?.stringByAppendingPathComponent(dbFileName)
let database = FMDatabase(path: dbPath)
/* Open database read-only. */
if (!database.openWithFlags(1)) {
println("Could not open database at \(dbPath).")
} else {
self.database = database;
}
}
func closeDatabase() {
if (database != nil) {
database.close()
}
}
func query(queryString:String) {
if let db = database, q = db.executeQuery(queryString, withArgumentsInArray: nil) {
while q.next() {
let data = q.stringForColumn("columnName")
// Do whatever here with fetched data, usually add it to an array and return that array
}
}
}
}
Use and Access Existing SQLite Database on iOS
SQLite database interaction can be made simple and clean by using FMDB Framework
. FMDB is an Objective-C wrapper for the SQLite C interface.
Reference worth reading:
FMDB Framework Docs
Sample Project With Storyboard
Initial Setup
Add the SQLite DB
like 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
- First download the FMDB framework
- Extract the framework now copy all the file from
src/fmdb
folder (not thesrc/sample
orsrc/extra
folders). - Click your project in the left column of Xcode.
- Click the main target in the middle column.
- Click the “Build Phases” tab.
- Expand the arrow next to “Link Binary With Libraries”.
- Click the “+” button.
- Search for libsqlite3.0.dylib and double click it.
Copying your existing database
into app's document
in didFinishLaunchingWithOptions:
and maintain the database path through out the application.
In your AppDelegate add the following code.
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
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.h
file to use 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];
}
How to use existing sqlite database use in ios using core data?
in short: you cant :)
longer:
you need to keep using sqlite if you wanna keep the db. You can't easily'migrate' a DB from sqlite to cordata. coredata does USE sqlite - true - but it does so in a 'private/proprietary' format that isn't documented and can change over time.
==> stick with sqlite
IF you need to switch, open the old db on the device and write a class to read from sqlite and import to CD.
=> you still need to write a swift sqlite 'reader' class and a core data importer
Core data with existing sqlite db
The fact that Core Data uses SQLite is supposed to be an implementation detail. In fact, the Core Data documentation specifically say that you should not, in any way, access the database itself outside Core Data.
Likewise, you are not supposed to force your own database underneath Core Data.
If you want to import an existing database into Core Data, you need to write some code that will iterate through each entry in your existing database, and create corresponding objects to be inserted into Core Data.
FWIW, the Z_ stuff is Core Data decorations on their database.
Do not mess with the database. Now, it turns out that you can do a few things with the database, but you really should't -- unless you are an expert -- and then you still shouldn't, but probably will.
Related Topics
Change Language in the App Programmatically in iOS
Is It Considered a Private API to Use App-Prefs:Root
Property Not Working with Getter and Setter
How to Post String with Special Character and Thai Language Using Xml Parsing in Objective C
Use of Unresolved Identifier' in Swift
Afnetworking 3.0 Migration: How to Post with Headers and Http Body
Dynamically Changing Font Size of Uilabel
How to Play a Sound Using Swift
How to Initialize/Instantiate a Custom Uiview Class with a Xib File in Swift
Wrong Color in Interface Builder
What Is the Height of Iphone's Onscreen Keyboard
Xcode - Ld: Library Not Found for -Lpods
Allow Only Alphanumeric Characters for a Uitextfield