(1)注意点:
在SELECT查询时,不能分步写成如下形式,否则会出现死循环:
int stepResult=sqlite3_step(statement);
while (stepResult==SQLITE_ROW) {
}
只能合并成一句写成:
while (sqlite3_step(statement)==SQLITE_ROW) {
}
因为,sqlite3_step的执行方式是如下:
** ^If the SQL statement being executed returns any data, then [SQLITE_ROW]
** is returned each time a new row of data is ready for processing by the
** caller. The values may be accessed using the [column access functions].
** sqlite3_step() is called again to retrieve the next row of data.
#import "ViewController.h"
#import <sqlite3.h>
@interface ViewController ()
- (IBAction)insert:(id)sender;
- (IBAction)delete:(id)sender;
- (IBAction)update:(id)sender;
- (IBAction)select:(id)sender;
@end
@implementation ViewController
static sqlite3 *_db;
- (void)viewDidLoad {
[self setupDatabaseAndTable];
[super viewDidLoad];
// Do any additional setup after loading the view, typically from a nib.
}
- (void)didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
-(void)setupDatabaseAndTable{
NSString *filename=[[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"iOS.sql"];
NSLog(@"%@",filename);
int openDBResult=sqlite3_open(filename.UTF8String, &_db);
if (openDBResult==SQLITE_OK) {
//创建表
NSString *createTableSql=@"CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,age INTEGER);";
char *error=nil;
int createTableResult=sqlite3_exec(_db, createTableSql.UTF8String, NULL, 0, &error);
if (createTableResult==SQLITE_OK) {
NSLog(@"创建表成功");
}else{
NSLog(@"创建表失败");
}
NSLog(@"打开数据库成功");
}else{
NSLog(@"打开数据库失败");
}
}
- (IBAction)insert:(id)sender {
for (int i=0; i<50; i++) {
NSString *name=[NSString stringWithFormat:@"jack%d",arc4random()%100];
int age=arc4random()%100;
NSString *insertTableSql=[NSString stringWithFormat:@"INSERT INTO user(name,age) VALUES('%@',%d);",name,age];
char *error=nil;
int insertResult=sqlite3_exec(_db, insertTableSql.UTF8String, NULL, 0, &error);
if (insertResult==SQLITE_OK) {
NSLog(@"%d %@ %d",i,name,age);
}else{
NSLog(@"插入数据失败");
}
}
}
- (IBAction)delete:(id)sender {
NSString *deleteRowSql=@"DELETE FROM user WHERE id=1;";
char *error=nil;
int deleteResult=sqlite3_exec(_db, deleteRowSql.UTF8String, NULL, 0, &error);
if (deleteResult==SQLITE_OK) {
NSLog(@"删除数据成功");
}else{
NSLog(@"删除数据失败");
}
}
- (IBAction)update:(id)sender {
NSString *updateRowSql=@"UPDATE user SET age=130 WHERE id=3;";
char *error=nil;
int updateResult=sqlite3_exec(_db, updateRowSql.UTF8String, NULL, 0, &error);
if (updateResult==SQLITE_OK) {
NSLog(@"修改数据库成功");
}else{
NSLog(@"修改数据库失败");
}
}
- (IBAction)select:(id)sender {
NSString *selectRowSql=@"SELECT id,name,age FROM user WHERE id=5;";
sqlite3_stmt *statement;
int stmtResult=sqlite3_prepare_v2(_db, selectRowSql.UTF8String, -1, &statement, nil);
if (stmtResult==SQLITE_OK) {
NSLog(@"stmtResult成功");
//以下语句分步写会出现死循环
while (sqlite3_step(statement)==SQLITE_ROW) {
NSLog(@"stepResult成功");
int uid=sqlite3_column_int(statement, 0);
const unsigned char *uname=sqlite3_column_text(statement, 1);
int uage=sqlite3_column_int(statement, 2);
NSLog(@"%d %s %d",uid,uname,uage);
}
}else{
NSLog(@"stmtResult失败");
}
}
@end
(2)防止SQL注入的简单处理,利用sqlite3_bind_text(int)等进行变量绑定,这个语句会对输入的字符进行处理。
- (IBAction)select:(id)sender {
NSString *selectRowSql=@"SELECT id,name,age FROM user WHERE name=?;";
sqlite3_stmt *statement;
int stmtResult=sqlite3_prepare_v2(_db, selectRowSql.UTF8String, -1, &statement, nil);
if (stmtResult==SQLITE_OK) {
NSLog(@"stmtResult成功");
sqlite3_bind_text(statement, 1, "jack1", -1, NULL);
//以下语句分步写会出现死循环
while (sqlite3_step(statement)==SQLITE_ROW) {
NSLog(@"stepResult成功");
int uid=sqlite3_column_int(statement, 0);
const unsigned char *uname=sqlite3_column_text(statement, 1);
int uage=sqlite3_column_int(statement, 2);
NSLog(@"%d %s %d",uid,uname,uage);
}
}else{
NSLog(@"stmtResult失败");
}
}
(3)LIKE模糊查询关键词的使用,需要注意的是查询的词的两边需要各增加一个%,而如果用变量替换用转义的话,需要使用%%表示%,所以一般格式是“%%关键字%%”。
- (IBAction)select:(id)sender {
NSString *selectRowSql=@"SELECT id,name,age FROM user WHERE name LIKE ?;";
sqlite3_stmt *statement;
int stmtResult=sqlite3_prepare_v2(_db, selectRowSql.UTF8String, -1, &statement, nil);
if (stmtResult==SQLITE_OK) {
NSLog(@"stmtResult成功");
sqlite3_bind_text(statement, 1, "%%jack1%%", -1, NULL);
//以下语句分步写会出现死循环
while (sqlite3_step(statement)==SQLITE_ROW) {
NSLog(@"stepResult成功");
int uid=sqlite3_column_int(statement, 0);
const unsigned char *uname=sqlite3_column_text(statement, 1);
int uage=sqlite3_column_int(statement, 2);
NSLog(@"%d %s %d",uid,uname,uage);
}
}else{
NSLog(@"stmtResult失败");
}
}