IOS Intermediate Data Persistence-Simple database (Sqlite3)

SQLite is an embedded and lightweight SQL database. SQLite is implemented by C. It is widely used in small Web applications including browsers (most browsers that support HTML5, IE), iOS, Android, and some portable requirements.

Database is nothing more than increase, delete, change, check four kinds. In addition to the query thought, the other three methods are more similar

// before using the database, open the database

-(void) opendb

{

if (db ! = Nil) {

return;

}

// database stored in the caches folder in Shahe

nsstring * Cachespath = [nssearchpathfordirectoriesindomains(nscachesdirectory, nsuserdomainmask, YES)lastobject];

nsstring * DbPath = [Cachespath stringbyappendingpathcomponent:@ "Class21.sqlite" /c11>];

// The full path of the first parameter to fill a database

// If the database file already exists, it is open and if the database file does not exist, first create and then open

int result = Sqlite3_open([DbPath utf8string], &db);

if (Result = = Sqlite_ok) {

NSLog (@ " Open Database successfully ~");

// Create a table of SQL statements

nsstring * createsql = @ "CREATE TABLE Students (number INTEGER PRIMARY KEY autoincremen T is null, Name text is not NULL, Gender text is not NULL, and age text is DEFAULT) ";

// execute SQL statement

sqlite3_exec (db, [Createsql utf8string], null, null, null);

}

}

// Close Database

-(void) closedb

{

int result = sqlite3_close(db);

if (Result = = Sqlite_ok) {

NSLog (@ " database off ~");

db = nil;

}

}

Insert

-(void) Insertnewstudent: (Student *) stu

{

// Open Database

[ self opendb];

// declaration follow Pointer

sqlite3_stmt * stmt = nil;

// write an SQL statement (insert)

nsstring * sql = @ "INSERT into Students (name,gender,age) VALUES (?,?,?)" ;

// Verify that the SQL statement is correct,1 means that the length of the SQL statement is not limited

int result = sqlite3_prepare_v2(db, [SQL utf8string],-1, &stmt, NULL);

NSLog (@ "result =%d", result);

if (sqlite_ok = = result) {

// if the SQL statement is correct, bind the data ( in the case of a binding SQL statement, i.e.: Will ? ). Replace with the value to be deposited )

// When a question mark is bound , the marker starts at 1 , not 0

Sqlite3_bind_text (stmt, 1, [Stu. Name utf8string],-1, NULL);

// bind a second question Mark

Sqlite3_bind_text (stmt, 2, [Stu. Gender utf8string],-1, NULL);

// bind a third question mark

Sqlite3_bind_int (stmt, 3, Stu. age);

// execute SQL statement, stepping

Sqlite3_step (stmt);

}

// release , no release will cause memory leaks

sqlite3_finalize(stmt);

}

Update ( modify )

-(void) Updatestudentname: (nsstring *) newName withnumber: (int) number

{

// Open Database

[ self opendb];

// declaration follow Pointer

sqlite3_stmt * stmt = nil;

// write SQL statements (update, modify)

nsstring * sql = @ "Update Students set name =?" where number =? " ;

// Validation

int result = sqlite3_prepare_v2(db, [SQL utf8string],-1, & stmt, NULL);

if (sqlite_ok = = result) {

// binding

Sqlite3_bind_text (stmt, 1, [newName utf8string],-1, NULL);

Sqlite3_bind_int (stmt, 2, number);

// specify SQL statement, stepping

Sqlite3_step (stmt);

}

// Release

sqlite3_finalize(stmt);

}

Delete

-(void) Deletestudentwithnumber: (int) number

{

// Open Database

[ self opendb];

// declaration follow Pointer

sqlite3_stmt * stmt = nil;

// write SQL statements

nsstring * sql= @ "Delete from Students where number =?" ;

Nsinteger result = sqlite3_prepare_v2(db, [SQL utf8string],1, & stmt, NULL);

if (sqlite_ok = = result) {

Sqlite3_bind_int64 (stmt, 1, number);

Sqlite3_step (stmt);

}

sqlite3_finalize(stmt);

}

Search

-(nsmutablearray *) selectallstudents

{

// Open Database

[ self opendb];

// declaration follow Pointer

sqlite3_stmt * stmt = nil;

// write SQL statements

nsstring * sql = @ "SELECT * from Students";

// Verify that the SQL statement is correct

int result = sqlite3_prepare_v2(db, [SQL utf8string],-1, &stmt, NULL);

if (sqlite_ok = = result) {

// Create an array

nsmutablearray * allstudent = [[nsmutablearrayalloc]init];

// execute SQL statement, take value

//sqlite_row has another line ready.

while (sqlite3_step(stmt) = = Sqlite_row) {

//number name Gender age

// take the results of this cycle

// When a value is taken from the database, the field's marker starts at 0

int number = sqlite3_column_int(stmt, 0);

nsstring * name = [nsstringstringwithutf8string:(constchar *) Sqlite3_column_text(stmt, 1)];

nsstring * gemder = [nsstringstringwithutf8string:(constchar *) sqlite3_column_text(stmt, 2)];

int age = sqlite3_column_int(stmt, 3);

// Use the extracted data to encapsulate the student object and add it to the array

Student * stu = [[Student alloc] initwithnumber: Number name: Name Gender: Gemder Age: Age];

[Allstudent AddObject: Stu];

[Stu release];

NSLog (@ "number =%d,name =%@,gemder =%@,age =%d", number,name,gemder,age);

}

sqlite3_finalize(stmt);

return allstudent;

}

// Release

sqlite3_finalize(stmt);

return nil;

}

-(nsmutablearray *) Selectstudentswithgender: (nsstring *) gender

{

[ self opendb];

sqlite3_stmt * stmt = nil;

nsstring * sql = @ "Select Name,age from Students where gender =?" ;

int result = sqlite3_prepare_v2(db, [SQL utf8string],-1, &stmt, NULL);

NSLog (@ "result =%d", result);

if (sqlite_ok = = result) {

nsmutablearray * Stuarray = [nsmutablearrayarray];

// Bind First

Sqlite3_bind_text (stmt, 1, [Gender utf8string],-1, NULL);

while (sqlite3_step(stmt) = = Sqlite_row) {

// Take value

nsstring * name = [nsstringstringwithutf8string:(constchar *) Sqlite3_column_text(stmt, 0)];

int age = sqlite3_column_int(stmt, 1);

// Package Student object, add to array central

Student * stu = [[Student alloc]initwithnumber:0 Name : Name gender: Gender Age: Age];

[Stuarray AddObject: Stu];

[Stu release];

}

sqlite3_finalize(stmt);

return Stuarray;

}

sqlite3_finalize(stmt);

return nil;

}