SQLite instance tutorial using SQLite in iOS-iOS development

Statement You are welcome to repost this article, but please respect the author's Labor achievements. repost this article and keep the statement in this box. Thank you. Article Source: http://blog.csdn.net/iukey

SQLite is a good thing for mobile platforms. I have always wanted to write a tutorial on SQLite, but I don't know where to start. I 've been thinking about it for a long time. Let's talk about it from a small demo. I wrote a simplified dictionary that provides the basic functions of adding, deleting, querying, and modifying.

The project structure is as follows. The last step is as follows.

You can see that I have queried "cc" and all related entries are queried.

Now, I want to explain my project. First, you can view my project directory. queryresultlist is the interface control class, and DB is the database operation class.

The entire project process: Enter the content to be queried in the search box and click search. The returned results of the underlying fuzzy search are displayed in tableview.

Let's start with the underlying operations. At present, I have implemented the insert and query operations. After deletion and modification, I will make up the following:

1. Create a database

-(Const char *) getfilepath {// obtain the database path return [[nsstring stringwithformat: @ "% @/documents/L", nshomedirectory ()] utf8string];}

// DB. h // iukey # import <Foundation/Foundation. h> # import "/usr/include/sqlite3.h" @ interface DB: nsobject {sqlite3 * PDB; // database handle} @ property (nonatomic, assign) sqlite3 * PDB; -(bool) insertrecordwithen :( nsstring *) en CN :( nsstring *) Cn comment :( nsstring *) comment; // insert a record-(nsmutablearray *) quary :( nsstring *) STR; // query-(const char *) getfilepath; // obtain the database path-(bool) createdb; // create a database-(bool) createtable; // create a Table @ end

2. Create a table

-(Bool) createtable {char * err; char * SQL = "CREATE TABLE Dictionary (ID integer primary key autoincrement, en nvarchar (64), CN nvarchar (128 ), comment nvarchar (256) "; // Create a table statement if (SQL = NULL) {return no;} If (sqlite_ OK! = Sqlite3_open ([self getfilepath], & PDB) {return no;} If (sqlite_ OK = sqlite3_exec (PDB, SQL, null, null, & ERR )) {// sqlite3_close (PDB); Return yes;} else {// return no ;}}

3. Insert a record

-(Bool) insertrecordwithen :( nsstring *) en CN :( nsstring *) Cn comment :( nsstring *) Comment {int ret = 0; If (sqlite_ OK! = Sqlite3_open ([self getfilepath], & PDB) {// open the database return no;} Char * SQL = "insert into Dictionary (EN, CN, comment) values (?,?,?); "; // Insert statement, three parameters: sqlite3_stmt * stmt; // If (sqlite3_prepare_v2 (PDB, SQL,-1, & stmt, nil) = sqlite_ OK) {// prepare the statement sqlite3_bind_text (stmt, 1, [en utf8string],-1, null); // bind the sqlite3_bind_text (stmt, 2, [CN utf8string],-1, null); sqlite3_bind_text (stmt, 3, [comment utf8string],-1, null);} else {return no ;} if (sqlite_done = (ret = sqlite3_step (stmt) {// execute sqlite3_finalize (stmt); sqlite3_close (PDB); Return yes;} else {return no ;}}

4. Query

-(Nsmutablearray *) quary :( nsstring *) STR {nsmutablearray * arr = [[nsmutablearray alloc] init]; // store the query result if (sqlite_ OK! = Sqlite3_open ([self getfilepath], & PDB) {return no;} Char * SQL = "select * From dictionary where en like? Or CN like? Or comment like ?; "; // Query statement sqlite3_stmt * stmt; If (sqlite3_prepare_v2 (PDB, SQL,-1, & stmt, nil) = sqlite_ OK) {// prepare sqlite3_bind_text (stmt, 1, [[nsstring stringwithformat: @ "% @ %", STR] utf8string],-1, null); sqlite3_bind_text (stmt, 2, [nsstring stringwithformat: @ "% @ %", STR] utf8string],-1, null); sqlite3_bind_text (stmt, 3, [[nsstring stringwithformat: @ "% @ %", STR] utf8string],-1, null);} else {return nil;} while (sqlite_row = sqlite3_step (stmt )) {// execute char * _ en = (char *) sqlite3_column_text (stmt, 1); char * _ Cn = (char *) sqlite3_column_text (stmt, 2 ); char * _ comment = (char *) substring (stmt, 3); nsmutabledictionary * dict = [[nsmutabledictionary alloc] init]; // single record [dict setobject: [nsstring stringwithcstring: _ en encoding: nsutf8stringencoding] forkey: @ "Ken"]; [dict setobject: [nsstring stringwithcstring: _ Cn encoding: nsutf8stringencoding] forkey: @ "kcn"]; [dict setobject: [nsstring stringwithcstring: _ comment encoding: nsutf8stringencoding] forkey: @ "kcomment"]; [arr addobject: dict]; // insert to result array} sqlite3_finalize (stmt ); sqlite3_close (PDB); return [arr autorelease]; // returns an array of query results}

5. DB Initialization

I first defined a macro to identify whether the program is running for the first time. If it is running for the first time, I need to run the function for creating databases and tables. Otherwise, I will not run it. For details, refer to the Code:

# Define firstinit 1 // set it to 1 when the first operation is performed; otherwise, it is 0-(ID) Init {self = [Super init]; If (self! = Nil) {# If firstinit [self createdb]; [self createtable]; [self insertrecordwithen: @ "cctv1" CN: @ "CCTV 1 Set" comment: @ "sb TV station 1"]; // For the convenience of testing, I inserted some records [self insertrecordwithen: @ "cctv2" CN: @ "CCTV 2 sets" comment: @ "sb TV 2"]; [self insertrecordwithen: @ "cctv3" CN: @ "3 sets of CCTV" comment: @ "sb TV 3"]; [self insertrecordwithen: @ "cctv4" CN: @ "CCTV 4 sets" comment: @ "sb TV 4"]; [self insertrecordwithen: @ "cctv5" CN: @ "CCTV 5 sets" comment: @ "sb TV 5"]; [self insertrecordwithen: @ "cctv6" CN: @ "CCTV 6 sets" comment: @ "sb TV 6"]; [self insertrecordwithen: @ "cctv7" CN: @ "7 sets of CCTV" comment: @ "sb TV 7"]; [self insertrecordwithen: @ "cctv8" CN: @ "8 sets of CCTV" comment: @ "sb TV 8"]; [self insertrecordwithen: @ "cctv9" CN: @ "9 sets of CCTV" comment: @ "sb TV 9"]; [self insertrecordwithen: @ "cctv10" CN: @ "10 sets of CCTV" comment: @ "sb TV 10"]; [self insertrecordwithen: @ "cctv11" CN: @ "11 sets of CCTV" comment: @ "sb TV 11"]; [self insertrecordwithen: @ "cctv12" CN: @ "12 sets of CCTV" comment: @ "sb TV 12"]; # endif} return self ;}

For the moment, let's talk about the interface of the underlying database.

// Queryresultlist. h // iukey # import <uikit/uikit. h> # import "DB. H "@ interface queryresultlist: uitableviewcontroller <uisearchbardelegate> {nsmutablearray * Marr; // tableview data source dB * dB; // database object uisearchbar * searchbar; // search box} @ property (nonatomic, retain) nsmutablearray * Marr; @ property (nonatomic, retain) dB * dB; @ property (nonatomic, retain) uisearchbar * searchbar; @ end

-(ID) initwithstyle :( uitableviewstyle) style {self = [Super initwithstyle: style]; If (Self) {Marr = [[nsmutablearray alloc] init]; // table data source DB = [[dB alloc] init]; // database controller searchbar = [[uisearchbar alloc] initwithframe: cgrectmake (44.0, 0,200.0, 44)]; // search for the control searchbar. delegate = self; // set the delegate self for the search control. navigationitem. titleview = searchbar;} return self ;}

Next, we implement table data source delegation.

# Pragma mark-Table view Data Source-(nsinteger) numberofsectionsintableview :( uitableview *) tableview {return 1; // Number of partitions}-(nsinteger) tableview :( uitableview *) tableview numberofrowsinsection :( nsinteger) Section {return [Marr count]; // number of rows}-(uitableviewcell *) tableview :( uitableview *) tableview cellforrowatindexpath :( nsindexpath *) indexpath {static nsstring * cellidentifier = @ "cell"; uitableviewcell * cell = [tableview dequeuereusablecellwithidentifier: cellidentifier]; for (uiview * view in cell. contentview. subviews) {[view removefromsuperview];} If (cell = nil) {Cell = [[[uitableviewcell alloc] initwithstyle: Invalid reuseidentifier: cellidentifier] autorelworkflow];} uilabel * lblen = [[uilabel alloc] initwithframe: cgrectmake (5.0, 5.0, 300.0, 30.0)]; // display the English text label control uilabel * lblcn = [[uilabel alloc] initwithframe: cgrectmake (5.0, 35.0, 300.0, 30.0)]; // Chinese uilabel * lblcomment = [[uilabel alloc] initwithframe: cgrectmake (5.0, 65.0, 300.0, 30.0)]; // clear lblen In the background color. backgroundcolor = [uicolor clearcolor]; lblcn. backgroundcolor = [uicolor clearcolor]; lblcomment. backgroundcolor = [uicolor clearcolor]; // lblen. TEXT = [[Marr objectatindex: indexpath. row] objectforkey: @ "Ken"]; lblcn. TEXT = [[Marr objectatindex: indexpath. row] objectforkey: @ "kcn"]; lblcomment. TEXT = [[Marr objectatindex: indexpath. row] objectforkey: @ "kcomment"]; [cell. contentview addsubview: lblen]; [cell. contentview addsubview: lblcn]; [cell. contentview addsubview: lblcomment]; cell. selectionstyle = uitableviewcellselectionstylenone; // do not highlight [lblen release]; [lblcn release]; [lblcomment release]; return cell ;}

Then implement the search delegate method:

#pragma mark - UISearchBar delegate- (void) searchBarSearchButtonClicked:(UISearchBar*)activeSearchbar{    [mArr removeAllObjects];    NSString* query= searchBar.text;     NSMutableArray* arr = [db quary:query];    for ( NSMutableDictionary* dict in arr) {        [mArr addObject:dict];    }    [searchBar resignFirstResponder];    [self.tableView reloadData];}

This is just a simple demo. I will sort out the basic usage of SQLite and attach the complete project file: dictionarydemo.