IPhone development database usage

Because the first iPhone application uses the database, the iPhone database is used first. The iPhone uses the SQLite database. I used the Firefox plug-in SQLite.
Manager to manage SQLite. This plug-in is easy to use and allows you to easily create and manage SQLite in a view. If you don't talk nonsense, go to the topic.
To use SQLite, first introduce the libsqlite3.0.dylib file in frameworks. I skipped the specific steps, then create a database, and add the database to the resources directory after it is created (remember to check copy
Items into... this option), now the preparations are ready, the following code is written.
For ease of use and future maintenance, I have created a class here to encapsulate the database-related code. Create an nsobject class named gadatabase here and add the following code to the implementation file:

# Import
<Sqlite3.h>


ID
Getcolvalue (sqlite3_stmt * stmt, int icol ){


Int type = sqlite3_column_type (stmt, icol );


Switch (type ){


Case
Sqlite_integer:



Return
[Nsnumber numberwithint: sqlite3_column_int (stmt,
Icol)];




Break;


Case
Sqlite_float:



Return
[Nsnumber numberwithdouble: sqlite3_column_double (stmt,
Icol)];




Break;


Case
Sqlite_text:



Return
[Nsstring stringwithuf8string: sqlite3_column_text (stmt,
Icol)];




Break;


Case
Sqlite_blob:



Return
[Nsdata datawithbytes: sqlite3_column_blob (stmt, icol)
Length: sqlite3_column_bytes (stmt, icol)];




Break;


Case
Sqlite_null:



Return
@"";




Break;



Default:



Return
@ "NONE ";




Break;


}

}


Here, I use the C-style definition and the definition is out of @ implementation for ease of use. With this function, it will be very convenient for data extraction in the future, the data type can be root
Increase or decrease according to actual conditions. OK. Now Add the following code between @ implementation and @ end to get the SQLite address in the iPhone:

+ (Nsstring
*) Pathfordatabase {


Nsarray * arrayofpaths =
Nssearchpathfordirectoriesindomains (nsdocumentdirectory,
Nsuserdomainmask, yes );


Nsstring * Path = [arrayofpaths objectatindex: 0];


Path = [path
Stringbyappendingpathcomponent: @ "yourdatabasename. SQLite"];




Nslog (PATH );




Nsfilemanager * filemanager = [nsfilemanager
Defaultmanager];


If (! [Filemanager fileexistsatpath: path]) {


Nsstring
* Databasesource = [[nsbundle mainbundle] resourcepath]
Stringbyappendingpathcomponent :@"

Yourdatabasename. SQLite

"];



If (! [Filemanager copyitematpath: databasesource topath: Path
Error: Nil]) {



Return
Nil;



}


}


Return path;

}


Now you can write SQL statements. Here are some examples:

Query a field:

-(Nsstring
*) Select :( nsstring *) parameter {


Sqlite3 * database;


Sqlite3_stmt * STM;


Nsstring * result = [nsstring string];


Nsstring * SQL = [nsstring stringwithformat: @ "select columnname from
Table where columnname = '% @' ", parameter];




If (sqlite3_open ([[gadatabase pathfordatabase] utf8string],
& Database) = sqlite_ OK ){



If (sqlite3_prepare_v2 (Database, [SQL utf8string],-1,
& STM, null) = sqlite_ OK ){




If (sqlite3_step (STM) = sqlite_row ){




Result =
Getcolvalue (STM, 0 );




}



}



Sqlite3_finalize (STM );


}


Sqlite3_close (database );


Return result;

}

To query multiple fields, you can use an array and a custom class to store them. For example:

-(Nsmutablearray
*) Selectusers {


Sqlite3 * database;


Sqlite3_stmt * STM;


Nsmutablearray * result = [[nsmutablearray alloc]
Init];


Nsstring * SQL = @ "select * from users ";




If (sqlite3_open ([[gadatabase pathfordatabase] utf8string],
& Database) = sqlite_ OK ){



If (sqlite3_prepare_v2 (Database, [SQL utf8string],-1,
& STM, null) = sqlite_ OK ){




While (sqlite3_step (STM) = sqlite_row ){




Gadata
* Userobj = [[gadata alloc] init];




Userobj. RID
= Getcolvalue (STM, 0 );





Userobj. Username = getcolvalue (STM, 1 );





Userobj. Password = getcolvalue (STM, 2 );




[Result
Addobject: userobj];


[Userobj release];




}



}



Sqlite3_finalize (STM );


}


Sqlite3_close (database );


Return
Result;

}


The gadata class is defined as follows:

# Import
<Foundation/Foundation. h>


@ Interface gadata:
Nsobject {


Nsnumber * RID;


Nsstring * username;


Nsstring * password;

}


@ Property (nonatomic,
Retain) nsnumber * RID;

@ Property (nonatomic,
Retain) nsstring * username;

@ Property (nonatomic,
Retain) nsstring * password;


@ End



# Import
"Gadata. H"


@ Implementation
Gadata


@ Synthesize
RID;

@ Synthesize
Username;

@ Synthesize
Password;


@ End

Add data to the database:


-(Void) adduser :( gadata *) adduserobj {


Sqlite3 * database;


Nsstring * SQL = [nsstring stringwithformat: @ "insert into users
(Username, password) values ('% @', '% @')",






Adduserobj. username,
Adduserobj. Password];




Int status = sqlite3_open ([[gadatabase pathfordatabase]
Utf8string], & database );


If (status! = Sqlite_ OK ){



Return;


}


Status = sqlite3_exec (Database, [SQL utf8string], 0, 0,
Null );


If (status! = Sqlite_ OK ){



Return;


}


Sqlite3_close (database );

}


Deletion and modification are similar to addition. They are nothing more than SQL statements, so they are no longer used as an example. Let's talk about the time function in SQLite, so far, I have only used functions about the number of days of computing, so I will not introduce them. You can search for them online and see the following SQL statement:

Select columnname
From table where
(Julianday (date (columnname)-julianday (date ('now ')> 10


This function returns the number of days, starting from January 1, November 24, 4714 BC, Greenwich Mean Time. The date () function returns a date in YYYY-MM-DD format. Therefore, the above statement is not difficult to understand.
I have written so much about the database for the time being. If there are any bad or wrong places, please point out it. Let's learn it together. Finally, I would like to remind you that if resources is modified
The database content in the directory should be deleted, re-compiled, or find the path when the program is running, delete the database files in the documents folder, and re-compile and run the program.
OK. Only in this way will the database in your program be updated. Remember!