feeling/FMDB2_1/DBDaoHelper.m

//
//  DBDaoHelper.m
//  SaintiFrameWork
//
//  Created by yan.jm on 14/11/6.
//  Copyright (c) 2016年 yan.jm. All rights reserved.
//

#import "DBDaoHelper.h"
#import "DBHelper.h"
#import "MusicModel.h"
@implementation DBDaoHelper
//创建数据库表
+(BOOL)createAllTable
{
    FMDatabase *db =[DBHelper openDatabase];
    BOOL result = [db executeUpdate:@"CREATE TABLE TypeList(artist_id integer ,all_artist_ting_uid integer ,all_artist_id integer ,language text,publishtime text ,album_no integer ,pic_big text,pic_small text,country text,area integer,lrclink text, hot integer ,file_duration integer ,del_status integer ,resource_type integer ,copy_type integer ,relate_status integer ,all_rate integer ,has_mv_mobile integer ,toneid integer ,song_id integer ,title text ,ting_uid integer ,author text ,album_id integer ,album_title text ,is_first_publish integer ,havehigh integer ,charge integer ,has_mv integer ,learn integer ,piao_id integer ,listen_total integer,type integer )"];
    [db executeUpdate:@"CREATE TABLE DownList(artist_id integer ,all_artist_ting_uid integer ,all_artist_id integer ,language text,publishtime text ,album_no integer ,pic_big text,pic_small text,country text,area integer,lrclink text, hot integer ,file_duration integer ,del_status integer ,resource_type integer ,copy_type integer ,relate_status integer ,all_rate integer ,has_mv_mobile integer ,toneid integer ,song_id integer ,title text ,ting_uid integer ,author text ,album_id integer ,album_title text ,is_first_publish integer ,havehigh integer ,charge integer ,has_mv integer ,learn integer ,piao_id integer ,listen_total integer,type integer )"];
    [db executeUpdate:@"CREATE TABLE 'MyXinqing' (mtime integer,content text)"];
    [db executeUpdate:@"CREATE TABLE myType('type' INTEGER PRIMARY KEY AUTOINCREMENT ,typeName text )"];
    
    [db executeUpdate:@"insert into 'myType' (type,typeName) values('1','欢快')"];
    [db executeUpdate:@"insert into 'myType' (type,typeName) values('2','甜蜜')"];
    [db executeUpdate:@"insert into 'myType' (type,typeName) values('3','安静')"];
    [db executeUpdate:@"insert into 'myType' (type,typeName) values('4','伤感')"];
    [db executeUpdate:@"insert into 'myType' (type,typeName) values('5','思念')"];
    
    [db close];
    if (result )
    {
        return YES;
    }
    else
    {
        return NO;
    }
}
////插入我的便签信息
//+(BOOL )insertNotePadWith:(NotePad *)notepad
//{
//    FMDatabase *db =[DBHelper openDatabase];
//    BOOL result = [db executeUpdate:@"INSERT INTO 'TABLE_NOTE'('note_color','note_text','note_datetime','note_week','note_clocktime') VALUES(?,?,?,?,?)",notepad.strNoteColor,notepad.strNoteText,notepad.strNoteDateTime,notepad.strNoteWeek,notepad.strClockTime];
//    [db close];
//    return result;
//}
+(BOOL)DeleteMusicWith:(NSString *)type song_id:(NSString *)song_id
{
    
    FMDatabase *db = [DBHelper openDatabase];
    NSString *str = [NSString stringWithFormat:@"DELETE FROM TypeList WHERE type = '%@' and song_id = '%@'",type,song_id];
    BOOL result = [db executeUpdate:str];
    [db close];
    return result;
}
//根据id查询便签信息
+(NSMutableArray *)selectMusicWithType:(NSInteger)type
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM TypeList WHERE type = ?",[NSString stringWithFormat:@"%zd",type]];
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    
    while (result.next)
    {
        FMSongListModel *model = [[FMSongListModel alloc]init];
        //根据列名取得数据
        model.artist_id = [result intForColumn:@"artist_id"];
        model.all_artist_ting_uid = [result intForColumn:@"all_artist_ting_uid"];
        model.all_artist_id = [result intForColumn:@"all_artist_id"];
        model.language = [result stringForColumn:@"language"];
        model.publishtime = [result stringForColumn:@"publishtime"];
        model.album_no = [result intForColumn:@"album_no"];
        model.pic_big = [result stringForColumn:@"pic_big"];
        model.pic_small = [result stringForColumn:@"pic_small"];
        model.country = [result stringForColumn:@"country"];
        model.area = [result intForColumn:@"area"];
        model.lrclink = [result stringForColumn:@"lrclink"];
        model.hot = [result intForColumn:@"hot"];
        model.file_duration = [result intForColumn:@"file_duration"];
        model.del_status = [result intForColumn:@"del_status"];
        model.resource_type = [result intForColumn:@"resource_type"];
        model.copy_type = [result intForColumn:@"copy_type"];
        model.relate_status = [result intForColumn:@"relate_status"];
        model.all_rate = [result intForColumn:@"all_rate"];
        model.has_mv_mobile = [result intForColumn:@"has_mv_mobile"];
        model.toneid = [result intForColumn:@"toneid"];
        model.song_id = [result intForColumn:@"song_id"];
        model.title = [result stringForColumn:@"title"];
        model.ting_uid = [result intForColumn:@"ting_uid"];
        model.author = [result stringForColumn:@"author"];
        model.album_id = [result intForColumn:@"album_id"];
        model.album_title = [result stringForColumn:@"album_title"];
        model.is_first_publish = [result intForColumn:@"is_first_publish"];
        model.havehigh = [result intForColumn:@"havehigh"];
        model.charge = [result intForColumn:@"charge"];
        model.has_mv = [result intForColumn:@"has_mv"];
        model.learn = [result intForColumn:@"learn"];
        model.piao_id = [result intForColumn:@"piao_id"];
        model.listen_total = [result intForColumn:@"listen_total"];
        [arr addObject:model];
    }
    return arr;
}
+(BOOL)insertMyTypeWith:(NSString *)type
{
    
    FMDatabase *db = [DBHelper openDatabase];
    NSString *str = [NSString stringWithFormat:@"insert into 'myType' (typeName) values('%@')",type];
    BOOL result = [db executeUpdate:str];
    [db close];
    return result;
}

+(BOOL)DeleteMyTypeWith:(NSString *)type
{
    
    FMDatabase *db = [DBHelper openDatabase];
    NSString *str = [NSString stringWithFormat:@"DELETE FROM myType WHERE type = '%@'",type];
     NSString *strMusic = [NSString stringWithFormat:@"DELETE FROM TypeList WHERE type = '%@'",type];
    BOOL result = [db executeUpdate:str];
    [db executeUpdate:strMusic];
    [db close];
    return result;
}
//根据id查询便签信息
+(NSMutableArray *)myType
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM myType order by type asc"];
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    
    while (result.next)
    {
        //根据列名取得数据
        NSString *strName = [result stringForColumn:@"typeName"];;
        
        [arr addObject:strName];
    }
    return arr;
}
//根据id查询便签信息
+(NSMutableArray *)myTypeId
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM myType order by type asc"];
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    
    while (result.next)
    {
        //根据列名取得数据
        NSString *strName = [result stringForColumn:@"type"];;
        
        [arr addObject:strName];
    }
    return arr;
}
+(BOOL)insertXinqingWith:(NSString *)time content:(NSString *)content
{
    
    FMDatabase *db = [DBHelper openDatabase];
    NSString *str = [NSString stringWithFormat:@"insert into 'MyXinqing' (mtime,content) values('%@','%@')",time,content];
    BOOL result = [db executeUpdate:str];
    [db close];
    return result;
}
//根据id查询便签信息
+(NSMutableArray *)selecXinqing
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM MyXinqing order by mtime DESC"];
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    
    while (result.next)
    {
        FMSongListModel *model = [[FMSongListModel alloc]init];
        //根据列名取得数据
        model.pic_small = [result stringForColumn:@"mtime"];;
       model.pic_big = [result stringForColumn:@"content"];
        
        [arr addObject:model];
    }
    return arr;
}

+(BOOL)insertMusicWith:(NSInteger )songid type:(NSInteger)type name:(NSString *)name
{
    
    FMDatabase *db = [DBHelper openDatabase];
    NSString *str = [NSString stringWithFormat:@"insert into 'TypeList' (songid,type,name) values('%zd','%zd','%@')",songid,type,name];
    BOOL result = [db executeUpdate:str];
    [db close];
    return result;
}
+(BOOL)insertMusicWith:(FMSongListModel * )model type:(NSInteger)type
{
    
    FMDatabase *db = [DBHelper openDatabase];
    NSString *str = [NSString stringWithFormat:@"insert into 'TypeList' values('%zd','%zd','%zd','%@','%@','%zd','%@','%@','%@','%zd','%@','%zd','%zd','%zd','%zd','%zd','%zd','%zd','%zd','%zd','%zd','%@','%zd','%@','%zd','%@','%zd','%zd','%zd','%zd','%zd','%zd','%zd','%zd')",model.artist_id,model.all_artist_ting_uid,model.all_artist_id,model.language,model.publishtime,model.album_no,model.pic_big,model.pic_small,model.country,model.area,model.lrclink,model.hot,model.file_duration,model.del_status,model.resource_type,model.copy_type,model.relate_status,model.all_rate,model.has_mv_mobile,model.toneid,model.song_id,model.title,model.ting_uid,model.author,model.album_id,model.album_title,model.is_first_publish,model.havehigh,model.charge,model.has_mv,model.learn,model.piao_id,model.listen_total,type];
    BOOL result = [db executeUpdate:str];
    [db close];
    return result;
}
//根据id查询便签信息
+(NSMutableArray *)selectDownMusicWithType:(NSInteger)type
{
    FMDatabase *db =[DBHelper openDatabase];
    //执行查询语句
    FMResultSet *result = [db executeQuery:@"SELECT * FROM DownList"];
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    
    while (result.next)
    {
        FMSongListModel *model = [[FMSongListModel alloc]init];
        //根据列名取得数据
        model.artist_id = [result intForColumn:@"artist_id"];
        model.all_artist_ting_uid = [result intForColumn:@"all_artist_ting_uid"];
        model.all_artist_id = [result intForColumn:@"all_artist_id"];
        model.language = [result stringForColumn:@"language"];
        model.publishtime = [result stringForColumn:@"publishtime"];
        model.album_no = [result intForColumn:@"album_no"];
        model.pic_big = [result stringForColumn:@"pic_big"];
        model.pic_small = [result stringForColumn:@"pic_small"];
        model.country = [result stringForColumn:@"country"];
        model.area = [result intForColumn:@"area"];
        model.lrclink = [result stringForColumn:@"lrclink"];
        model.hot = [result intForColumn:@"hot"];
        model.file_duration = [result intForColumn:@"file_duration"];
        model.del_status = [result intForColumn:@"del_status"];
        model.resource_type = [result intForColumn:@"resource_type"];
        model.copy_type = [result intForColumn:@"copy_type"];
        model.relate_status = [result intForColumn:@"relate_status"];
        model.all_rate = [result intForColumn:@"all_rate"];
        model.has_mv_mobile = [result intForColumn:@"has_mv_mobile"];
        model.toneid = [result intForColumn:@"toneid"];
        model.song_id = [result intForColumn:@"song_id"];
        model.title = [result stringForColumn:@"title"];
        model.ting_uid = [result intForColumn:@"ting_uid"];
        model.author = [result stringForColumn:@"author"];
        model.album_id = [result intForColumn:@"album_id"];
        model.album_title = [result stringForColumn:@"album_title"];
        model.is_first_publish = [result intForColumn:@"is_first_publish"];
        model.havehigh = [result intForColumn:@"havehigh"];
        model.charge = [result intForColumn:@"charge"];
        model.has_mv = [result intForColumn:@"has_mv"];
        model.learn = [result intForColumn:@"learn"];
        model.piao_id = [result intForColumn:@"piao_id"];
        model.listen_total = [result intForColumn:@"listen_total"];
        [arr addObject:model];
    }
    return arr;
}


+(NSMutableArray *)selectDownMusicWithName:(NSString *)name
{
    FMDatabase *db =[DBHelper openDatabase];
    NSString *sql =[NSString stringWithFormat:@"SELECT * FROM TypeList  where album_title like '%%%@%%'",name];
    //执行查询语句
    FMResultSet *result = [db executeQuery:sql];
    NSMutableArray *arr = [[NSMutableArray alloc]init];
    
    while (result.next)
    {
        FMSongListModel *model = [[FMSongListModel alloc]init];
        //根据列名取得数据
        model.artist_id = [result intForColumn:@"artist_id"];
        model.all_artist_ting_uid = [result intForColumn:@"all_artist_ting_uid"];
        model.all_artist_id = [result intForColumn:@"all_artist_id"];
        model.language = [result stringForColumn:@"language"];
        model.publishtime = [result stringForColumn:@"publishtime"];
        model.album_no = [result intForColumn:@"album_no"];
        model.pic_big = [result stringForColumn:@"pic_big"];
        model.pic_small = [result stringForColumn:@"pic_small"];
        model.country = [result stringForColumn:@"country"];
        model.area = [result intForColumn:@"area"];
        model.lrclink = [result stringForColumn:@"lrclink"];
        model.hot = [result intForColumn:@"hot"];
        model.file_duration = [result intForColumn:@"file_duration"];
        model.del_status = [result intForColumn:@"del_status"];
        model.resource_type = [result intForColumn:@"resource_type"];
        model.copy_type = [result intForColumn:@"copy_type"];
        model.relate_status = [result intForColumn:@"relate_status"];
        model.all_rate = [result intForColumn:@"all_rate"];
        model.has_mv_mobile = [result intForColumn:@"has_mv_mobile"];
        model.toneid = [result intForColumn:@"toneid"];
        model.song_id = [result intForColumn:@"song_id"];
        model.title = [result stringForColumn:@"title"];
        model.ting_uid = [result intForColumn:@"ting_uid"];
        model.author = [result stringForColumn:@"author"];
        model.album_id = [result intForColumn:@"album_id"];
        model.album_title = [result stringForColumn:@"album_title"];
        model.is_first_publish = [result intForColumn:@"is_first_publish"];
        model.havehigh = [result intForColumn:@"havehigh"];
        model.charge = [result intForColumn:@"charge"];
        model.has_mv = [result intForColumn:@"has_mv"];
        model.learn = [result intForColumn:@"learn"];
        model.piao_id = [result intForColumn:@"piao_id"];
        model.listen_total = [result intForColumn:@"listen_total"];
        [arr addObject:model];
    }
    return arr;
}
+(BOOL)insertDownMusicWith:(NSInteger )songid type:(NSInteger)type name:(NSString *)name
{
    
    FMDatabase *db = [DBHelper openDatabase];
    NSString *str = [NSString stringWithFormat:@"insert into 'DownList' (songid,type,name) values('%zd','%zd','%@')",songid,type,name];
    BOOL result = [db executeUpdate:str];
    [db close];
    return result;
}
+(BOOL)insertDownMusicWith:(FMSongListModel * )model type:(NSInteger)type
{
    
    FMDatabase *db = [DBHelper openDatabase];
    NSString *str = [NSString stringWithFormat:@"insert into 'DownList' values('%zd','%zd','%zd','%@','%@','%zd','%@','%@','%@','%zd','%@','%zd','%zd','%zd','%zd','%zd','%zd','%zd','%zd','%zd','%zd','%@','%zd','%@','%zd','%@','%zd','%zd','%zd','%zd','%zd','%zd','%zd','%zd')",model.artist_id,model.all_artist_ting_uid,model.all_artist_id,model.language,model.publishtime,model.album_no,model.pic_big,model.pic_small,model.country,model.area,model.lrclink,model.hot,model.file_duration,model.del_status,model.resource_type,model.copy_type,model.relate_status,model.all_rate,model.has_mv_mobile,model.toneid,model.song_id,model.title,model.ting_uid,model.author,model.album_id,model.album_title,model.is_first_publish,model.havehigh,model.charge,model.has_mv,model.learn,model.piao_id,model.listen_total,type];
    BOOL result = [db executeUpdate:str];
    [db close];
    return result;
}
////删除便签信息
//+(void)deleteWithNoteId:(NotePad *)notepad
//{
//    FMDatabase *db = [DBHelper openDatabase];
//    [db executeUpdate:@"DELETE FROM TABLE_NOTE WHERE note_id = ?",[NSString stringWithFormat:@"%d",notepad.noteId]];
//    [db close];
//}
@end