iOS sqlite3 具体代码粘贴

- (id)init {

    if ((self = [super init])) {

        dataBase_state = FALSE;

         path = [[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"] stringByAppendingPathComponent:@"userInfo.sqlite3"];

    }

    return self;

}

- (void)close {

    if (userInfo_database != NULL) {

        sqlite3_close(userInfo_database);

        userInfo_database = NULL;

    }

    dataBase_state = FALSE;

}

- (BOOL)open {

    if (dataBase_state == TRUE) {

        return YES;

    }

    if (sqlite3_open([path UTF8String], &userInfo_database) != SQLITE_OK) {

        sqlite3_close(userInfo_database);

        return NO;

    }

    char *errorMsg;

    //创建个人信息表

    NSString *sqlStr = @"create table if not exists USERINFO (NAME text,TELNUMBER text, SEX text,AGE integer,ADDRESS text,REMARK text,ID INTEGER PRIMARY KEY AUTOINCREMENT);";

    if (sqlite3_exec(userInfo_database, [sqlStr UTF8String], NULL, NULL, &errorMsg)) {

        NSLog(@"%s",errorMsg);

        sqlite3_close(userInfo_database);

        return FALSE;

    }

    return YES;

}

#pragma mark - add

- (BOOL)insertRecord_UserInfo:(NSString *)userName UserTel:(NSString *)telNumber UserSex:(NSString *)userSex UserAge:(NSInteger)userage UserAddress:(NSString *)address UserRemark :(NSString *)remark {

    sqlite3_stmt *stmt;

    NSString *sqlStr = @"insert into USERINFO (NAME,TELNUMBER, SEX, AGE,ADDRESS,REMARK) values(?, ?, ?, ?, ?, ?);";

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

        sqlite3_bind_text(stmt, 1, [userName UTF8String], -1, NULL);

        sqlite3_bind_text(stmt, 2, [telNumber UTF8String], -1, NULL);

        sqlite3_bind_text(stmt, 3, [userSex UTF8String], -1, NULL);

        sqlite3_bind_int(stmt, 4, userage);

        sqlite3_bind_text(stmt, 5, [address UTF8String], -1, NULL);

        sqlite3_bind_text(stmt, 6, [remark UTF8String], -1, NULL);

    }else {

        sqlite3_finalize(stmt);

        NSLog(@" error msg: %s",sqlite3_errmsg(userInfo_database));

        return NO;

    }

    if (sqlite3_step(stmt) != SQLITE_DONE) {

        NSLog(@"msg: %s",sqlite3_errmsg(userInfo_database));

        sqlite3_finalize(stmt);

        return FALSE;

    }

    sqlite3_finalize(stmt);

    return YES;

}

#pragma mark - delete

- (BOOL)deleteUserFromUserInfo:(NSInteger)primarykey {

    NSString *sqlStr = @"delete from USERINFO where ID = ?;";

    sqlite3_stmt *stmt;

    if (sqlite3_prepare_v2(userInfo_database, [sqlStr UTF8String], -1, &stmt, NULL) != SQLITE_OK) {

        sqlite3_finalize(stmt);

        return NO ;

    }

    sqlite3_bind_int(stmt, 1, primarykey);

    BOOL result =  sqlite3_step(stmt);

    sqlite3_finalize(stmt);

    return result;

}

#pragma mark  remove

- (BOOL)clearUserInfoTable {

    NSString *sqlStr = @"delete from USERINFO";

    sqlite3_stmt *stmt;

    if (sqlite3_prepare_v2(userInfo_database, [sqlStr UTF8String], -1, &stmt, nil) != SQLITE_OK) {

        sqlite3_finalize(stmt);

        return NO;

    }

    if (sqlite3_step(stmt) == SQLITE_DONE) {

        sqlite3_finalize(stmt);

        return YES;

    }

    return NO;

}

#pragma mark - search

- (void)searchUserInfoUserName:(NSString *)searchName Result:(NSMutableArray *)resutlArray{

    if (searchName.length <=0) {

        return;

    }

    sqlite3_stmt *stmt;

    NSString *sqlStr = @"select NAME ,TELNUMBER,SEX ,AGE ,ADDRESS,REMARK,ID from USERINFO where NAME = ?;";

    if (sqlite3_prepare_v2(userInfo_database, [sqlStr UTF8String], -1, &stmt, NULL) != SQLITE_OK) {

        NSLog(@"%s ",sqlite3_errmsg(userInfo_database));

        sqlite3_finalize(stmt);

        return;

    }

    sqlite3_bind_text(stmt, 1, [searchName UTF8String], -1, NULL);

    while (sqlite3_step(stmt) == SQLITE_ROW) {

        char *nameString = (char *) sqlite3_column_text(stmt, 0);

        char *telString = (char *)sqlite3_column_text(stmt, 1);

        char *sexString = (char *)sqlite3_column_text(stmt, 2);

        int age = sqlite3_column_int(stmt, 3);

        char *addressString = (char *)sqlite3_column_text(stmt, 4);

        char *remarkString = (char *)sqlite3_column_text(stmt, 5);

        int primaryKey = sqlite3_column_int(stmt, 6);

        

        NSString *userName = (nameString ? [NSString stringWithUTF8String:nameString] : @" ");

        NSString *userTel = (telString ? [NSString stringWithUTF8String:telString] : @" ");

        NSString *userSex = (sexString ? [NSString stringWithUTF8String:sexString] : @" ");

        NSInteger userAge = (NSInteger)age;

        NSString *userAddress = (addressString ? [NSString stringWithUTF8String:addressString] : @" ");

        NSString *userRemark = (remarkString ? [NSString stringWithUTF8String:remarkString] : @" ");

        

        NSMutableDictionary *resultDict = [[NSMutableDictionary alloc] init];

        [resultDict setObject:userName forKey:@"userName"];

        [resultDict setObject:userTel forKey:@"userTel"];

        [resultDict setObject:userSex forKey:@"userSex"];

        [resultDict setObject:[NSNumber numberWithInteger:userAge] forKey:@"userAge"];

        [resultDict setObject:userAddress forKey:@"userAddress"];

        [resultDict setObject:userRemark forKey:@"userRemark"];

        [resultDict setObject:[NSNumber numberWithInt:primaryKey] forKey:@"primaryKey"];

        [resutlArray addObject:resultDict];

        [resultDict release];

        resultDict = nil;

    }

    sqlite3_finalize(stmt);

}

- (void)searchUserInfoUserSex:(NSString *)searchSex Result:(NSMutableArray *)resutlArray {

    if (searchSex.length <=0) {

        return;

    }

    sqlite3_stmt *stmt;   // 模糊查询

    NSString *querySQL = [NSString stringWithFormat:@"SELECT NAME ,TELNUMBER,SEX ,AGE ,ADDRESS,REMARK,ID from USERINFO where SEX like '%@\%%'",searchSex];

    if (sqlite3_prepare_v2(userInfo_database, [querySQL UTF8String], -1, &stmt, NULL) != SQLITE_OK) {

        NSLog(@"%s ",sqlite3_errmsg(userInfo_database));

        sqlite3_finalize(stmt);

        return;

    }

    while (sqlite3_step(stmt) == SQLITE_ROW) {

        char *nameString = (char *) sqlite3_column_text(stmt, 0);

        char *telString = (char *)sqlite3_column_text(stmt, 1);

        char *sexString = (char *)sqlite3_column_text(stmt, 2);

        int age = sqlite3_column_int(stmt, 3);

        char *addressString = (char *)sqlite3_column_text(stmt, 4);

        char *remarkString = (char *)sqlite3_column_text(stmt, 5);

        int primaryKey = sqlite3_column_int(stmt, 6);

        

        NSString *userName = (nameString ? [NSString stringWithUTF8String:nameString] : @" ");

        NSString *userTel = (telString ? [NSString stringWithUTF8String:telString] : @" ");

        NSString *userSex = (sexString ? [NSString stringWithUTF8String:sexString] : @" ");

        NSInteger userAge = (NSInteger)age;

        NSString *userAddress = (addressString ? [NSString stringWithUTF8String:addressString] : @" ");

        NSString *userRemark = (remarkString ? [NSString stringWithUTF8String:remarkString] : @" ");

        

        NSMutableDictionary *resultDict = [[NSMutableDictionary alloc] init];

        [resultDict setObject:userName forKey:@"userName"];

        [resultDict setObject:userTel forKey:@"userTel"];

        [resultDict setObject:userSex forKey:@"userSex"];

        [resultDict setObject:[NSNumber numberWithInteger:userAge] forKey:@"userAge"];

        [resultDict setObject:userAddress forKey:@"userAddress"];

        [resultDict setObject:userRemark forKey:@"userRemark"];

        [resultDict setObject:[NSNumber numberWithInt:primaryKey] forKey:@"primaryKey"];

        [resutlArray addObject:resultDict];

        [resultDict release];

        resultDict = nil;

    }

    sqlite3_finalize(stmt);

}

- (void)searchUserInfoUserAge:(NSString *)searchAge Result:(NSMutableArray *)resutlArray {

    if (searchAge.length <=0) {

        return;

    }

    sqlite3_stmt *stmt;

    NSString *sqlStr = @"select NAME ,TELNUMBER,SEX ,AGE ,ADDRESS,REMARK, ID from USERINFO where AGE = ?;";

    if (sqlite3_prepare_v2(userInfo_database, [sqlStr UTF8String], -1, &stmt, NULL) != SQLITE_OK) {

        NSLog(@"%s ",sqlite3_errmsg(userInfo_database));

        sqlite3_finalize(stmt);

        return;

    }

    sqlite3_bind_text(stmt, 1, [searchAge UTF8String], -1, NULL);

    while (sqlite3_step(stmt) == SQLITE_ROW) {

        char *nameString = (char *) sqlite3_column_text(stmt, 0);

        char *telString = (char *)sqlite3_column_text(stmt, 1);

        char *sexString = (char *)sqlite3_column_text(stmt, 2);

        int age = sqlite3_column_int(stmt, 3);

        char *addressString = (char *)sqlite3_column_text(stmt, 4);

        char *remarkString = (char *)sqlite3_column_text(stmt, 5);

        int primaryKey = sqlite3_column_int(stmt, 6);

        

        NSString *userName = (nameString ? [NSString stringWithUTF8String:nameString] : @" ");

        NSString *userTel = (telString ? [NSString stringWithUTF8String:telString] : @" ");

        NSString *userSex = (sexString ? [NSString stringWithUTF8String:sexString] : @" ");

        NSInteger userAge = (NSInteger)age;

        NSString *userAddress = (addressString ? [NSString stringWithUTF8String:addressString] : @" ");

        NSString *userRemark = (remarkString ? [NSString stringWithUTF8String:remarkString] : @" ");

        

        NSMutableDictionary *resultDict = [[NSMutableDictionary alloc] init];

        [resultDict setObject:userName forKey:@"userName"];

        [resultDict setObject:userTel forKey:@"userTel"];

        [resultDict setObject:userSex forKey:@"userSex"];

        [resultDict setObject:[NSNumber numberWithInteger:userAge] forKey:@"userAge"];

        [resultDict setObject:userAddress forKey:@"userAddress"];

        [resultDict setObject:userRemark forKey:@"userRemark"];

        [resultDict setObject:[NSNumber numberWithInt:primaryKey] forKey:@"primaryKey"];

        [resutlArray addObject:resultDict];

        [resultDict release];

        resultDict = nil;

    }

    sqlite3_finalize(stmt);

}

- (BOOL)serachUserInfoUserName:(NSString *)userName UserTel:(NSString *)userTel{

    if (userName.length<=0&&userTel.length <=0) {

        return NO;

    }

    NSInteger count = 0;

    sqlite3_stmt *stmt;

    NSString *sqlStr = @"select NAME ,TELNUMBER, SEX,AGE,ADDRESS,REMARK from USERINFO where NAME = ? and TELNUMBER = ?;";

    if (sqlite3_prepare_v2(userInfo_database, [sqlStr UTF8String], -1, &stmt, NULL)!=SQLITE_OK) {

        NSLog(@"%s ",sqlite3_errmsg(userInfo_database));

        sqlite3_finalize(stmt);

        return NO;

    }

    sqlite3_bind_text(stmt, 1, [userName UTF8String], -1, NULL);

    sqlite3_bind_text(stmt, 2, [userTel UTF8String], -1, NULL);

    if (sqlite3_step(stmt) == SQLITE_ROW) {

        count ++;

    }

    sqlite3_finalize(stmt);

    if (count >0) {

        return YES;

    }else {

        return NO;

    }

}

- (void)readAllUserInfo :(NSMutableArray *)userInfoArray {

    NSString *sqlStr = @"select * from USERINFO";

    sqlite3_stmt *stmt;

    if (sqlite3_prepare_v2(userInfo_database, [sqlStr UTF8String], -1, &stmt, nil) != SQLITE_OK) {

        sqlite3_finalize(stmt);

        return ;

    }

    while (sqlite3_step(stmt) == SQLITE_ROW) {

        char *nameString = (char *) sqlite3_column_text(stmt, 0);

        char *telString = (char *)sqlite3_column_text(stmt, 1);

        char *sexString = (char *)sqlite3_column_text(stmt, 2);

        int age = sqlite3_column_int(stmt, 3);

        char *addressString = (char *)sqlite3_column_text(stmt, 4);

        char *remarkString = (char *)sqlite3_column_text(stmt, 5);

        int primaryKey = sqlite3_column_int(stmt, 6);

        NSString *userName = (nameString ? [NSString stringWithUTF8String:nameString] : @" ");

        NSString *userTel = (telString ? [NSString stringWithUTF8String:telString] : @" ");

        NSString *userSex = (sexString ? [NSString stringWithUTF8String:sexString] : @" ");

        NSInteger userAge = (NSInteger)age;

        NSString *userAddress = (addressString ? [NSString stringWithUTF8String:addressString] : @" ");

        NSString *userRemark = (remarkString ? [NSString stringWithUTF8String:remarkString] : @" ");

        

        NSMutableDictionary *resultDict = [[NSMutableDictionary alloc] init];

        [resultDict setObject:userName forKey:@"userName"];

        [resultDict setObject:userTel forKey:@"userTel"];

        [resultDict setObject:userSex forKey:@"userSex"];

        [resultDict setObject:[NSNumber numberWithInteger:userAge] forKey:@"userAge"];

        [resultDict setObject:userAddress forKey:@"userAddress"];

        [resultDict setObject:userRemark forKey:@"userRemark"];

        [resultDict setObject:[NSNumber numberWithInt:primaryKey] forKey:@"primaryKey"];

        [userInfoArray addObject:resultDict];

        [resultDict release];

        resultDict = nil;

    }

    sqlite3_finalize(stmt);

}

#pragma mark - change

- (BOOL)updateUserInfoPrimaryKey:(NSInteger)primarykey UserName:(NSString *)userName UserTel:(NSString *)userTel UserSex:(NSString *)userSex UserAge:(NSInteger)userAge UserAddress:(NSString *)userAddress UserRemark:(NSString *)userRemark {

    if (userName.length <=0) {

        return NO;

    }

    sqlite3_stmt *stmt_upd;

    NSString *sqlStr = @"update USERINFO set NAME = ? , TELNUMBER = ? , SEX = ? , AGE = ? , ADDRESS = ? , REMARK = ? where ID = ?;";

    if (sqlite3_prepare_v2(userInfo_database, [sqlStr UTF8String], -1, &stmt_upd, NULL) == SQLITE_OK) {

        sqlite3_bind_text(stmt_upd, 1, [userName UTF8String], -1, NULL);

        sqlite3_bind_text(stmt_upd, 2, [userTel UTF8String], -1, NULL);

        sqlite3_bind_text(stmt_upd, 3, [userSex UTF8String], -1, NULL);

        sqlite3_bind_int(stmt_upd, 4, userAge);

        sqlite3_bind_text(stmt_upd, 5, [userAddress UTF8String], -1, NULL);

        sqlite3_bind_text(stmt_upd, 6, [userRemark UTF8String], -1, NULL);

        sqlite3_bind_int(stmt_upd, 7, primarykey);

    } else {

        NSLog(@"%s ",sqlite3_errmsg(userInfo_database));

        sqlite3_finalize(stmt_upd);

        return NO;

    }

    if (sqlite3_step(stmt_upd) !=SQLITE_DONE) {

        NSLog(@"%s ",sqlite3_errmsg(userInfo_database));

        sqlite3_finalize(stmt_upd);

        return NO;

    }

    sqlite3_finalize(stmt_upd);

    return YES;

}

- (NSInteger)getUserInfoTableCount {

    NSInteger count = 0;

    sqlite3_stmt *stmt;

    NSString *sqlStr = @"select count(*) from USERINFO";

    if (sqlite3_prepare_v2(userInfo_database, [sqlStr UTF8String], -1, &stmt, NULL)!= SQLITE_OK) {

        sqlite3_finalize(stmt);

        return count;

    }

    if (sqlite3_step(stmt) == SQLITE_ROW) {

        count = sqlite3_column_int(stmt, 0);

    }

    sqlite3_finalize(stmt);

    return count;

}