ios使用sqlite操作数据库简单实例

static pthread_mutex_t dbMutex=PTHREAD_MUTEX_INITIALIZER;
static sem_t* dbSem[10];
static sqlite3* db;
static sqlite3_stmt* insertStmt=NULL;
static sqlite3_stmt* queryStmt=NULL;

void createAndOpenDB(void);
void* insertIntoDB(void*);
void queryFromDB(void);
void readFromDB();
void createInsertThread(sem_t* sem);

void queryFromDB(void)
{
    //sem_t* thisSem=(sem_t*)info;
    pthread_mutex_lock(&dbMutex);
    if(queryStmt==NULL){
        NSString* selectSQL=@"select count(*) from contacts";
        if(sqlite3_prepare_v2(db, [selectSQL cStringUsingEncoding:NSUTF8StringEncoding], -1, &queryStmt, NULL)!=SQLITE_OK)
            @throw [NSException exceptionWithName:@"query error" reason:nil userInfo:nil];
    }
    if(sqlite3_step(queryStmt)!=SQLITE_ROW)
        @throw [NSException exceptionWithName:@"query error" reason:nil userInfo:nil];
    int count=sqlite3_column_int(queryStmt, 0);
    NSLog(@"%d\n",count);
    sqlite3_reset(queryStmt);
    pthread_mutex_unlock(&dbMutex);
    //sem_post(thisSem);
}
void* insertIntoDB(void* info)
{
    NSLog(@"start");
    NSAutoreleasePool* pool=[[NSAutoreleasePool alloc]init];
    //pthread_cond_t* thisCond=(pthread_cond_t*)info;
    sem_t* thisSem=(sem_t*)info;
    pthread_mutex_lock(&dbMutex);
    sqlite3_exec(db, [@"begin transaction" cStringUsingEncoding:NSUTF8StringEncoding], NULL, NULL, NULL);
    NSString* insertSQL=@"insert into contacts values(NULL,?,?)";
    if(insertStmt==NULL){
        if(sqlite3_prepare_v2(db,[insertSQL cStringUsingEncoding:NSUTF8StringEncoding], -1, &insertStmt, NULL)!=SQLITE_OK){
            @throw [NSException exceptionWithName:@"prepare statement error" reason:nil userInfo:nil];
        }
    }
    NSString* name=@"eeeyes";
    const char* nameBytes=[name cStringUsingEncoding:NSUTF8StringEncoding];
    NSString* telphone=@"1234";
    const char* telphoneBytes=[telphone cStringUsingEncoding:NSUTF8StringEncoding];
    for(int i=0;i<10000;i++){
        sqlite3_bind_text(insertStmt, 1, nameBytes, -1, SQLITE_STATIC);
        sqlite3_bind_text(insertStmt, 2, telphoneBytes, -1, SQLITE_STATIC);
        if(sqlite3_step(insertStmt)!=SQLITE_DONE){
            NSLog(@"insert error:%s",sqlite3_errmsg(db));
            sqlite3_close(db);
            @throw [NSException exceptionWithName:@"insert error" reason:nil userInfo:nil];
        }
        sqlite3_reset(insertStmt);
    }
    sqlite3_exec(db, [@"commit" cStringUsingEncoding:NSUTF8StringEncoding], NULL, NULL, NULL);
//    sleep(20);
//    pthread_cond_signal(thisCond);
    sem_post(thisSem);
    pthread_mutex_unlock(&dbMutex);
    //[pool drain];
    [pool release];
    NSLog(@"end");
}
void createInsertThread(sem_t* sem)
{
    pthread_t threadID;
    //pthread_create(NULL, NULL, insertIntoDB, NULL);
    pthread_create(&threadID, NULL, insertIntoDB, sem);
}
void createAndOpenDB(void)
{
    //init wait condition
    for(int i=0;i<10;i++)
         dbSem[i]=sem_open([[NSString stringWithFormat:@"%d",i]cStringUsingEncoding:NSASCIIStringEncoding], O_CREAT,S_IRUSR,0);
    //create database
    sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);//single thread module
    if(sqlite3_open([@"/eeeyes.db" cStringUsingEncoding:NSUTF8StringEncoding], &db)!=SQLITE_OK){
        sqlite3_close(db);
        @throw [NSException exceptionWithName:@"create db fail" reason:nil userInfo:nil];
    }
    //create table
    NSString* createTableSQL=@"create table if not exists contacts(id integer primay key autoincreament,"
                             "name text,telphone text)";
    char* erroMessage;
    if(sqlite3_exec(db, [createTableSQL cStringUsingEncoding:NSUTF8StringEncoding], NULL, NULL, &erroMessage)!=SQLITE_OK){
        NSLog(@"create table fail:%s",erroMessage);
        @throw [NSException exceptionWithName:@"create table fail" reason:nil userInfo:nil];
    }
}
@implementation AppDelegate

- (void)dealloc
{
[_window release];
[super dealloc];
}

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{

    clock_t start,end;
    createAndOpenDB();
    start=clock();
    for(int i=0;i<10;i++)
        createInsertThread(*(dbSem+i));
    //sleep(10);
    for(int i=0;i<10;i++){
        sem_wait(*(dbSem+i));
    }
    queryFromDB();
    end=clock();
    printf("total seconds:%f\n",(double)(end-start)/CLOCKS_PER_SEC);
    self.window = [[[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]] autorelease];
    // Override point for customization after application launch.
    self.window.backgroundColor = [UIColor whiteColor];
    [self.window makeKeyAndVisible];
    return YES;
}
@end
似乎ios对无名信号量的支持不是很好，所以使用了有名信号量。
连接过去和未来，我们为此而存在

摘自 eeeyes

补充：移动开发 , 其他 ,