#define kDBName @"SHS150711.sqlite"//数据库表名
//数据库指针(对象)
static sqlite3 *db = nil;
+ (sqlite3 *)open{
@synchronized(self){
//打开过了直接返回
if (db != nil) {
return db;
}else{
//第一个参数: 数据库路径
//第二个参数: 数据库对象地址, 通过地址改值, 返回一个打开的数据库
//1. 数据库路径
//注意: 数据库是备份的数据, 需要备份的数据都是存放在沙盒的 documents 里面
NSString *documents = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)objectAtIndex:0];
NSString *dbPath = [documents stringByAppendingPathComponent:kDBName];
//2. 创建并且打开数据库, 如果没有该数据库会自动创建并且打开
int result = sqlite3_open(dbPath.UTF8String, &db);
//该函数的作用是打开路径数据库, 并且给db 赋值.
//3. 判断数据库是否打开, 如果打开, 则创建数据库表
if (result == SQLITE_OK) {
//4. sql 语句
NSString *sqlString = @"CREATE TABLE 'studentInfo' ('uid' INTEGER PRIMARY KEY NOT NULL, 'username' text, 'pwd' text)";
//5. 执行sql语句
//sqlite3 执行函数
//db : 数据库对象
//sql sql 语句 C字符串
//回调函数, 查询时使用
//作用: 数据库db 执行sqlstring 语句.并返回执行结果, int 型
int result2 = sqlite3_exec(db, sqlString.UTF8String,nil, nil, nil);
if (result2 == SQLITE_OK) {
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"表创建成功");
}else{
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"表创建失败, 或者已经创建过");
}
}//if 结束
}//else 结束
return db;
}//sync 结束
}
+ (void)close{
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据库关闭");
db = nil;
}
}
//添加数据
+ (BOOL)insertStudent:(Student *)student{
BOOL isSuccess = NO;
//1. 数据库对象
sqlite3 *db = [self open];
//2. sql语句
NSString *sqlString = [NSString stringWithFormat:@"INSERT INTO 'studentInfo' values(%ld, '%@', '%@')", student.uid, student.username, student.pwd];
//3. 执行exe
int result = sqlite3_exec(db, sqlString.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据库插入成功");
isSuccess = YES;
}else{
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据库插入失败");
}
return isSuccess;
}
//删除数据
+ (BOOL)deleteStudent:(Student *)student{
BOOL isSuccess = NO;
//1. 数据库对象
sqlite3 *db = [self open];
//2. sql 语句
NSString *sqlString = [NSString stringWithFormat:@"DELETE FROM 'studentInfo' WHERE uid = %ld", student.uid];
//3. 执行
int result = sqlite3_exec(db, sqlString.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据删除成功");
isSuccess = YES;
}
return isSuccess;
}
//更改数据
+ (BOOL)updateStudent:(Student *)student pwd:(NSString *)pwd{
BOOL isSuccess = NO;
//1. 数据库对象
sqlite3 *db = [self open];
//2. sql语句
NSString *sqlString = [NSString stringWithFormat:@"UPDATE 'studentInfo' set 'pwd' = '%@' WHERE uid = %ld;", pwd, student.uid];
//3. 执行
int result = sqlite3_exec(db, sqlString.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据更新成功");
isSuccess = YES;
}else{
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据更新失败");
}
return isSuccess;
}
//查询某一数据
+ (Student *)findStudentByUID:(NSInteger)uid{
Student *student = nil;//创建返回对象
//1. 数据库对象
sqlite3 *db = [self open];
//2. sql 字符串
NSString *sqlString = [NSString stringWithFormat:@"SELECT * FROM 'studentInfo' WHERE uid = %ld", uid];
//3. 创建 stmt statement 语句对象
sqlite3_stmt *stmt = nil;//作用 执行sql语句
//4. 预执行, 查看有无语法错误, 给stmt 赋值 sql 语句
//sqlite3_prepare_v2()
//db 数据库对象
//sql 语句
//-1 语句执行长度 -1 为全部长度
//&stmt 语句对象地址, 如果预执行正确则赋值给stmt sql语句
//nil 没有执行的sql 语句, 一般给nil
int result = sqlite3_prepare_v2(db, sqlString.UTF8String, -1, &stmt, nil);
//5. 判断 如果预 执行成功, 执行相关操作
if (result == SQLITE_OK) {
//6.执行 sqlite3_step(stmt) ,执行函数有返回值, 如果是插入, 删除, 更新, 返回值为 SQLITE_DOWN,
//如果是查找操作, 则会返回SQLITE_ROW, 当前行并且指向下一行, 直到没有值, 返回 SQLITE_DOWN
if (sqlite3_step(stmt) == SQLITE_ROW) {
//7. 如果有值, 则逐列取值 sqlite3_column_类型(stmt, 列下标, 从0 开始)
NSInteger uid = sqlite3_column_int(stmt, 0);
const unsigned char *cName = sqlite3_column_text(stmt, 1);
//username
NSString *username = [NSString stringWithUTF8String:(const char *)cName];
//pwd
NSString *pwd = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
student = [[Student alloc] initWithUID:uid username:username pwd:pwd];
}//if step 结束
}//if result 结束
//8. 释放stmt 语句对象 , 无论执行是否正确, 都需要释放.
sqlite3_finalize(stmt);
if (student == nil) {
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"查找失败");
}else{
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"查找成功");
}
return student;
}
//查询所有数据
+ (NSArray *)findAllStudents{
//返回值 可变数组
NSMutableArray *stuArray = [NSMutableArray array];
//1. 数据库对象
sqlite3 *db = [self open];
//2. sql 语句
NSString *sqlString = @"SELECT * FROM 'studentInfo';";
//3. stmt 语句对象
sqlite3_stmt *stmt = nil;
//4. prepare_v2 预执行
int result = sqlite3_prepare_v2(db, sqlString.UTF8String, - 1, &stmt, nil);
//5. 判断
if (result == SQLITE_OK) {
//6. 执行 查询多条记录, 需要用while 语句
// == row 说明有值, 则取值
while (sqlite3_step(stmt) == SQLITE_ROW) {
//7. 逐列取值
//uid
NSInteger uid = sqlite3_column_int(stmt, 0);
//username
NSString *username = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
//pwd
NSString *pwd = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
Student *student = [[Student alloc] initWithUID:uid username:username pwd:pwd];
[stuArray addObject:student];
}//while 查询结束
}//if 预执行结束
//8. 释放
sqlite3_finalize(stmt);
if (stuArray.count == 0) {
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"没有数据或查找失败");
}else{
NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"查找成功");
}
return stuArray;
}
Xcode 操作数据库
最新推荐文章于 2021-05-03 21:58:00 发布