在XCode中要想对数据库操作,必须先做一下两步:
第一步:先导入SQLite框架
第二步:在导入库头文件 #import <sqlite3.h>
第三步:新建一个工具类 DataBaseHandle
DataBaseHandle.h文件
#import@interface DataBaseHandle : NSObject
//单例类共同命名规则:一般以shared或default+类名
+(DataBaseHandle *)sharedDataBaseHandle;
//打开数据库
-(void)openDB;
//关闭数据库
-(void)closeDB;
//创建表
-(void)createTable;
//插入数据
-(void)inserName:(NSString *)name
gender:(NSString *)gender
age:(NSInteger)age;
//同UID更改一个新数据
-(void)upDateWithUID:(NSInteger)uid;
//通过UID删除一条数据
-(void)deleteWithUID:(NSInteger)uid;
//查询全部信息
-(void)searchAll;
//根据name查询
-(void)searchWithName:(NSString *)name;
@end
DataBaseHandle.m文件
#import "DataBaseHandle.h"
//导入数据库头文件
#import<sqlite3.h>
@interface DataBaseHandle ()
//属性(documents文件夹下的一个叫做person.sqlite的数据库)
@property(nonatomic,copy)NSString *dbPath;
@end
//放在静态区
static DataBaseHandle *dataBase = nil;
@implementation DataBaseHandle
+(DataBaseHandle *)sharedDataBaseHandle{
if (dataBase == nil) {
dataBase = [[DataBaseHandle alloc] init];
}
return dataBase;
}
//懒加载(重写属性的get方法)
-(NSString *)dbPath{
if (_dbPath == nil) {
NSString *documentsPathStr = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
_dbPath = [documentsPathStr stringByAppendingPathComponent:@"person.sqlite"];
}
return _dbPath;
}
//第一步:创建库
static sqlite3 *db = nil;
//第二步:打开数据库
-(void)openDB{
//打开数据库的函数sqlite3_open
//参数1:是数据库的路径,且是UTF8的格式
int result = sqlite3_open(self.dbPath.UTF8String, &db);
if (result == SQLITE_OK) {
NSLog(@"打开数据库");
}else{ NSLog(@"打开失败");
NSLog(@"%d",result);}
}
//第三步创建表
-(void)createTable{
//创建一个表的语句
NSString *sqlString = @"create table person (uid integer primary key autoincrement not null, name text, gender text, age integer)";
//第一个参数:数据库
//第二个参数:sql语句,要用 utf - 8 的格式
//第三个参数:结果的回调函数
//第四个参数:回调函数的参数
//第五个参数:错误信息
int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
//判断是否创建成功
if (result == SQLITE_OK) {
NSLog(@"建表成功");
}else
{
NSLog(@"建表失败 %d",result);
}
//打印数据库的地址
NSLog(@"_bdPath == %@",_dbPath);
}
//第四步插入
-(void)inserName:(NSString *)name
gender:(NSString *)gender
age:(NSInteger)age{
//第一步:写sql语句
NSString *insertString =@"insert into person (name, gender, age) values (?, ?, ?)";
sqlite3_stmt *stmt = nil;
//参数1:数据库
//参数2:sql语句
//参数3:如果为正 例如1,表示在取参数的时候只取一个字节 使用负数表示取值取到碰到结束符号('\000','u000')
//参数4:伴随指针(会伴随着数据库的操作,获取值或绑定值)
//参数5:取值的时候如果取出的不全,那么剩下的都存在这里
//第二步:执行(预执行)
int result = sqlite3_prepare(db, insertString.UTF8String, -1, &stmt, NULL);
//判断如果预执行成功的话,那么久要往里面放数据了
if (result == SQLITE_OK) {
//向预执行的sql语句里面插入参数-->取代?
//参数1:伴随指针
//参数2:确定插入?的位置 从1开始
//参数3:和上面-1是一样的
//参数4:回调函数
sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
sqlite3_bind_text(stmt, 2, gender.UTF8String, -1, NULL);
sqlite3_bind_int64(stmt, 3, age);
//sql语句完成
//执行伴随指针,如果为SQLITE_DONE 代表执行成功 并且成功的插入暑假
if (sqlite3_step(stmt) == SQLITE_DONE) {
NSLog(@"插入成功");
}else{
NSLog(@"插入失败%d",result);
}
}else{
NSLog(@"%d",result);
}
//一定要记住释放到伴随指针
sqlite3_finalize(stmt);
}
//第五步:同UID更改一个新数据
-(void)upDateWithUID:(NSInteger)uid{
NSString *updateString =@"update person set name = '王军' where uid = ?";
sqlite3_stmt *stmt = nil;
//伴随指针
int result = sqlite3_prepare(db, updateString.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
sqlite3_bind_int64(stmt, 1, uid);
if (sqlite3_step(stmt) == SQLITE_DONE) {
NSLog(@"更改成功");
}else{
NSLog(@"%d",result);
}
}else{
NSLog(@"%d",result);
}
sqlite3_finalize(stmt);
}
//第六步:通过UID删除一条数据
-(void)deleteWithUID:(NSInteger)uid{
//简单操作方式
NSString *deleteString =[NSString stringWithFormat:@"delete from person where uid = %ld",uid];
int result = sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"删除成功");
}else{
NSLog(@"删除失败%d",result);
}
}
//第七步:查询全部信息
-(void)searchAll{
NSString *searchString = @"select * from person";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare(db, searchString.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
//当sqlite3_step(stmt) == SQLITE_ROW的时候,代表还有一条数据
while (sqlite3_step(stmt) == SQLITE_ROW) {
//第二参数:表示参数的位置,从0开始
int uid = sqlite3_column_int(stmt, 0);//具体记录从0开始 字段从1开始
NSLog(@"%d",uid);
NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSLog(@"%@",name);
NSString *gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
NSLog(@"%@",gender);
int age = sqlite3_column_int(stmt, 3);
NSLog(@"%d",age);
}
}else{
NSLog(@"%d",result);
}
sqlite3_finalize(stmt);
}
//第八步:根据name查询
-(void)searchWithName:(NSString *)name{
}
//第十步:关闭数据库
-(void)closeDB{
int result = sqlite3_close(db);
if (result ==SQLITE_OK) {
NSLog(@"关闭成功");
}else{
NSLog(@"关闭失败 %d",result);
}
}
@end
第四步:在ViewController中调用
#import "ViewController.h"
//导入头文件
#import "DataBaseHandle.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
//1:导入libsqlite.tbd框架
//2:新建工具类
DataBaseHandle *dataBase = [DataBaseHandle sharedDataBaseHandle];
[dataBase openDB];
[dataBase createTable];
[dataBase inserName:@"班长" gender:@"男" age:90];
[dataBase upDateWithUID:3];
[dataBase deleteWithUID:2];
[dataBase searchAll];
[dataBase closeDB];
// Do any additional setup after loading the view, typically from a nib.
}
- (void)didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
@end
