转自:http://www.2cto.com/kf/201411/354688.html
-
嵌入式数据库
SQLite嵌入式数据库优点
1.支持事件,不需要配置,不需要安装,不需要管理员
2.支持发部分SQL92
3.完整的数据库保存在磁盘上面一个文件,同一个数据库文件可以在不同机器上面使用,最大支持数据库到2T
4.整个系统少于3万行,少于250KB的内存占用
Linux系统级的SQLite技术实现框架libsqlite3.0.dylib
开始使用SQLite
1.引入头文件
2.打开数据库
3.执行SQL命令
4.关闭数据库
一下代码展示整个数据库的 打开,关闭,增加,删除,查找,修改,
创建一个单例
1234567891011121314151617181920212223242526272829303132333435363738394041424344#
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
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223#
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类1234567#
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 调用方法123456789101112131415161718192021//打印路径
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:@
"女"
];