一 插件简介：
其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
#import
@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) {
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
#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
#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(@"符合条件的都被删除");
}
提供本代码下载地址（本实例只是本人练习，若有问题望指正）：源代码下载
IOS关于LKDBHelper实体对象映射插件运用
京公网安备 11010802041100号