ios 在 Objective C 中打开和创建 SQLite 数据库

Opening and creating SQLite database in Objective C

提问by Marten

I want to develop an iOS app, so I am learning Objective C. I followed a tutorial for using a SQLite database in my app. (http://www.techotopia.com/index.php/An_Example_SQLite_based_iOS_7_Application)

我想开发一个 iOS 应用程序，所以我正在学习 Objective C。我遵循了在我的应用程序中使用 SQLite 数据库的教程。( http://www.techotopia.com/index.php/An_Example_SQLite_based_iOS_7_Application)

I have the following code for opening and creating of my database, but it doesn't work! I get the message "Failed to open or create database". Can someone tell me what is wrong?

我有以下代码用于打开和创建我的数据库，但它不起作用！我收到消息“无法打开或创建数据库”。有人可以告诉我出了什么问题吗？

- (void)viewDidLoad
{
    [super viewDidLoad];
    NSString *docsDir;
    NSArray *dirPaths;

    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, YES);

    docsDir = dirPaths[0];

    // Build the patht to the database file
    _databasePath = [[NSString alloc]initWithString: [docsDir stringByAppendingPathComponent: @"contacts.db"]];

    NSFileManager *filemgr = [NSFileManager defaultManager];

    if ([filemgr fileExistsAtPath:_databasePath] == NO) {
        const char *dbpath = [_databasePath UTF8String];

        if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT";

            if (sqlite3_exec(_contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK) {
                _lblStatus.text = @"Failed to create table";

            }

            sqlite3_close(_contactDB);
        } else {
            _lblStatus.text = @"Failed to open or create database";
        }
    }
}

I have included 'libsqlite3.dylib' and added #import to the H file.

我已经包含了 'libsqlite3.dylib' 并将 #import 添加到 H 文件中。

回答by manujmv

In your code, there is some mistake

在您的代码中，有一些错误

// Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, YES);

Use NSDocumentDirectorynot NSDocumentationDirectory

使用NSDocumentDirectory而不是NSDocumentationDirectory

I think it will solve your problem. In my suggestion, you can try core data. It is more simple You can try this tutorial http://www.raywenderlich.com/934/core-data-tutorial-for-ios-getting-started

我认为它会解决你的问题。在我的建议中，您可以尝试使用核心数据。更简单你可以试试这个教程 http://www.raywenderlich.com/934/core-data-tutorial-for-ios-getting-started

It is really simple and easily understandable.

这真的很简单，很容易理解。

回答by nitin kachhadiya

DBConnection.h

数据库连接.h

#import <Foundation/Foundation.h>
#import <sqlite3.h>

// database name demo.sqlite

#define DB_NAME @"demo.sqlite"//@"Conferencedata////NSLog"

@interface DBConnection : NSObject
{
    @private sqlite3 *g_database;
}

@property (nonatomic,assign,readwrite) sqlite3 *database;

+ (DBConnection *) sharedConnection;
+ (BOOL) executeQuery:(NSString *)query;
+ (NSMutableArray *) fetchResults:(NSString *)query;
+ (int) rowCountForTable:(NSString *)table where:(NSString *)where;
+ (void) errorMessage:(NSString *)msg;
+ (void) closeConnection;

- (id)initConnection;
- (void)close;

@end

DBConnection.m

数据库连接文件

#import "DBConnection.h"
#import <UIKit/UIKit.h>
#include <sys/xattr.h>

//static sqlite3_stmt *statement = nil;

@interface DBConnection (Private)
- (void) createEditableCopyOfDatabaseIfNeeded;
- (BOOL)addSkipBackupAttributeToItemAtURL:(NSURL *)URL;
- (void) initializeDatabase;
@end

@implementation DBConnection
static DBConnection *conn = NULL;

@synthesize database = g_database;

+ (DBConnection *) sharedConnection {
if (!conn) {
conn = [[DBConnection alloc] initConnection];
}
return conn;
}

#pragma mark - Static Methods

+(BOOL) executeQuery:(NSString *)query{
    BOOL isExecuted = NO;

    sqlite3 *database = [DBConnection sharedConnection].database;
    sqlite3_stmt *statement = nil;
    const char *sql = [query UTF8String];
    if (sqlite3_prepare_v2(database, sql, -1, &statement , NULL) != SQLITE_OK) {
        //NSLog(@"Error: failed to prepare agenda query statement with message '%s'.", sqlite3_errmsg(database));
        //NSString *errorMsg = [NSString stringWithFormat:@"Failed to prepare query statement - '%s'.", sqlite3_errmsg(database)];
        //[DBConnection errorMessage:errorMsg];
        //NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
        return isExecuted;
    }

    // Execute the query.
if(SQLITE_DONE == sqlite3_step(statement)) {
        isExecuted = YES;
    }

// finlize the statement.
sqlite3_finalize(statement);
    statement = nil;

    return isExecuted;
}
+(NSMutableArray *) fetchResults:(NSString *)query{
    NSMutableArray *results = [NSMutableArray arrayWithCapacity:0];
    sqlite3 *database = [DBConnection sharedConnection].database;
    sqlite3_stmt *statement = nil;

    const char *sql = [query UTF8String];
    if (sqlite3_prepare_v2(database, sql, -1, &statement , NULL) != SQLITE_OK) {
        //NSLog(@"Error: failed to prepare fetch results statement with message '%s'.", sqlite3_errmsg(database));
        NSString *errorMsg = [NSString stringWithFormat:@"Failed to prepare query statement - '%s'.", sqlite3_errmsg(database)];
        [DBConnection errorMessage:errorMsg];
        //NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
        return results;
    }

    while (sqlite3_step(statement) == SQLITE_ROW) {

        id value = nil;
        NSMutableDictionary *rowDict = [NSMutableDictionary dictionaryWithCapacity:0];
        for (int i = 0 ; i < sqlite3_column_count(statement) ; i++) {

            /*
if (strcasecmp(sqlite3_column_decltype(statement,i),"Boolean") == 0) {
value = [NSNumber numberWithBool:(BOOL)sqlite3_column_int(statement,i)];
} else */

            if (sqlite3_column_type(statement,i) == SQLITE_INTEGER) {
                value = [NSNumber numberWithInt:(int)sqlite3_column_int(statement,i)];
            } else if (sqlite3_column_type(statement,i) == SQLITE_FLOAT) {
                value = [NSNumber numberWithFloat:(float)sqlite3_column_double(statement,i)];   
            } else {

                if (sqlite3_column_text(statement,i) != nil) {
                    value = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement,i)];
                } else {
                    value = @"";
                }
            }

            if (value) {
                [rowDict setObject:value forKey:[NSString stringWithUTF8String:sqlite3_column_name(statement,i)]];
            }
        }

        [results addObject:rowDict];
        ////NSLog(@"rowDict -- %@", rowDict);
    }

    sqlite3_finalize(statement);
    statement = nil;

    return results;
}
+(int) rowCountForTable:(NSString *)table where:(NSString *)where{
    int tableCount = 0;
NSString *query = @"";

    if (where != nil && ![where isEqualToString:@""]) {
        query = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ WHERE %@",
                 table,where];
    } else {
        [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@",
         table];
    }

sqlite3_stmt *statement = nil;

    sqlite3 *database = [DBConnection sharedConnection].database;
    const char *sql = [query UTF8String];
    if (sqlite3_prepare_v2(database, sql, -1, &statement , NULL) != SQLITE_OK) {
        return 0;
    }

if (sqlite3_step(statement) == SQLITE_ROW) {    
        tableCount = sqlite3_column_int(statement,0);
    }

sqlite3_finalize(statement);
return tableCount;
}
+(void) errorMessage:(NSString *)msg{
    //UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"ERROR" message:msg delegate:nil cancelButtonTitle:@"OK" otherButtonTitles:nil];
    //[alert show];
    //[alert release];
}
+(void) closeConnection{
    sqlite3 *database = [DBConnection sharedConnection].database;
    if (sqlite3_close(database) != SQLITE_OK) {
        //NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(g_database));
        NSString *errorMsg = [NSString stringWithFormat:@"Failed to open database with message - '%s'.", sqlite3_errmsg(database)];
        [DBConnection errorMessage:errorMsg];
    }
}
-(id) initConnection {

    self = [super init];

if (self) {
//database = g_database;
if (g_database == nil) {
// The application ships with a default database in its bundle. If anything in the application
// bundle is altered, the code sign will fail. We want the database to be editable by users,
// so we need to create a copy of it in the application's Documents directory.
[self createEditableCopyOfDatabaseIfNeeded];
// Call internal method to initialize database connection
[self initializeDatabase];
}
}
return self;
}

#pragma mark - save db

-(void)createEditableCopyOfDatabaseIfNeeded {
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *dbDirectory = [documentsDirectory stringByAppendingPathComponent:[NSString stringWithFormat:@"%@", [[[NSBundle mainBundle] infoDictionary] objectForKey:@"CFBundleDisplayName"]]];

    if (![fileManager fileExistsAtPath:dbDirectory]) {
        [fileManager createDirectoryAtPath:dbDirectory withIntermediateDirectories:NO attributes:nil error:nil];
        [self addSkipBackupAttributeToItemAtURL:[[[NSURL alloc] initFileURLWithPath:dbDirectory isDirectory:YES] autorelease]];
    }

    NSString *writableDBPath = [dbDirectory stringByAppendingPathComponent:DB_NAME];
    success = [fileManager fileExistsAtPath:writableDBPath];
if (success) return;
    // The writable database does not exist, so copy the default to the appropriate location.
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:DB_NAME];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        //NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);

        NSString *errorMsg = [NSString stringWithFormat:@"Failed to create writable database file with message - %@.", [error localizedDescription]];
        [DBConnection errorMessage:errorMsg];
    }
}
-(BOOL)addSkipBackupAttributeToItemAtURL:(NSURL *)URL{
    const char* filePath = [[URL path] fileSystemRepresentation];

    const char* attrName = "com.apple.MobileBackup";
    u_int8_t attrValue = 1;

    int result = setxattr(filePath, attrName, &attrValue, sizeof(attrValue), 0, 0);
    return result == 0;
}

#pragma mark - Open the database connection and retrieve minimal information for all objects.

-(void)initializeDatabase {
    // The database is stored in the application bundle.
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);

    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *dbDirectory = [documentsDirectory stringByAppendingPathComponent:[NSString stringWithFormat:@"%@", [[[NSBundle mainBundle] infoDictionary] objectForKey:@"CFBundleDisplayName"]]];


    NSString *path = [dbDirectory stringByAppendingPathComponent:DB_NAME];
////NSLog(@"SQLite Root: %s", [path UTF8String]);

    // Open the database. The database was prepared outside the application.
    if (sqlite3_open([path UTF8String], &g_database) != SQLITE_OK) {
        // Even though the open failed, call close to properly clean up resources.
        sqlite3_close(g_database);
g_database = nil;
        //NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(g_database));
        NSString *errorMsg = [NSString stringWithFormat:@"Failed to open database with message - '%s'.", sqlite3_errmsg(g_database)];
        [DBConnection errorMessage:errorMsg];
    }
}
-(void)close {

if (g_database) {
// Close the database.
if (sqlite3_close(g_database) != SQLITE_OK) {
//NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(g_database));
            NSString *errorMsg = [NSString stringWithFormat:@"Failed to open database with message - '%s'.", sqlite3_errmsg(g_database)];
            [DBConnection errorMessage:errorMsg];
}
g_database = nil;
}
}

@end

use for create table and db

用于创建表和数据库

[DBConnection executeQuery:@"CREATE DATABASE demo"];
        if([DBConnection executeQuery:@"CREATE TABLE IF NOT EXISTS product (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)"]){

        }else{
            NSLog(@"CREATE TABLE IF NOT EXISTS product (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)");
        }

回答by mithlesh jha

You should do following:

您应该执行以下操作：

1) Change NSDocumentationDirectory to NSDocumentDirectory in NSSearchPathForDirectoriesInDomains function parameter in your code.

1) 在代码中的 NSSearchPathForDirectoriesInDomains 函数参数中将 NSDocumentationDirectory 更改为 NSDocumentDirectory。

2) Your code inside first 'if' block is not correct. It should instead be following:

2）您在第一个“if”块中的代码不正确。它应该如下：

if ([filemgr fileExistsAtPath:_databasePath] == NO)  {
// The database does not exist, so copy the default to the appropriate location.
        NSString *defaultDBPath = [[NSBundle mainBundle] pathForResource:@"contacts.db" ofType:nil];
        BOOL success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
        if (!success) {
            // Failed to copy db from bundle to documents folder.
        }
}

3) Finally write the code you already have to open the database.

3）最后编写您已经拥有的打开数据库的代码。

if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {
        char *errMsg;
        const char *sql_stmt = "CREATE TABLE IF NOT EXISTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT";

        if (sqlite3_exec(_contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK) {
            _lblStatus.text = @"Failed to create table";

        }

        sqlite3_close(_contactDB);
    } else {
        _lblStatus.text = @"Failed to open or create database";
    }

回答by Mithun R

how to add save get in sqlite

如何在sqlite中添加save get

ViewController.hcreatedata@property (strong, nonatomic) NSString *databasePath; @property (nonatomic) sqlite3 *contactDB;

ViewController.h createdata @property(strong, nonatomic) NSString *databasePath; @property (nonatomic) sqlite3 *contactDB;

ViewController.m

视图控制器.m

- (void)viewDidLoad {
[super viewDidLoad];


NSString *docsDir;
NSArray *dirPaths;

// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,    
NSUserDomainMask, YES);
docsDir = dirPaths[0];

// Build the path to the database file
_databasePath = [[NSString alloc]initWithString: [docsDir 
stringByAppendingPathComponent:@"mithun.db"]];

NSLog(@"CREATED DB PATH :%@", _databasePath);

//temp database path
///Users/nschool/Library/Developer/CoreSimulator/Devices/D4755C93-  38F5-4F51-933D-68501B039326/data/Containers/Data/Application/2DCCFB9B-8E96-43CC-A7FC-1AB3E8627397/Documents/contacts.db

NSFileManager *filemgr = [NSFileManager defaultManager];

if ([filemgr fileExistsAtPath: _databasePath ] == NO) {

    const char *dbpath = [_databasePath UTF8String];

    //sqlite3_open(const char *filename, <#sqlite3 **ppDb#>)

    if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {

        char *errMsg;
        //for query
        const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACT    
     (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT,    
     PHONE TEXT)";

          //check sqlite3 connection
        if (sqlite3_exec(_contactDB, sql_stmt, NULL, NULL, &errMsg)   
       != SQLITE_OK) {
            _status.text = @"Failed to create table";
        }
        sqlite3_close(_contactDB);
    }
    else {
        _status.text = @"Failed to open/create database";
    }
   }



}

savedata

保存数据

- (IBAction)saveData:(id)sender {

const char *dbpath = [_databasePath UTF8String];

sqlite3_stmt *statement;

if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {

    NSString *insertSQL = [NSString stringWithFormat:
            @"INSERT INTO CONTACT (name, address,      
         phone) VALUES (\"%@\", \"%@\", \"%@\")",
             _name.text, _address.text, _phone.text];

    NSLog(@"Insert query :%@", insertSQL);

    const char *insert_stmt = [insertSQL UTF8String];

    sqlite3_prepare_v2(_contactDB, insert_stmt,-1, &statement, NULL);

    if (sqlite3_step(statement) == SQLITE_DONE) {

        _status.text = @"Contact added";
        _address.text = @"";
        _phone.text = @"";

        UIAlertController *test = [UIAlertController    
     alertControllerWithTitle:@"Test" message:@"Test"  
 preferredStyle:UIAlertControllerStyleActionSheet];

        UIAlertAction *ok = [UIAlertAction actionWithTitle:@"OK"    
     style:UIAlertActionStyleDefault handler:nil];

        UIAlertAction *cancel = [UIAlertAction 
     actionWithTitle:@"Cancel" style:UIAlertActionStyleCancel  
  handler:nil];

        [test addAction:ok];
        [test addAction:cancel];

        [self presentViewController:test animated:YES completion:nil         
 ];



    }
    else {
        _status.text = @"Failed to add contact";
    }

    sqlite3_finalize(statement);
    sqlite3_close(_contactDB);
}

}

}

回答by sasidharan.M

.h

。H

#import <UIKit/UIKit.h>
#import <sqlite3.h>

@interface ViewController : UIViewController<UITextFieldDelegate>
@property (weak, nonatomic) IBOutlet UIImageView *imageviewTxt;

 @property (weak, nonatomic) IBOutlet UITextField *nameTxt;
  @property (weak, nonatomic) IBOutlet UITextField *mobilenoTxt;
   @property (weak, nonatomic) IBOutlet UITextField *destinationTxt;
 @property (weak, nonatomic) IBOutlet UILabel *status;
 - (IBAction)submit:(id)sender;
@property (strong, nonatomic) NSString *databasePath;
@property (nonatomic) sqlite3 *contactDB;

@end

回答by Jacob Barnard

You could use my library, LabQLite. It has a really handy way of connecting/opening/closing an SQLite 3 database for you, all encapsulated in an object-oriented feel.

你可以使用我的图书馆LabQLite。它为您提供了一种非常方便的连接/打开/关闭 SQLite 3 数据库的方法，所有这些都封装在面向对象的感觉中。

[LabQLiteDatabaseController activateSharedControllerWithFileFromLocalBundle:@"my_sqlite_3_database.sqlite3" toBeCopiedToAndUsedFromDirectory:@"" assumingNSDocumentDirectoryAsRootPath:YES overwrite:NO error:&error];

I've also created a model generator tool that you could use to get classes that represent your tables/views in the SQLite 3 database. Said classes would be instantly usable with LabQLite: https://github.com/jmbarnardgh/labqlite_model_generator

我还创建了一个模型生成器工具，您可以使用它来获取代表 SQLite 3 数据库中的表/视图的类。所述类可以立即与 LabQLite 一起使用：https: //github.com/jmbarnardgh/labqlite_model_generator