SQLite SQLite - 锐客网

需要注意的是这里面sqlite3 是我们要用的句柄。
文章图片
SQLite API

@property(nonatomic,assign)sqlite3 *db; //打开数据库 -(void)openDatabase { if(sqlite3_open([[self filepath] UTF8String],&(_db))==SQLITE_OK) { if((!sqlite3_exec(_db, "create table if not exist contacts(id integer primary key autoincrement,name text,mobile text)", NULL, NULL, NULL))==SQLITE_OK) { NSLog(@"fail write db"); } }else { //如果失败将错误信息写入文件里边 NSLog(@"create fail db"); }} //关闭数据库 -(void)dealloc { sqlite3_close(_db); }

解决非法注入sql语句问题,之后的sql语句都需要这样书写

文章图片
image.png

//添加数据 - (void)onAddContact:(Contact *)contact { //初始化一个指针 sqlite3_stmt *stmt=NULL; //制作一个sql语句 NSString *sql=@"insert into contacts(name,mobile)values('?','?')"; //用prepare来添加数据，防止恶意注入的代码,[sql UTF8String]这句话是将字符串转成C能识别的代码 if(sqlite3_prepare(_db, [sql UTF8String], -1, &stmt, NULL)==SQLITE_OK) { //将需要加入的数据绑定,bind的时候这里是从1开始 sqlite3_bind_text(stmt, 1, [contact.name UTF8String], -1, NULL); sqlite3_bind_text(stmt, 2, [contact.mobile UTF8String], -1, NULL); //判断语句是否执行完成 if(sqlite3_step(stmt)==SQLITE_DONE) { //获取最后添加的id赋值给contact contact.serialId=sqlite3_last_insert_rowid(_db); //添加数据 [self.contacts addObject:contact]; [self.tableView reloadData]; } //清理一下这个语句 sqlite3_finalize(stmt); } //推出当前页面 [self.navigationController popViewControllerAnimated:YES]; }

读取操作

-(void)read { //初始化指针 sqlite3_stmt *stmt=NULL; //指定sql语句 NSString *sql=@"select * from contacts"; //进行防恶意sql语句注入 if(sqlite3_prepare(_db, [sql UTF8String], -1, &stmt, NULL)==SQLITE_OK) { //表示查完这条数据如果还有的话，接着查下一条 while (sqlite3_step(stmt)==SQLITE_ROW) { //这里读取这个id是从0开始的 int64_t serialId=sqlite3_column_int64(stmt, 0); //读取第二个 const char *name=(const char *)sqlite3_column_text(stmt, 1); //读取第三个 const char *mobile=(const char *)sqlite3_column_text(stmt, 2); //如果name和mobile都有值,进行赋值 if(name&&mobile) { Contact *contact=[[Contact alloc]init]; contact.serialId=serialId; contact.name=[NSString stringWithUTF8String:name]; contact.mobile=[NSString stringWithUTF8String:mobile]; } } //清楚操作 sqlite3_finalize(stmt); } }

删除操作

- (void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath { if (editingStyle == UITableViewCellEditingStyleDelete) {NSInteger index = [indexPath row]; int64_t serialId = [self.contacts[index] serialId]; NSString *sql = @"delete from contacts where id = ?"; sqlite3_stmt *stmt = NULL; if (sqlite3_prepare(_db, [sql UTF8String], -1, &stmt, NULL) == SQLITE_OK) { sqlite3_bind_int64(stmt, 1, serialId); if (sqlite3_step(stmt) == SQLITE_DONE) { [self.contacts removeObjectAtIndex:index]; [self.tableView reloadData]; }sqlite3_finalize(stmt); }} }

搜索

- (void)updateSearchResultsForSearchController:(UISearchController *)searchController { NSString *text = searchController.searchBar.text; NSMutableArray *contacts = nil; if ([text length]) { NSString *searchText = [NSString stringWithFormat:@"%%%@%%",text]; contacts = [NSMutableArray array]; sqlite3_stmt *stmt = NULL; NSString *sql = @"select * from contacts where name like ? or mobile like ?"; if(sqlite3_prepare(_db,[sql UTF8String], -1 , &stmt, NULL) == SQLITE_OK) { sqlite3_bind_text(stmt, 1, [searchText UTF8String], -1, NULL); sqlite3_bind_text(stmt, 2, [searchText UTF8String], -1, NULL); while (sqlite3_step(stmt) == SQLITE_ROW) { int64_t serialId = sqlite3_column_int64(stmt, 0); const char *name = (const char *)sqlite3_column_text(stmt, 1); const char *mobile = (const char *)sqlite3_column_text(stmt, 2); if (name && mobile) { Contact *contact =[[Contact alloc] init]; contact.serialId = serialId; contact.name = [NSString stringWithUTF8String:name]; contact.mobile = [NSString stringWithUTF8String:mobile]; [contacts addObject:contact]; } } sqlite3_finalize(stmt); } }self.filteredContacts = contacts; [self.tableView reloadData]; }