·
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
#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