SQLite Database---Storing complex objects in a database

1. Insert Data---Here Book is a class of books

#pragma makr Inserting data
-(void) insetIntoTableWithID: (int) nameID withName: (NSString *) name withSex: (NSString *) sex withBook: (Book *) abook
{

    // Archive abook (archive first)
    NSMutableData * data = [NSMutableData data];
    NSKeyedArchiver * archiver = [[NSKeyedArchiver alloc] initForWritingWithMutableData: data];
    [archiver encodeObject: abook forKey: abook.bookName]; // abook.bookName *** key cannot be the same or it will be overwritten
    [archiver finishEncoding];
    // sql statement
    NSString * sqlString = [NSString stringWithFormat: @ "INSERT INTO‘ user_hh ’(‘ id ’,’ name ’,‘ sex ’,‘ book ’) VALUES (?,?,?,?)"] ;;
    sqlite3_stmt * stmt = nil;
    int result = sqlite3_prepare (db, [sqlString UTF8String], -1, & stmt, NULL);
    if (result == SQLITE_OK) {
        // bound field
        // fields are written in sql, fields start from the beginning
        sqlite3_bind_int (stmt, 1, nameID);
        sqlite3_bind_text (stmt, 2, [name UTF8String], -1, NULL);
        sqlite3_bind_text (stmt, 3, [sex UTF8String], -1, NULL);
        sqlite3_bind_blob (stmt, 4, [data bytes], (int) [data length], NULL);
        // carried out
        sqlite3_step (stmt);
    }
    // End
    sqlite3_finalize (stmt);

    // insert statement
    / *
     NSString * insertSql = [NSString stringWithFormat: @ "INSERT INTO 'user_hh' ('id', 'name', 'sex', 'book') VALUES ('% d', '% @', '% @', ' % @ ') ", nameID, name, sex, data];
    // execute SQL statement
    int result = sqlite3_exec (db, [insertSql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog (@ "Insert successfully");
    } else {
    
        NSLog (@ "Insertion failed");
    }
     * /
  
}

2. Querying the Database

#pragma mark Querying the database
-(void) selectDataFromTable
{

    NSString * selectSql = [NSString stringWithFormat: @ "SELECT * FROM‘ user_hh ’"];
   
    // Save the query result set
    sqlite3_stmt * stmt = nil;
    // Prepare to query data (pre-access)
    int result = sqlite3_prepare (db, [selectSql UTF8String], -1, & stmt, NULL);
    if (result == SQLITE_OK) {
        
        // determine if it is the last line, is it necessary to continue
        // Here we use a while loop to execute it line by line ****** without if ******
        while (sqlite3_step (stmt) == SQLITE_ROW) {
            // Take out the data of each column
            
            // 1. Take out the data of the id column
            int numberID = sqlite3_column_int (stmt, 0);
            
            // 2. Take out the data of the name column
            const unsigned char * nameChar = sqlite3_column_text (stmt, 1);
            NSString * name = [NSString stringWithUTF8String: (const char *) nameChar];
            
            // 3. Take out the data of the sex column
            const unsigned char * sexChar = sqlite3_column_text (stmt, 2);
            NSString * sex = [NSString stringWithUTF8String: (const char *) sexChar];
            
            // 4.Take out the BOOK column ****** Bridge ******
            
            // NSData * data = (__bridge NSData *) (sqlite3_column_blob (stmt, 3));
            const void * bytes = (sqlite3_column_blob (stmt, 3));
            int length = sqlite3_column_bytes (stmt, 3);
            NSData * data = [[NSData alloc] initWithBytes: bytes length: length];
            
            // Unarchive
            NSKeyedUnarchiver * unArchiver = [[NSKeyedUnarchiver alloc] initForReadingWithData: data];
            Book * thisBook = [unArchiver decodeObjectForKey: @ "Journey to the West〉"];
            [unArchiver finishDecoding]; // End unarchiving
            
            NSLog (@ "% d% @% @% @", numberID, name, sex, thisBook.bookName);
        }
        
        // End the query --- Important ****** Otherwise, the database cannot be closed ******
        sqlite3_finalize (stmt);
    }
}

3. Here the book class needs to encode and deserialize the property (Nscoding protocol)

-(void)encodeWithCoder:(NSCoder *)aCoder
{

    [aCoder encodeObject:self.bookName forKey:@"bookName"];
    
} -(id)initWithCoder:(NSCoder *)aDecoder
{

    self = [super init]; if (self) {
        self.bookName = [aDecoder decodeObjectForKey:@"bookName"];
    } return self;
}

SQLite Database---Storing complex objects in a database