A brief introduction of fuzzy query and common function _ios applied SQLite in IOS development

SQLite Fuzzy Query
One, example

Note: This article is a simple example of SQLite fuzzy query

1. Create a new model that inherits from NSObject

The code in the class:

Copy Code code as follows:

//
YYPerson.h
03-Fuzzy Query
//
Created by Apple on 14-7-27.
Copyright (c) 2014 wendingding. All rights reserved.
//

#import <Foundation/Foundation.h>

@interface Yyperson:nsobject
@property (nonatomic, assign) int ID;
@property (nonatomic, copy) NSString *name;
@property (nonatomic, assign) int age;

@end

2. Create a new tool class to manage the model

The code in the tool class is designed as follows:

YYPersonTool.h file

Copy Code code as follows:

//
YYPersonTool.h
03-Fuzzy Query
//
Created by Apple on 14-7-27.
Copyright (c) 2014 wendingding. All rights reserved.
//

#import <Foundation/Foundation.h>

@class Yyperson;
@interface Yypersontool:nsobject
/**
* Save a contact
*/
+ (void) Save: ( yyperson*) person;

/**
* Search All Contacts
*/
+ (Nsarray *) query;
+ (Nsarray *) Querywithcondition: (NSString *) condition;
@end

YYPERSONTOOL.M file

Copy Code code as follows:

//
yypersontool.m
03-Fuzzy Query
//
Created by Apple on 14-7-27.
Copyright (c) 2014 wendingding. All rights reserved.
//

#import "YYPersonTool.h"
#import "YYPerson.h"

#import <sqlite3.h>
@interface Yypersontool ()
//@property (nonatomic,assign) sqlite3 *db;
@end

Copy Code code as follows:

@implementation Yypersontool

static Sqlite3 *_db;
First you need to have a database
+ (void) initialize
{
Get the path to the database file
NSString *doc=[nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) lastObject];
NSString *filename=[doc stringbyappendingpathcomponent:@ "Person.sqlite"];
Converts an OC string to a C-language string
const char *cfilename=filename.utf8string;

1. Open the database file (if the database file does not exist, the function will automatically create the database file)
int result = Sqlite3_open (cFileName, &_db);
if (RESULT==SQLITE_OK) {//Open successfully
NSLog (@ "Successfully open database");

2. Create a table
const char *sql= "CREATE TABLE IF not EXISTS t_person (ID integer PRIMARY KEY autoincrement,name text not null,age integer Not NULL); ";

Char *errmsg=null;
result = Sqlite3_exec (_db, SQL, NULL, NULL, &AMP;ERRMSG);
if (RESULT==SQLITE_OK) {
NSLog (@ "Success of the Table");
}else
{
printf ("Create a table failure---%s", errmsg);
}
}else
{
NSLog (@ "Open database Failed");
}

}
Save a piece of data
+ (void) Save: (Yyperson *) person
{
1. Splicing SQL statements

NSString *sql=[nsstring stringwithformat:@ "INSERT into T_person (name,age) VALUES ('%@ ',%d);", Person.name,person.age ;

2. Execute SQL statement
Char *errmsg=null;
Sqlite3_exec (_db, SQL. Utf8string, NULL, NULL, &ERRMSG);
if (errmsg) {//If there is an error message
NSLog (@ "Insert data failure--%s", errmsg);
}else
{
NSLog (@ "Insert data succeeded");
}

}

+ (Nsarray *) query
{
return [self querywithcondition:@ "];
}

Fuzzy query
+ (Nsarray *) Querywithcondition: (NSString *) condition
{

Array that holds all the contacts that are queried
Nsmutablearray *persons=nil;
/*
[NSString stringwithformat:@ "SELECT ID, Name, age from T_person WHERE name like '%%%@%% ' order by age ASC;", condition];
NSString *nssql=[nsstring stringwithformat:@ "Select Id,name,age from T_person WHERE name=%@;", condition];
*/
NSString *nssql=[nsstring stringwithformat:@ "Select Id,name,age from T_person WHERE name like '%%%@%% ' order by age ASC;" , condition];
NSLog (@ "%@", nssql);
const char *sql=nssql.utf8string;

Sqlite3_stmt *stmt=null;

Preparation before making a query
if (SQLITE3_PREPARE_V2 (_db, SQL,-1, &stmt, NULL) ==SQLITE_OK) {//sql Statement no problem
NSLog (@ "No problem with query statement");

Persons=[nsmutablearray array];

Each time the Sqlite3_step function is invoked, stmt points to the next record
while (Sqlite3_step (stmt) ==sqlite_row) {//Find a record

Remove data
(1) Remove the value of the No. 0 column field (value of type int)
int Id=sqlite3_column_int (stmt, 0);
(2) Remove the value of the 1th column field (value of text type)
Const unsigned char *name=sqlite3_column_text (stmt, 1);
(3) Remove the value of the 2nd column field (value of type int)
int Age=sqlite3_column_int (stmt, 2);

Yyperson *p=[[yyperson Alloc]init];
P.id=id;
P.name=[nsstring stringwithutf8string: (const char *) name];
P.age=age;
NSLog (@ "%@", p.name);
[Persons addobject:p];
NSLog (@ "haha%@", persons);
}
}else
{
NSLog (@ "query statement has a problem");
}

NSLog (@ "haha%@", persons);
return persons;
}
@end

3. In storyboard, remove the original controller, place a navigation controller and Uitableviewcontroller controller, and associate

In code, let the host controller inherit directly from the Uitableviewcontroller

The code is designed as follows:

YYVIEWCONTROLLER.M file

Copy Code code as follows:

//
Yyviewcontroller.m
03-Fuzzy Query
//
Created by Apple on 14-7-27.
Copyright (c) 2014 wendingding. All rights reserved.
//

#import "YYViewController.h"
#import "YYPerson.h"
#import "YYPersonTool.h"

@interface Yyviewcontroller () <UISearchBarDelegate>

Add an array to save the person
@property (Nonatomic,strong) Nsarray *persons;
@end

Copy Code code as follows:

@implementation Yyviewcontroller

#pragma mark-lazy Load
-(Nsarray *) persons
{
if (_persons==nil) {
_persons=[yypersontool query];
}
return _persons;
}

1. Add a search box to the initialization method
-(void) viewdidload
{
[Super Viewdidload];

Set the search box
Uisearchbar *search=[[uisearchbar Alloc]init];
Search.frame=cgrectmake (0, 0, 300, 44);
search.delegate=self;
Self.navigationitem.titleview=search;
}

2. Set the TableView data
How many rows of data are set
-(Nsinteger) TableView: (UITableView *) TableView numberofrowsinsection: (nsinteger) Section
{
return 10;
return self.persons.count;
}
-(UITableViewCell *) TableView: (UITableView *) TableView Cellforrowatindexpath: (Nsindexpath *) IndexPath
{
1. Go to cache to take CLL, if not then create and mark yourself
Static NSString *id=@ "ID";
UITableViewCell *cell=[tableview Dequeuereusablecellwithidentifier:id];
if (Cell==nil) {
Cell=[[uitableviewcell Alloc]initwithstyle:uitableviewcellstylesubtitle Reuseidentifier:id];
}

2. Set data for each cell
First remove the data model
Yyperson *person=self.persons[indexpath.row];
Sets the name and age of this cell
Cell.textlabel.text=person.name;
Cell.detailtextlabel.text=[nsstring stringwithformat:@ "Age%d", person.age];
3. Return cell
return cell;
}

-(Ibaction) Add: (Uibarbuttonitem *) Sender {
Initialize some fake data
Nsarray *names = @[@ "Simon drew Blood", @ "Simon Cramp", @ "Simon Ventilation", @ "Simon blowing Snow", @ "the East Gate Draw blood", @ "East Gate Cramp", @ "Dongmen ventilation", @ "East Gate blowing Snow", @ "North Door Draw Blood", @ "North door Cramp", @ "South door convulsions", @ "South Gate" Blowing Snow "];
for (int i = 0; i<20; i++) {
Yyperson *p = [[Yyperson alloc] init];
P.name = [NSString stringwithformat:@ "%@-%d", Names[arc4random_uniform (Names.count)], Arc4random_uniform (100)];
P.age = Arc4random_uniform (20) + 20;
[Yypersontool Save:p];
}
}

#pragma proxy method for the mark-search box
-(void) Searchbar: (Uisearchbar *) Searchbar textdidchange: (NSString *) SearchText
{
Self.persons=[yypersontool Querywithcondition:searchtext];
Refresh Table
[Self.tableview Reloaddata];
[Searchbar Resignfirstresponder];
}

@end

Implementation effect:

Second, simple description

About: NSString *nssql=[nsstring stringwithformat:@ "Select Id,name,age from T_person WHERE name like '%%%@%% ' SC; ", condition];

Note: Name like ' Simon ' is the equivalent of name = ' Simon '.
Name like '% West ', for modulo two, simple description

About: NSString *nssql=[nsstring stringwithformat:@ "Select Id,name,age from T_person WHERE name like '%%%@%% ' SC; ", condition];

Note: Name like ' Simon ' is the equivalent of name = ' Simon '.
Name like '% West ', for fuzzy Search, the search string contains the ' West ', the left can be any string, the right can be any string, the string.
But in stringWithFormat:% is an escape character, two% only represents one%.
Print view: Paste Search, search string contains the ' West ', the left can be any string, the right can be any string, the string.
But in stringWithFormat:% is an escape character, two% only represents one%.

SQLite commonly used functions
One, simple explanation

1. Open the Database

Copy Code code as follows:

int Sqlite3_open (

const char *filename,//file path for database

Sqlite3 **ppdb//Database instance

);

2. Execute any SQL statements

Copy Code code as follows:

int Sqlite3_exec (

sqlite3*,//an Open database instance

const char *sql,//SQL statement to execute

Int (*callback) (void*,int,char**,char**),//callback after execution of SQL statement

void *, the 1th parameter of the//callback function

Char **errmsg//error message

);

3. Check the legality of the SQL statement (preparation before querying)

Copy Code code as follows:

int Sqlite3_prepare_v2 (

Sqlite3 *db,//Database instance

const char *zsql,//SQL statement to check

int Nbyte,//maximum byte length of SQL statement

Sqlite3_stmt **ppstmt,//sqlite3_stmt instance, used to obtain database data

const CHAR **pztail

);

4. Query one row of data

Copy Code code as follows:

int Sqlite3_step (sqlite3_stmt*); If a row of data is queried, the Sqlite_row is returned

5. Use stmt to get the value of a field (the Subscript for a field starts at 0)

Copy Code code as follows:

Double sqlite3_column_double (sqlite3_stmt*, int icol); Floating-point data

int Sqlite3_column_int (sqlite3_stmt*, int icol); Integral type data

Sqlite3_int64 Sqlite3_column_int64 (sqlite3_stmt*, int icol); Long-integer data

const void *sqlite3_column_blob (sqlite3_stmt*, int icol); Binary Text data

Const unsigned char *sqlite3_column_text (sqlite3_stmt*, int icol); String data

Second, SQLite coding

1. Create, open, close the database

Create or open a database

Copy Code code as follows:

Path is the location of the database file

Sqlite3 *db = NULL;

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

Code resolution:

Sqlite3_open () will open the database based on the file path, and if not, a new database will be created. If result equals constant SQLITE_OK, the database is opened successfully

Sqlite3 *db: An Open database instance

The path to the database file must be passed in a C string (not NSString)

Close database: sqlite3_close (db);

2. Execute SQL statements that do not return data

Execute a creation statement

Copy Code code as follows:

char *errormsg = NULL; Used to store error messages

Char *sql = "CREATE table if not exists T_person (ID integer primary key autoincrement, name text, age integer);";

int result = SQLITE3_EXEC (db, SQL, NULL, NULL, &ERRORMSG);

Code resolution:

Sqlite3_exec () can execute any SQL statements, such as create a table, update, insert, and delete operations. But generally do not use it to execute query statements, because it does not return the query to the data

Sqlite3_exec () A statement that can also be executed:

(1) Open the transaction: BEGIN TRANSACTION;

(2) ROLLBACK TRANSACTION: rollback;

(3) Submitting the transaction: commit;

3. Insert data with placeholder

Copy Code code as follows:

Char *sql = "INSERT into T_person (name, age) VALUES (?,?);";

Sqlite3_stmt *stmt;

if (SQLITE3_PREPARE_V2 (DB, SQL,-1, &stmt, NULL) = = SQLITE_OK) {

Sqlite3_bind_text (stmt, 1, "Hen",-1, NULL);

Sqlite3_bind_int (stmt, 2, 27);

}

if (Sqlite3_step (stmt)!= Sqlite_done) {

NSLog (@ "Insert data error");

}

Sqlite3_finalize (stmt);

Code resolution:

The SQLITE3_PREPARE_V2 () return value equals SQLITE_OK, stating that the SQL statement is ready to succeed without a syntax problem

Sqlite3_bind_text (): Most binding functions have only 3 parameters

(1) The 1th parameter is sqlite3_stmt * type

(2) The 2nd parameter refers to the position of the placeholder, the position of the first placeholder is 1, not 0

(3) The 3rd parameter refers to the value to be bound by the placeholder

(4) The 4th parameter refers to the length of the data passed in the 3rd parameter, and for the C string, you can pass-1 instead of the length of the string

(5) The 5th parameter is an optional function callback, typically used to complete the memory cleanup after the statement is executed

Sqlite_step (): Execute SQL statement, return Sqlite_done representative successful execution completed

Sqlite_finalize (): Destroying SQLITE3_STMT * objects

4. Query data

Copy Code code as follows:

Char *sql = "Select Id,name,age from T_person;";

Sqlite3_stmt *stmt;

if (SQLITE3_PREPARE_V2 (DB, SQL,-1, &stmt, NULL) = = SQLITE_OK) {

while (Sqlite3_step (stmt) = = Sqlite_row) {

int _id = sqlite3_column_int (stmt, 0);

Char *_name = (char *) Sqlite3_column_text (stmt, 1);

NSString *name = [NSString stringwithutf8string:_name];

int _age = Sqlite3_column_int (stmt, 2);

NSLog (@ "id=%i, name=%@, age=%i", _id, Name, _age);

}

}

Sqlite3_finalize (stmt);

Code resolution:

Sqlite3_step () returns the Sqlite_row representative traversing to a new record

Sqlite3_column_* () is used to get the corresponding value for each field, and the 2nd parameter is the index of the field, starting at 0