iOS 之 数据库

建立一个类文件，在.h文件里写入几个属性

#import <Foundation/Foundation.h>

@interface Person : NSObject

@property (nonatomic, assign)NSInteger number;

@property (nonatomic, copy)NSString *name;

@property (nonatomic, copy)NSString *gender;

@property (nonatomic, assign)NSInteger age;

@end

再建立一个类文件，在.h文件里声明几个需要数据库使用的方法

#import <Foundation/Foundation.h>

#import "Person.h"

#warning 第一步 引入sqlite3.h 数据库头文件

#import <sqlite3.h>

@interface MyModel : NSObject

+ (instancetype)sharePerson;

- (void)openDB;

- (void)insertStudent:(Person *)person;

- (void)updateStudent:(Person *)person number:(NSInteger)number;

- (void)deleStudent:(NSInteger)number;

- (NSMutableArray *)selectAllStudent;

@end

在.m文件里写入声明的方法

#import "MyModel.h"

@implementation MyModel

+ (instancetype)sharePerson

{

    static MyModel *model = nil;

    if (model == nil) {

        model = [[MyModel alloc]init];

    }

    return model;

}

#warning 第二步 加入libsqlite3.0 库文件

#warning 第三步 创建数据库对象

static sqlite3 *db = nil;

#warning 第四步 打开数据库

- (void)openDB

{

    if (db != nil) {

        NSLog(@"数据库已经打开");

        return;

    }

    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *path = [array lastObject];

    NSString *sqlitePath = [path stringByAppendingPathComponent:@"student.sqlite"];

    NSLog(@"sqlitePath == %@", sqlitePath);

    //打开数据库

    int result = sqlite3_open(sqlitePath.UTF8String, &db);

    if (result == SQLITE_OK) {

        NSLog(@"数据库打开成功");

        //数据库建表

        NSString *createSql = @"CREATE TABLE IF NOT EXISTS lanou23(number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, gender TEXT, age, INTEGER)";

        int RESULT = sqlite3_exec(db, createSql.UTF8String, NULL, NULL, nil);

        if (RESULT == SQLITE_OK) {

            NSLog(@"创建表成功");

        }else

        {

            NSLog(@"创建表失败");

        }

    }

    else

    {

        NSLog(@"数据库打开失败");

    }

}

#warning 第五步 插入数据

- (void)insertStudent:(Person *)person

{

    //创建插入语句

    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO lanou23(name, gender, age) values('%@', '%@', '%ld')", person.name, person.gender, person.age];

    

    int RESULT = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);

    if (RESULT == SQLITE_OK) {

        NSLog(@"插入成功");

    }else

    {

         NSLog(@"插入失败");

    }

}

#warning 第六步 修改

- (void)updateStudent:(Person *)person number:(NSInteger)number

{

     NSString *updateSql = [NSString stringWithFormat:@"UPDATE lanou23 SET name = '%@', gender = '%@', age = '%ld' WHERE number = '%ld'",person.name, person.gender, person.age ,number];

    

    int result = sqlite3_exec(db, updateSql.UTF8String, NULL, NULL, nil);

    if (result == SQLITE_OK) {

        NSLog(@"修改成功");

    }else

    {

        NSLog(@"修改失败");

    }

}

#warning 第七步 删除

- (void)deleStudent:(NSInteger)number

{

    NSString *deleSql = [NSString stringWithFormat:@"DELETE FROM lanou23 WHERE number = '%ld'", number];

    

    int result = sqlite3_exec(db, deleSql.UTF8String, NULL, NULL, nil);

    if (result == SQLITE_OK) {

        NSLog(@"删除成功");

    }else

    {

         NSLog(@"删除失败");

    }

}

#warning 第八步 查询所有学生

- (NSMutableArray *)selectAllStudent

{

    NSMutableArray *array = [NSMutableArray array];

    

    NSString *selectSql = @"SELECT * FROM lanou23";//查询所有

    

//    NSString *selectSql = [NSString stringWithFormat:@"SELECT * FROM lanou23 WHERE name = '%@'", @"123"];//查询某一个

    //声明数据库跟随指针,作用是跟踪表里的每一行

    sqlite3_stmt *stmt = nil;

    //查询之前准备工作:将数据库对象db,sql语句.stmt关联起来

    //参数1.数据库对象

    //参数2.sql语句

    //参数3.sql语句长度,-1为不限制长度

    //参数4.数据库跟随指针

    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);

    if (result == SQLITE_OK) {

        NSLog(@"查询成功");

        //循环遍历每一条数据

        while (sqlite3_step(stmt) == SQLITE_ROW) {

            //取值, 1为name下标

            const unsigned char * name = sqlite3_column_text(stmt, 1);

            const unsigned char * gender = sqlite3_column_text(stmt, 2);

            int number = sqlite3_column_int(stmt, 0);

            int age = sqlite3_column_int(stmt, 3);

            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];

            NSString *genderStr = [NSString stringWithUTF8String:(const char *)gender];

            NSInteger numberInteger = number;

            NSInteger ageInteger = age;

            Person *pp = [[Person alloc]init];

            pp.name = nameStr;

            pp.gender = genderStr;

            pp.age = ageInteger;

            pp.number = numberInteger;

            [array addObject:pp];

        }

    }else

    {

        NSLog(@"查询失败");

    }

    return array;

}

#pragma mark 关闭数据库

- (void)closeDB

{

    // 1. 关闭数据库

    int result = sqlite3_close(db);

    // 2. 判断是否关闭成功

    if (result == SQLITE_OK) {

        NSLog(@"关闭成功");

#warning 关闭成功后，把数据库对象置为nil

        db = nil;

    } else {

        NSLog(@"关闭失败");

    }

}

@end

在需要调用数据库的页面.h文件里引入数据库的头文件及属性类的头文件

#import <UIKit/UIKit.h>

#import "Person.h"

#import "MyModel.h"

@interface RootViewController : UIViewController

@end

在需要调用数据库的页面.m文件里调用所需要使用的数据库文件方法

#import "RootViewController.h"

@interface RootViewController ()

@end

@implementation RootViewController

- (void)viewDidLoad {

    [super viewDidLoad];

    // Do any additional setup after loading the view.

    self.view.backgroundColor = [UIColor cyanColor];

    MyModel *model = [MyModel sharePerson];

    [model openDB];

    Person *pp = [[Person alloc]init];

    pp.name = @"123";

    pp.gender = @"男";

    pp.age = 23;

    [model insertStudent:pp];

    Person *pp1 = [[Person alloc]init];

    pp1.name = @"456";

    pp1.gender = @"女";

    pp1.age = 26;

    [model updateStudent:pp1 number:1];

    [model deleStudent:3];

    self.array = [model selectAllStudent];

    for (Person *ppp in self.array) {

        NSLog(@"ppp.name ===== %@", ppp.name);

    }

    

}

- (void)didReceiveMemoryWarning {

    [super didReceiveMemoryWarning];

    // Dispose of any resources that can be recreated.

}

/*

#pragma mark - Navigation

// In a storyboard-based application, you will often want to do a little preparation before navigation

- (void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender {

    // Get the new view controller using [segue destinationViewController].

    // Pass the selected object to the new view controller.

}

*/

@end