- 新建个类：DataBaseHandle 导入数据库：#import <sqlite3.h>
2.DataBaseHandle.h 文件
代码如下：
//
+(DataBaseHandle *)shareDataBaseHandle;
//
-(void)getPath:(NSString *)path;
//打开数据库
-(void)openDB;
//关闭数据库
-(void)closeDB;
//创建表
-(void)creatTable;
//插入数据
-(void)insertName:(NSString *)name gender:(NSString *)gender age:(NSInteger)age;
//通过uid去更新数据
-(void)updateWithUID:(NSInteger)uid;
//通过uid去删除数据
-(void)deleteWithUID:(NSInteger)uid;
-(void)searchAll;
//查找所以有数据
-(void)searchWithName:(NSString *)name;
2.DataBaseHandle.m文件
代码如下：
//数据库的存储路径
@property(nonatomic,strong)NSString * dbPath;
@end
//
static DataBaseHandle * dataBase = nil;
@implementation DataBaseHandle
//将DataBaseHandle该类写为单例类：方便外部使用
+(DataBaseHandle *)shareDataBaseHandle{
if (dataBase==nil) {
dataBase=[[DataBaseHandle alloc] init];
}
return dataBase;
}
//获取路径
-(void)getPath:(NSString *)path{
}
//揽加载((数据库路径)
-(NSString *)dbPath{
if (_dbPath==nil) {
//获取document路径；
NSString *document= [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES)lastObject];
//在document下创建文件夹
_dbPath=[document stringByAppendingPathComponent:@"person.sqlite"];
}
return _dbPath;
}
//因为在很多地方都会使用数据库，所以初始化一个数据库的静态变量
static sqlite3 * db = nil;
//打开数据库
-(void)openDB{
//第一个参数：数据库的路径
//第二个参数：数据库的地址
int result= sqlite3_open(self.dbPath.UTF8String, &db);
//
if(result == SQLITE_OK){
NSLog(@"数据库打开成功");
}
else{
NSLog(@"数据库打开失败");
}
}
//关闭数据库
-(void)closeDB{
int result= sqlite3_close(db);
if (result==SQLITE_OK) {
NSLog(@"数据库关闭成功");
}
else{
NSLog(@"数据库关闭失败");
}
}
//创建表
-(void)creatTable{
//
//数据库只有打开成功后才可以创建表
//建表
//第一个参数代表数据库
//2.sql语句
//IF NOT EXISTS:如果表不存在那么就创建
//primary key 主键
//autoincreament 自增
//创建一个person 表，字段id interger主键 自增不能为空，name text 类型。gender text类型 age interger类型
// const char* sql = "create table IF NOT EXISTS person (id integer PRIMARY KEY AUTOINCREMENT NOT NULL,name text ,gender text,age integer);";
NSString * creatStr=@"create table IF NOT EXISTS person (id integer PRIMARY KEY AUTOINCREMENT NOT NULL,name text ,gender text,age integer);";
int result = sqlite3_exec(db, creatStr.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"创建表成功");
}
else{
NSLog(@"创建表失败"); }
NSLog(@"-dbPath = %@",self.dbPath);
}
//
//插入数据
-(void)insertName:(NSString *)name gender:(NSString *)gender age:(NSInteger)age{
//是准备sql语句
//当values的值不确定的情况下使用？去代替
NSString * insertStr= @"insert into person(name,gender,age)values(?,?,?)";
//创建伴随指针
sqlite3_stmt * stmt = nil;
int result=sqlite3_prepare(db, insertStr.UTF8String, -1, &stmt, NULL);
//判断执行的结果
if (result == SQLITE_OK) {
//进行值绑定
//第一个参数：伴随指针
//第二个参数：？位置从一开始
//第三个参数：要插入的值
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(@"插入失败");
}
}
//释放伴随指针
sqlite3_finalize(stmt);
}
//通过uid更新数据
-(void)updateWithUID:(NSInteger)uid
{
//sql语句:
NSString *update = @"update person set name = '老王' where id = ?";
//创建伴随指针
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare(db, update.UTF8String, -1, &stmt, NULL);
if(result == SQLITE_OK){
//伴随指针绑定参数
sqlite3_bind_int64(stmt, 1, uid);
//执行完毕:
if(sqlite3_step(stmt) == SQLITE_DONE){
NSLog(@"数据库更新成功");
}else
{NSLog(@"数据库更新失败");
}}
sqlite3_finalize(stmt);
}
//通过uid去删除数据
-(void)deleteWithUID:(NSInteger)uid
{
//准备sql语句:
NSString *deleStr = [NSString stringWithFormat:@"delete from person where id = %ld",uid];
int result = sqlite3_exec(db, deleStr.UTF8String, NULL, NULL, NULL);
if(result == SQLITE_OK)
{
NSLog(@"删除成功");
}else
{
NSLog(@"删除失败");
}
}
-(void)searchAll{
//sql语句
NSString * searchAllAtr = @"select * from person";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare(db, searchAllAtr.UTF8String, -1,&stmt , NULL);
if (result == SQLITE_OK) {
while (sqlite3_step(stmt)==SQLITE_ROW) {
// 获取数据表中整型列的信息
int stu_id=sqlite3_column_int(stmt, 0);
NSLog(@"stu_id is %d",stu_id);
// 获取数据表中字符型的列的信息
NSLog(@"%@",[NSString stringWithFormat:@"%s",sqlite3_column_text(stmt, 1)]);
NSLog(@"%@",[NSString stringWithFormat:@"%s",sqlite3_column_text(stmt, 2)] );
NSLog(@"%@",[NSString stringWithFormat:@"%s",sqlite3_column_text(stmt, 3)] );
}
}
NSLog(@"查询所有成功");
}
//查找所以有数据
//根据名字查找相关数据信息
-(void)searchWithName:(NSString *)name{
//sql语句
NSString *searchStr = [NSString stringWithFormat:@"select *from person where name = '%@'",name];
//创建伴随指针
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare(db, searchStr.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
//取数据
//调用一次step就从&stmt取一次数据
//sqlite3_step每执行一次这个函数就是取出下一条记录
while (sqlite3_step(stmt) == SQLITE_ROW) {
//取出数据
//取出第一列字段的值(int 类型)
int ID = sqlite3_column_int(stmt, 0);
//取出第二列字段的值(text 类型)
const unsigned char *name = sqlite3_column_text(stmt, 1);
//取出第三列字段的值(text 类型)
const unsigned char *gender = sqlite3_column_text(stmt, 2);
//取出第四列字段的值(int 类型)
int age = sqlite3_column_int(stmt, 3);
NSLog(@"%d %s %s %d",ID,name,gender,age);
}
}else{
NSLog(@"查找失败");
}
sqlite3_finalize(stmt);
}
5.ViewController.m
- (void)viewDidLoad {
[super viewDidLoad];
DataBaseHandle * database=[DataBaseHandle shareDataBaseHandle];
//打开数据库
[database openDB];
//创建表
[database creatTable];
//[database closeDB];
//插入数据
[database insertName:@"xxx" gender:@"girl" age:18];
[database insertName:@"小花" gender:@"女" age:12];
[database insertName:@"小铭" gender:@"男" age:21];
//删除
[database deleteWithUID:9];
//更新
[database updateWithUID:2];
//所有
[database searchAll];
[database searchWithName:@"xxx"];
}
