iOS对数据库的操作

对数据库的操作
@interface HMViewController () <UITableViewDataSource, UISearchBarDelegate>
@property (weak, nonatomic) IBOutlet UITextField *nameField;
@property (weak, nonatomic) IBOutlet UITextField *priceField;
/** 数据库对象实例 */
@property (nonatomic, assign) sqlite3 *db;
@property (weak, nonatomic) IBOutlet UITableView *tableView;
- (IBAction)insert;
@property (nonatomic, strong) NSMutableArray *shops;
@end


@implementation HMViewController


- (NSMutableArray *)shops
{
    if (!_shops) {
        self.shops = [[NSMutableArray alloc] init];
    }
    return _shops;
}


- (void)viewDidLoad
{
    [super viewDidLoad];
    
    // 增加搜索框
    UISearchBar *searchBar = [[UISearchBar alloc] init];
    searchBar.frame = CGRectMake(0, 0, 320, 44);
    searchBar.delegate = self;
    self.tableView.tableHeaderView = searchBar;
    
    // 初始化数据库
    [self setupDb];
    
    // 查询数据
    [self setupData];
    
    // 关闭数据库
    //    sqlite3_close();
}


#pragma mark - UISearchBarDelegate
- (void)searchBar:(UISearchBar *)searchBar textDidChange:(NSString *)searchText
{
    [self.shops removeAllObjects];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT name,price FROM t_shop WHERE name LIKE '%%%@%%' OR  price LIKE '%%%@%%' ;", searchText, searchText];
    // stmt是用来取出查询结果的
    sqlite3_stmt *stmt = NULL;
    // 准备
    int status = sqlite3_prepare_v2(self.db, sql.UTF8String, -1, &stmt, NULL);
    if (status == SQLITE_OK) { // 准备成功 -- SQL语句正确
        while (sqlite3_step(stmt) == SQLITE_ROW) { // 成功取出一条数据
            const char *name = (const char *)sqlite3_column_text(stmt, 0);
            const char *price = (const char *)sqlite3_column_text(stmt, 1);
            
            HMShop *shop = [[HMShop alloc] init];
            shop.name = [NSString stringWithUTF8String:name];
            shop.price = [NSString stringWithUTF8String:price];
            [self.shops addObject:shop];
        }
    }
    
    [self.tableView reloadData];
}


/**
 查询数据
 */
- (void)setupData
{
    const char *sql = "SELECT name,price FROM t_shop;";
    // stmt是用来取出查询结果的
    sqlite3_stmt *stmt = NULL;
    // 准备
    int status = sqlite3_prepare_v2(self.db, sql, -1, &stmt, NULL);
    if (status == SQLITE_OK) { // 准备成功 -- SQL语句正确
        while (sqlite3_step(stmt) == SQLITE_ROW) { // 成功取出一条数据
            const char *name = (const char *)sqlite3_column_text(stmt, 0);
            const char *price = (const char *)sqlite3_column_text(stmt, 1);
            
            HMShop *shop = [[HMShop alloc] init];
            shop.name = [NSString stringWithUTF8String:name];
            shop.price = [NSString stringWithUTF8String:price];
            [self.shops addObject:shop];
        }
    }
}


/**
 初始化数据库
 */
- (void)setupDb
{
    // 打开数据库(连接数据库)
    NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"shops.sqlite"];
    // 如果数据库文件不存在, 系统会自动创建文件自动初始化数据库
    int status = sqlite3_open(filename.UTF8String, &_db);
    if (status == SQLITE_OK) { // 打开成功
        NSLog(@"打开数据库成功");
        
        // 创表
        const char *sql = "CREATE TABLE IF NOT EXISTS t_shop (id integer PRIMARY KEY, name text NOT NULL, price real);";
        char *errmsg = NULL;
        sqlite3_exec(self.db, sql, NULL, NULL, &errmsg);
        if (errmsg) {
            NSLog(@"创表失败--%s", errmsg);
        }
    } else { // 打开失败
        NSLog(@"打开数据库失败");
    }
}


- (IBAction)insert {
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO t_shop(name, price) VALUES ('%@', %f);", self.nameField.text, self.priceField.text.doubleValue];
    sqlite3_exec(self.db, sql.UTF8String, NULL, NULL, NULL);
    
    // 刷新表格
    HMShop *shop = [[HMShop alloc] init];
    shop.name = self.nameField.text;
    shop.price = self.priceField.text;
    [self.shops addObject:shop];
    [self.tableView reloadData];
}


#pragma mark - 数据源方法
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    return self.shops.count;
}


- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
    static NSString *ID = @"shop";
    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:ID];
    if (!cell) {
        cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:ID];
        cell.backgroundColor = [UIColor grayColor];
    }
    
    HMShop *shop = self.shops[indexPath.row];
    cell.textLabel.text = shop.name;
    cell.detailTextLabel.text = shop.price;
    
    return cell;
}
@end




/**
 NSMutableString *sql = [NSMutableString string];
 
 for (int i = 0; i<1000; i++) {
 NSString *name = [NSString stringWithFormat:@"iPhone%d", i];
 double price = arc4random() % 10000 + 100;
 int leftCount = arc4random() % 1000;
 [sql appendFormat:@"insert into t_shop(name, price, left_count) values ('%@', %f, %d);\n", name, price, leftCount];
 }
 
 [sql writeToFile:@"/Users/apple/Desktop/shops.sql" atomically:YES encoding:NSUTF8StringEncoding error:nil];
 */






//FMDB的使用方法
#import "HMViewController.h"
//#import "FMDB.h"
#import "HMShop.h"
#import "HMShopTool.h"


@interface HMViewController ()
//@property (nonatomic, strong) FMDatabase *db;
@end


@implementation HMViewController


- (void)viewDidLoad
{
    [super viewDidLoad];


    // 1.打开数据库
//    NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"shops.sqlite"];
//    self.db = [FMDatabase databaseWithPath:path];
//    [self.db open];
//    
//    // 2.创表
//    [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_shop (id integer PRIMARY KEY, name text NOT NULL, price real);"];
    // executeQuery:查询数据
//    [self.db executeQuery:<#(NSString *), ...#>];
    
    // executeUpdate:除查询数据以外的其他操作
//    [self.db executeUpdate:<#(NSString *), ...#>];
}


- (void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event
{
//    for (int i = 0; i<100; i++) {
//        HMShop *shop = [[HMShop alloc] init];
//        shop.name = [NSString stringWithFormat:@"枕头--%d", i];
//        shop.price = arc4random() % 200;
//        [HMShopTool addShop:shop];
//    }
    
    NSArray *shops = [HMShopTool shops];
    for (HMShop *shop in shops) {
        NSLog(@"%@ %f", shop.name, shop.price);
    }
    
//    [self.db executeUpdate:@"DELETE FROM t_shop WHERE price < 800;"];
//    
//    [self query];
}


- (void)query
{
    // 得到结果集
//    FMResultSet *set = [self.db executeQuery:@"SELECT * FROM t_shop;"];
//    
//    // 不断往下取数据
//    while (set.next) {
//        // 获得当前所指向的数据
//        NSString *name = [set stringForColumn:@"name"];
//        double price = [set doubleForColumn:@"price"];
//        NSLog(@"%@ %f", name, price);
//    }
}


- (void)insert
{
//    for (int i = 0; i<100; i++) {
//        NSString *name = [NSString stringWithFormat:@"手机-%d", i];
//#warning 这里的字符串不用再加上''
//        [self.db executeUpdateWithFormat:@"INSERT INTO t_shop(name, price) VALUES (%@, %d);", name, arc4random()%1000];
//    }
}


@end






//事件的处理
- (void)awakeFromNib
{
    self.backgroundColor = [UIColor greenColor];
    
//    UIButton *button = [UIButton buttonWithType:UIButtonTypeContactAdd];
//    button.center = CGPointMake(100, 100);
//    [button addTarget:self action:@selector(click) forControlEvents:UIControlEventTouchUpInside];
//    [self addSubview:button];
//    self.button = button;
    
    HMRedView *redView = [[HMRedView alloc] init];
    redView.frame = CGRectMake(100, 100, 100, 100);
    [self addSubview:redView];
    self.redView = redView;
}


//- (void)click
//{
//    NSLog(@"点击了按钮");
//}


- (void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event
{
    NSLog(@"HMGreenView----touchesBegan");
}


- (UIView *)hitTest:(CGPoint)point withEvent:(UIEvent *)event
{
    return self.redView;
}
@end




@implementation HMRedView


- (id)initWithFrame:(CGRect)frame
{
    self = [super initWithFrame:frame];
    if (self) {
        self.backgroundColor = [UIColor redColor];
    }
    return self;
}


- (void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event
{
    NSLog(@"HMRedView----touchesBegan");
}
@end


//FMDB将任意对象存入数据库中
@interface HMViewController ()
@property (nonatomic, strong) FMDatabase *db;
@end


@implementation HMViewController


- (void)viewDidLoad
{
    [super viewDidLoad];
    
    [self setup];
    
    [self readShops];
}


- (void)setup
{
    // 初始化
    NSString *path = @"/Users/apple/Desktop/shops.data";
    self.db = [FMDatabase databaseWithPath:path];
    [self.db open];
    
    // 2.创表
    [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_shop (id integer PRIMARY KEY, shop blob NOT NULL);"];
}


- (void)readShops
{
    FMResultSet *set = [self.db executeQuery:@"SELECT * FROM t_shop LIMIT 10,10;"];
    while (set.next) {
        NSData *data = [set objectForColumnName:@"shop"];
        HMShop *shop = [NSKeyedUnarchiver unarchiveObjectWithData:data];
        NSLog(@"%@", shop);
    }
//    NSMutableArray *shops = [NSKeyedUnarchiver unarchiveObjectWithFile:@"/Users/apple/Desktop/shops.data"];
//    NSLog(@"%@", [shops subarrayWithRange:NSMakeRange(20, 10)]);
}


- (void)addShops
{
//    NSMutableArray *shops = [NSMutableArray array];
//    for (int i = 0; i<1000; i++) {
//        HMShop *shop = [[HMShop alloc] init];
//        shop.name = [NSString stringWithFormat:@"商品--%d", i];
//        shop.price = arc4random() % 10000;
//        [shops addObject:shop];
//    }
//    [NSKeyedArchiver archiveRootObject:shops toFile:@"/Users/apple/Desktop/shops.data"];
    for (int i = 0; i<100; i++) {
        HMShop *shop = [[HMShop alloc] init];
        shop.name = [NSString stringWithFormat:@"商品--%d", i];
        shop.price = arc4random() % 10000;
        
        NSData *data = [NSKeyedArchiver archivedDataWithRootObject:shop];
        [self.db executeUpdateWithFormat:@"INSERT INTO t_shop(shop) VALUES (%@);", data];
    }
}


@end




//下拉放大的实现
const CGFloat HMTopViewH = 350;


@interface HMViewController ()
@property (nonatomic, weak) UIImageView *topView;
@end


@implementation HMViewController


- (void)viewDidLoad
{
    [super viewDidLoad];
    
    // 设置内边距(让cell往下移动一段距离)
    self.tableView.contentInset = UIEdgeInsetsMake(HMTopViewH * 0.5, 0, 0, 0);
    
    UIImageView *topView = [[UIImageView alloc] init];
    topView.image = [UIImage imageNamed:@"biaoqingdi"];
    topView.frame = CGRectMake(0, -HMTopViewH, 320, HMTopViewH);
    topView.contentMode = UIViewContentModeScaleAspectFill;
    [self.tableView insertSubview:topView atIndex:0];
    self.topView = topView;
}


#pragma mark - 数据源方法
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    return 20;
}


- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
    static NSString *ID = @"cell";
    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:ID];
    if (!cell) {
        cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:ID];
    }
    
    cell.textLabel.text = [NSString stringWithFormat:@"测试数据---%d", indexPath.row];
    
    return cell;
}


- (void)scrollViewDidScroll:(UIScrollView *)scrollView
{
    // 向下拽了多少距离
    CGFloat down = -(HMTopViewH * 0.5) - scrollView.contentOffset.y;
    if (down < 0) return;
    
    CGRect frame = self.topView.frame;
    // 5决定图片变大的速度,值越大,速度越快
    frame.size.height = HMTopViewH + down * 5;
    self.topView.frame = frame;
}


@end




//修改视屏名称
// 创业天使-xxx 120101_超清.mp4 --> 120101-创业天使-xxx.mp4
int main(int argc, const char * argv[])
{
    @autoreleasepool {
        NSFileManager *mgr = [NSFileManager defaultManager];
        NSString *dir = @"/Users/apple/Desktop/videos";
        NSArray *subpaths = [mgr subpathsAtPath:dir];
        for (NSString *subpath in subpaths) {
            if (![subpath hasSuffix:@"mp4"]) continue;
            // 获得全路径
            NSString *fullSubpath = [dir stringByAppendingPathComponent:subpath];
            // 获得文件名
            NSString *filename = [subpath.lastPathComponent stringByDeletingPathExtension];
            
            // 根据文件名获取对应的前缀和后缀
            NSString *prefix = [filename stringByMatching:@"(\\d{6})_.清" capture:YES];
            NSString *suffix = [filename stringByMatching:@"(.+) \\d{6}_.清" capture:YES];
            NSString *newFilename = [NSString stringWithFormat:@"%@-%@", prefix, suffix];
            
            // 生成新的全路径
            NSString *newFullSubpath = [fullSubpath stringByReplacingOccurrencesOfString:filename withString:newFilename];
//            NSData *data = [NSData dataWithContentsOfFile:fullSubpath];
//            [data writeToFile:newFullSubpath atomically:YES];
//            [mgr removeItemAtPath:fullSubpath error:nil];
            
            // 剪切\移动
            [mgr moveItemAtPath:fullSubpath toPath:newFullSubpath error:nil];
        }
    }
    return 0;
}