#import "LCDBManager.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
Student *s1 = [Student new];
s1.number = 1;
s1.name = @"宋立龙";
s1.gender = @"男";
s1.age = 23;
s1.score = 59.9;
Student *s2 = [Student new];
s2.number = 2;
s2.name = @"李金良";
s2.gender = @"男";
s2.age = 24;
s2.score = 60;
Student *s3 = [Student new];
s3.number = 3;
s3.name = @"去洪亮";
s3.gender = @"男";
s3.age = 25;
s3.score = 61;
LCDBManager *manager = [LCDBManager shareManager];
//打开数据库
[manager openDataBase];
//创建表
// [manager creatTable];
//
// //插入数据
// [manager insertDataFromStudent:s1];
// [manager insertDataFromStudent:s2];
// [manager insertDataFromStudent:s3];
//
//删除数据
[manager deleteDataWithNumber:3];
//修改书据
[manager updateDataForName:@"李聪" withAge:24];
//查找数据
NSArray *result = [manager selectedAllData];
NSLog(@"%@",result[1]);
//关闭数据库
[manager closeDataBase];
}
#import <Foundation/Foundation.h>
#import <CoreAudioKit/CoreAudioKit.h>
@interface Student : NSObject
@property(assign,nonatomic) NSUInteger number;
@property(strong,nonatomic) NSString *name;
@property(assign,nonatomic) NSUInteger age;
@property(strong,nonatomic) NSString *gender;
@property(assign,nonatomic) CGFloat score;
@end
#import "Student.h"
@implementation Student
-(NSString *)description {
return [NSString stringWithFormat:@"%@",_name];
}
@end
#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "Student.h"
@interface LCDBManager : NSObject<NSCopying,NSMutableCopying>
//创建单例得静态方法
+(instancetype)shareManager;
//打开数据库
-(void)openDataBase;
//关闭数据库
-(void)closeDataBase;
//创建表
-(void)creatTable;
//增
-(void)insertDataFromStudent:(Student *)student;
//删
-(void)deleteDataWithNumber:(NSUInteger )number;
//改
-(void)updateDataForName:(NSString *)name withAge:(NSUInteger)age;
//查
-(NSArray *)selectedAllData;
@end
#import "LCDBManager.h"
@implementation LCDBManager
static sqlite3 *db = nil;
static LCDBManager *manager = nil;
+(instancetype)shareManager {
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
manager = [self new];
});
return manager;
}
//完善单例
+(instancetype)allocWithZone:(struct _NSZone *)zone {
@synchronized(manager) {
if(!manager)
{
manager = [super allocWithZone:zone];
}
}
return manager;
}
-(instancetype)init {
@synchronized(self) {
self = [super init];
return self;
}
}
-(id)copyWithZone:(NSZone *)zone {
return self;
}
-(id)mutableCopyWithZone:(NSZone *)zone {
return self;
}
-(id)mutableCopy {
return self;
}
-(id)copy {
return self;
}
//数据库操作
-(void)openDataBase {
if(nil!=db) {
return;
}
//路径
NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
path = [path stringByAppendingPathComponent:@"LCStudent.sqlite"];
//打开数据库
int result = sqlite3_open(path.UTF8String, &db);
if(result == SQLITE_OK) {
NSLog(@"打开成功 ");
}else {
NSLog(@"打开失败,失败代码是%d",result);
}
}
-(void)closeDataBase {
//关闭数据库
int result = sqlite3_close(db);
if(result == SQLITE_OK)
{
NSLog(@"关闭成功");
}else {
NSLog(@"关闭失败,错误代码e为%d",result);
}
}
//创建表
-(void)creatTable {
NSString *sqlWord = @"CREATE TABLE IF NOT EXISTS 's_student' ('number' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, 'name' TEXT NOT NULL, 'age' INTEGER DEFAULT 18,'gender' TEXT DEFAULT 男, 'score' REAL NOT NULL)";
//执行
int result = sqlite3_exec(db, sqlWord.UTF8String, NULL, NULL, NULL);
if(result == SQLITE_OK) {
NSLog(@"创建表成功");
}else {
NSLog(@"创建表失败,错误代码为%d",result);
}
}
//增
-(void)insertDataFromStudent:(Student *)student {
//准备
NSString *sqlWord = [NSString stringWithFormat:@"insert into 's_student' values (%lu,'%@',%lu,'%@',%lf)",student.number,student.name,student.age,student.gender,student.score];
//执行
int result = sqlite3_exec(db, sqlWord.UTF8String, NULL, NULL, NULL);
if(result == SQLITE_OK) {
NSLog(@"增加成功");
}else {
NSLog(@"增加失败,错误代码为%d",result);
}
}
//删
-(void)deleteDataWithNumber:(NSUInteger)number {
//准备sql语句
NSString *sqlWord = [NSString stringWithFormat:@"delete from s_student where number = %lu",number];
//执行
int result = sqlite3_exec(db, sqlWord.UTF8String, NULL, NULL, NULL);
if(result == SQLITE_OK) {
NSLog(@"删除成功");
}else {
NSLog(@"删除成功,错误代码%d",result);
}
}
//改
-(void)updateDataForName:(NSString *)name withAge:(NSUInteger)age {
NSString *sqlWord = [NSString stringWithFormat:@"update 's_student' set name = '%@' where age = %lu",name,age];
int result = sqlite3_exec(db, sqlWord.UTF8String, NULL, NULL, NULL);
//
if(result == SQLITE_OK ) {
NSLog(@"修改成功");
}else {
NSLog(@"修改失败%d",result);
}
}
//查//全部
-(NSArray *)selectedAllData {
//准备可变数组
NSMutableArray *dataArray = nil;
//创建伴随指针
sqlite3_stmt *stmt = nil;
//准备sql语句
NSString *sqlWord = @"select *from 's_student'";
//执行语句
int result = sqlite3_prepare(db, sqlWord.UTF8String, -1,&stmt, NULL);
if(result == SQLITE_OK) {
dataArray = [[NSMutableArray alloc] initWithCapacity:10];
while (sqlite3_step(stmt) == SQLITE_ROW) {
Student *student = [Student new];
student.number =sqlite3_column_int(stmt, 0);
student.name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
student.age = sqlite3_column_int(stmt, 2);
student.gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
student.score = sqlite3_column_double(stmt, 4);
[dataArray addObject:student];
}
}else {
NSLog(@"查询失败");
}
//释放伴随指针
sqlite3_finalize(stmt);
return dataArray;
}
@end