需要注意的是这里面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];
}