FMDB

/*
 * @DO 插入电话号码
 * @param phoen 电话号码相关信息
 * @return 主键（电话号码）
 */
- (int) insertPhone:(Phone *) phone
{
     NSString *sql = [NSString stringWithFormat:
                   @"INSERT INTO IM_PHONE(PHTYPE,"
                   "PHCODE,UPDATEDT) VALUES "
                   "('%@','%@','%@')",
                   [phone phType],
                   [phone phCode],
                   [phone updateDT]
                   ];
     NSLog(@"%@",sql);
     [db executeUpdate:sql];
     if ([db hadError])
     {
       NSLog(@"insertWith ERROR %d: %@", [db lastErrorCode], [db lastErrorMessage]);
       return (- 1);
     }
     NSLog(@"PK ID = %d",[db lastInsertRowId]);
     return [db lastInsertRowId];

 

http://www.xuanyusong.com/archives/638

http://www.cnblogs.com/xiaobaizhu/archive/2012/12/10/2812178.html 

@interface FkDB ()

@end

@implementation FkDB

+(id)share{
    static dispatch_once_t onceToken;
    static FkDB *db=nil;
    dispatch_once(&onceToken, ^{
        db=[[FkDB alloc] init];
    });
    return db;
}




#pragma mark -创建数据库
-(void)CreateTable{
    NSString *docs=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    docs=[docs stringByAppendingPathComponent:@"fp.db"];
    
    _db=[FMDatabase databaseWithPath:docs];
    _db=[_db retain];
        [_db open];

    if (![_db tableExists:@"FpInfo"]) {
       BOOL flag= [_db executeUpdate:@"CREATE TABLE FpInfo (FPInfoID INTEGER PRIMARY KEY AUTOINCREMENT,FPtype text ,agent  text ,agentNO  text ,agentNOType  text ,buyerUnitAddress  text ,buyerunitbankAccount  text ,buyerunitbankName  text ,buyerUnitName  text ,buyerUnitPhone  text , buyerunitSBH  text ,checker  text ,issueType  text ,payee  text ,remark  text ,salesTaxpayerSBH  text ,salesunitbankName  text ,salesTaxpayerName  text ,bankName  text ,bankAccount  text ,carrierSBH  text ,carrierName  text ,carrierBankName  text,carrierBankName1 text ,carrierBankAccount  text ,consignmentSBH  text ,consignmentName  text ,carNo  text ,shipTon  text ,receiveSBJ  text ,receiveName  text ,draweeSBJ  text ,draweeName  text ,reach  text ,transportGoodInfo  text)"];
        if (flag) NSLog(@"create table fpinfo sucess");
        else NSLog(@"create table fpinfo failed");
    }
    
    if (![_db tableExists:@"FpDetail"]) {
        BOOL flag= [_db executeUpdate:@"CREATE TABLE FpDetail (FpDetailID INTEGER PRIMARY KEY AUTOINCREMENT ,FPInfoID text, projectName  text,taxMoney  text,taxPrice  text,vatTax  text,issueType  text,measurementunit  text,goodsNum  text,taxRate  text,cargoServices text,specificationsName text,noTaxPrice text,notaxMoney  text,levyRates text,payProject text)"];
        if (flag)   NSLog(@"create table FpDetail sucess");
        else  NSLog(@"create table FpDetail failed");
    }
}

#pragma mark -插入sql语句

-(NSString *)BuildInsertTableName:(NSString *)tablename keyArray:(NSArray *)keyArrary valueArrary:(NSArray *)valueArrary PK:(int)pk  fptype:(NSString *)fptype{
    NSMutableString *appendStr=[NSMutableString string];
    NSMutableString *paramStr=[NSMutableString string];
    [appendStr appendFormat:@"INSERT INTO %@(",tablename];
    if ([tablename isEqualToString:@"FpDetail"]) {
        [appendStr appendString:@"FPInfoID,"];
        [paramStr appendFormat:@"'%@',",[NSString stringWithFormat:@"%zi",pk]];
    }
    else{
        [appendStr appendString:@"FPtype,"];
        [paramStr appendFormat:@"'%@',",fptype];
    }
    for (int i=0; i<keyArrary.count; i++) {
        NSString *value=valueArrary[i];
        if (i==keyArrary.count-1) {
            [appendStr appendFormat:@"%@)VALUES (",keyArrary[i]];
              [paramStr appendFormat:@"'%@')",value];
        }
        else{
            [appendStr appendFormat:@"%@,",keyArrary[i]];
            [paramStr appendFormat:@"'%@',",value];
        }
    }
    [appendStr appendString:paramStr];
    return appendStr;
    
}


#pragma mark -删除sql语句

-(void)DeleteFpDetailByID:(int)ID{
    [_db executeUpdate:@"DELETE FROM FpDetail WHERE FPInfoID = ?",[NSNumber numberWithInt:ID]];
    if ([_db hadError])
    {
        NSLog(@"insertWith ERROR %d: %@", [_db lastErrorCode], [_db lastErrorMessage]);
    }
}

#pragma mark -查询

-(NSArray *)SelectFpInfoByID:(int)ID{

  NSMutableArray *temp=[NSMutableArray array];

    FMResultSet *rs=[_db executeQuery:@"SELECT * FROM FpInfo WHERE FPInfoID = ?",[NSNumber numberWithInt:ID]];
     while ([rs next]){
         FPInfo *info= [FPInfo  fpinfoWithSet:rs];
         [temp addObject:info];
     }
    return temp;
}


-(NSMutableArray *)SelectFpInfo{
    
    NSMutableArray *temp=[NSMutableArray array];
    
    FMResultSet *rs=[_db executeQuery:@"SELECT * FROM FpInfo ORDER BY FpInfoID DESC"];

while ([rs next]){
        FPInfo *info= [FPInfo  fpinfoWithSet:rs];
        [temp addObject:info];
    }
    return temp;
}

-(NSArray *)SelectFpDetailByID:(int)ID{
    
    NSMutableArray *temp=[NSMutableArray array];
    
    FMResultSet *rs=[_db executeQuery:@"SELECT * FROM FpDetail WHERE FPInfoID = ?",[NSNumber numberWithInt:ID]];
    while ([rs next]){
        FPDetail *info= [FPDetail FPDetailWithSet:rs];
        [temp addObject:info];
    }
    return temp;
}


#pragma mark -插入数据返回主键

-(int )insertFpInfowithSql:(NSString *)sql{
    NSLog(@"%@",sql);
    [_db executeUpdate:sql];
    if ([_db hadError])
    {
        NSLog(@"insertWith ERROR %d: %@", [_db lastErrorCode], [_db lastErrorMessage]);
        return (- 1);
    }
    return [_db lastInsertRowId];
}