数据库分析
1.是否创建数据库文件
//判断文件是否存在是否是目录，不是就创建一个目录
+ (NSString *)dbPathWithDirectoryName:(NSString *)directoryName
{
NSString *docsdir = [NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSFileManager *filemanage = [NSFileManager defaultManager];
if (directoryName == nil || directoryName.length == 0) {
docsdir = [docsdir stringByAppendingPathComponent:@"JKBD"];
} else {
docsdir = [docsdir stringByAppendingPathComponent:directoryName];
}
BOOL isDir;
BOOL exit =[filemanage fileExistsAtPath:docsdir isDirectory:&isDir];
if (!exit || !isDir) {
[filemanage createDirectoryAtPath:docsdir withIntermediateDirectories:YES attributes:nil error:nil];
}
NSString *dbpath = [docsdir stringByAppendingPathComponent:@"jkdb.sqlite"];
return dbpath;
}
// isDirectory是否是目录
//返回创建的目录
+ (NSString *)dbPath
{
return [self dbPathWithDirectoryName:nil];
}
//由于存储数据可能是大量的耗时操作，所以我们使用到串行的队列（保证线程安全）
//创建串行的数据库队列，实现多线程
- (FMDatabaseQueue *)dbQueue
{
if (_dbQueue == nil) {
_dbQueue = [[FMDatabaseQueue alloc] initWithPath:[self.class dbPath]];
}
return _dbQueue;
}
//为创建的数据库更改文件名
- (BOOL)changeDBWithDirectoryName:(NSString *)directoryName
{
//如果创建了队列就清空队列
if (_instance.dbQueue) {
_instance.dbQueue = nil;
}
//使用新的名字重新创建队列
_instance.dbQueue = [[FMDatabaseQueue alloc] initWithPath:[JKDBHelper dbPathWithDirectoryName:directoryName]];
int numClasses;
Class *classes = NULL;
numClasses = objc_getClassList(NULL,0);
if (numClasses >0 )
{
classes = (__unsafe_unretained Class *)malloc(sizeof(Class) * numClasses);
numClasses = objc_getClassList(classes, numClasses);
for (int i = 0; i < numClasses; i++) {
if (class_getSuperclass(classes[i]) == [JKDBModel class]){
id class = classes[i];
[class performSelector:@selector(createTable) withObject:nil];
}
}
free(classes);
}
return YES;
}
//JKDBHelper 类是用来创建单例，创建以及获取path ，创建FMDatabaseQueue，以及更改path以及FMDatabaseQueue
//下面是封装的模型类，保存的类继承自这个JKDBModel
/** SQLite五种数据类型 */
#define SQLTEXT @"TEXT"
#define SQLINTEGER @"INTEGER"
#define SQLREAL @"REAL"
#define SQLBLOB @"BLOB"
#define SQLNULL @"NULL"
#define PrimaryKey @"primary key"
#define primaryId @"pk" 主键
//
// JKBaseModel.m
// JKBaseModel
//
// Created by zx_04 on 15/6/27.
// Copyright (c) 2015年 joker. All rights reserved.
// github:https://github.com/Joker-King/JKDBModel
#import "JKDBModel.h"
#import "JKDBHelper.h"
#import <objc/runtime.h>
@implementation JKDBModel
#pragma mark - override method
//创建Table
+ (void)initialize
{
if (self != [JKDBModel self]) {
[self createTable];
}
}
//初始化的时候将类里面的键值对分别保存到存有键值的数组以及存有的值的数组里面
- (instancetype)init
{
self = [super init];
if (self) {
//将类里面的属性，除了transients里的，值保存到_columeNames，类型保存到_columeTypes
NSDictionary *dic = [self.class getAllProperties];
_columeNames = [[NSMutableArray alloc] initWithArray:[dic objectForKey:@"name"]];
_columeTypes = [[NSMutableArray alloc] initWithArray:[dic objectForKey:@"type"]];
}
return self;
}
#pragma mark - base method
/**
* // 获取该类的所有属性，返回的类型里面是个字典，包括类里面的值以及值的类型（TEXT、INTEGER、REAL、BLOB、NULL），已经取出除了数据库里面不需要的属性
*/
+ (NSDictionary *)getPropertys
{
NSMutableArray *proNames = [NSMutableArray array];
NSMutableArray *proTypes = [NSMutableArray array];
NSArray *theTransients = [[self class] transients];
unsigned int outCount, i;
objc_property_t *properties = class_copyPropertyList([self class], &outCount);
for (i = 0; i < outCount; i++) {
objc_property_t property = properties[i];
//获取属性名
NSString *propertyName = [NSString stringWithCString:property_getName(property) encoding:NSUTF8StringEncoding];
if ([theTransients containsObject:propertyName]) {
continue;
}
[proNames addObject:propertyName];
//获取属性类型等参数
NSString *propertyType = [NSString stringWithCString: property_getAttributes(property) encoding:NSUTF8StringEncoding];
/*
各种符号对应类型，部分类型在新版SDK中有所变化，如long 和long long
c char C unsigned char
i int I unsigned int
l long L unsigned long
s short S unsigned short
d double D unsigned double
f float F unsigned float
q long long Q unsigned long long
B BOOL
@ 对象类型 //指针 对象类型 如NSString 是@“NSString”
64位下long 和long long 都是Tq
SQLite 默认支持五种数据类型TEXT、INTEGER、REAL、BLOB、NULL
因为在项目中用的类型不多，故只考虑了少数类型
*/
if ([propertyType hasPrefix:@"T@\"NSString\""]) {
[proTypes addObject:SQLTEXT];
} else if ([propertyType hasPrefix:@"T@\"NSData\""]) {
[proTypes addObject:SQLBLOB];
} else if ([propertyType hasPrefix:@"Ti"]||[propertyType hasPrefix:@"TI"]||[propertyType hasPrefix:@"Ts"]||[propertyType hasPrefix:@"TS"]||[propertyType hasPrefix:@"TB"]||[propertyType hasPrefix:@"Tq"]||[propertyType hasPrefix:@"TQ"]) {
[proTypes addObject:SQLINTEGER];
} else {
[proTypes addObject:SQLREAL];
}
}
free(properties);
//将类里面除去theTransients里面的属性，都返回以@"name":属性值,@"type",属性在数据库里面对应的类型
return [NSDictionary dictionaryWithObjectsAndKeys:proNames,@"name",proTypes,@"type",nil];
}
/** 获取所有属性，包含主键pk */// 获取所有属性，相比上一个多了一个主键而已
+ (NSDictionary *)getAllProperties
{
NSDictionary *dict = [self.class getPropertys];
NSMutableArray *proNames = [NSMutableArray array];
NSMutableArray *proTypes = [NSMutableArray array];
[proNames addObject:primaryId];
[proTypes addObject:[NSString stringWithFormat:@"%@ %@",SQLINTEGER,PrimaryKey]];
[proNames addObjectsFromArray:[dict objectForKey:@"name"]];
[proTypes addObjectsFromArray:[dict objectForKey:@"type"]];
//将类里面除去theTransients里面的属性，都返回以@"name":属性值,@"type",属性在数据库里面对应的类型，相比上面多了主键
return [NSDictionary dictionaryWithObjectsAndKeys:proNames,@"name",proTypes,@"type",nil];
}
// 数据库中是否存在表 ，tableExists是FMDB里面的方法，根据表名查询表是否存在
+ (BOOL)isExistInTable
{
__block BOOL res = NO;
JKDBHelper *jkDB = [JKDBHelper shareInstance];
[jkDB.dbQueue inDatabase:^(FMDatabase *db) {
NSString *tableName = NSStringFromClass(self.class);
res = [db tableExists:tableName];
}];
return res;
}
// 获取列名
+ (NSArray *)getColumns
{
JKDBHelper *jkDB = [JKDBHelper shareInstance];
NSMutableArray *columns = [NSMutableArray array];
[jkDB.dbQueue inDatabase:^(FMDatabase *db) {
NSString *tableName = NSStringFromClass(self.class);
//调用的是FMDB里面的方法，获取表的概要
FMResultSet *resultSet = [db getTableSchema:tableName];
while ([resultSet next]) {
NSString *column = [resultSet stringForColumn:@"name"];
[columns addObject:column];
}
}];
return [columns copy];
}
/**
* 创建表
* 如果已经创建，返回YES
*/
+ (BOOL)createTable
{
__block BOOL res = YES;
JKDBHelper *jkDB = [JKDBHelper shareInstance];
[jkDB.dbQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
NSString *tableName = NSStringFromClass(self.class);
NSString *columeAndType = [self.class getColumeAndTypeString];
NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@);",tableName,columeAndType];
if (![db executeUpdate:sql]) {
res = NO;
*rollback = YES;
return;
};
NSMutableArray *columns = [NSMutableArray array];
FMResultSet *resultSet = [db getTableSchema:tableName];
while ([resultSet next]) {
NSString *column = [resultSet stringForColumn:@"name"];
[columns addObject:column];
}
NSString *str = columns;
NSDictionary *dict = [self.class getAllProperties];
NSArray *properties = [dict objectForKey:@"name"];
NSPredicate *filterPredicate = [NSPredicate predicateWithFormat:@"NOT (SELF IN %@)",columns];
//过滤数组，，如果有新添加的字段就更新数据库的字段
NSArray *resultArray = [properties filteredArrayUsingPredicate:filterPredicate];
for (NSString *column in resultArray) {
NSUInteger index = [properties indexOfObject:column];
NSString *proType = [[dict objectForKey:@"type"] objectAtIndex:index];
NSString *fieldSql = [NSString stringWithFormat:@"%@ %@",column,proType];
//语句用于在已有的表中添加、修改或删除列
NSString *sql = [NSString stringWithFormat:@"ALTER TABLE %@ ADD COLUMN %@ ",NSStringFromClass(self.class),fieldSql];
if (![db executeUpdate:sql]) {
res = NO;
*rollback = YES;
return ;
}
}
}];
return YES;
}
- (BOOL)saveOrUpdate
{
id primaryValue = [self valueForKey:primaryId];
if ([primaryValue intValue] <= 0) {
return [self save];
}
return [self update];
}
- (BOOL)saveOrUpdateByColumnName:(NSString*)columnName AndColumnValue:(NSString*)columnValue
{
id record = [self.class findFirstByCriteria:[NSString stringWithFormat:@"where %@ = %@",columnName,columnValue]];
if (record) {
id primaryValue = [record valueForKey:primaryId]; //取到了主键PK
if ([primaryValue intValue] <= 0) {
return [self save];
}else{
self.pk = [primaryValue intValue];
return [self update];
}
}else{
return [self save];
}
}
//保存所有的数据，以普通的方式
- (BOOL)save
{
NSString *tableName = NSStringFromClass(self.class);
NSMutableString *keyString = [NSMutableString string];
NSMutableString *valueString = [NSMutableString string];
NSMutableArray *insertValues = [NSMutableArray array];
for (int i = 0; i < self.columeNames.count; i++) {
NSString *proname = [self.columeNames objectAtIndex:i];
//主键不用保存，自动保存
if ([proname isEqualToString:primaryId]) {
continue;
}
[keyString appendFormat:@"%@,", proname];
[valueString appendString:@"?,"];
id value = [self valueForKey:proname];
if (!value) {
value = @"";
}
[insertValues addObject:value];
}
//keyString __NSCFString * @"duty,account,name,sex,portraitPath,imageData,moblie,descn,age,createTime,height" 0x0000608000266d00
[keyString deleteCharactersInRange:NSMakeRange(keyString.length - 1, 1)];
//valueString __NSCFString * @"?,?,?,?,?,?,?,?,?,?,?," 0x0000608000268480
[valueString deleteCharactersInRange:NSMakeRange(valueString.length - 1, 1)];
JKDBHelper *jkDB = [JKDBHelper shareInstance];
__block BOOL res = NO;
[jkDB.dbQueue inDatabase:^(FMDatabase *db) {
NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@(%@) VALUES (%@);", tableName, keyString, valueString];
res = [db executeUpdate:sql withArgumentsInArray:insertValues];
self.pk = res?[NSNumber numberWithLongLong:db.lastInsertRowId].intValue:0;
//很好的打印方式，可以记住
NSLog(res?@"插入成功":@"插入失败");
}];
return res;
}
// 批量保存用户对象 ，对象必须是继承JKDBModel的实例变量,以事物的方式
+ (BOOL)saveObjects:(NSArray *)array
{
//判断是否是JKBaseModel的子类,不是直接返回，不进行保存
for (JKDBModel *model in array) {
if (![model isKindOfClass:[JKDBModel class]]) {
return NO;
}
}
__block BOOL res = YES;
JKDBHelper *jkDB = [JKDBHelper shareInstance];
//通过单例里面的
// 如果要支持事务dbQueue 进行数据库的调用
[jkDB.dbQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
for (JKDBModel *model in array) {
NSString *tableName = NSStringFromClass(model.class);
NSMutableString *keyString = [NSMutableString string];
NSMutableString *valueString = [NSMutableString string];
NSMutableArray *insertValues = [NSMutableArray array];
for (int i = 0; i < model.columeNames.count; i++) {
NSString *proname = [model.columeNames objectAtIndex:i];
if ([proname isEqualToString:primaryId]) {
continue;
}
[keyString appendFormat:@"%@,", proname];
[valueString appendString:@"?,"];
id value = [model valueForKey:proname];
if (!value) {
value = @"";
}
[insertValues addObject:value];
}
[keyString deleteCharactersInRange:NSMakeRange(keyString.length - 1, 1)];
[valueString deleteCharactersInRange:NSMakeRange(valueString.length - 1, 1)];
NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@(%@) VALUES (%@);", tableName, keyString, valueString];
BOOL flag = [db executeUpdate:sql withArgumentsInArray:insertValues];
model.pk = flag?[NSNumber numberWithLongLong:db.lastInsertRowId].intValue:0;
NSLog(flag?@"插入成功":@"插入失败");
if (!flag) {
res = NO;
*rollback = YES;
return;
}
}
}];
return res;
}
// 更新单个对象,[对象 update]
- (BOOL)update
{
JKDBHelper *jkDB = [JKDBHelper shareInstance];
__block BOOL res = NO;
[jkDB.dbQueue inDatabase:^(FMDatabase *db) {
NSString *tableName = NSStringFromClass(self.class);
id primaryValue = [self valueForKey:primaryId];
if (!primaryValue || primaryValue <= 0) {
return ;
}
NSMutableString *keyString = [NSMutableString string];
NSMutableArray *updateValues = [NSMutableArray array];
for (int i = 0; i < self.columeNames.count; i++) {
//列名
NSString *proname = [self.columeNames objectAtIndex:i];
//如果列名是主键，不跟更新，自动调节
if ([proname isEqualToString:primaryId]) {
continue;
}
[keyString appendFormat:@" %@=?,", proname];
id value = [self valueForKey:proname];
if (!value) {
value = @"";
}
[updateValues addObject:value];
}
//删除最后那个逗号
[keyString deleteCharactersInRange:NSMakeRange(keyString.length - 1, 1)];
NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET %@ WHERE %@ = ?;", tableName, keyString, primaryId];
[updateValues addObject:primaryValue];
res = [db executeUpdate:sql withArgumentsInArray:updateValues];
NSLog(res?@"更新成功":@"更新失败");
}];
return res;
}
// 批量更新用户对象,往数据库保存的是多个模型
+ (BOOL)updateObjects:(NSArray *)array
{
for (JKDBModel *model in array) {
if (![model isKindOfClass:[JKDBModel class]]) {
return NO;
}
}
__block BOOL res = YES;
JKDBHelper *jkDB = [JKDBHelper shareInstance];
// 如果要支持事务
[jkDB.dbQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
for (JKDBModel *model in array) {
NSString *tableName = NSStringFromClass(model.class);
id primaryValue = [model valueForKey:primaryId];
if (!primaryValue || primaryValue <= 0) {
res = NO;
*rollback = YES;
return;
}
NSMutableString *keyString = [NSMutableString string];
NSMutableArray *updateValues = [NSMutableArray array];
for (int i = 0; i < model.columeNames.count; i++) {
NSString *proname = [model.columeNames objectAtIndex:i];
if ([proname isEqualToString:primaryId]) {
continue;
}
[keyString appendFormat:@" %@=?,", proname];
id value = [model valueForKey:proname];
if (!value) {
value = @"";
}
[updateValues addObject:value];
}
//删除最后那个逗号
[keyString deleteCharactersInRange:NSMakeRange(keyString.length - 1, 1)];
NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET %@ WHERE %@=?;", tableName, keyString, primaryId];
[updateValues addObject:primaryValue];
BOOL flag = [db executeUpdate:sql withArgumentsInArray:updateValues];
NSLog(flag?@"更新成功":@"更新失败");
if (!flag) {
res = NO;
*rollback = YES;
return;
}
}
}];
return res;
}
// 删除单个对象 根据单个对象的primaryId删除
- (BOOL)deleteObject
{
JKDBHelper *jkDB = [JKDBHelper shareInstance];
__block BOOL res = NO;
[jkDB.dbQueue inDatabase:^(FMDatabase *db) {
NSString *tableName = NSStringFromClass(self.class);
id primaryValue = [self valueForKey:primaryId];
if (!primaryValue || primaryValue <= 0) {
return ;
}
NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?",tableName,primaryId];
res = [db executeUpdate:sql withArgumentsInArray:@[primaryValue]];
NSLog(res?@"删除成功":@"删除失败");
}];
return res;
}
/** 批量删除用户对象，根据多个对象的primaryId删除 */
+ (BOOL)deleteObjects:(NSArray *)array
{
for (JKDBModel *model in array) {
if (![model isKindOfClass:[JKDBModel class]]) {
return NO;
}
}
__block BOOL res = YES;
JKDBHelper *jkDB = [JKDBHelper shareInstance];
// 如果要支持事务
[jkDB.dbQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {
for (JKDBModel *model in array) {
NSString *tableName = NSStringFromClass(model.class);
id primaryValue = [model valueForKey:primaryId];
if (!primaryValue || primaryValue <= 0) {
return ;
}
NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?",tableName,primaryId];
BOOL flag = [db executeUpdate:sql withArgumentsInArray:@[primaryValue]];
NSLog(flag?@"删除成功":@"删除失败");
if (!flag) {
res = NO;
*rollback = YES;
return;
}
}
}];
return res;
}
/** 通过条件删除数据 */
+ (BOOL)deleteObjectsByCriteria:(NSString *)criteria
{
JKDBHelper *jkDB = [JKDBHelper shareInstance];
__block BOOL res = NO;
[jkDB.dbQueue inDatabase:^(FMDatabase *db) {
NSString *tableName = NSStringFromClass(self.class);
NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ %@ ",tableName,criteria];
res = [db executeUpdate:sql];
NSLog(res?@"删除成功":@"删除失败");
}];
return res;
}
/** 通过条件删除 (多参数）--2 */
+ (BOOL)deleteObjectsWithFormat:(NSString *)format, ...
{
va_list ap;
va_start(ap, format);
NSString *criteria = [[NSString alloc] initWithFormat:format locale:[NSLocale currentLocale] arguments:ap];
va_end(ap);
return [self deleteObjectsByCriteria:criteria];
}
/** 清空表 */
+ (BOOL)clearTable
{
JKDBHelper *jkDB = [JKDBHelper shareInstance];
__block BOOL res = NO;
[jkDB.dbQueue inDatabase:^(FMDatabase *db) {
NSString *tableName = NSStringFromClass(self.class);
NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@",tableName];
res = [db executeUpdate:sql];
NSLog(res?@"清空成功":@"清空失败");
}];
return res;
}
/** 查询全部数据 */
+ (NSArray *)findAll
{
NSLog(@"jkdb---%s",__func__);
JKDBHelper *jkDB = [JKDBHelper shareInstance];
NSMutableArray *users = [NSMutableArray array];
[jkDB.dbQueue inDatabase:^(FMDatabase *db) {
NSString *tableName = NSStringFromClass(self.class);
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@",tableName];
FMResultSet *resultSet = [db executeQuery:sql];
while ([resultSet next]) {
JKDBModel *model = [[self.class alloc] init];
for (int i=0; i< model.columeNames.count; i++) {
NSString *columeName = [model.columeNames objectAtIndex:i];
NSString *columeType = [model.columeTypes objectAtIndex:i];
if ([columeType isEqualToString:SQLTEXT]) {
[model setValue:[resultSet stringForColumn:columeName] forKey:columeName];
} else if ([columeType isEqualToString:SQLBLOB]) {
[model setValue:[resultSet dataForColumn:columeName] forKey:columeName];
} else {
[model setValue:[NSNumber numberWithLongLong:[resultSet longLongIntForColumn:columeName]] forKey:columeName];
}
}
[users addObject:model];
FMDBRelease(model);
}
}];
return users;
}
//根据条件查找一个模型(多参数）
+ (instancetype)findFirstWithFormat:(NSString *)format, ...
{
va_list ap;
va_start(ap, format);
NSString *criteria = [[NSString alloc] initWithFormat:format locale:[NSLocale currentLocale] arguments:ap];
va_end(ap);
return [self findFirstByCriteria:criteria];
}
/** 查找某条数据，只显示第一个 */
+ (instancetype)findFirstByCriteria:(NSString *)criteria
{
NSArray *results = [self.class findByCriteria:criteria];
if (results.count < 1) {
return nil;
}
return [results firstObject];
}
//通过keyId进行查找数据
+ (instancetype)findByPK:(int)inPk
{
NSString *condition = [NSString stringWithFormat:@"WHERE %@=%d",primaryId,inPk];
return [self findFirstByCriteria:condition];
}
//根据条件查找多个模型(多参数）
+ (NSArray *)findWithFormat:(NSString *)format, ...
{
va_list ap;
va_start(ap, format);
NSString *criteria = [[NSString alloc] initWithFormat:format locale:[NSLocale currentLocale] arguments:ap];
va_end(ap);
return [self findByCriteria:criteria];
}
/** 通过条件查找数据 */
+ (NSArray *)findByCriteria:(NSString *)criteria
{
JKDBHelper *jkDB = [JKDBHelper shareInstance];
NSMutableArray *users = [NSMutableArray array];
[jkDB.dbQueue inDatabase:^(FMDatabase *db) {
NSString *tableName = NSStringFromClass(self.class);
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ %@",tableName,criteria];
FMResultSet *resultSet = [db executeQuery:sql];
while ([resultSet next]) {
JKDBModel *model = [[self.class alloc] init];
for (int i=0; i< model.columeNames.count; i++) {
NSString *columeName = [model.columeNames objectAtIndex:i];
NSString *columeType = [model.columeTypes objectAtIndex:i];
if ([columeType isEqualToString:SQLTEXT]) {
[model setValue:[resultSet stringForColumn:columeName] forKey:columeName];
} else if ([columeType isEqualToString:SQLBLOB]) {
[model setValue:[resultSet dataForColumn:columeName] forKey:columeName];
} else {
[model setValue:[NSNumber numberWithLongLong:[resultSet longLongIntForColumn:columeName]] forKey:columeName];
}
}
[users addObject:model];
FMDBRelease(model);
}
}];
return users;
}
#pragma mark - util method
//获取到的是@"pk INTEGER primary key,duty TEXT,account TEXT,name TEXT,sex TEXT,portraitPath TEXT,imageData BLOB,moblie TEXT,descn TEXT,age INTEGER,createTime INTEGER,height INTEGER"直接用于创建数据库
//#pragma mark -获取类里面所有属性组成的字符串，以逗号分隔，用来写table的出事化语句
+ (NSString *)getColumeAndTypeString
{
NSMutableString* pars = [NSMutableString string];
NSDictionary *dict = [self.class getAllProperties];
NSMutableArray *proNames = [dict objectForKey:@"name"];
NSMutableArray *proTypes = [dict objectForKey:@"type"];
for (int i=0; i< proNames.count; i++) {
[pars appendFormat:@"%@ %@",[proNames objectAtIndex:i],[proTypes objectAtIndex:i]];
if(i+1 != proNames.count)
{
[pars appendString:@","];
}
}
return pars;
}
//重写描述方法，用来打印类的属性，并以键值对额形式表示
- (NSString *)description
{
NSString *result = @"";
NSDictionary *dict = [self.class getAllProperties];
NSMutableArray *proNames = [dict objectForKey:@"name"];
for (int i = 0; i < proNames.count; i++) {
NSString *proName = [proNames objectAtIndex:i];
id proValue = [self valueForKey:proName];
result = [result stringByAppendingFormat:@"%@:%@\n",proName,proValue];
}
return result;
}
#pragma mark - 继承的类里面如果不需要的属性放在数据库里面就将属性写在这个方法里面
/** 如果子类中有一些property不需要创建数据库字段，那么这个方法必须在子类中重写
*/
+ (NSArray *)transients
{
return [NSArray array];
}
*******************************以下使用**********************
1.插入，支持多条线程
//可以多个线程一起进行插入操作
- (IBAction)insertData:(id)sender {
UIImage *image = [UIImage imageNamed:@"portrait"];
NSData *imageData = UIImagePNGRepresentation(image);
for (int i = 0; i < 10; i++) {
User *user = [[User alloc] init];
user.name = [NSString stringWithFormat:@"麻子%d",i];
user.sex = @"男";
user.age = 10+i;
user.createTime = 1368082020;
user.imageData = imageData;
dispatch_async(dispatch_get_global_queue(0, 0), ^{
[user save];
});
}
}
/** 子线程一:插入多条用户数据 */
- (IBAction)insertData2:(id)sender {
dispatch_queue_t q1 = dispatch_queue_create("queue1", NULL);
dispatch_async(q1, ^{
for (int i = 0; i < 5; ++i) {
User *user = [[User alloc] init];
user.name = @"赵五";
user.sex = @"女";
user.age = i+5;
[user save];
}
});
}
- (IBAction)insertData3:(id)sender {
for (int i = 0; i < 1000; ++i) {
User *user = [[User alloc] init];
user.name = @"张三";
user.sex = @"男";
user.age = i+5;
[user save];
}
}
/** 子线程三：事务插入数据 */
- (IBAction)insertData4:(id)sender {
dispatch_async(dispatch_get_global_queue(0, 0), ^{
NSMutableArray *array = [NSMutableArray array];
for (int i = 0; i < 500; i++) {
User *user = [[User alloc] init];
user.name = [NSString stringWithFormat:@"李四%d",i];
user.age = 10+i;
user.sex = @"女";
[array addObject:user];
}
[User saveObjects:array];
});
}
2.删除支持多线程，条件的（自己写where）和全部删除，删除单个对象[user deleteObject]，事务的方式删除多个对象
[User deleteObjects:array];
#pragma mark - 删除数据
/** 通过条件删除数据 */
- (IBAction)deleteData:(id)sender {
// [User deleteObjectsByCriteria:@" WHERE pk < 10"];
[User deleteObjectsWithFormat:@"Where %@ < %d",@"pk",10];
}
/** 创建多个线程删除数据 */
- (IBAction)deleteData2:(id)sender {
for (int i = 0; i < 5; i++) {
User *user = [[User alloc] init];
user.pk = 1+i;
dispatch_async(dispatch_get_global_queue(0, 0), ^{
[user deleteObject];
});
}
}
/** 子线程用事务删除数据 */
- (IBAction)deleteData3:(id)sender {
dispatch_async(dispatch_get_global_queue(0, 0), ^{
NSMutableArray *array = [NSMutableArray array];
for (int i = 0; i < 500; i++) {
User *user = [[User alloc] init];
user.pk = 501+i;
[array addObject:user];
}
[User deleteObjects:array];
});
}
3.更新，支持多线程，单个更新 ，利用事物多个更新
#pragma mark - 修改数据
/** 创建多个线程更新数据 */
- (IBAction)updateData1:(id)sender {
UIImage *image = [UIImage imageNamed:@"eay"];
NSData *imageData = UIImagePNGRepresentation(image);
for (int i = 0; i < 5; i++) {
User *user = [[User alloc] init];
user.name = [NSString stringWithFormat:@"更新%d",i];
user.age = 120+i;
user.pk = 5+i;
user.imageData = imageData;
dispatch_async(dispatch_get_global_queue(0, 0), ^{
[user update];
});
}
}
/**单个子线程批量更新数据，利用事务 */
- (IBAction)updateData:(id)sender {
dispatch_queue_t q3 = dispatch_queue_create("queue3", NULL);
dispatch_async(q3, ^{
NSMutableArray *array = [NSMutableArray array];
for (int i = 0; i < 500; i++) {
User *user = [[User alloc] init];
user.name = [NSString stringWithFormat:@"啊我哦%d",i];
user.age = 88+i;
user.pk = 10+i;
[array addObject:user];
}
[User updateObjects:array];
});
}
4.查询，支持多线程，支持条件查询[User findFirstByCriteria:@" WHERE age = 20 "]
，以及查询所有 [User findAll]
分页查询
[User findByCriteria:[NSString stringWithFormat:@" WHERE pk > %d limit 10",pk]]返回数组
多条件查询‘[User findFirstWithFormat:@" WHERE %@ = %d ",@"age",10]返回模型（数组里的第一个元素），
或者返回数组[User findWithFormat:@" WHERE %@ < %d",@"age",20]
/** 查询单条记录 */
- (IBAction)queryData1:(id)sender {
dispatch_async(dispatch_get_global_queue(0, 0), ^{
NSLog(@"第一条:%@",[User findFirstByCriteria:@" WHERE age = 20 "]);
});
}
/** 条件查询多条记录 */
- (IBAction)queryData2:(id)sender {
dispatch_async(dispatch_get_global_queue(0, 0), ^{
NSLog(@"小于20岁:%@",[User findByCriteria:@" WHERE age < 20 "]);
});
}
/** 查询全部数据 */
- (IBAction)queryData3:(id)sender {
dispatch_async(dispatch_get_global_queue(0, 0), ^{
NSLog(@"全部:%@",[User findAll]);
});
}
/** 分页查询数据 */
- (IBAction)queryData:(id)sender {
static int pk = 5;
NSArray *array = [User findByCriteria:[NSString stringWithFormat:@" WHERE pk > %d limit 10",pk]];
pk = ((User *)[array lastObject]).pk;
NSLog(@"array:%@",array);
}
此外，也可以不使用queue
#pragma mark - 不使用FMDatabaseQueue，直接使用FMDatabase
- (NSString *)getDBPath
{
NSString* docsdir = [NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSFileManager *filemanage = [NSFileManager defaultManager];
docsdir = [docsdir stringByAppendingPathComponent:@"FMDBDemo"];
BOOL isDir;
BOOL exit =[filemanage fileExistsAtPath:docsdir isDirectory:&isDir];
if (!exit || !isDir) {
[filemanage createDirectoryAtPath:docsdir withIntermediateDirectories:YES attributes:nil error:nil];
}
NSString *dbpath = [docsdir stringByAppendingPathComponent:@"myDB.sqlite"];
return dbpath;
}
/** 用db创建User表*/
- (IBAction)dbCreateUserTable:(id)sender {
[User createUserTableByDB];
}
/** 用db插入User数据*/
- (IBAction)dbInsertData:(id)sender {
//多线程插入数据
for (int i = 0; i < 5; i++) {
dispatch_async(dispatch_get_global_queue(0, 0), ^{
User *user = [[User alloc] init];
user.name = @"dbName一";
user.ID_no = [NSString stringWithFormat:@"%d",55555+i];
user.age = 555+i;
[user saveByDB];
});
}
//利用事务插入数据
dispatch_async(dispatch_get_global_queue(0, 0), ^{
NSMutableArray *array = [NSMutableArray array];
for (int i = 0; i < 5; i++) {
User *user = [[User alloc] init];
user.name = @"db事务";
user.ID_no = [NSString stringWithFormat:@"%d",66666+i];
user.age = 66+i;
[array addObject:user];
[user release];
}
[User saveObjectsByDB:array];
});
}
/** 用db删除User数据*/
- (IBAction)dbDeleteData:(id)sender {
//多线程插入数据
for (int i = 0; i < 5; i++) {
dispatch_async(dispatch_get_global_queue(0, 0), ^{
User *user = [[User alloc] init];
user.ID = i+5;
[user deleteObjectByDB];
});
}
//利用事务删除数据
dispatch_async(dispatch_get_global_queue(0, 0), ^{
NSMutableArray *array = [NSMutableArray array];
for (int i = 0; i < 5; i++) {
User *user = [[User alloc] init];
user.ID = i+10;
[array addObject:user];
[user release];
}
[User deleteObjectsByDB:array];
});
}
/** 用db更新User数据*/
- (IBAction)dbUpdateData:(id)sender {
//多线程插入数据
for (int i = 0; i < 5; i++) {
dispatch_async(dispatch_get_global_queue(0, 0), ^{
User *user = [[User alloc] init];
user.name = @"db更新";
user.ID_no = [NSString stringWithFormat:@"%d",55+i];
user.age = 55555+i;
user.ID = i +1;
[user saveByDB];
});
}
//利用事务更新数据
dispatch_async(dispatch_get_global_queue(0, 0), ^{
NSMutableArray *array = [NSMutableArray array];
for (int i = 0; i < 5; i++) {
User *user = [[User alloc] init];
user.name = @"db更新";
user.ID_no = [NSString stringWithFormat:@"%d",55+i];
user.age = 55555+i;
user.ID = i +10;
[array addObject:user];
[user release];
}
[User updateObjectsByDB:array];
});
}
/** 用db查询User数据*/
- (IBAction)dbQueryData:(id)sender {
//查询全部
dispatch_async(dispatch_get_global_queue(0, 0), ^{
[User findAllByDB];
});
//条件查询
dispatch_async(dispatch_get_global_queue(0, 0), ^{
[User findBySqlByDB:@" WHERE age = 20 "];
});
//查询单个
dispatch_async(dispatch_get_global_queue(0, 0), ^{
[User findFirstBySqlByDB:@" WHERE age = 20 "];
});
}
注意：使用框架的过程中记得引入libsqlite3.dylib，因为会有多线程操作，所以用单例和FMDatabaseQueue来执行，要避免queue里调用queue执行操作。
子类在实例化时，直接创建对应的数据库表，这里用dispatch_once实现，只需要创建一次。子类中覆写createTable方法
感谢大神的框架:地址：https://github.com/Joker-King/JKDBModel
