SQLite3数据库
发布时间:2020-12-12 19:49:34 所属栏目:百科 来源:网络整理
导读:SQLite3采用SQLite数据库存储数据 支持SQL语句,方便查询 插件:MesaSQLite //// ViewController.m// SQLiteTest//// Created by jerehedu on 15/2/2.// Copyright (c) 2015年 jereh. All rights reserved.//#import "ViewController.h"//1.导入SQLite3头文
SQLite3采用SQLite数据库存储数据 支持SQL语句,方便查询 插件:MesaSQLite // // ViewController.m // SQLiteTest // // Created by jerehedu on 15/2/2. // Copyright (c) 2015年 jereh. All rights reserved. // #import "ViewController.h" //1.导入SQLite3头文件 #import <sqlite3.h> @interface ViewController () { //2.声明SQLite3对象 sqlite3 *db; } @end
#pragma mark 获得沙盒路径 - (NSString *)getUserDocumentPath { //获得沙盒路径 NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES); NSString *documentPath = [path lastObject]; return documentPath; } #pragma mark 追加路径 - (NSString *)appendingPathComponent:(NSString *)documentPath andFileName:(NSString *)fileName { NSString *sqlitePath = [documentPath stringByAppendingPathComponent:fileName]; return sqlitePath; } #pragma mark 创建或打开数据库 -(BOOL)openOrCreateSQLiteWithDBPath:(NSString *)dbpath{ const char *p = [dbpath UTF8String]; int res = sqlite3_open(p,&db); if (res == SQLITE_OK) { return YES; }else{ return NO; } } #pragma mark 执行sql语句 -(BOOL)execSQLNoQueryWithSQL:(NSString *)sql{ int res = sqlite3_exec(db,[sql UTF8String],NULL,NULL); if (res == SQLITE_OK){ return YES; }else{ return NO; } } #pragma mark 返回stmt,无参数 -(sqlite3_stmt *)execQueryWithSQL:(NSString *)sql{ sqlite3_stmt *stmt; int pre_res = sqlite3_prepare_v2(db,-1,&stmt,NULL); if (pre_res == SQLITE_OK){ return stmt; } return NULL; } #pragma mark 有参数 -(sqlite3_stmt *)execQueryWithSQL:(NSString *)sql andWithParams:(NSArray *)params{ sqlite3_stmt *stmt; int pre_res = sqlite3_prepare_v2(db,NULL); if (pre_res == SQLITE_OK){ //绑定参数 //判断参数列表是否为空 if (params != nil) { //循环绑定参数 for (int i = 0; i < params.count; i++) { // sqlite3_bind_int(stmt,i+1,[params[i] intValue]); //要判断参数类型 id obj = params[i]; if (obj == nil) { sqlite3_bind_null(stmt,i+1); } else if ([obj respondsToSelector:@selector(objCType)]){ //respondsToSelector判断对象是否包含objCType方法(数字有) //strstr(char *,char *)判断是否在char中出现过 if (strstr("ilsILS",[obj objCType])) { sqlite3_bind_int(stmt,[obj intValue]); }else if (strstr("fd",[obj objCType])) { sqlite3_bind_double(stmt,[obj doubleValue]); }else{ stmt = NULL; } }else if ([obj respondsToSelector:@selector(UTF8String)]){ sqlite3_bind_text(stmt,[obj UTF8String],NULL); }else{ stmt = NULL; } } } return stmt; } return NULL; } @implementation ViewController - (void)viewDidLoad { [super viewDidLoad]; //获得沙盒路径 NSString *dbPath; dbPath = [self getUserDocumentPath]; NSString *sqlitePath; sqlitePath = [self appendingPathComponent:dbPath andFileName:@"test.sqlite"];//追加路径(实际不存在) NSLog(@"%@",dbPath); //5.判断是否打开成功 if ([self openOrCreateSQLiteWithDBPath:sqlitePath]) { NSLog(@"db is open"); //构造SQL语句 NSString *sql = @"create table if not exists demo(d_id integer primary key autoincrement,d_name varchar(20))"; int exec_res = sqlite3_exec(db,NULL); if (exec_res == SQLITE_OK) { NSLog(@"table is created"); } /* 参数: 1.sqlite3 对象 2.sql语句 3.回调函数 4.回调函数的参数 5.错误信息 */ //插入记录,构造sql语句 NSString *insert_sql = @"insert into demo(d_name) values ('zhangziyao')"; if ([self execSQLNoQueryWithSQL:insert_sql]) { NSLog(@"one recoder is inserted"); } //修改 NSString *update_sql = @"update demo set d_name = 'chenyi' where d_id = 1"; if ([self execSQLNoQueryWithSQL:update_sql]) { NSLog(@"one recoder is updated"); } //删除 NSString *delete_sql = @"delete from demo where d_name='wuyuqiu'"; if ([self execSQLNoQueryWithSQL:delete_sql]) { NSLog(@"one recoder is deleted"); } //查询 int search_d_id = 3; NSString *search_name = @"_h%"; NSString *search_sql = @"select * from demo where d_id>? and d_name like ?"; sqlite3_stmt *stmt; if ([self execQueryWithSQL:search_sql]) { stmt = [self execQueryWithSQL:search_sql andWithParams:@[[NSNumber numberWithInt: search_d_id],search_name]]; while (sqlite3_step(stmt) == SQLITE_ROW) { int d_id = sqlite3_column_int(stmt,0); const unsigned char *d_name = sqlite3_column_text(stmt,1); NSString *name = [NSString stringWithUTF8String:(char*)d_name]; NSLog(@"id=%d,name=%@",d_id,name); _idLabel.text = [NSString stringWithFormat:@"id=%d",d_id]; _nameLabel.text = [NSString stringWithFormat:@"name=%@",name]; } } //释放stmt sqlite3_finalize(stmt); //关闭数据库 sqlite3_close(db); NSNumber *n = [NSNumber numberWithDouble:1.0]; NSLog(@"%s",[n objCType]); } } - (void)didReceiveMemoryWarning { [super didReceiveMemoryWarning]; // Dispose of any resources that can be recreated. } @end (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |