Encapsulate the database to query student information (add, delete, query, and modify)

Encapsulate the database to query student information (add, delete, query, and modify)

Next to the content of the previous Article (call the method for opening and closing the database)

// Query all students
+ (NSMutableArray *) getAllStudents;
// Query a single student
+ (Student *) getStudentWithID :( NSInteger) aID;
// Add a student
+ (BOOL) insertStudent :( Student *) aStudent;
// Modify the Name of a student
+ (BOOL) updateStudentWithName :( NSString *) aName byID :( NSInteger) aID;
// Delete a student
+ (BOOL) deleteStudentWithID :( NSInteger) aID;

. M file

// Query all students
+ (NSMutableArray *) getAllStudents
{
Sqlite3 * db = [DataBase openDB]; // open the DataBase

// Database operation pointer, stmt: statement
Sqlite3_stmt * stmt = nil;

// Verify the correctness of the SQL statement
// Parameter 1: Database pointer, parameter 2: SQL statement, parameter 3: length of the SQL statement (-1 automatic matching length, representing an infinite length), parameter 4: returns the database operation pointer. Parameter 5: preparation in the future. reserved parameter; generally written as NULL
Int result = sqlite3_prepare_v2 (db, "select * from Student",-1, & stmt, NULL );
NSMutableArray * studentArray = [NSMutableArray array];

// Determine the SQL statement execution result (whether to execute)
If (result = SQLITE_ OK ){
While (sqlite3_step (stmt) = SQLITE_ROW) // a row of data exists.
{
Int ID = sqlite3_column_int (stmt, 0); // The number of columns starts from 0.
Const unsigned char * name = sqlite3_column_text (stmt, 1 );
Const unsigned char * sex = sqlite3_column_text (stmt, 2 );
Int age = sqlite3_column_int (stmt, 3 );

// Obtain the blob type
// 1: Get the length
Int length = sqlite3_column_bytes (stmt, 4 );
// 2: Get Data
Const void * photo = sqlite3_column_blob (stmt, 4 );
// 3: Convert to NSData
NSData * photoData = [NSData dataWithBytes: photo length: length];
// 4: Convert to UIImage
UIImage * image = [UIImage imageWithData: photoData];

// Encapsulate the student Model
Student * student = [[Student alloc] init];
Student. ID = ID;
Student. name = [NSString stringwithuf8string :( const char *) name];
Student. sex = [NSString stringwithuf8string :( const char *) sex];
Student. age = age;
Student. photo = image;

// Add to array
[StudentArray addObject: student];
}

}
// Release the stmt pointer
Sqlite3_finalize (stmt );
// Close the database
[DataBase closeDB];
Return studentArray;
}
// Query a single student
+ (Student *) getStudentWithID :( NSInteger) aID
{
Sqlite3 * db = [DataBase openDB];
Sqlite3_stmt * stmt = nil;
NSString * sqlString = [NSString stringWithFormat: @ "select * from Student where id = % d", aID];
Int result = sqlite3_prepare_v2 (db, [sqlString UTF8String],-1, & stmt, NULL );

Student * student = nil;

If (result = SQLITE_ OK ){
If (sqlite3_step (stmt) = SQLITE_ROW ){
Int ID = sqlite3_column_int (stmt, 0 );
Const unsigned char * name = sqlite3_column_text (stmt, 1 );
Const unsigned char * sex = sqlite3_column_text (stmt, 2 );
Int age = sqlite3_column_int (stmt, 3 );

Int length = sqlite3_column_bytes (stmt, 4 );
Const void * photo = sqlite3_column_blob (stmt, 4 );
NSData * photoData = [NSData dataWithBytes: photo length: length];
UIImage * image = [UIImage imageWithData: photoData];

Student = [[Student alloc] init];
Student. ID = ID;
Student. name = [NSString stringwithuf8string :( const char *) name];
Student. sex = [NSString stringwithuf8string :( const char *) sex];
Student. age = age;
Student. photo = image;

}
}
Sqlite3_finalize (stmt );
[DataBase closeDB];
Return student;
}
// Add a student
+ (BOOL) insertStudent :( Student *) aStudent
{
Sqlite3 * db = [DataBase openDB];
Sqlite3_stmt * stmt = nil;
NSString * sqlString = [NSString stringWithFormat: @ "insert into Student (name, sex, age) values ('% @', '% @', % d)", aStudent. name, aStudent. sex, aStudent. age];
Int result = sqlite3_prepare_v2 (db, [sqlString UTF8String],-1, & stmt, nil );
If (result = SQLITE_ OK ){
If (sqlite3_step (stmt) = SQLITE_DONE) // you can determine whether the statement has been executed.
{
Sqlite3_finalize (stmt );
[DataBase closeDB];
Return YES;
}
}
Sqlite3_finalize (stmt );
[DataBase closeDB];
Return NO;
}
// Modify the Name of a student
+ (BOOL) updateStudentWithName :( NSString *) aName byID :( NSInteger) aID
{
Sqlite3 * db = [DataBase openDB];
Sqlite3_stmt * stmt = nil;
NSString * sqlString = [NSString stringWithFormat: @ "update Student set name = '% @ 'where id = % d", aName, aID];
Int result = sqlite3_prepare_v2 (db, [sqlString UTF8String],-1, & stmt, NULL );
If (result = SQLITE_ OK ){
If (sqlite3_step (stmt) = SQLITE_DONE ){
Sqlite3_finalize (stmt );
[DataBase closeDB];
Return YES;
}
}
Sqlite3_finalize (stmt );
[DataBase closeDB];
Return NO;
}
// Delete a student
+ (BOOL) deleteStudentWithID :( NSInteger) aID
{
Sqlite3 * db = [DataBase openDB];
Sqlite3_stmt * stmt = nil;
NSString * sqlString = [NSString stringWithFormat: @ "delete from Student where id = % d", aID];
Int result = sqlite3_prepare_v2 (db, [sqlString UTF8String],-1, & stmt, NULL );
If (result = SQLITE_ OK ){
If (sqlite3_step (stmt) = SQLITE_DONE ){
Sqlite3_finalize (stmt );
[DataBase closeDB];
Return YES;
}
}
Sqlite3_finalize (stmt );
[DataBase closeDB];
Return NO;
}