- (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;
}