fmdb

#import <UIKit/UIKit.h>
#import "FMDatabase.h"
@interface RootViewController : UIViewController<UITableViewDataSource,UITableViewDelegate,UITextFieldDelegate>
{
    //FMDatabase作用为操作数据库：创建数据库，创建数据库中的表
    //的增删改查等功能
    FMDatabase *_dataBase;
    
    //创建数据源数组
    NSMutableArray *_mutArray;
    //存储选中行的数据库中的id值
    NSString *_t1Id;
    NSString *_userName;
   
    
}
@property (strong, nonatomic) IBOutlet UITextField *nameTextField;
@property (strong, nonatomic) IBOutlet UITextField *pwdTextField;
@property (strong, nonatomic) IBOutlet UITableView *tbView;
@property (strong, nonatomic) IBOutlet UITextField *ageTextField;
- (IBAction)pressSaveToDataBaseBtn:(id)sender;
- (IBAction)deleBtn:(id)sender;
- (IBAction)pressSearchBtn:(id)sender;
- (IBAction)updateBtn:(id)sender;

@end

#import "RootViewController.h"

@interface RootViewController ()

@end

@implementation RootViewController

- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil
{
    self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];
    if (self) {
        // Custom initialization
        //创建数据库
        
        //获得该应用程序的沙盒目录
        NSArray *array=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *sandBoxString=[array objectAtIndex:0];
        NSLog(@"%@",sandBoxString);
        
        //拼凑完整数据库路劲  command shift+g 前往文件夹
        NSString *sqlPathString=[NSString stringWithFormat:@"%@/MyDataBase.sqlite",sandBoxString];
        
        _dataBase=[[FMDatabase alloc]initWithPath:sqlPathString];
        //通过open方法创建和打开数据库，如果指定路径下不存在数据库代表创建数据库，要是存在代表打开数据库
        bool isOpen=[_dataBase open];
        if (isOpen) {
            NSLog(@"OK");
            //创建表格
            //创建表格的SQL语句
            NSString *createTableString=@"create table if not exists table1(id integer primary key autoincrement,username varchar(256),password varchar(256),age int)";
            BOOL isCreatTable=[_dataBase executeUpdate:createTableString];
            if (isCreatTable) {
                NSLog(@"创建表格成功");
            }else{
                NSLog(@"创建表格失败");
            } 
            
        }else{
            NSLog(@"failed");
        }
    }
    return self;
}

- (void)viewDidLoad
{
    [super viewDidLoad];
    // Do any additional setup after loading the view from its nib.
   // _mutArray=[[NSMutableArray alloc]init];
    _mutArray=[NSMutableArray arrayWithCapacity:10];//可变数组默认添加十个空间，当数组越界时系统会自动放大空间
    self.nameTextField.delegate=self;
    self.pwdTextField.delegate=self;
    self.ageTextField.delegate=self;
}

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}
//插入
- (IBAction)pressSaveToDataBaseBtn:(id)sender {
//向表（table1）中插入数据
//插入的SQL语句
    NSString *insert=@"insert into table1 (username,password,age) values (?,?,?)";
    //executeUpdate后边的参数必须为对象类型
    BOOL isInsertOk=[_dataBase executeUpdate:insert,self.nameTextField.text,self.pwdTextField.text,self.ageTextField.text];
    if (isInsertOk) {
        NSLog(@"插入数据成功");
    }else{
        NSLog(@"插入数据失败");
    }
}
//删除
- (IBAction)deleBtn:(id)sender {
    //删除的SQL语句
    NSString *deleString=[NSString stringWithFormat:@"delete from table1 where id= %@",_t1Id];
    [_dataBase executeUpdate:deleString];
    
    //从新加载数据
    NSDictionary *dict=[[NSDictionary alloc]initWithObjectsAndKeys:[NSNumber numberWithInt:[_t1Id intValue]],_userName, nil];
    [_mutArray removeObject:dict];
    [_tbView reloadData];
    
}
//查询
- (IBAction)pressSearchBtn:(id)sender {
    [_mutArray removeAllObjects];//如果不写，没点击查询都会多一个对象在tableview里
    //创建查询SQL语句
    NSString *selectString=@"select * from table1";//查询表的所有数据
   // NSString *selectString=@"select *from tabel1 where id<=2";//按照一定条件查询
    FMResultSet *set=[_dataBase executeQuery:selectString];
    while ([set next]) {
//        //stringForColumn后面时字段的名称
//        NSLog(@"%@",[set stringForColumn:@"username"]);
//          NSLog(@"%i",[set intForColumn:@"age"]);
//        //按照下表获得字段中的数据
//        NSLog(@"%@",[set stringForColumnIndex:2]);
        //年龄作为value用户名作为key封装到字典中
        NSNumber *t1Id=[NSNumber numberWithInt:[set intForColumn:@"id"]];
        NSString *username=[set stringForColumn:@"username"];
        //将每一行的用户名和密码封装到字典
        NSDictionary *dict=[[NSDictionary alloc]initWithObjectsAndKeys:t1Id,username ,nil];
        [_mutArray addObject:dict];
       
    }
    //刷新表格
    [self.tbView reloadData];
}
//修改
- (IBAction)updateBtn:(id)sender {
    //修改数据的SQL语句，
    NSString *updateString=[NSString stringWithFormat:@"update table1 set username = ?,password = ?,age = ? where id = %@",_t1Id];
    //修改指定行的信息
    [_dataBase executeUpdate:updateString,self.nameTextField.text,self.pwdTextField.text,self.ageTextField.text];
    //修改数据后从新加载数据
    [_tbView reloadData];
    
}
#pragma -mark UITabelViewDataSources
//返回行号
-(NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    return _mutArray.count;
}
-(UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
    static NSString *str=@"sa";
    UITableViewCell *cell=[tableView dequeueReusableCellWithIdentifier:str];
    if (cell==nil) {
        cell=[[UITableViewCell alloc]initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:str];
    }
    //获得数据源中的元素
    NSDictionary *dict=[_mutArray objectAtIndex:indexPath.row];
    NSArray *keyArray=[dict allKeys];
    //因为数据源中的每个字典中只有一个键值对
    cell.textLabel.text=[keyArray objectAtIndex:0];
    cell.detailTextLabel.text=[[dict objectForKey:cell.textLabel.text] stringValue];
    
    
    return cell;
}
#pragma -mark UITableViewDelegate
-(void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath
{
    //数据库表中ID，作用为可以根据该id进行数据的删除，更改，和查询指定行的操作
    UITableViewCell *cell=[tableView cellForRowAtIndexPath:indexPath];
   //获得当前行的数据库的id值
    _t1Id=cell.detailTextLabel.text;
    _userName=cell.textLabel.text;
    
    
    NSString *selectString=[NSString stringWithFormat:@"select * from table1 where id = %@",_t1Id];
    FMResultSet *set=[_dataBase executeQuery:selectString];
    while ([set next]) {
        self.nameTextField.text=[set stringForColumn:@"username"];
        self.pwdTextField.text=[set stringForColumn:@"password"];
        self.ageTextField.text=[NSString stringWithFormat:@"%i",[set intForColumn:@"age"]];
    }
    
}
#pragma -mark UITextFieldDelegate
- (BOOL)textFieldShouldReturn:(UITextField *)textField
{
    [super resignFirstResponder];
    return YES;
}
@end

#import <UIKit/UIKit.h>
#import "FMDatabase.h"
@interface LYMAppDelegate : UIResponder <UIApplicationDelegate>
{
    //操作SQL数据库
    FMDatabase *_dataBase;
}
@property (strong, nonatomic) UIWindow *window;

@end
#import "LYMAppDelegate.h"

@implementation LYMAppDelegate

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
    self.window = [[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]];
    // Override point for customization after application launch.
    
    [self createDataBase];
    [self createTable];
   // [self insertData];
   // [self modifyData];
 //   [self searchData];
    [self deleteData];
    
    
    self.window.backgroundColor = [UIColor whiteColor];
    [self.window makeKeyAndVisible];
    return YES;
}
//创建数据库
-(void)createDataBase
{
//获得沙盒目录路径
    NSArray *array=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *sandBoxString=[array objectAtIndex:0];
    NSLog(@"%@",sandBoxString);
    //构建数据库路径
    NSString *dataBaseString=[NSString stringWithFormat:@"%@/MyDataBase2.sqlite",sandBoxString];
    //实例化FMDataBase，通过它实现在指定路径下创建数据库
    _dataBase=[[FMDatabase alloc]initWithPath:dataBaseString];
    //创建打开或打开数据库
    [_dataBase open];
}
//创建表格
-(void)createTable
{
    //创建表格的SQL语句
    NSString *createTableString=@"create table if not exists Students (id integer primary key autoincrement,username varchar(256),age int,img blob)";//blob二进制
    [_dataBase executeUpdate:createTableString];
    
}
//插入数据
-(void)insertData
{
    //创建插入SQL语句
    NSString *insertString=@"insert into Students (username,age,img) values (?,?,?)";
    UIImage *image=[UIImage imageNamed:@"a.png"];
    NSData *data=UIImagePNGRepresentation(image);
    [_dataBase executeUpdate:insertString,@"zhang",@"21",data];
}
//查询数据库
-(void)searchData
{
    //创建查询SQL语句
    NSString *selectString=@"select * from Students";
    FMResultSet *set=[_dataBase executeQuery:selectString];
    while ([set next]) {
        NSLog(@"%@",[set stringForColumn:@"username"]);
       //从数据库中读取二进制数据
        NSData *data=[set dataForColumn:@"img"];
        UIImage *img=[[UIImage alloc]initWithData:data];
        UIImageView *imgView=[[UIImageView alloc]initWithFrame:CGRectMake(100, 100, 100, 100)];
        imgView.image=img;
        [self.window addSubview:imgView];
        
    }
    
}
//更改数据
-(void)modifyData
{
    //创建更改数据SQL语句
    NSString *updateString=@"update Students set username = ?,age = ?,img = ? where id=1";
    UIImage *image=[UIImage imageNamed:@"b.png"];
    NSData *data=UIImagePNGRepresentation(image);
    [_dataBase executeUpdate:updateString ,@"wang",@"50",data];
}
//删除数据
-(void)deleteData
{
    //创建删除数据SQL语句
    NSString *deleString=@"delete from Students where id=1";
    [_dataBase executeUpdate:deleString];
}