ios sqlite数据库的基本操作

1.创建数据库

 

 NSString *docsDir;
    NSArray *dirPaths;
    
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSLog(@"dirPaths is %@",dirPaths);
    docsDir = [dirPaths objectAtIndex:0];
    NSLog(@"docDir is %@",docsDir);
    
    // Build the path 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];
        NSLog(@"dbpath is %s",dbpath);
        if (sqlite3_open(dbpath, &contactDB)==SQLITE_OK)     //contactDB的定义实在.h文件中    sqlite3 *contactDB;
        {
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS(ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT,PHONE TEXT)";
            if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg)!=SQLITE_OK) {
                status.text = @"创建表失败\n";
            }
        }
        else 
        {
            status.text = @"创建/打开数据库失败";
        }
    }

2.插入数据

sqlite3_stmt *statement;
    
    const char *dbpath = [databasePath UTF8String];   //数据库文件的路径
    
    if (sqlite3_open(dbpath, &contactDB)==SQLITE_OK) {
        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO CONTACTS (name,address,phone) VALUES(\"%@\",\"%@\",\"%@\")",name.text,address.text,phone.text];
        const char *insert_stmt = [insertSQL UTF8String];
        //预处理
        sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement)==SQLITE_DONE) {
            status.text = @"已存储到数据库";
            name.text = @"";
            address.text = @"";
            phone.text = @"";
        }
        else
        {
            status.text = @"保存失败";
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }

3.查询数据

 

const char *dbpath = [databasePath UTF8String];
    sqlite3_stmt *statement;
    
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) 
    {
        NSString *querySQL = [NSString stringWithFormat:@"SELECT address,phone from contacts where name=\"%@\"",name.text];
        const char *query_stmt = [querySQL UTF8String];
        if (sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK) 
        {
            if (sqlite3_step(statement) == SQLITE_ROW) 
            {
                NSString *addressField = [[NSString alloc] initWithUTF8String:(const char *)sqlite3_column_text(statement, 0)];
                address.text = addressField;
                
                NSString *phoneField = [[NSString alloc] initWithUTF8String:(const char *)sqlite3_column_text(statement, 1)];   //1可能是列数
                phone.text = phoneField;
                
                status.text = @"已查到结果";
                [addressField release];
                [phoneField release];
            }
            else {
                status.text = @"未查到结果";
                address.text = @"";
                phone.text = @"";
            }
            sqlite3_finalize(statement);
        }
        
        sqlite3_close(contactDB);
    }

4.预处理的用法

NSString *filename = [self dataFilePath];              //数据库文件的路径
NSLog(@"%@",filename);
if (sqlite3_open([filename UTF8String], &db) != SQLITE_OK) {
sqlite3_close(db);
NSAssert(NO,@"数据库打开失败。");
} else {
NSString *qsql = [NSString stringWithFormat: @"SELECT %@,%@,%@ FROM %@ where %@ = ?", FIELDS_NAME_SID, FIELDS_NAME_SNAME, FIELDS_NAME_SCLASS, TABLE_NAME,FIELDS_NAME_SID];
NSLog(@"%@",qsql);
sqlite3_stmt *statement;
//预处理过程
if (sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
//绑定参数开始
sqlite3_bind_text(statement, 1, [studentId.text UTF8String], -1, NULL);
//执行
while (sqlite3_step(statement) == SQLITE_ROW) {
char *field1 = (char *) sqlite3_column_text(statement, 0);
NSString *field1Str = [[NSString alloc] initWithUTF8String: field1];
studentId.text = field1Str;
char *field2 = (char *) sqlite3_column_text(statement, 1);
NSString *field2Str = [[NSString alloc] initWithUTF8String: field2];
studentName.text = field2Str;
char *field3 = (char *) sqlite3_column_text(statement, 2);
NSString *field3Str = [[NSString alloc] initWithUTF8String: field3];
studentClass.text = field3Str;
[field1Str release];
[field2Str release];
[field3Str release];
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}