IOS关于LKDBHelper实体对象映射插件运用

浏览数：37 / 时间：2015年06月11日

一插件简介：

其github地址:https://github.com/li6185377/LKDBHelper-SQLite-ORM

全面支持 NSArray,NSDictionary, ModelClass, NSNumber, NSString, NSDate, NSData, UIColor, UIImage, CGRect, CGPoint, CGSize, NSRange, int,char,float, double, long.. 等属性的自动化操作(插入和查询)

二实例内容：

采用pods进行加载LKDBHelper插件,若有下载源代码调试时记得更新一下(平常项目中记得对libsqlite3.dylib进行引用);

技术分享
本实例创建一个父实体BaseBean,后面其它实体都进行继承;

1：父实体的代码内容

BaseBean.h内容:

#import <Foundation/Foundation.h>
#import <LKDBHelper/LKDBHelper.h>

@interface BaseBean : NSObject

@end


//这个NSOBJECT的扩展类 可以查看详细的建表sql语句
@interface NSObject(PrintSQL)
+(NSString*)getCreateTableSQL;
@end

*这边可以放一些其它实体都公有的属性，及lkdbhelper数据库的地址；其中PrintSQL是对NSObject的扩展，可以查看创建表的sql语句；

BaseBean.m内容:

#import "BaseBean.h"

@implementation BaseBean


+(LKDBHelper *)getUsingLKDBHelper
{
    static LKDBHelper* db;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        NSString *sqlitePath = [BaseBean downloadPath];
        NSString* dbpath = [sqlitePath stringByAppendingPathComponent:[NSString stringWithFormat:@"lkdbhelperTest.db"]];
        db = [[LKDBHelper alloc]initWithDBPath:dbpath];
    });
    return db;
}

/**
 *  @author wujunyang, 15-05-21 16:05:44
 *
 *  @brief  路径
 *  @return <#return value description#>
 */
+ (NSString *)downloadPath{
        NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
        NSString *downloadPath = [documentPath stringByAppendingPathComponent:@"wjy"];
        NSLog(@"%@",downloadPath);
        return downloadPath;
}
@end


@implementation NSObject(PrintSQL)

+(NSString *)getCreateTableSQL
{
    LKModelInfos* infos = [self getModelInfos];
    NSString* primaryKey = [self getPrimaryKey];
    NSMutableString* table_pars = [NSMutableString string];
    for (int i=0; i<infos.count; i++) {
        
        if(i > 0)
            [table_pars appendString:@","];
        
        LKDBProperty* property =  [infos objectWithIndex:i];
        [self columnAttributeWithProperty:property];
        
        [table_pars appendFormat:@"%@ %@",property.sqlColumnName,property.sqlColumnType];
        
        if([property.sqlColumnType isEqualToString:LKSQL_Type_Text])
        {
            if(property.length>0)
            {
                [table_pars appendFormat:@"(%ld)",(long)property.length];
            }
        }
        if(property.isNotNull)
        {
            [table_pars appendFormat:@" %@",LKSQL_Attribute_NotNull];
        }
        if(property.isUnique)
        {
            [table_pars appendFormat:@" %@",LKSQL_Attribute_Unique];
        }
        if(property.checkValue)
        {
            [table_pars appendFormat:@" %@(%@)",LKSQL_Attribute_Check,property.checkValue];
        }
        if(property.defaultValue)
        {
            [table_pars appendFormat:@" %@ %@",LKSQL_Attribute_Default,property.defaultValue];
        }
        if(primaryKey && [property.sqlColumnName isEqualToString:primaryKey])
        {
            [table_pars appendFormat:@" %@",LKSQL_Attribute_PrimaryKey];
        }
    }
    NSString* createTableSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@)",[self getTableName],table_pars];
    return createTableSQL;
}

@end

2：子实体CarBean的内容，其是另外一个实体UserBean的一个外键

CarBean.h内容：

#import <Foundation/Foundation.h>
#import "BaseBean.h"

@interface CarBean : BaseBean

@property(assign,nonatomic)int carID;
@property(strong,nonatomic)NSString *carNum;
@property(strong,nonatomic)NSString *address;
@property(assign,nonatomic)float carWidth;
@end

*注意继承BaseBean

CarBean.m内容：

#import "CarBean.h"

@implementation CarBean


+(void)initialize
{
    //单个要不显示时
    [self removePropertyWithColumnName:@"address"];
    //多列要不显示时
    //[self removePropertyWithColumnNameArray:];
    
    //修改列对应到表时 重命名新列名
    [self setTableColumnName:@"MyCarWidth" bindingPropertyName:@"carWidth"];
}

/**
 *  @author wjy, 15-01-27 18:01:53
 *
 *  @brief  是否将父实体类的属性也映射到sqlite库表
 *  @return BOOL
 */
+(BOOL) isContainParent{
    return YES;
}
/**
 *  @author wjy, 15-01-26 14:01:01
 *
 *  @brief  设定表名
 *  @return 返回表名
 */
+(NSString *)getTableName
{
    return @"carbean";
}
/**
 *  @author wjy, 15-01-26 14:01:22
 *
 *  @brief  设定表的单个主键
 *  @return 返回主键表
 */
+(NSString *)getPrimaryKey
{
    return @"carID";
}

/////复合主键  这个优先级最高
//+(NSArray *)getPrimaryKeyUnionArray
//{
//    return @[@"carID",@"carNum"];
//}

@end

*主要注意关于可以把一些属性过滤掉，就不会创建到表中，也可以对列名进行重定义，其它几个代码有详细说明

3：子实体UserBean，同样继承BaseBean

UserBean.h内容：

#import <Foundation/Foundation.h>
#import "BaseBean.h"
#import "CarBean.h"

@interface UserBean : BaseBean

@property(assign,nonatomic)int ID;
@property(strong,nonatomic)NSString *userName;
@property(strong,nonatomic)NSString *password;
@property(assign,nonatomic)int age;

@property(strong,nonatomic)CarBean *myCar;
@end


UserBean.m内容：

#import "UserBean.h"

@implementation UserBean


+(void)initialize
{
    [self removePropertyWithColumnName:@"error"];
}

/**
 *  @author wjy, 15-01-27 18:01:53
 *
 *  @brief  是否将父实体类的属性也映射到sqlite库表
 *  @return BOOL
 */
+(BOOL) isContainParent{
    return YES;
}
/**
 *  @author wjy, 15-01-26 14:01:01
 *
 *  @brief  设定表名
 *  @return 返回表名
 */
+(NSString *)getTableName
{
    return @"userBean";
}
/**
 *  @author wjy, 15-01-26 14:01:22
 *
 *  @brief  设定表的单个主键
 *  @return 返回主键表
 */
+(NSString *)getPrimaryKey
{
    return @"ID";
}
@end

4：开始进行针对数据库进行操作

4.1 功能包括删除所有表，清理指定表的数据，创建表插入数据，其中插入数据会自动判断表是否存在，若不存在则先创建表再插入，特别说明就是当一个列被定义为int且是主键时，它要是没有被赋值就会自动增长

    LKDBHelper* globalHelper = [BaseBean getUsingLKDBHelper];
    
    //删除所有的表
    [globalHelper dropAllTable];
    
    //清理所有数据
    [LKDBHelper clearTableData:[UserBean class]];
    
    UserBean *user=[[UserBean alloc] init];
    //user.ID=1000;  //特别说明 如果是主键 没给它赋值它就会自动增长
    user.userName=@"WUJY";
    user.password=@"123456";
    user.age=10;
    
    CarBean *car=[[CarBean alloc]init];
    car.carNum=@"D88888";
    car.address=@"厦门软件园";
    car.carWidth=12.5;
    
    user.myCar=car;
    
    //插入数据 如果表不存在 它会自动创建再插入 实体实例化LKDBHelper 若是继承记得引用 否则会没有效果
    [user saveToDB];
    
    //另外一种插入
    user.age=29;
    [globalHelper insertToDB:user];

4.2 关于事务的操作

    //事物  transaction 这边故意让它插入失败
    [globalHelper executeForTransaction:^BOOL(LKDBHelper *helper) {
        
        user.ID = 10;
        user.userName=@"wujy10";
        BOOL success = [helper insertToDB:user];
        
        user.ID = 9;
        user.userName=@"wujy09";
        success = [helper insertToDB:user];
        
        //重复主键
        user.ID = 10;
        BOOL insertSucceed = [helper insertWhenNotExists:user];
        
        //insert fail
        if(insertSucceed == NO)
        {
            ///rollback
            return NO;
        }
        else
        {
            ///commit
            return YES;
        }
    }];

4.3 关于异步操作，对于其它操作也同样支持异步操作，采用callback方式进行回调

    user.userName=@"异步";
    [globalHelper insertToDB:user callback:^(BOOL result) {
        NSLog(@"异步插入是否成功：%@",result>0?@"是":@"否");
    }];

    //异步查询
    [globalHelper search:[UserBean class] where:nil orderBy:nil offset:0 count:100 callback:^(NSMutableArray *array) {
        for (id obj in array) {
            NSLog(@"异步：%@",[obj printAllPropertys]);
        }
    }];

4.4 关于查询功能的几种操作，特别注意条件的写法，其它在代码中有相应的注解；

    NSMutableArray* searchResultArray=nil;
    
    //同步搜索 执行sql语句 把结果再变成对象
    searchResultArray=[globalHelper searchWithSQL:@"select * from @t" toClass:[UserBean class]];
    for (id obj in searchResultArray) {
        NSLog(@"sql语句：%@",[obj printAllPropertys]);
    }
    
    //使用对象对进查询操作  offset是跳过多少行 count是查询多少条
    searchResultArray=[UserBean searchWithWhere:nil orderBy:nil offset:0 count:100];
    for (id obj in searchResultArray) {
        NSLog(@"实体：%@",[obj printAllPropertys]);
    }
    
    //查询一列时的操作 count为0时则查所有列
    NSArray* nameArray = [UserBean searchColumn:@"userName" where:nil orderBy:nil offset:0 count:0];
    NSLog(@"%@",[nameArray componentsJoinedByString:@","]);
    
    //查询多列时的操作
    NSArray* twoColumn=[UserBean searchColumn:@"ID,userName" where:nil orderBy:nil offset:0 count:0];
    for (UserBean *obj in twoColumn) {
        NSLog(@"%d-%@",obj.ID,obj.userName);
    }

    //组合查询四种 and or like in
    searchResultArray=[UserBean searchWithWhere:[NSString stringWithFormat:@"userName like ‘%%JY%%‘"] orderBy:nil offset:0 count:0];
    NSLog(@"LIKE :%lu",searchResultArray.count);
    
    searchResultArray=[UserBean searchWithWhere:[NSString stringWithFormat:@"age=29 and userName=‘WUJY‘"] orderBy:nil offset:0 count:0];
    NSLog(@"AND :%lu",searchResultArray.count);
    
    searchResultArray=[globalHelper search:[UserBean class] where:@{@"age":@29,@"userName":@"WUJY"} orderBy:nil offset:0 count:0];
    NSLog(@"AND %lu",searchResultArray.count);
    
    searchResultArray=[UserBean searchWithWhere:[NSString stringWithFormat:@"age=29 or userName=‘WUJY‘"] orderBy:nil offset:0 count:0];
    NSLog(@"OR %lu",searchResultArray.count);
    
    searchResultArray=[UserBean searchWithWhere:[NSString stringWithFormat:@"age in (10,29)"] orderBy:nil offset:0 count:0];
    NSLog(@"in %lu",searchResultArray.count);
    
    searchResultArray=[globalHelper search:[UserBean class] where:@{@"age":@[@10,@29]} orderBy:nil offset:0 count:0];
    NSLog(@"in %lu",searchResultArray.count);
    
    //查询符合条件的条数
    NSInteger rowCount=[UserBean rowCountWithWhere:@"age=29"];
    NSLog(@"rowCount %ld",rowCount);

*注意：

单条件:  
@"rowid = 1"  或者  @{@"rowid":@1}

多条件：
@“rowid = 1 and sex = 0"  或者    @{@"rowid":@1,@"sex":@0}
如果是or类型的条件，则只能用字符串的形式：@"rowid = 1 or sex = 0"

in条件：
@"rowid in (1,2,3)"   或者     @{@"rowid":@[@1,@2,@3]}
多条件带in：@"rowid in (1,2,3) and sex=0 "   或者    @{@"rowid":@[@1,@2,@3],@"sex":@0}

时间也只能用字符串：
@"date >= ‘2013-04-01 00:00:00‘"

like也只能用字符串：
@"userName like ‘%%JY%%‘"

4.5 更新操作

    //带条件更新
    user.userName=@"踏浪帅";
    BOOL isUpDate=[globalHelper updateToDB:user where:@{@"ID":@10}];
    NSLog(@"是否更新成功:%@",isUpDate>0?@"是":@"否");
    
    //根据条件获得后进行更新
    searchResultArray=[UserBean searchWithWhere:[NSString stringWithFormat:@"userName=‘%@‘",@"WUJY"] orderBy:nil offset:0 count:100];
    user=[searchResultArray firstObject];
    user.password=@"aa123456";
    BOOL moreUpdate=[globalHelper updateToDB:user where:nil];
    NSLog(@"根据条件获得后进行更新:%@",moreUpdate>0?@"是":@"否");
    
    //更新 访问表名 更新内容跟条件进行更新
    BOOL updateTab=[globalHelper updateToDBWithTableName:@"userBean" set:@"password=‘aa123456‘" where:@"age=10"];
    NSLog(@"访问表名更新内容跟条件进行更新:%@",updateTab>0?@"是":@"否");
    
    //根据 实类进行更新
    BOOL updateClass=[globalHelper updateToDB:[UserBean class] set:@"password=‘cnblogs‘" where:@"userName=‘WUJY‘"];
    NSLog(@"根据实类进行更新:%@",updateClass>0?@"是":@"否");

4.6 删除操作

    //删除功能
    user=[globalHelper searchSingle:[UserBean class] where:@{@"age":@10} orderBy:nil];
    BOOL ishas=[globalHelper isExistsModel:user];
    if (ishas) {
        [globalHelper deleteToDB:user];
    }
    
    //删除多条
    BOOL isDeleteMore=[globalHelper deleteWithClass:[UserBean class] where:@"age=29"];
    if (isDeleteMore) {
        NSLog(@"符合条件的都被删除");
    }