FMDB数据库的简单实用

拼搏现实的明天。 2021-11-22 14:58 359阅读 0赞

引入![291015244558289.png][]和FMDB第三方类库，  demo地址：http://pan.baidu.com/s/1c0pbfxA

1 #define dataBasePath [[(NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES)) lastObject]stringByAppendingPathComponent:dataBaseName]
     2 #define dataBaseName @"MyDatabase.db"
     3 #import "ViewController.h"
     4 #import "FMDatabase.h"
     5 #import "FMDatabaseAdditions.h"
     6 #import "FMDatabaseQueue.h"
     7 
     8 
     9 #import "MYDataBase.h"
    10 @interface ViewController ()
    11 
    12 @end
    13 
    14 @implementation ViewController
    15 
    16 - (void)viewDidLoad
    17 {
    18     [super viewDidLoad];
    19     // Do any additional setup after loading the view, typically from a nib.
    20     FMDatabase *db = [FMDatabase databaseWithPath:dataBasePath];
    21     if (![db open]) {
    22         NSLog(@"no");
    23         return;
    24     }NSLog(@"111");
    25     //建立新表
    26     [db executeUpdate:@"CREATE TABLE PersonList (Name text, Age integer, Sex integer, Phone text, Address text, Photo blob)"];
    27     
    28     //插入一条数据
    29     [db executeUpdate:@"INSERT INTO PersonList (Name, Age, Sex, Phone, Address, Photo) VALUES (?,?,?,?,?,?)",
    30      @"Jone", [NSNumber numberWithInt:20], [NSNumber numberWithInt:0], @"091234567", @"Taiwan, R.O.C", nil];
    31     //更新数据
    32     [db executeUpdate:@"UPDATE PersonList SET Age = ? WHERE Name = ?",[NSNumber numberWithInt:30],@"John"];
    33     
    34     
    35    //取数据
    36     //返回数据库中第一条满足条件的结果
    37     NSString *aa=[db stringForQuery:@"SELECT Name FROM User WHERE Name = ?",@"John"];
    38     
    39     //fan返回满足条件的全部结果
    40    // FMResultSet *rs = [db executeQuery:@"SELECT Name, Age FROM PersonList"];
    41     //
    42     FMResultSet *rs = [db executeQuery:@"SELECT * FROM PersonList"];
    43     
    44     while ([rs next]) {
    45         
    46         NSString *name = [rs stringForColumn:@"Name"];
    47         
    48         int age = [rs intForColumn:@"Age"];
    49         NSLog(@"name=%@,age = %d",name,age);
    50         
    51     }
    52     
    53     [rs close];
    54     
    55     
    56     
    57     //快速读取一个row的一个数据
    58     //找地址
    59     
    60     NSString *address = [db stringForQuery:@"SELECT Address FROM PersonList WHERE Name = ?",@"John"];
    61     NSLog(@"address=%@",address);
    62                          //找年齡
    63                     
    64                          int age = [db intForQuery:@"SELECT Age FROM PersonList WHERE Name = ?",@"John"];
    65     
    66     NSLog(@"age2 = %d",age);
    67     
    68     
    69     //可以多个参数穿进去
    70     NSDictionary *argsDict = [NSDictionary dictionaryWithObjectsAndKeys:@"donghongqiang", @"name",@"14",@"Age",@"dezhou",@"Address", nil];
    71     [db executeUpdate:@"INSERT INTO PersonList (Name,Age,Address) VALUES (:name,:Age,:Address)" withParameterDictionary:argsDict];
    72 
    73     
    74     NSString *address2 = [db stringForQuery:@"SELECT Address FROM PersonList WHERE Name = ?",@"donghongqiang"];
    75     NSLog(@"address2=%@",address2);
    76     
    77     
    78 }

对一些多线程操作的情况，要使用FMDatabaseQueue来操作，防止多线程对一个数据库操作发生错误

1 -(void)FMDbatabasequeue
     2 
     3 {
     4 
     5     
     6 
     7     // 创建，最好放在一个单例的类中
     8 
     9     FMDatabaseQueue *queue = [FMDatabaseQueuedatabaseQueueWithPath:dataBasePath];
    10 
    11     
    12 
    13     // 使用
    14 
    15     [queue inDatabase:^(FMDatabase *db) {
    16 
    17         [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumbernumberWithInt:1]];
    18 
    19         [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumbernumberWithInt:2]];
    20 
    21         [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumbernumberWithInt:3]];
    22 
    23         
    24 
    25         FMResultSet *rs = [db executeQuery:@"select * from foo"];
    26 
    27         while ([rs next]) {
    28 
    29             // …
    30 
    31         }
    32 
    33     }];
    34 
    35     
    36 
    37     // 如果要支持事务
    38 
    39     [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    40 
    41         [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumbernumberWithInt:1]];
    42 
    43         [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumbernumberWithInt:2]];
    44 
    45         [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumbernumberWithInt:3]];
    46 
    47         
    48 
    49 //        if (whoopsSomethingWrongHappened) {
    50 
    51 //            *rollback = YES;
    52 
    53 //            return;
    54 
    55 //        }
    56 
    57         // etc…
    58 
    59         [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumbernumberWithInt:4]];
    60 
    61     }];
    62 
    63     
    64 
    65     
    66 
    67     
    68 
    69     
    70 
    71     
    72 
    73     [queue close];
    74 
    75 }
    76 
    77

可以封装成单例的类，来进行操作，

//  Created by apple on 14-4-28.
    //  Copyright (c) 2014年 apple. All rights reserved.
    //
    #define dataBasePath [[(NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES)) lastObject]stringByAppendingPathComponent:dataBaseName]
    #define dataBaseName @"MyDatabase.db"
    
    #import <Foundation/Foundation.h>
    #import "FMDatabaseQueue.h"
    #import "Pins.h"
    #import "FMDatabaseAdditions.h"
    #import "FMDatabase.h"
    @interface MYDataBase : NSObject
    {
        
    }
    @property(nonatomic,retain)FMDatabaseQueue *dbQueue;
    
    +(MYDataBase*)sharedInstance;
    -(Pins*)rsToPins:(FMResultSet*)rs;
    - (void)addPins:(Pins*)pin;
    - (NSArray *)getPins;
    @end

//
    //  MYDataBase.m
    //  TextFMDB
    //
    //  Created by apple on 14-4-28.
    //  Copyright (c) 2014年 apple. All rights reserved.
    //
    
    #import "MYDataBase.h"
    static MYDataBase *mydatabase = nil;
    @implementation MYDataBase
    
    - (id)init
    {
        
        self = [super init];
        if (self)
        {
           // self.dbFile = [DbFileManager dbFilePath];
            self.dbQueue = [FMDatabaseQueue databaseQueueWithPath:dataBasePath];
            
            
        }
        return  self;
    }
    +(MYDataBase*)sharedInstance{
        
        
        @synchronized(self)
        {
            if (mydatabase == nil)
                mydatabase = [[MYDataBase alloc] init];
        }
        return mydatabase;
    }
    
    //huoq获取pin
    -(Pins*)rsToPins:(FMResultSet*)rs
    {
        Pins *pin = [[Pins alloc] init] ;
    //    user.ID = [rs intForColumn:@"_id"];
    //    user.name = [rs stringForColumn:@"name"];
    //    user.password = [rs  stringForColumn:@"password"];
        return pin;
        
    }
    
    - (void)addPins:(Pins*)pin
    {
        [self.dbQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
            [db open];
    //        NSString *sql = @"insert into tbl_user(name, password) values (?, ?)";
    //        [db executeUpdate:sql,user.name, user.password];
            [db close];
        }];
    }
    
    - (NSArray *)getPins;
    {
        __block NSMutableArray *users = [[NSMutableArray alloc] init];
        [self.dbQueue inDatabase:^(FMDatabase *db)   {
            [db open];
            NSString *sql = @"select * from tbl_user ";
            FMResultSet *rs = [db executeQuery:sql];
            while ([rs next])
            {
                [users addObject:[self rsToPins :rs]];
            }
            [db close];
        }];
        return users;
    }
    
    @end

其中的pins类

#import <Foundation/Foundation.h>
    
    @interface Pins : NSObject
    {
        
    }
    @property(nonatomic,assign)int ID;
    @property(nonatomic,retain)NSString *name;
    @property(nonatomic,retain)NSString *address;
    @property(nonatomic,retain)NSData *imagedata;
    
    //+(Pins*)sharedInstance;
    
    @end

#import "Pins.h"
    
    @implementation Pins
    @synthesize ID = _ID,name = _name,address = _address,imagedata = _imagedata;
    
    
    
    @end

转载于:https://www.cnblogs.com/KingQiangzi/p/3698686.html

[291015244558289.png]: /images/20211122/2f5b9bf2b61745629d373ddf9686b41c.png