嵌入式数据库
SQLite嵌入式数据库的优势
1.支持事件,你并不需要配置,无需安装,不需要管理员
2.支持部分脂肪SQL92
3.完整的数据库被存储在磁盘上的文件的顶部,相同的数据库文件可以在不同机器上面使用,最大支持数据库2T
4.整个系统少于3万行,少于250KB的内存占用
Linux系统级的SQLite技术实现框架libsqlite3.0.dylib
開始使用SQLite
1.引入<sqlite3.h>头文件
2.打开数据库
3.运行SQL命令
4.关闭数据库
一下代码展示整个数据库的 打开,关闭,添加,删除,查找,改动,
创建一个单例
#import <Foundation/Foundation.h>
@class Student;
@interface DataBaseHandle : NSObject
//创建一个单例
+(DataBaseHandle *)shareDB; //获取Documents路径
- (NSString *)documentsPath; //打开数据库
- (void)openDB; //关闭数据库
- (void)closeDB; //创建表
- (void)createTable; //插入信息
- (void)insertStudent:(Student *)stu; //改动信息
- (void)updateMessage; //删除信息
- (void)deleteMessage; //查找所有
- (void)selectAllStudent; //条件查找
- (void)selectWithSex:(NSString *)sex; @end
#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "Student.h"
//全局单利对象
static DataBaseHandle *shareModle = nil;
@implementation DataBaseHandle
#pragma mark 实现单利
+ (DataBaseHandle *)shareDB
{
if (nil == shareModle)
{
shareModle = [[DataBaseHandle alloc] init];
}
return shareModle;
} #pragma mark 获取Documents路径
- (NSString *)documentsPath
{
NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
return documentPath;
} //在操作数据之前引入框架(sql...3.0)
//声明一个数据库的对象
static sqlite3 *db = nil;
#pragma mark 打开数据库
- (void)openDB
{
//推断数据库是不是为空
if (nil == db)
{
//与documents路径拼接数据库存放路径(获得存放路径)
NSString *dbPath = [[self documentsPath] stringByAppendingString:@"/Student.sqlite"];
//依据路径打开数据库,如该路径下没有数据库,就自己主动创建一个数据库
//打开数据库(c语言中的语法)
int result = sqlite3_open(dbPath.UTF8String, &db);
//推断是否打开数据库成功
if (result == SQLITE_OK)
{
NSLog(@"数据库打开成功");
}
else
{
NSLog(@"数据库打开失败");
}
}
} #pragma mark 关闭数据库 - (void)closeDB
{
int result = sqlite3_close(db);
if (result == SQLITE_OK)
{
//假设关闭成功,置为nill
db = nil;
NSLog(@"数据库关闭成功");
}
else
{
NSLog(@"数据库关闭失败");
}
} #pragma mark 创建表
- (void)createTable
{
//要创建表,就要使用SQL语句.
//SQL语句在这是以字符串的形式存在的
NSString *createString = @"CREATE TABLE IF NOT EXISTS student (sid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , sname TEXT, ssex TEXT, sage TEXT)";
//运行SQL语句
int result = sqlite3_exec(db, createString.UTF8String, NULL, NULL, NULL); if (result == SQLITE_OK)
{
NSLog(@"创建表成功");
}
else
{
NSLog(@"创建失败");
} } #pragma mark 插入信息
- (void)insertStudent:(Student *)stu
{
//准备插入语句
NSString *insertString = [NSString stringWithFormat:@"INSERT INTO student (sname,ssex,sage) VALUES ('%@','%@','%@')",stu.name,stu.sex,stu.age];
//运行语句
int result = sqlite3_exec(db, insertString.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK)
{
NSLog(@"插入成功");
}
else
{
NSLog(@"插入失败");
} } #pragma mark 改动数据
- (void)updateMessage
{
//准备改动语句
NSString *undataString = @"UPDATE student SET ssex = '女' ,sname = '小梦' WHERE sid = 10";
int result = sqlite3_exec(db, undataString.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK)
{
NSLog(@"改动成功");
}
else
{
NSLog(@"改动失败");
} } #pragma mark 删除数据
- (void)deleteMessage
{
//准备删除语句
NSString *deleteString = @"DELETE FROM student WHERE sid = 1";
int result = sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK)
{
NSLog(@"delete OK");
}
else
{
NSLog(@"delete _NO");
} } #pragma mark 查找所有
- (void)selectAllStudent
{
//准备查找语句
NSString *selectAll = @"SELECT *FROM student";
//创建伴随指针
sqlite3_stmt *stmt = nil; //预备运行
int result = sqlite3_prepare(db, selectAll.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK)
{
//在没有查找完毕之前一直循环运行
while (sqlite3_step(stmt) == SQLITE_ROW)
{
//取出sid(第0列)
NSInteger sid = sqlite3_column_int(stmt, 0);
//取出sname(第1列)
NSString *sname = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
//取出ssex(第2列)
NSString *ssex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
//取出sage(第3列)
NSString *sage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
NSLog(@"sid:%d sname:%@ ssex:%@ sage:%@",sid,sname,ssex,sage); }
sqlite3_finalize(stmt);
}
else
{
//假设查找失败,结束伴随指针
sqlite3_finalize(stmt);
NSLog(@"查找失败");
} }
#pragma mark 条件查找
- (void)selectWithSex:(NSString *)sex
{
//准备查找语句
NSString *selecstString = @"SELECT * FROM student WHERE ssex = ? ";
//创建伴随指针
sqlite3_stmt *stmt = nil;
//预运行
int result = sqlite3_prepare(db, selecstString.UTF8String, -1, &stmt, NULL); if (result == SQLITE_OK)
{
//绑定?的值
//"1"代表第一个问号"? "和哪个參数绑定
sqlite3_bind_text(stmt, 1, sex.UTF8String, -1, NULL);
while (sqlite3_step(stmt) == SQLITE_ROW)
{ NSInteger sid = sqlite3_column_int(stmt, 0);
NSString *sname = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSString *ssex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
NSString *sage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
NSLog(@"sid: %d sname: %@ ssex: %@ sage: %@",sid,sname,ssex,sage); } }
else
{
//关闭指针
sqlite3_finalize(stmt);
NSLog(@"查询失败");
}
} @end
创建一个Student类
#import <Foundation/Foundation.h> @interface Student : NSObject
@property(nonatomic,strong)NSString *name;
@property(nonatomic,strong)NSString *age;
@property(nonatomic,strong)NSString *sex;
@end
在 ViewController.m中 ViewDidLoad 调用方法
//打印路径
NSLog(@"%@",[[DataBaseHandle shareDB] documentsPath]);
//打开数据库
[[DataBaseHandle shareDB] openDB];
//创建表
[[DataBaseHandle shareDB] createTable]; //插入信息
// Student *stu = [[Student alloc] init];
// stu.name = @"小露";
// stu.age = @"29";
// stu.sex = @"女";
// [[DataBaseHandle shareDB] insertStudent:stu];
//改动
// [[DataBaseHandle shareDB] updateMessage];
//删除
// [[DataBaseHandle shareDB] deleteMessage];
//查找所有信息
// [[DataBaseHandle shareDB] selectAllStudent];
//依据条件查找信息
[[DataBaseHandle shareDB] selectWithSex:@"女"];
版权声明：本文博主原创文章，博客，未经同意不得转载。