一、sqlite3 使用操作
前提:在项目中导入 libsqlite3.0.tdb 数据库。
1.sqlite3 相关一些基本函数用法:
1.1、打开数据库
SQLITE_API int sqlite3_open(
constchar *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
该函数传的两个参数如下使用,例:
static sqlite3 * db ;
NSString * dataBaseFile = [dataBaseHandledataBaseFile];
int result = sqlite3_open([dataBaseFileUTF8String], &db);
if (result == SQLITE_OK) //说明已经打开数据库;
1.2、当数据库被打开后执行创建数据库表:
SQLITE_API int sqlite3_exec(
sqlite3*, /* An open database */
constchar *sql, /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**), /* Callback function */
void *, /* 1st argument to callback */
char **errmsg /* Error msg written here */
);
// 创建数据库表语句 , primary key autoincrement修饰主键在表中值是唯一存在(最后一个单词自动增加)
// 条件:
// 1.表中必须要有一个主键;
// 2.在插入数据时主键自动增加使用 autoincrement修饰;
// 3.主键的值在表中是唯一的,如果插入的数据主键值一样则不能添加在表内
NSString * sqliteStr = @"create table if not exists StudentList(stu_number integer primary key autoincrement,stu_name text,stu_gender text,stu_age integer)";
// 执行语句
sqlite3_exec(db, [sqliteStrUTF8String], NULL,NULL, NULL);
1.3、关闭数据库
int result = sqlite3_close(db);
NSLog(@"%@",result ==SQLITE_OK ? @"关闭成功":@"关闭失败");
1.4、插入数据相关几个函数
1.4.1、验证数据库语句,nByte 传 -1 时自动计算 zSql 数据库语句长度
SQLITE_API int sqlite3_prepare_v2(
sqlite3 *db, /* Database handle */
constchar *zSql, /* SQL statement, UTF-8 encoded */
int nByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
constchar **pzTail /* OUT: Pointer to unused portion of zSql */
);
1.4.2、数据库管理指针 sqlite3_stmt 类型
typedef structsqlite3_stmt sqlite3_stmt;
1.4.3、绑定数据
// 第二个参数表示 数据库语句中的第 k 个 "?" ,从 k>=1 。
SQLITE_API int sqlite3_bind_text(sqlite3_stmt*,int,constchar*,int,void(*)(void*));
SQLITE_API int sqlite3_bind_int(sqlite3_stmt*,int, int);
SQLITE_API int sqlite3_bind_double(sqlite3_stmt*,int, double);
...............
如 sql 语句:
@"select * from StudentList where stu_number = ? and stu_name = ?"
// 绑定参数据如下:
// 帮定参数,在之前先省去了一些步骤,稍后看例子。
sqlite3_bind_int(stmt, 1, (int)number);
sqlite3_bind_text(stmt, 2, [@"李四"UTF8String], -1,NULL);
1.4.4、执行数据库语句
SQLITE_API int sqlite3_step(sqlite3_stmt*);
1.4.5、释放数据库管理指针
SQLITE_API int sqlite3_finalize(sqlite3_stmt *pStmt);
插入数据示例:
// 插入数据
-(void)insertDataWithKeyValues:(StudentEntity *)entity
{
// 1.打开数据库
[self openDataBase];
// 2.插入语句
NSString * sqlStr = @"insert into StudentList(stu_name,stu_gender,stu_age,stu_number)values(?,?,?,?)";
// 3.创建数据管理指针
sqlite3_stmt * stmt = nil ;
// 4.验证数据库语句,
int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"可以插入数据");
// 5.帮定数据
// 参数:数据库管理指针 , 在 sqlStr 的第n个 ?, 数据库语句 , 语句长度(-1表示自动计算长度) ,
sqlite3_bind_text(stmt, 1, [entity.name UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [entity.gender UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 3, (int)entity.age);
sqlite3_bind_int(stmt, 4, (int)entity.number);
// 6.让 sql 语句执行
sqlite3_step(stmt);
}
// 7.释放
sqlite3_finalize(stmt);
// 8.关闭数据库
[self closeDataBase];
}
1.5、从数据库中取出每条数据的每一列对应的数据
// iCol 表示每个字段所在的列位置,从 0 开始。
SQLITE_API double sqlite3_column_double(sqlite3_stmt*,int iCol);
SQLITE_API int sqlite3_column_int(sqlite3_stmt*,int iCol);
SQLITE_API constunsigned char *sqlite3_column_text(sqlite3_stmt*,int iCol);
..................
1.6、数据库操作语句
ListName 为创建的表名,one 、two 和 three 都是表中每一列的字段。
// 创建数据库表
@"create table if not exists ListName(one text primary key autoincrement , two text , three integer,......)";
// 插入数据
@"insert into ListName(one , two , three,....) values(? , ? , ? , ....) ";
// 查询
@"select * from ListName" // 查询表中所有数据
@"select * from ListName where one = ? " //带有一个条件的查询
@"select * from ListName where one = ? and two = ?" // 满足一个条件的查询
@"select * from ListName where one = ? or three = ?" // 满足其中任意一个条件的查询
@"select * from ListName where three > ?" // 表中满足 three > 当前绑定的值 的 数据
@"select one,three from ListName where two = ? order by three disc(或是 asce)" // 查询表中满足(two = 当前绑定的值)条件的 one 和 three 值,并以 three 大小降序排列返回
@"select * from ListName where three > ? limit 5" // 满足条件的5条数据
@"select * from ListName where three > ? limit 3,5" // 跳过前三条数据 接着取后5条
@"select one,two from ListName where ...... " //选出满足条件数据条的 one 和 two 值
// 模糊查询所有字段 two 包含 likeName 内容的数据都找出来
[NSString stringWithFormat:@"select * from ListName where two like '%%%@%%'",likeName];
// 修改数据
@"update ListName set one = ? where three >= ?"
// 删除数据
@"delete from ListName" // 删除表中所有数据
@"delete from ListName where one = ?" // 删除表中满足条件的数据
// 删除整张表
@"drop table ListName"
2. 把 c 的字符串转换成 OC 中的 NSString 类型
[NSStringstringWithUTF8String:(char *)sqlite3_column_text(stmt,1)]
3.整个项目代码
3.1数据模型文件代码
//
// StudentEntity.h
// DataBaseDome
//
// Created by 瞿杰 on 2017/6/19.
// Copyright © 2017年 yiniu. All rights reserved.
//
#import <Foundation/Foundation.h>
@interface StudentEntity : NSObject
@property(nonatomic,assign)NSInteger number;
@property(nonatomic,copy)NSString *name;
@property(nonatomic,copy)NSString *gender;
@property(nonatomic,assign)NSInteger age;
@end
//
// StudentEntity.m
// DataBaseDome
//
// Created by 瞿杰 on 2017/6/19.
// Copyright © 2017年 yiniu. All rights reserved.
//
#import "StudentEntity.h"
@implementation StudentEntity
@end
3.2 数据库操作文件
//
// DataBaseHandle.h
// DataBaseDome
//
// Created by 瞿杰 on 2017/6/19.
// Copyright © 2017年 yiniu. All rights reserved.
//
#import <Foundation/Foundation.h>
@class StudentEntity ;
@interface DataBaseHandle : NSObject
+(instancetype)dataBaseHandleWithDataBaseName:(NSString *)dataBaseName;
// 打开数据库
-(void)openDataBase ;
// 关闭数据库
-(void)closeDataBase ;
// 插入数据
-(void)insertDataWithKeyValues:(StudentEntity *)entity ;
// 更新
-(void)updateStudentGender:(NSString *)gender byNumber:(NSInteger)number ;
// 查询
// 查询所有数据
-(NSArray<StudentEntity *> *)selectAllKeyValues ;
// 根据条件查询
-(StudentEntity *)selectOneStudentByNumber:(NSInteger)number ;
// 模糊查询,只要包含 likeName 内容
-(StudentEntity *)selectOneStudentLikeName:(NSString *)likeName ;
// 删除表中数据
-(void)deleteOneStudentByNumber:(NSInteger)number ;
// 删除表
-(void)dropTable;
@end
//
// DataBaseHandle.m
// DataBaseDome
//
// Created by 瞿杰 on 2017/6/19.
// Copyright © 2017年 yiniu. All rights reserved.
//
#import "DataBaseHandle.h"
#import "StudentEntity.h"
#import <sqlite3.h>
@interface DataBaseHandle ()
@property (nonatomic , copy)NSString * dataBaseName ;
@end
@implementation DataBaseHandle
static sqlite3 * db ;
+(instancetype)dataBaseHandleWithDataBaseName:(NSString *)dataBaseName
{
DataBaseHandle * dataBaseHandle = [[self alloc] init];
dataBaseHandle.dataBaseName = dataBaseName ;
NSString * dataBaseFile = [dataBaseHandle dataBaseFile];
// 打开数据库
int result = sqlite3_open([dataBaseFile UTF8String], &db);
if (result == SQLITE_OK) {
// 创建数据库表语句 , primary key autoincrement 修饰主键在表中值是唯一存在(最后一个单词自动增加)
// 条件:
// 1.表中必须要有一个主键;
// 2.在插入数据时主键自动增加使用 autoincrement 修饰;
// 3.主键的值在表中是唯一的,如果插入的数据主键值一样则不能添加在表内
NSString * sqliteStr = @"create table if not exists StudentList(stu_number integer primary key autoincrement,stu_name text,stu_gender text,stu_age integer)";
// 执行语句
sqlite3_exec(db, [sqliteStr UTF8String], NULL, NULL, NULL);
}
return dataBaseHandle ;
}
// 数据库文件存放所在的 Caches 文件夹路径
-(NSString *)dataBasePath
{
return [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject];
}
// 数据库文件路径
-(NSString *)dataBaseFile
{
return [[self dataBasePath] stringByAppendingPathComponent:[self.dataBaseName stringByAppendingString:@".db"]];
}
// 打开数据库
-(void)openDataBase
{
NSString * dataBaseFile = [self dataBaseFile];
NSLog(@"%@",dataBaseFile);
int result = sqlite3_open([dataBaseFile UTF8String], &db);
if (result == SQLITE_OK) {
NSLog(@"打开成功");
}
else{
NSLog(@"打开失败");
}
}
// 关闭数据库
-(void)closeDataBase
{
int result = sqlite3_close(db);
NSLog(@"%@",result == SQLITE_OK ? @"关闭成功":@"关闭失败");
}
// 插入数据
-(void)insertDataWithKeyValues:(StudentEntity *)entity
{
// 1.打开数据库
[self openDataBase];
// 2.插入语句
NSString * sqlStr = @"insert into StudentList(stu_name,stu_gender,stu_age,stu_number)values(?,?,?,?)";
// 3.创建数据管理指针
sqlite3_stmt * stmt = nil ;
// 4.验证数据库语句,
int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"可以插入数据");
// 5.帮定数据
// 参数:数据库管理指针 , 在 sqlStr 的第n个 ?, 数据库语句 , 语句长度(-1表示自动计算长度) ,
sqlite3_bind_text(stmt, 1, [entity.name UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [entity.gender UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 3, (int)entity.age);
sqlite3_bind_int(stmt, 4, (int)entity.number);
// 6.让 sql 语句执行
sqlite3_step(stmt);
}
// 7.释放
sqlite3_finalize(stmt);
// 8.关闭数据库
[self closeDataBase];
}
// 更新数据
-(void)updateStudentGender:(NSString *)gender byNumber:(NSInteger)number
{
[self openDataBase];
sqlite3_stmt * stmt = nil ;
NSString * sql = @"update StudentList set stu_gender = ? where stu_number = ?";
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) { // 是否可以执行
sqlite3_bind_text(stmt, 1, [gender UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 2, (int)number);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
[self closeDataBase];
}
// 查询所有数据
-(NSArray<StudentEntity *> *)selectAllKeyValues
{
// 1.打开数据库
[self openDataBase];
// 2.准备语句
NSString * sql = @"select * from StudentList ";
// 3.创建数据管理指针
sqlite3_stmt * stmt = nil ;
// 4.验证语句是否正确
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
NSMutableArray * mArr = [[NSMutableArray alloc] initWithCapacity:0];
if (result == SQLITE_OK) {
// 5.获取数据
while (sqlite3_step(stmt) == SQLITE_ROW) {
StudentEntity * entity = [[StudentEntity alloc] init];
[mArr addObject:entity];
entity.number = sqlite3_column_int(stmt, 0);
entity.name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
entity.gender = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 2)];
entity.age = sqlite3_column_int(stmt, 3);
}
}
// 6.释放 和 关闭数据库
sqlite3_finalize(stmt);
[self closeDataBase];
return mArr ;
}
// 查询某一个满足条件的数据
-(StudentEntity *)selectOneStudentByNumber:(NSInteger)number
{
[self openDataBase];
// 数据库语句:* 表示每条数据的所有字段;? 表示需要帮定的值
NSString * sql = @"select * from StudentList where stu_number = ?";
// 多条件查询如:@"select * from StudentList where stu_number = ? and stu_name = ?"
// @"select * from StudentList where stu_number = ? or stu_name"
// @"select * from StudentList where stu_number > ?"
// @"select * from StudentList where stu_number > ? limit 5" 满足条件的5条数据
// @"select * from StudentList where stu_number > ? limit 3,5" 跳过前三条数据 接着取后5条
// @"select * from StudentList where stu_number > ? order by stu_age disc " 在数据库中的数据满足条件 stu_number > ?(帮定的值) 选出的个数,然后以 stu_age 列把数据降序排列
// @"select stu_name,stu_age from StudentList where ...... " 选出满足条件数据条的 stu_name 和 stu_age 值
// 创建数据管理指针
sqlite3_stmt * stmt = nil ;
StudentEntity * entity = [[StudentEntity alloc] init];
// 验证语句是否正确
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
// 帮定参数
sqlite3_bind_int(stmt, 1, (int)number);
sqlite3_bind_text(stmt, 2, [@"李四" UTF8String], -1, NULL);
while (sqlite3_step(stmt) == SQLITE_ROW) {
entity.number = sqlite3_column_int(stmt, 0);
entity.name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
entity.gender = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 2)];
entity.age = sqlite3_column_int(stmt, 3);
}
}
sqlite3_finalize(stmt);
[self closeDataBase];
return entity ;
}
// 模糊查询
-(StudentEntity *)selectOneStudentLikeName:(NSString *)likeName
{
[self openDataBase];
sqlite3_stmt * stmt = nil ;
StudentEntity * entit = [[StudentEntity alloc] init] ;
NSString * sql = [NSString stringWithFormat:@"select * from StudentList where stu_name like '%%%@%%'",likeName];
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"可以模糊查询");
// sqlite3_bind_text(stmt, 1, [likeName UTF8String], -1, NULL);
while (sqlite3_step(stmt) == SQLITE_ROW) {
entit.number = sqlite3_column_int(stmt, 0);
entit.name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
entit.gender = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 2)];
entit.age = sqlite3_column_int(stmt, 3);
}
}
sqlite3_finalize(stmt);
[self closeDataBase];
return entit ;
}
// 删除表中的数据
-(void)deleteOneStudentByNumber:(NSInteger)number
{
[self openDataBase];
NSString * sql = @"delete from StudentList where stu_number = ?";
sqlite3_stmt * stmt = nil ;
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
sqlite3_bind_int(stmt, 1, (int)number);
// 执行语句
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
[self closeDataBase];
}
// 删除整个表
-(void)dropTable
{
[self openDataBase];
NSString * sql = @"drop table StudentList";
sqlite3_stmt * stmt = nil ;
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"成功删除当前表");
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
[self closeDataBase];
}
@end
3.3 使用数据库操作的测试代码
//
// ViewController.h
// DataBaseDome
//
// Created by 瞿杰 on 2017/6/19.
// Copyright © 2017年 yiniu. All rights reserved.
//
#import <UIKit/UIKit.h>
@interface ViewController : UIViewController
@end
//
// ViewController.m
// DataBaseDome
//
// Created by 瞿杰 on 2017/6/19.
// Copyright © 2017年 yiniu. All rights reserved.
//
#import "ViewController.h"
#import "DataBaseHandle.h"
#import "StudentEntity.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
DataBaseHandle * dataBaseHandle = [DataBaseHandle dataBaseHandleWithDataBaseName:@"StudentDB"];
// 查询所有缓存的数据
NSArray * allStudents = [dataBaseHandle selectAllKeyValues];
// 1.插入数据
StudentEntity * entity = [[StudentEntity alloc] init];
entity.number = 1000 ;
entity.name = @"张三";
entity.gender = @"男";
entity.age = 20 ;
StudentEntity * entity2 = [[StudentEntity alloc] init];
entity2.number = 1001 ;
entity2.name = @"李四";
entity2.gender = @"女";
entity2.age = 25 ;
[dataBaseHandle insertDataWithKeyValues:entity];
[dataBaseHandle insertDataWithKeyValues:entity2];
// 2.查询所有数据
allStudents = [dataBaseHandle selectAllKeyValues];
// 查询单个数据
StudentEntity * selectStudent = [dataBaseHandle selectOneStudentByNumber:1000];
StudentEntity * selectStudent2 = [dataBaseHandle selectOneStudentByNumber:1001];
// 3.更新数据
[dataBaseHandle updateStudentGender:@"女" byNumber:1000];
[dataBaseHandle updateStudentGender:@"男" byNumber:1001];
// 查询所有数据
allStudents = [dataBaseHandle selectAllKeyValues];
[dataBaseHandle deleteOneStudentByNumber:1001];
// 查询所有数据
allStudents = [dataBaseHandle selectAllKeyValues];
// 查询单个数据
selectStudent = [dataBaseHandle selectOneStudentByNumber:1000];
selectStudent2 = [dataBaseHandle selectOneStudentByNumber:1001];
// 删除整张表
[dataBaseHandle dropTable];
// 查询所有数据
allStudents = [dataBaseHandle selectAllKeyValues];
}
- (void)didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
@end