sqlite 数据库基本操作

//

//  DBBasic.m

//  XTest

//

//  Created by user on 11-12-27.

//  Copyright (c) 2011年 __MyCompanyName__. All rights reserved.

//

#import "DBBasic.h"

@implementation DBBasic

+ (void)createTest

{

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *dbName = [[paths objectAtIndex:0] stringByAppendingPathComponent:@"cache.db"];

    sqlite3 *db = nil;

    NSLog(@"path %@", dbName);

    

    if(sqlite3_open([dbName UTF8String], &db) == SQLITE_OK){

        NSLog(@"open ok!");

    }

    

    const char *sql = "create table if not exists short_cache (url text primary key, time integer, data bolb, size integer)";

    if(sqlite3_exec(db, sql, NULL, NULL, NULL) == SQLITE_OK){

        NSLog(@"table create ok!");

    }

    

    NSString *insertSql = @"insert or replace into short_cache values (?,?,?,?)";

    sqlite3_stmt *stmt;

    if(sqlite3_prepare_v2(db, [insertSql UTF8String], -1, &stmt, nil)==SQLITE_OK){

        NSLog(@"prepare ok!");

    }

    

    NSString *url = @"http://www.baidu.com";

    NSString *content = @"this is content";

    sqlite3_bind_text(stmt, 1, [url UTF8String], [url length], NULL);

    sqlite3_bind_int(stmt, 2, time(0));

    sqlite3_bind_blob(stmt, 3, (void *)[content UTF8String], [content length], NULL);

    sqlite3_bind_int(stmt, 4, [content length]);

    

    if(sqlite3_step(stmt) == SQLITE_DONE){

        NSLog(@"step ok!");

    }

    

    sqlite3_finalize(stmt);

    

    NSString *queryStr = @"select time, data, size from short_cache where url = ?";

    

    sqlite3_prepare_v2(db, [queryStr UTF8String], [queryStr length], &stmt, NULL);

    sqlite3_bind_text(stmt, 1, [url UTF8String], [url length], NULL);

    while (sqlite3_step(stmt) == SQLITE_ROW) {

        int time = sqlite3_column_int(stmt, 0);

        const char *content = sqlite3_column_blob(stmt, 1);

        NSString *ctx = [NSString stringWithUTF8String:content];

        NSLog(@"insert time:%d ctx:%@", time, ctx);

    }

    

    sqlite3_finalize(stmt);

    

    sqlite3_close(db);

}

@end