SQLite database in iOS uses bold type to store and read dictionaries

When I was doing the app collection, I found that my data is a dictionary, how to save the dictionary to the database? Read a lot of blogs, the dictionary should be used in the database of the bold type to save, but added to the database, not read out, for this to break the brain, in order to solve this problem, it took a good time.

Previously thought that the bold type is used to store binary, can store pictures and so on, and my data stored in the database is indeed binary, so read all is binary, so that can not be converted into a dictionary. Later found that my method of saving data is wrong, the bold type can not only put binary can also put data, it simply overturned my view of the bold type,BLOB, just a data block , completely follow the input storage, not only the binary system.

Store dictionary (write to database):

First convert the dictionary to the NSData type, and then save it to the database, especially to note that NSData data cannot be inserted using SQL statements, because to write to the database the BLOB type of binary data, but also to write the length of the data bytes, So do not insert BLOB type data when writing SQL statements, not much, directly on the example:

Sqlite3_stmt *stmt =Nil;//nsdictionary turn NSDataNSData *data = [nsjsonserialization datawithjsonobject:[[list initwithlist:list] objectforkey:@"Cover_image"] Options:nsjsonwritingprettyprinted Error:Nil];//Add other fields you can use this insert method NSString*SQLSTR = [NSStringstringwithformat:@"INSERT into Hjl_list (Id, title, Actual_count, Collectors_count) VALUES ('%@ ', '%@ ', '%@ ', '%@ ')", List. Id, List. Title, List. Actual_count, List. Collectors_count];//sql UPDATE statement, in order to write data of the BLOB typeNSString*sqlstr_data = [NSStringstringwithformat:@"Update hjl_list set cover_img=?" where id= '%@ ' ", List. Id];intresult = sqlite3_exec (db, [Sqlstr utf8string],Nil,Nil,Nil);//Execute UPDATE statementintRESULT2 = Sqlite3_prepare (db, [Sqlstr_data utf8string],-1, &stmt,Nil);if(Result = = Sqlite_ok && result2 = = SQLITE_OK) {//Use the SQLITE3_BIND_BLOB64 statement to insert the data in a binding manner, and the bytes is correct when queriedSqlite3_bind_blob64 (stmt,1, [data bytes], [data length],Nil);if(Sqlite3_step (stmt) = = Sqlite_done) {NSLog(@"Add Success"); }return YES; }NSLog(@"%@ failed", SQLSTR);return  NO;

Read data (read from database):

Sqlite3 *db = [db OpenDatabase]; Sqlite3_stmt *stmt =Nil;NSString*SQLSTR = [NSStringstringwithformat:@"SELECT * from Hjl_list"];intresult = Sqlite3_prepare (db, [Sqlstr utf8string],-1, &stmt,Nil);if(Result = = SQLITE_OK) { while(Sqlite3_step (stmt) = = Sqlite_row) {Const void*op = Sqlite3_column_blob (stmt,4);intSize = Sqlite3_column_bytes (stmt,4); NSData *data = [NSData datawithbytes:op length:size];//The DIC here is the dictionary read from the database            nsdictionary*dic = [nsjsonserialization jsonobjectwithdata:data options:nsjsonreadingmutablecontainers Error:Nil]; }

SQLite database in iOS uses bold type to store and read dictionaries