Time: Friday, 02 June 2017
Note: Part of the content of this article is from Mucho.com. @ Mu Course Net: http://www.imooc.com
Teaching sample source code: none
Personal learning source code: https://github.com/zccodere/s...
Chapter 1: Learning Guide
1-1 Course Introduction
SQLite operation
Lightweight relational database management system embedded database Low occupancy of resources Strong portability Fast speed SQLite3
Chapter II: Data Types and Instructions
2-1 Data Types and Instructions
Data types supported by SQLite
No Boolean type, instead of integer 0 or 1 No date, time type data, stored in TEXT, REAL type, usually processed as a string
SQLite statement
Chapter 3: Operating database
3-1 Open Database 1
Create a database
1. Open the database using sqlite3_open function 2. Use sqlite3_exec function to execute Create Table statement 3. Release resources using sqlite3_close function
3-2 Open Database 2
Write the SQL Manager class, which is generated by the singleton pattern
+ (SQLManager *)shareManager{ static dispatch_once_t once; dispatch_once(&once,^{ manager = [[self alloc] init]; [manager createDataBaseTableIfNeeded]; }); return manager; }
3-3 Open Database 3
Call method
// The first parameter is the complete path of the database file // The second parameter is the database DataBase if(sqlite3_open([writetablePath UTF8String], &db) != SQLITE_OK){// SQLITE_OK stands for open success // fail sqlite3_close(db);// close database NSAssert(NO, @"Failed to open database!"); }else{ // Successful TODO }
3-4 Execution Statement
Call method
// The first parameter is the database db object // The second parameter is the SQL statement // The third and fourth are the parameters passed by the callback function and the callback function, respectively. // The fifth parameter is an error message sqlite3_exec(db, [createSQL UTF8String], nil, nil, &err)
3-5 Close the database
Call method
sqlite3_close(db);
Complete code for this chapter
// Get the database path - (NSString *)applicationDocumentsDirectoryFile{ NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentDirectory = [paths firstObject]; NSString *filePath = [documentDirectory stringByAppendingPathComponent:kNameFile]; return filePath; } // Create a database - (void)createDataBaseTableIfNeeded { NSString *writetablePath = [self applicationDocumentsDirectoryFile]; NSLog(@"The address of the database is:%@",writetablePath); // The first parameter is the complete path of the database file // The second parameter is the database DataBase if(sqlite3_open([writetablePath UTF8String], &db) != SQLITE_OK){// SQLITE_OK stands for open success // fail sqlite3_close(db);// close database NSAssert(NO, @"Failed to open database!"); }else{ char *err; NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS StudentName (idNum TEXT PRIMARY KEY,name TEXT);"]; // The first parameter is the database db object // The second parameter is the SQL statement // The third and fourth are the parameters passed by the callback function and the callback function, respectively. // The fifth parameter is an error message if(sqlite3_exec(db, [createSQL UTF8String], nil, nil, &err) != SQLITE_OK){ // fail sqlite3_close(db);// close database NSAssert(NO, @"Failure in table building!"); } sqlite3_close(db); } }
Chapter IV: Pretreatment and Binding
4-1 pretreatment
Query data
Call method
// The first parameter is the database db object // The second parameter is the SQL statement // The third parameter is the length of the execution statement, - 1 is the full length. // The fourth parameter is the statement object // The fifth parameter is the unexecuted statement part NULL if(sqlite3_prepare_v2(db, [querySQL UTF8String], -1, &statement, NULL) == SQLITE_OK){ // Pretreatment Successful TODO }else{ // Pretreatment Failure TODO }
4-2 binding
Call method
// First Parametric Statement Object // The ordinal number of the second parameter parameter starting to execute // The third parameter is the value we want to bind. // The length of the string bound by the fourth parameter // Fifth parameter pointer NULL sqlite3_bind_text(statement, 1, [idNum UTF8String], -1, NULL);
4-3 ergodic process
Call method
// A return value of the SQLITE_ROW constant is found. if (sqlite3_step(statement) == SQLITE_ROW) { // Successful query with data TODO }else{ // Query failed, no data TODO }
4-4 Data Extraction
Call method
// Extraction of data // First Parametric Statement Object // Index of the second parameter field char *idNum = (char *)sqlite3_column_text(statement, 0);
Complete code for this chapter
// query - (StudentModel *)searchWithIdNum:(StudentModel *)model{ NSString *path = [self applicationDocumentsDirectoryFile]; if(sqlite3_open([path UTF8String], &db) != SQLITE_OK){// SQLITE_OK stands for open success // fail sqlite3_close(db);// close database NSAssert(NO, @"Failed to open database!"); }else{ NSString *querySQL = @"SELECT idNum,name FROM StudentName where idNum = ?"; sqlite3_stmt *statement;//Statement object // The first parameter is the database db object // The second parameter is the SQL statement // The third parameter is the length of the execution statement, - 1 is the full length. // The fourth parameter is the statement object // The fifth parameter is the unexecuted statement part NULL if(sqlite3_prepare_v2(db, [querySQL UTF8String], -1, &statement, NULL) == SQLITE_OK){ // Query the database by pressing the primary key NSString *idNum = model.idNum; // First Parametric Statement Object // The ordinal number of the second parameter parameter starting to execute // The third parameter is the value we want to bind. // The length of the string bound by the fourth parameter // Fifth parameter pointer NULL sqlite3_bind_text(statement, 1, [idNum UTF8String], -1, NULL); // A return value of the SQLITE_ROW constant is found. if (sqlite3_step(statement) == SQLITE_ROW) { // Extraction of data // First Parametric Statement Object // Index of the second parameter field char *idNum = (char *)sqlite3_column_text(statement, 0); // data conversion NSString *idNumStr = [[NSString alloc] initWithUTF8String:idNum]; char *name = (char *)sqlite3_column_text(statement, 1); NSString *nameStr = [[NSString alloc] initWithUTF8String:name]; StudentModel *respModel = [[StudentModel alloc] init]; respModel.idNum = idNumStr; respModel.name = nameStr; sqlite3_finalize(statement); sqlite3_close(db); NSLog(@"Successful data query! idNum=%@",model.idNum); return respModel; } } sqlite3_finalize(statement); sqlite3_close(db); } NSLog(@"Data query failed! idNum=%@",model.idNum); StudentModel *respModel = [[StudentModel alloc] init]; respModel.idNum = @"200"; respModel.name = @"Test 2"; return respModel; }
Chapter 5: Modifying the database
5-1 binding process
Modify data
5-2 Modification of Database
Complete code
// Save data - (int)insert:(StudentModel *)model{ NSString *path = [self applicationDocumentsDirectoryFile]; if(sqlite3_open([path UTF8String], &db) != SQLITE_OK){// SQLITE_OK stands for open success // fail sqlite3_close(db);// close database NSAssert(NO, @"Failed to open database!"); }else{ // Writing SQL Statements NSString *insertSQL = @"INSERT OR REPLACE INTO StudentName (idNum,name) VALUES (?,?)"; // Define precompiled statements sqlite3_stmt *statement; // Pretreatment if(sqlite3_prepare_v2(db, [insertSQL UTF8String], -1, &statement, NULL) == SQLITE_OK){ // Binding parameters sqlite3_bind_text(statement, 1, [model.idNum UTF8String], -1, NULL); sqlite3_bind_text(statement, 2, [model.name UTF8String], -1, NULL); // Execute preprocessing statements if (sqlite3_step(statement) != SQLITE_DONE) { // Operation not completed NSAssert(NO, @"Failed to save data!"); } NSLog(@"Data saved successfully! idNum=%@,name=%@",model.idNum,model.name); sqlite3_finalize(statement); sqlite3_close(db); } } return 0; }
Chapter VI: Summary
6-1 Summary
Summary of steps
