(principle) based on SQLite3 lightweight package, a line of code to implement additions and deletions to search

Recently wrote the project useful to the database, wrote a lot of egg-ache SQL statement, each time is a few lines of code, and each time is repeated without a bit of technical content of code, although there are many based on SQLite package, but still feel enough to use the object-oriented!
In order to no longer write duplicate code, took a few days, based on SQLite3 simple encapsulation, implemented a line of code to solve additions and deletions and other commonly used functions! Not too advanced knowledge, mainly using the runtime and KVC:

First we create a person class that everyone is familiar with, and declare two properties, which will be analyzed in the following class

@interface Person : NSObject@property(nonatomicNSString *name;@property(nonatomicassignNSInteger age;@end

Create a table

believe that the following sentence statement is familiar to everyone, do not introduce

if not exists Person (id integer primary key autoincrement,name text,age integer)

However, in the development we are all based on model development, basically is a model corresponding to the database of a table, then the properties of each model is different, then how can we generate similar statements? I think of runtime, get a list of properties for a class through runtime, so here's the method:

/// Get all properties of the current class+ (Nsarray *) Getattributelistwithclass: (ID) className {//record number of attributes    unsigned intCount objc_property_t *properties = Class_copypropertylist ([classNameclass], &count); Nsmutablearray *temparraym = [NsmutablearrayArray]; for(inti =0; I < count; i++) {//objc_property_t Property typeobjc_property_t property = Properties[i];//Convert to objective C stringNSString *name = [NSString Stringwithcstring:property_getname (property) encoding:nsutf8stringencoding]; Nsassert (![ Name isequaltostring:@"Index"], @"The use of index as a property in model is forbidden, otherwise it will cause a syntax error");if([Name isequaltostring:@"Hash"]) { Break;    } [Temparraym Addobject:name]; } Free(properties);return[Temparraym copy];}

In this way we can get a list of all the properties of a class and save it in an array (index is a reserved keyword in the database, so we use an assertion here), but it is not enough to get the list of attributes, we also need to convert the corresponding OC type to SQL corresponding data type. Believe that through the above method to get the property name, we also know that through the runtime can get the property corresponding to the data type, then we can use the following method to convert it to the type required by SQLite

// OC type to SQL type+ (NSString *) occonversiontyletosqlwithstring: (NSString *) String {if([String isequaltostring:@"Long"] || [String isequaltostring:@"int"] || [String isequaltostring:@"BOOL"]) {return@"integer"; }if([String isequaltostring:@"NSData"]) {return@"Blob"; }if([String isequaltostring:@"Double"] || [String isequaltostring:@"Float"]) {return@"Real"; }//Custom array marker    if([String isequaltostring:@"Nsarray"] || [String isequaltostring:@"Nsmutablearray"]) {return@"Customarr"; }//Custom dictionary tag    if([String isequaltostring:@"Nsdictionary"] || [String isequaltostring:@"Nsmutabledictionary"]) {return@"Customdict"; }return@"Text";}

By the above method we convert the data type of OC to the data type of SQL and save it to the array (there are two custom types, which are used later), we have successfully obtained the property name of a model class and the corresponding SQL data type by the method above. It is then saved in a dictionary in the form of a key-value pair, such as:

@{@"name" : @"text",@"age":"integer"

It's not hard to get a statement after that.

// 该方法接收一个类型,内部通过遍历类的属性,字符串拼接获取完整的创表语句,并在内部执行sql语句,并返回结果- (BOOL)creatTableWithClassName:(id)className;

After the introduction of how to create a table, then let's say how to insert data into the database:
Let's take a look at the SQL statement that inserts the data: insert into Person (name,age) values (‘花菜ChrisCai98‘,89);
The front is all fixed format, also we can get the complete creation statement through the concatenation of the string;
We can already get a list of all the attributes of the person class, so how do we stitch up the SQL statements? Here, I've defined a way to do this.

/// 该方法接收一个对象作为参数(模型对象),并返回是否插入成功- (BOOL)insertDataFromObject:(id)object;/// 我们可以这样Person * p = [[Person alloc]init];p.name = @"花菜ChrisCai"18;[[GKDatabaseManager sharedManager] insertDataFromObject:p];

Inserting data

By inserting the data into the database through a simple code like the one above, we get a list of all the attributes of the person class by the method described above, then we can stitch the first half of the INSERT statement and then complete the operation of the second half by KVC.

/// Insert Data-(BOOL) Insertdatafromobject: (ID) Object {//create variable strings for stitching SQL statementsnsmutablestring * sqlString = [nsmutablestring stringwithformat:@"INSERT INTO%@ (", Nsstringfromclass ([Objectclass])]; [[Gkobjcproperty Getuserneedattributelistwithclass:[objectclass]] enumerateobjectsusingblock:^ (id _nonnull obj, nsuinteger idx, BOOL * _nonnull stop) {//splicing field names[SqlString appendformat:@"%@,", obj]; }];//Remove the comma behind[SqlString Deletecharactersinrange:nsmakerange (sqlstring.length-1,1)];//Splicing values[SqlString appendstring:@") VALUES ("];//stitching field values[[Gkobjcproperty Getsqlproperties:[objectclass]] enumeratekeysandobjectsusingblock:^ (ID _nonnull key, id _nonnull obj, BOOL * _nonnull stop) {//Stitching Propertiesif([Object Valueforkey:key]) {if([obj isequaltostring:@"Text"]) {[SqlString appendformat:@"'%@ ',", [object Valueforkey:key]]; }Else if([obj isequaltostring:@"Customarr"] || [Obj isequaltostring:@"Customdict"]) {//Array Dictionary transferNSData * data = [nsjsonserialization datawithjsonobject:[object valueforkey:key] Options:0Error:nil];                NSString * jsonstring = [[NSString alloc] initwithdata:data encoding: (nsutf8stringencoding)]; [SqlString appendformat:@"'%@ ',", jsonstring]; }Else if([obj isequaltostring:@"Blob"]){//NSData processingNSString * jsonstring = [[NSString alloc] initwithdata:[object Valueforkey:key] Encoding: (nsutf8stringencoding)]; [SqlString appendformat:@"'%@ ',", jsonstring]; }Else{[SqlString appendformat:@"%@,", [object Valueforkey:key]]; }        }Else{//No value to save null[SqlString appendformat:@"'%@ ',", [object Valueforkey:key]]; }    }];//Remove the comma behind[SqlString Deletecharactersinrange:nsmakerange (sqlstring.length-1,1)];//Add the following parentheses[SqlString appendformat:@");"];//EXECUTE statementreturn[Self executesqlstring:sqlstring];}

In the above method, we used the previously mentioned custom type, through the custom type we know the need to store a dictionary or an array, where we convert the array and dictionary into a JSON string in the form of a database;

Here we have completed the creation and insertion of data into the table operation, and then we see how to implement a line of code from the database to take out the values, here we provide 6 query interface,

• The interfaces provided are as follows:

- (NSArray *)selecteDataWithClass:(id)className;// 根据类名查询对应表格内所有数据// 获取表的总行数- (id)selecteFormClass:(id)className index:(NSInteger)index;// 获取指定行数据- (NSArray *)selectObject:(Class)className key:(id)key operate:(NSString *)operate value:(id)value;// 指定条件查询class:(id)className;// 自定义语句查询- (NSArray *)selectObject:(Class)className propertyName:(NSString *)propertyName type:(GKDatabaseSelectLocation)type content:(NSString *)content;// 模糊查询

By using the first method, which receives a class masterpiece as a parameter, you can simply implement a line of code to query the data in the table.

class]];

Below we focus on the following core approach, all other methods are based on the implementation of this method

/// Custom statement query-(Nsarray *) selectedatawithsqlstring: (NSString *) sqlStringclass:(ID) className {//Create a model arrayNsmutablearray *models = nil;//1. Prepare the querySqlite3_stmt *stmt;//variables for extracting dataintresult = SQLITE3_PREPARE_V2 (database, sqlstring.utf8string,-1, &stmt, NULL);//2. Determine if you are readyif(SQLITE_OK = = result) {models = [NsmutablearrayArray];//Get attribute list an array group such as nameNsarray * arr = [Gkobjcproperty getuserneedattributelistwithclass:[classnameclass]];//Get property list name and SQL data type such as Name:textNsdictionary * dict = [Gkobjcproperty getsqlproperties:[classnameclass]];//Ready while(Sqlite_row = = Sqlite3_step (stmt)) {//Extract to a single piece of data__block ID OBJC = [[[ClassNameclass] Alloc]init]; for(inti =0; i < Arr.count; i++) {//default No. 0 element is a table primary key so the element starts with the first one//Use KVC to complete assignment if([Dict[arr[i]] isequaltostring:@"Text"]) {[OBJC setvalue:[nsstring stringwithformat:@"%@", [Self textforcolumn:i +1STMT:STMT]] [forkey:arr[i]]; }Else if([Dict[arr[i]] isequaltostring:@"Real"]) {[OBJC setvalue:[nsstring stringwithformat:@"%f", [Self doubleforcolumn:i +1STMT:STMT]] [forkey:arr[i]]; }Else if([Dict[arr[i]] isequaltostring:@"integer"]) {[OBJC setvalue:[nsstring stringwithformat:@"%i", [Self intforcolumn:i +1STMT:STMT]] [forkey:arr[i]]; }Else if([Dict[arr[i]] isequaltostring:@"Customarr"]) {//Array processingNSString * str = [self textforcolumn:i +1STMT:STMT];                    NSData * data = [str datausingencoding:nsutf8stringencoding]; Nsarray * Resultarray = [nsjsonserialization jsonobjectwithdata:data options:0Error:nil];                [OBJC Setvalue:resultarray forkey:arr[i]; }Else if([Dict[arr[i]] isequaltostring:@"Customdict"]) {//Dictionary processingNSString * str = [self textforcolumn:i +1STMT:STMT];                    NSData * data = [str datausingencoding:nsutf8stringencoding]; Nsdictionary * resultdict = [nsjsonserialization jsonobjectwithdata:data options:0Error:nil];                [OBJC setvalue:resultdict forkey:arr[i]; }Else if([Dict[arr[i]] isequaltostring:@"Blob"]) {//binary processingNSString * str = [self textforcolumn:i +1STMT:STMT];                    NSData * data = [str datausingencoding:nsutf8stringencoding];                [OBJC Setvalue:data forkey:arr[i];        }} [Models ADDOBJECT:OBJC]; }    }return[Models copy];}

Inside the method, we create an object based on the class passed in (using __block because the properties of the object need to be modified inside the block), we get the corresponding SQL type, and the property name in the previous method, and this is not repeated here. By executing the corresponding method of SQL type, the data is extracted from the data and assigned to the object by KVC, and it is worth mentioning that we can know that we are using an array or a dictionary by the custom field (customarr,customdict). The JSON string in the database is then converted to an array or dictionary, which is then assigned to the object using KVC!

To this basically all the functions are implemented, other such as update data, delete data, delete the table, etc. have provided specific interface, here does not introduce, the source has detailed comments, but also has a demo, there is a need to download the self,

The above are personal summary of this period of time, if there is no place, you can comment below
You can also contact me through qq:4593679, such as feel good to remember star Oh ~, thank you!!!
Source Address: Https://github.com/ChrisCaixx/GKDatabase

(principle) based on SQLite3 lightweight package, a line of code to implement additions and deletions to search