SQLite数据库的基本使用

·

SQLite是一款轻型的数据库,是一种关系型数据库管理系统,它的设计目的是嵌入式设备中使用,SQLite占用资源非常低,非常适合移动设备中使用,而且是开源免费的 

·SQLite最新版本是3.0,使用前需要导入libsqlite3.0.dylib·操作数据库的流程:
·打开数据库

·编译SQL语句·

执行SQL语句·

销毁SQL语句

关闭数据库 

SQLite 常用函数

sqlite3_open()//打开数据库
sqlite3_close()//关闭数据库
sqlite3_exec()//执⾏行sql语句,内部包括(prepare、finalize)
sqlite3_prepare_v2()//编译SQL语句
sqlite3_step()//执⾏行查询SQL语句
sqlite3_finalize()//结束sql语句
sqlite3_bind_text()//绑定参数
sqlite3_column_text()//查询字段上的数据

//示例demo使用数据库实现如下对用户的添加和修改 

1 在ViewControllerViewController.h里
#import <UIKit/UIKit.h>
@interface ViewController : UIViewController
@property (weak, nonatomic) IBOutlet UITableView *userListTableView;
- (IBAction)editAction:(id)sender;
@end
2 在ViewControllerViewController.m里
#import "ViewController.h"

#import "DataBaseManager.h"
#import "User.h"

@interface ViewController ()<UITableViewDataSource, UITableViewDelegate>
{
    NSArray *_data;
}

@end

@implementation ViewController

#pragma mark View Life Cycle
- (void)viewDidLoad {
    [super viewDidLoad];

    self.userListTableView.delegate = self;
    self.userListTableView.dataSource = self;
    

}

- (void)viewWillAppear:(BOOL)animated
{
    [super viewWillAppear:animated];
    
    [self loadData];
}


#pragma mark - Actions
- (IBAction)editAction:(id)sender {
    
    //设置表视图的编辑模式
    [self.userListTableView setEditing:!self.userListTableView.editing animated:YES];
    
}

#pragma mark - Data
//加载数据
- (void)loadData
{
    DataBaseManager *manager = [DataBaseManager sharedManager];
    
    _data = [manager queryAllUser];
}


#pragma mark - TableView DataSource
//TableView组的个数
- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView
{
    return 1;
}

//TableView每组单元格个数
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    return _data.count;
}

//每一个单元格
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
    static NSString *identifier = @"Cell_01";
    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:identifier];
    if (cell == nil) {
        cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleValue1
                                      reuseIdentifier:identifier];
    }
    
    User *user = _data[indexPath.row];
    
    cell.textLabel.text = user.username;
    cell.detailTextLabel.text = [NSString stringWithFormat:@"%d", user.age];
    
    return cell;
}

#pragma mark - TableView Delegate
- (void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath
{
    if (editingStyle == UITableViewCellEditingStyleInsert) {
//        [_data addOject:];
//        [self.userListTableView reloadData];
    } else if (editingStyle == UITableViewCellEditingStyleDelete) {
        
        User *user = _data[indexPath.row];
        NSString *name = user.username;
        
        DataBaseManager *manager = [DataBaseManager sharedManager];
        
        [manager deleteUser:name];
        
        _data = [manager queryAllUser];
        
        //编辑操作之后，要刷新表视图
        //[self.userListTableView reloadData];
        
        [self.userListTableView deleteRowsAtIndexPaths:@[indexPath]
                                      withRowAnimation:UITableViewRowAnimationFade];
    }
}

@end
3 在AddViewController.h里

#import <UIKit/UIKit.h>

@interface AddViewController : UIViewController

{

    __weak IBOutlet UITextField *_usernameLabel;

    

    __weak IBOutlet UITextField *_passwordLabel;

    

    __weak IBOutlet UITextField *_ageLabel;

}

- (IBAction)addAction:(id)sender;

@end

4 在AddViewController.m里

#import "AddViewController.h"

#import "DataBaseManager.h"

#import "User.h"

@interface AddViewController ()

@end

@implementation AddViewController

- (void)viewDidLoad {

    [super viewDidLoad];

}

//添加用户

- (IBAction)addAction:(id)sender {

    

    if (_usernameLabel.text == nil || _passwordLabel.text == nil) {

        return;

    }

    

    User *user = [[User alloc] init];

    

    user.username = _usernameLabel.text;

    user.password = _passwordLabel.text;

    user.age = [_ageLabel.text intValue];

    

    DataBaseManager *manager = [DataBaseManager sharedManager];

    

    [manager addUser:user];

    

    [self.navigationController popViewControllerAnimated:YES];

}

@end

5在DataBaseManager里

#import <Foundation/Foundation.h>

@class User;

@interface DataBaseManager : NSObject

+ (DataBaseManager *)sharedManager;

//操作数据库的方法

- (void)addUser:(User *)user;

- (void)deleteUser:(NSString *)name;

- (NSArray *)queryAllUser;

@end

6在DataBaseManager.m里

#import "DataBaseManager.h"

#import "User.h"

#import <sqlite3.h>

#define kDataBaseName @"UserDataBase.sqlite"

static DataBaseManager *instance = nil;

@implementation DataBaseManager

+ (DataBaseManager *)sharedManager

{

    static dispatch_once_t onceToken;

    dispatch_once(&onceToken, ^{

        instance = [[DataBaseManager alloc] init];

    });

    return instance;

}

- (instancetype)init

{

    self = [super init];

    if (self) {

        [self copyDBFile];

    }

    return self;

}

- (void)copyDBFile

{

    NSFileManager *manager = [NSFileManager defaultManager];

    

    if (![manager fileExistsAtPath:[self dataBasePath]]) {

        

        NSString *filepath = [[NSBundle mainBundle] pathForResource:kDataBaseName ofType:nil];

        

        [manager copyItemAtPath:filepath toPath:[self dataBasePath] error:nil];

        

    }

    

}

- (NSString *)dataBasePath

{

    NSLog(@"%@", NSHomeDirectory());

    return [NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@", kDataBaseName];

}

//添加一条数据

- (void)addUser:(User *)user;

{

    sqlite3 *sqlite = nil;

    

    

    //1.打开数据库

    int openResult = sqlite3_open([[self dataBasePath] UTF8String], &sqlite);

    if (openResult != SQLITE_OK) {

        NSLog(@"打开失败");

        return;

    }

    

    //2.准备SQL语句

    NSString *sql = @"INSERT INTO UserList (username, password, age) VALUES (?, ?, ?)";

    sqlite3_stmt *stmt = nil;

    sqlite3_prepare(sqlite, [sql UTF8String], -1, &stmt, NULL);

    

    sqlite3_bind_text(stmt, 1, [user.username UTF8String], -1, NULL);

    sqlite3_bind_text(stmt, 2, [user.password UTF8String], -1, NULL);

    sqlite3_bind_int(stmt, 3, user.age);

    

    //3.执行

    int stepResult = sqlite3_step(stmt);

    if (stepResult == SQLITE_ERROR || stepResult == SQLITE_MISUSE) {

        NSLog(@"执行添加数据失败");

    }

    

    //4.完结

    sqlite3_finalize(stmt);

    

    //5.关闭数据库

    sqlite3_close(sqlite);

}

- (void)deleteUser:(NSString *)name;

{

    sqlite3 *sqlite = nil;

    

    

    //1.打开数据库

    int openResult = sqlite3_open([[self dataBasePath] UTF8String], &sqlite);

    if (openResult != SQLITE_OK) {

        NSLog(@"打开失败");

        return;

    }

    

    //2.准备SQL语句

    NSString *sql = @"DELETE FROM UserList WHERE username = ?";

    sqlite3_stmt *stmt = nil;

    sqlite3_prepare(sqlite, [sql UTF8String], -1, &stmt, NULL);

    

    sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL);

    

    

    //3.执行

    int stepResult = sqlite3_step(stmt);

    if (stepResult == SQLITE_ERROR || stepResult == SQLITE_MISUSE) {

        NSLog(@"执行删除数据失败");

    }

    

    //4.完结

    sqlite3_finalize(stmt);

    

    //5.关闭数据库

    sqlite3_close(sqlite);

}

- (NSArray *)queryAllUser

{

    sqlite3 *sqlite = nil;

    

    

    //1.打开数据库

    int openResult = sqlite3_open([[self dataBasePath] UTF8String], &sqlite);

    if (openResult != SQLITE_OK) {

        NSLog(@"打开失败");

        return nil;

    }

    

    NSMutableArray *users = [NSMutableArray array];

    

    //2.准备SQL语句

    NSString *sql = @"SELECT * FROM UserList";

    sqlite3_stmt *stmt = nil;

    sqlite3_prepare(sqlite, [sql UTF8String], -1, &stmt, NULL);

    

    //3.执行

    int stepResult = sqlite3_step(stmt);

    while (stepResult == SQLITE_ROW) {

        User *user = [[User alloc] init];

        

        const char *name = (const char *)sqlite3_column_text(stmt, 0);

        const char *pwd = (const char *)sqlite3_column_text(stmt, 1);

        int age = sqlite3_column_int(stmt, 2);

        

        user.username = [NSString stringWithUTF8String:name];

        user.password = [NSString stringWithUTF8String:pwd];

        user.age = age;

        

        [users addObject:user];

        

        stepResult = sqlite3_step(stmt);

    }

    

    

    //4.完结

    sqlite3_finalize(stmt);

    

    //5.关闭数据库

    sqlite3_close(sqlite);

    

    return users;

}

@end