定义个sqlite3
sqlite3 *_db;
一些方法的说明:
sqlite3 *db 数据库句柄
sqlite3_stmt *stmt
sqlite3_open() 打开数据库(没有数据库就创建)
sqlite3_exec() 执行非查询的sql语句
sqlite3_step() 调用sqlite3_prepare方法后sqlite3_step()函数将在记录集中移动
sqlite3_finalize() 释放结果集
sqlite3_close() 关闭数据库
1.创建/打开数据库
-(void)open_sqlite{
NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"test.sqlite"];
int result = sqlite3_open(filename.UTF8String, &_db);
if (result == SQLITE_OK) {
//create table t_product
/**
sqlite3_exec(sqlite3*,const char *sql,int (*callback)(void*,int,char**,char**),void *,char **errmsg)
**/
//sql 语句
const char *sql = "create table if not exists t_product(p_id integer primary key autoincrement,p_name text,p_number integer);";
char *errorMsg = NULL;
int result = sqlite3_exec(_db,sql,NULL,NULL,&errorMsg);
if(result == SQLITE_OK){
NSLog(@"create table OK !");
}else{
NSLog(@"create table error:%s",errorMsg);
}
}else{
NSLog(@"open database error!");
}
}
2. 查询数据
- (IBAction)query:(UIButton *)sender {
//执行一条p_id名称包含'3'语句
const char *sql = "select p_id, p_name ,p_number from t_product where p_id like ?";
//定义stmt结果集
sqlite3_stmt *stmt = NULL;
/**
sqlite3_prepare_v2(sqlite3 *db,const char *zSql,int nByte,sqlite3_stmt **ppStmt,const char **pzTail)
**/
int result = sqlite3_prepare_v2(_db,sql,-1,&stmt,NULL);
if (result == SQLITE_OK) {
NSLog(@"prepare OK");
NSString *condition = [NSString stringWithFormat:@"%%%@%%",@"1"];
// 设置占位符的内容
sqlite3_bind_text(stmt,1,condition.UTF8String,-1,NULL);
//执行SQL语句,从结果集中取出数据
// int stepResult = sqlite3_step(stmt);
while (sqlite3_step(stmt) == SQLITE_ROW) {
int pid = sqlite3_column_int(stmt,0);
const unsigned char *pname = sqlite3_column_text(stmt,1);
int pnumber = sqlite3_column_int(stmt,2);
NSLog(@"%d %s %d", pid, pname, pnumber);
}
}else{
NSLog(@"prepare error");
}
}
3.更新数据库
- (IBAction)update:(UIButton *)sender {
const char *sql = "insert or replace into t_product (p_id,p_name,p_number) values(10,'product_new',20)";
char *errorMsg = NULL;
int result = sqlite3_exec(_db, sql, NULL, NULL, &errorMsg);
if (result == SQLITE_OK) {
NSLog(@"update data success !");
}else{
NSLog(@"update data error :%s",errorMsg);
}
}
4.删除某一条数据
- (IBAction)delete:(UIButton *)sender {
const char *sql = "delete from t_product where p_id = 10 ";
char *errorMsg = NULL;
int result = sqlite3_exec(_db, sql, NULL, NULL, &errorMsg);
if (result == SQLITE_OK) {
NSLog(@"delete data success !");
}else{
NSLog(@"delete data error :%s",errorMsg);
}
}