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_p