要使用的时候,先创建全局属性:
@property (nonatomic, strong) FMDatabase *db;
1,获取数据库
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documents = [[paths objectAtIndex:0] stringByAppendingPathComponent:@"userInfoDB"];
NSFileManager *fileManager = [[NSFileManager alloc]init];
//创建新目录
BOOL createDirectory = [fileManager createDirectoryAtPath:documents withIntermediateDirectories:YES attributes:nil error:nil];
if (createDirectory) {
self.database_path = [documents stringByAppendingPathComponent:USER_DB_NAME];
NSLog(@"数据库地址:%@",self.database_path);
//获得数据库
self.db = [FMDatabase databaseWithPath:self.database_path];
}
2,创建表
- (BOOL)createUserrTable{
//建表语句,ID为主键
NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (%@ TEXT PRIMARY KEY, %@ TEXT, %@ TEXT, %@ TEXT, %@ TEXT)",TABLE_NAME, ID, NAME, BYNAME, PHOTO, HXID];
// NSLog(@"----:%@", sql);
BOOL result = [self.db executeUpdate:sql];
if (result) {
// NSLog(@"创表成功");
}else{
// NSLog(@"创表失败");
}
return result1;
}
3,插入数据
-(BOOL) insertUserTableWithUser:(User *)user{
BOOL result = NO;
if ([self.db open]) {
@try {
if ([self createUserrTable]) {
//插入语句(会覆盖主键值已存在的数据)
NSString *insertSql = [NSString stringWithFormat:@"insert or replace into %@ values('%@', '%@', '%@', '%@', '%@')", TABLE_NAME, user.userId , user.nickname, user.byName, user.photo, user.hxName];
//插入语句(适合没有主键的表,不会覆盖)
// NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO %@(%@, %@, %@, %@, %@) VALUES ('%@', '%@', '%@', '%@', '%@')", USER_TABLE_NAME, ID, NAME, BYNAME, PHOTO,HXID, user.userId , user.nickname, user.byName, user.photo, user.hxName];
result = [self.db executeUpdate:insertSql];
if (result) {
NSLog(@"用户数据插入成功");
}
else {
NSLog(@"用户数据插入失败");
}
}
}
@catch (NSException *exception) {}
@finally {}
[self.db close];
}
return result;
}
4,查询数据
-(User *) selectDataFromTableWithHxName:(NSString *)hxName orUserId:(NSString *)userId{
User *user = [[User alloc] init];
if ([self.db open]) {
@try {
if ([self createUserrTable]) {
//查询语句,根据id查询
NSString *sql = [NSString stringWithFormat:@"select * from %@ where %@ = '%@'",TABLE_NAME, ID, userId];
FMResultSet * rs = [self.db executeQuery:sql];
while ([rs next]) {
user.userId = [rs stringForColumn:ID];
user.nickname = [rs stringForColumn:NAME];
user.byName = [rs stringForColumn:BYNAME];
user.photo = [rs stringForColumn:PHOTO];
user.hxName = [rs stringForColumn:HXID];
}
}
}
@catch (NSException *exception) {}
@finally {}
[self.db close];
}
// [user logInfo];
return user;
}
5,删除数据
-(BOOL) deleteUserTableWithUser:(User *)user{
BOOL result = NO;
if ([self.db open]) {
@try {
if ([self createUserrTable]) {
NSString *deleteSql = [NSString stringWithFormat:@"delete from %@ where %@ = '%@'", TABLE_NAME, HXID, user.hxName];
result = [self.db executeUpdate:deleteSql];
if (result) {
NSLog(@"用户数据删除成功");
}
else {
NSLog(@"用户数据删除失败");
}
}
}
@catch (NSException *exception) {}
@finally {}
[self.db close];
}
return result;
}
6,修改数据
-(BOOL) updateUserTableWithUser:(User *)user{
BOOL result = NO;
if ([self.db open]) {
@try {
if ([self createUserrTable]) {
//修改语句,
NSString *sql = [self.db executeUpdate:@"update tableName set byName = ? WHERE id = ?", user.nickname, user.userId];
result = [self.db executeUpdate:sql];
if (result) {
NSLog(@"用户数据修改成功");
}
else {
NSLog(@"用户数据修改失败");
}
}
}
@catch (NSException *exception) {}
@finally {}
[self.db close];
}
return result;
}