iOS开发中 SQLite3的使用案例

创建表格

createSql = "create table if not exists oldCustomersForm(customerId long,name text,gender text,certiType text,certiCode text,birthday text)";

    if( sqlite3_exec(myDataBase, createSql, NULL, NULL, &error)==SQLITE_OK)

        NSLog(@"create oldCustomersForm success...");

    else

        NSLog(@"create oldCustomersForm fail.....");

    //**准客户表格

    createSql = "create table if not exists prospectivesForm(customerId long,name text,gender text,certiType text,certiCode text,birthday text)";

    if( sqlite3_exec(myDataBase, createSql, NULL, NULL, &error)==SQLITE_OK)

        NSLog(@"create prospectivesForm success...");

    else

        NSLog(@"create prospectivesForm fail.....");

增加内容

 //增加几条客户信息

        Old_Prospective_CuModel *object = (Old_Prospective_CuModel *)content;

        insertSql = [NSString stringWithFormat:@"insert into %@ (customerId,name,gender,certiType,certiCode,birthday) values(?,?,?,?,?,?)",formName];

        

        if(sqlite3_prepare_v2(myDataBase, [insertSql UTF8String], -1, &stmt, NULL) == SQLITE_OK){

            sqlite3_bind_int64(stmt, 1, object.customerId);

            sqlite3_bind_text(stmt, 2, [object.name==nil?@"":object.name UTF8String], -1, NULL);

            sqlite3_bind_text(stmt, 3, [object.gender==nil?@"":object.gender UTF8String], -1, NULL);

            sqlite3_bind_text(stmt, 4, [object.certiType==nil?@"":object.certiType UTF8String], -1, NULL);

            sqlite3_bind_text(stmt, 5, [object.certiCode==nil?@"":object.certiCode UTF8String], -1, NULL);

            sqlite3_bind_text(stmt, 6, [object.birthday==nil?@"":object.birthday UTF8String], -1, NULL);

            //NSLog(@"insert CustomersForm success...");

查找内容

 //**获取用户信息

        getSql = [NSString stringWithFormat:@"select *from %@",formName];

        if(sqlite3_prepare(myDataBase, [getSql UTF8String], -1, &stmt, NULL)==SQLITE_OK){

            while (sqlite3_step(stmt)==SQLITE_ROW) {

                Old_Prospective_CuModel *newModel = [Old_Prospective_CuModel model];

                newModel.customerId = (long)sqlite3_column_int64(stmt,0);

                newModel.name = [NSString stringWithCString:(char *)sqlite3_column_text(stmt, 1) encoding:NSUTF8StringEncoding];

                newModel.gender = [NSString stringWithCString:(char *)sqlite3_column_text(stmt, 2) encoding:NSUTF8StringEncoding];

                newModel.certiType = [NSString stringWithCString:(char *)sqlite3_column_text(stmt, 3) encoding:NSUTF8StringEncoding];

                newModel.certiCode = [NSString stringWithCString:(char *)sqlite3_column_text(stmt, 4) encoding:NSUTF8StringEncoding];

                newModel.birthday = [NSString stringWithCString:(char *)sqlite3_column_text(stmt, 5) encoding:NSUTF8StringEncoding];

清空表格内容

 [self openDataBase];

    NSString *clearSql = NULL;

    char *error;

    

    NSString *formName = nil;

    if(flag ==oldCustomersFormFlag)

        formName = @"oldCustomersForm";

    if(flag ==prospectivesFormFlag)

        formName = @"prospectivesForm";

    

    if((flag==oldCustomersFormFlag)||(flag ==prospectivesFormFlag)){

        //**客户信息表格

        

        clearSql = [NSString stringWithFormat:@"delete from %@",formName];

        if( sqlite3_exec(myDataBase, [clearSql UTF8String], NULL, NULL, &error)==SQLITE_OK)

            NSLog(@"delete CustomersForm success......");

        else

            NSLog(@"delete CustomersForm fail........");

        

        

    }

删除一条内容

 deleteSql = [NSString stringWithFormat:@"delete from companyCreditForm  where certificatetype = '%@' and certificatenumber = '%@' and customerId = '%@'",company.certificateType,company.certificateNumber,company.customerId];

            

            if(sqlite3_exec(myDataBase, [deleteSql UTF8String], NULL, NULL, &error) !=SQLITE_OK)

                NSLog(@"detele CompanyCredtiModel fail........");