要想运用数据库,首先得创建一个数据库:(这里用的是 Firefox 的 SQLite Manager 插件)
1.创建数据库:(点击红色框中的按钮),保存路径要选择自己能找到的地方
2.创建表结构:(选中“Tables”,右键 “create table”)
3.为表结构设置名称,并且添加字段(名称、类型、属性)
这样就创建好一个表空间了,接下来是对这个表空间的操作:
1.首先将创建的数据库源文件拖入工程的“Supporting Files”目录下
2.其次创建一个类(这里是:DB.h DB.m)来关联数据库并且可以打开数据库
DB.h
#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface DB : NSObject
+(sqlite3 *)openDB;
@end
DB.m(主要目的:获取数据库文件bundle路径)
#import "DB.h"
#import <sqlite3.h>
//定义数据库指针(静态,不释放)
static sqlite3 *dbPoint = nil;
@implementation DB
//获得数据库指针(打开数据库)
+(sqlite3 *)openDB
{
//如果已经获得数据库指针对象,则直接返回即可
if (dbPoint) {
return dbPoint;
}
//否则执行下边代码
NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
NSUserDomainMask, YES) lastObject];
path = [NSString stringWithFormat:@"%@/db",path];
//判断文件是否存在, 使用文件管理类NSFileManager中的方法
if (![[NSFileManager defaultManager] fileExistsAtPath:path]) {
//获得将要拷贝的文件的路径,bundle路径
NSString *sourcePath = [[NSBundle mainBundle]
pathForResource:@"DataBase" ofType:@"sqlite"];
NSLog(@"sourcePath == %@",sourcePath);
NSError *error = nil;
//如果不存在,就拷贝进来
[[NSFileManager defaultManager] copyItemAtPath:sourcePath toPath:path error:&error];
if (error) {//如果拷贝有错误,显示错误信息
NSLog(@"error == %@", error);
}
}
//创建数据库的指针对象
sqlite3_open([path UTF8String], &dbPoint);
return dbPoint;
}
@end
2.然后创建一个类(这里是:NovelDataBase.h NovelDataBase.m)来对数据库进行操作(增、删、改、查)
(注:根据自己的需求修改SQL语句以及其方法返回值)
//查询数据库
+(NSArray *)selectAllNovel
{
//创建数组用来保存查到的数据
NSMutableArray *arr = [NSMutableArray array] ;
//获得数据库指针
sqlite3 *db = [DB openDB];
//创建数据库的替身
sqlite3_stmt *stmt = nil;
//sql查询语句
NSString *sqlStr = [NSString stringWithFormat:@"select nv_id,nv_author,nv_bookname,nv_score from Novel"];
//通过sql语句进行查询,并将查询结果赋值给替身
int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {//检验sql语句是否正确
while (sqlite3_step(stmt) == SQLITE_ROW) {//如果正确,取出数据
int nid = sqlite3_column_int(stmt, 0);//第二个参数的值和前边sql语句中查询列的顺序所匹配
const unsigned char *nauthor = sqlite3_column_text(stmt, 1);
const unsigned char *nbookname = sqlite3_column_text(stmt, 2);
float nscore = sqlite3_column_double(stmt, 3);
//数据类型转换
NSString *author = [NSString stringWithUTF8String:(const char *)nauthor];
NSString *name = [NSString stringWithUTF8String:(const char *)nbookname];
//创建对象,存数据
NovelModel *aNovel = [NovelModel NovelModelWithId:nid author:author bookname:name score:nscore];
//将对象放入数组中
[arr addObject:aNovel];
}
}
//将替身放掉
sqlite3_finalize(stmt);
return arr;
}
//插入数据
+ (void)insertWithModel:(NovelModel *)model
{
sqlite3 *db = [DB openDB];
NSString *sql = [NSString stringWithFormat:
@"insert into Novel (nv_id,nv_author,nv_bookname,nv_score) values(%d,'%@','%@',%f)"
,model.nid,model.nAuthor,model.nName,model.nScore];
int result = sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"添加成功");
}
}
//更新
+ (void)updateWithId:(NSInteger)num bookname:(NSString *)name
{
sqlite3 *db = [DB openDB];
NSString *sql = [NSString stringWithFormat:
@"update Novel set nv_bookname = '%@' where nv_id = %d",name, num];
int result = sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"更新成功");
}
}
//删除
+ (void)deleteWithId:(NSInteger)num
{
sqlite3 *db = [DB openDB];
NSString *sql = [NSString stringWithFormat:
@"delete from Novel where nv_id = %d", num];
int result = sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"删除成功");
}
}