In iOS, DataBase SQL DataBase UI _ advanced

Source: Internet
Author: User

In iOS, DataBase SQL DataBase UI _ advanced
Structured query Lauguage: Structured query Language
1. create table if not exists Teacher (tea_id integer primary key autoincrement, tea_name text, tea_gender text, tea_age integer, tea_salary integer): 2. insert into Teacher (tea_id, tea_name, tea_gender, tea_age, tea_salary) values (110, 'Han go', 'male',) into the fields in the Table: 3. find all the content in the table (* query all the data in the instructor table) select * from Teacher 4. find all information about the corresponding fields in the table (all data under tea_name and tea_gender) select tea_name, tea_gender from Teacher 5. select * from Teacher where tea_name = 'Han go' according to the condition: 6. update Teacher set tea_gender = 'female 'where tea_id = 111 Delete: 7. delete from Teacher where tea_id = 110 8. delete all data in the table from Teacher 9. delete the entire table according to the table name drop table Teacher ---------------------------------------------- the following uses the actual example to add, delete, modify, and query the database: the layout is as follows (no code layout): 4 textfields 5 buttons
---------------------------------------- Prepare a Student model class:

Student.h@interface Student : NSObject@property(nonatomic,assign)NSInteger number;@property(nonatomic,copy)NSString *name;@property(nonatomic,copy)NSString *gender;@property(nonatomic,assign)NSInteger age;@endStudent.m@implementation Student- (void)dealloc{    self.name = nil;    self.gender = nil;    [super dealloc];}- (NSString *)description{    return [NSString stringWithFormat:@%ld %@ %@ %ld, self.number,self.name,self.gender,self.age];}@end
Using the singleton write interface and implementation method:
DataBaseHandle. h @ class Student; @ interface DataBaseHandle: NSObject // method for creating a single instance + (DataBaseHandle *) define DataBaseHandle; // Method for opening a database-(void) openDataBase; // Method for disabling the database-(void) closeDataBase; // interface for inserting Student objects-(void) insertStudent: (student *) Student; // return the interface (NSMutableArray *) selectAllStudent for all students in the table; // Delete the student (void) deleteOneStudentByNumber: (NSInteger) number based on the unique student ID; // modify the Student's name (void) updateStudentGender: (NSString *) gender ByNumber: (NSInteger) number based on the Student ID-(Student *) selectOneStudentByNumber: (NSInteger) number; @ end

DataBaseHandle. m
# Import DataBaseHandle. h # import
 
  
# Import Student. h @ implementation DataBaseHandlestatic DataBaseHandle * handle = nil; // method for creating a single instance + (DataBaseHandle *) using DataBaseHandle {@ synchronized (self) {if (handle = nil) {handle = [[DataBaseHandle alloc] init]; // you can access the database [handle openDataBase] after a single-instance object is created;} return handle ;} // return to the database path-(NSString *) dataBasePath {// put the database file in the student folder of Documents. sqlite return [[NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent: @ student. sqlite];}
 

Define a global database pointer in a static Zone

static sqlite3 *db = nil;

How to open a database

-(Void) openDataBase {// 1. obtain the path of the data file NSString * dbPath = [self dataBasePath]; // before using SQL, you must import the libsqlite3.0 dynamic link class library. libsqlite.3.0 is a shortcut, which is a benefit of the shortcut, when the version is updated, the new object class library is no longer imported, because the shortcut always points to the latest object class library (remember to import the header file) // 2. use SQL statements to open a database
// [DbPath UTF8String] converts the OC string to a C language string // sqlite3 indicates the database pointer // create the database pointer db // sqlite3 * db = nil; // After the method is executed, the database pointer db is not initialized. After the method is executed, there will be a database file // This method will first check whether there is a corresponding database file in the file path, if not, it will be created, if yes, directly open int result = sqlite3_open ([dbPath UTF8String], & db); // SQLITE_ OK indicates that SQL is successful if (result = SQLITE_ OK) {// NSLog (@ database opened successfully); // create a table // prepare an SQL statement NSString * sqlString = @ create table if not exists Student (stu_number integer primary key autoincrement, stu_name text, stu_gender text, stu_age integer); // execute the SQL statement sqlite3_exec (db, [sqlString UTF8String], NULL );} else {NSLog (@ database opening failed );}}

How to disable a database

 

-(Void) closeDataBase {int result = sqlite3_close (db); NSLog (@%@, (result = SQLITE_ OK )? @ Close successful: @ close failed );}

 

Interface for inserting student objects -- add

-(Void) insertStudent: (Student *) student {// 1. open the database [self openDataBase]; // 2. prepare the inserted SQL statement NSString * sqlString = @ insert into Student (stu_name, stu_gender, stu_age) values (?,?,?); // 3. create a database management pointer (Database Management Instruction Set) sqlite3_stmt * stmt = nil; // 4. verify that the SQL statement is correct // parameter 1: Database pointer, // parameter 2: SQL statement // parameter 3: the length of the SQL statement is written as-1, automatically calculate the maximum length of an SQL statement. Otherwise, you must calculate the length by yourself. // parameter 4: The management pointer of an SQL statement. // parameter 5: Reserved parameter. It will be used in the future. // 5. take the verification result and determine whether to perform the parameter binding operation int result = sqlite3_prepare_v2 (db, [sqlString UTF8String],-1, & stmt, NULL); if (result = SQLITE_ OK) {NSLog (@ inserted successfully); // parameter 1: SQL statement management pointer // parameter 2: In the preceding SQL statement? ,? The subscript starts from 1 // parameter 3: Data to be bound // parameter 4: Data Length // sqlite3_bind_text (stmt, 1, [student. name UTF8String],-1, NULL); // bind the data sqlite3_bind_text (stmt, 2, [student. gender UTF8String],-1, NULL); // bind the data sqlite3_bind_int (stmt, 3, (int) student to the stu_age field. age); // 6. execute SQL statement sqlite3_step (stmt);} // 7. release the management pointer sqlite3_finalize (stmt); // 8. close database [self closeDataBase];}

Return the interfaces of all students in the table -- Query 1

-(NSMutableArray *) selectAllStudent {// 1. open the database [self openDataBase]; // 2. prepare the SQL statement NSString * sqlString = @ select * from Student; // 3. create a butler pointer sqlite3_stmt * stmt = nil; // 4. verify that the SQL statement is correct. int result = sqlite3_prepare_v2 (db, [sqlString UTF8String],-1, & stmt, NULL); if (SQLITE_ OK = result) {NSLog (@ succeeded in searching all statements); // create a variable array storage to find all the student objects NSMutableArray * array = [NSMutableArray arrayWithCapacity: 0]; // If SQLITE_ROW is equal to row, it indicates that the next row has data and continues cyclically. If it is not equal to SQLITE_ROW, it means that the next row has no data and the loop ends while (sqlite3_step (stmt) = SQLITE_ROW) {// read the data of the field in sequence // The column number starts from scratch // The first column int number = sqlite3_column_int (stmt, 0 ); // The second column NSString * name = [NSString stringwithuf8string :( const char *) sqlite3_column_text (stmt, 1)]; // The third column NSString * gender = [NSString stringwithuf8string :( const char *) sqlite3_column_text (stmt, 2)]; // The fourth column int age = sqlite3_column_int (stmt, 3); // 5. create a Student object and assign Student * stu = [[Student alloc] init]; stu. number = number; stu. name = name; stu. gender = gender; stu. age = age; // Add it to the array [array addObject: stu]; // release [stu release];} // 6. release management pointer sqlite3_finalize (stmt); // close the database [self closeDataBase]; return array;} else {sqlite3_finalize (stmt); [self closeDataBase]; return nil ;}}

 

Search for students by student id -- check 2

 

-(Student *) selectOneStudentByNumber: (NSInteger) number {// 1. open the database [self openDataBase]; // 2. prepare the SQL statement NSString * sqlString = @ select * from Student where stu_number = ?; // 3. create management pointer sqlite3_stmt * stmt = nil; // 4. verify that the SQL statement is correct. int result = sqlite3_prepare_v2 (db, [sqlString UTF8String],-1, & stmt, NULL); // 5. determine the operation to be performed based on the verification result if (result = SQLITE_ OK) {// 6. bind the sqlite3_bind_int (stmt, 1, (int) number); // 7. traverse table data // create model Student Object Storage Student information Student * stu = [[Student alloc] init]; while (sqlite3_step (stmt) = SQLITE_ROW) {// assign an stu value to the student's Attributes Based on the Data found. number = number; stu. name = [NSString stringwithuf8string :( const char *) sqlite3_column_text (stmt, 1)]; stu. gender = [NSString stringwithuf8string :( const char *) sqlite3_column_text (stmt, 2)]; stu. age = sqlite3_column_int (stmt, 3);} // 8. release management pointer sqlite3_finalize (stmt); // 9. close the database [self closeDataBase]; return [stu autorelease];} else {// 10. release management pointer sqlite3_finalize (stmt); // 11. close the database [self closeDataBase];} return nil ;}


 

Delete a student based on the unique student id -- delete

 

-(Void) deleteOneStudentByNumber: (NSInteger) number {// 1. open the database [self openDataBase]; // 2. prepare to delete the SQL statement NSString * sqlString = @ delete from Student where stu_number = ?; // 3. create a database management pointer sqlite3_stmt * stmt = nil; // 4. verify that the SQL statement is correct. int result = sqlite3_prepare_v2 (db, [sqlString UTF8String],-1, & stmt, NULL); // 5. determine whether to perform the parameter binding operation if (result = SQLITE_ OK) {// 6. bind the passed parameter sqlite3_bind_int (stmt, 1, (int) number); // 7. execute the SQL statement sqlite3_step (stmt);} // 8. release the management pointer sqlite3_finalize (stmt); // 9. close database [self closeDataBase];}

 

 

Modify the Student name based on the unique student ID-Modify

-(Void) updateStudentGender: (NSString *) gender ByNumber: (NSInteger) number {// 1. open the database [self openDataBase]; // 2. prepare the SQL statement NSString * sqlString = @ update Student set stu_gender =? Where stu_number = ?; // 3. create a database management pointer sqlite3_stmt * stmt = nil; // 4. verify SQL statement int result = sqlite3_prepare_v2 (db, [sqlString UTF8String],-1, & stmt, NULL); // 5. determine the operation if (result = SQLITE_ OK) {// 6. bind sqlite3_bind_text (stmt, 1, [gender UTF8String],-1, NULL); // 7. bind student ID // The second parameter: Yes? In the SQL statement, the position starts from 1 sqlite3_bind_int (stmt, 2, (int) number); // 8. execute the SQL statement sqlite3_step (stmt);} // 9. release management pointer sqlite3_finalize (stmt); // 10. close database [self closeDataBase];}

Method call:

ViewController. m

 

# Import ViewController. h # import DataBaseHandle. h # import Student. h @ interface ViewController () @ property (retain, nonatomic) IBOutlet UITextField * numberField; // student ID @ property (retain, nonatomic) IBOutlet UITextField * nameField; // name @ property (retain, nonatomic) IBOutlet UITextField * genderField; // gender @ property (retain, nonatomic) IBOutlet UITextField * ageField; // age @ end
1. Add: add

 

 

-(IBAction) insertStudent :( UIButton *) sender {// determines if (0 = self. nameField. text. length | 0 = self. genderField. text. length | 0 = self. ageField. text. length) {return;} // create the Student object. Student * stu = [[Student alloc] init]; stu. name = self. nameField. text; stu. gender = self. genderField. text; stu. age = [self. ageField. text integerValue]; [[DataBaseHandle implements DataBaseHandle] insertStudent: stu]; [stu release];}

2. Update student information: Modify

 

-(IBAction) updateStudent :( UIButton *) sender {// obtain the content of the input box NSInteger number = [self. numberField. text integerValue]; NSString * gender = self. genderField. text; // call the method of modifying gender according to student ID [[[DataBaseHandle using DataBaseHandle] updateStudentGender: gender ByNumber: number];}

 

3. Delete Student: delete student

 

-(IBAction) deleteStudent :( UIButton *) sender {NSInteger number = [self. numberField. text integerValue]; // call the method for deleting a student based on the student ID [[[DataBaseHandle using DataBaseHandle] deleteOneStudentByNumber: number];}

 

 

4. Search for all students: Check 1

 

-(IBAction) selectAllStudent :( UIButton *) sender {// call NSMutableArray * contentArray = [[DataBaseHandle into DataBaseHandle] selectAllStudent]; for (Student * stu in contentArray) {NSLog (@%@, stu );}}

 


5. Search for a student: Check 2

 

-(IBAction) selectOneStudent :( id) sender {// obtain the content of the student id input box NSInteger number = [self. numberField. text integerValue]; // call Student * stu = [[DataBaseHandle into DataBaseHandle] selectOneStudentByNumber]; NSLog (@ % @, stu );}

Remember to release:

 

 

- (void)dealloc {    [_numberField release];    [_nameField release];    [_genderField release];    [_ageField release];    [super dealloc];}

Summary: general steps: 1. Open the database; 2. Prepare SQL statements; 3. Create a butler pointer; 4. Verify that the SQL statements are correct; 5. Determine the operations to perform based on the verification results; 6. Bind parameters. 7. traverse the data in the table (search for students based on the student ID). 8. Release the management pointer. 9. Close the database.

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.