UI数据库

#import "ViewController.h"
#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