#import <Foundation/Foundation.h>
@class student;
@interface DataBaseHandle : NSObject
//设置单例
+(DataBaseHandle *)shareDB;
//获取沙盒document路径
-(NSString *)documentPath;
//打开数据库
-(void)openDB;
//关闭数据库
-(void)closeDB;
//创建表
-(void)createTable;
//插入信息
-(void)insertStudent:(student *)stu;
//修改信息
-(void)updateMessage;
//删除信息
-(void)deleteMessage;
//查找全部
-(void)selectAllStudent;
//条件查找(按性别查找)
-(void)selectwithSex:(NSString *)sex;
*****************************************************
#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "student.h"
//全局单例对象
static DataBaseHandle *database=nil;
@implementation DataBaseHandle
#pragma mark 实现单例
+(DataBaseHandle *)shareDB
{
if (nil==database) {
database=[[DataBaseHandle alloc] init];
}
return database;
}
//获取document路径
-(NSString *)documentPath
{
return NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
}
//使用数据库步骤
//1.在操作数据库之前引入框架
//2.声明一个数据库对象
static sqlite3 *db=nil;
//打开数据库
-(void)openDB
{
if (nil==db) {
//拼接数据库存放路径
NSString *dbPath=[[self documentPath] stringByAppendingString:@"/student.sqlite"];
//根据路径打开数据库
//如果该路径下没有数据库,就自动创建一个
//打开数据库
int result= sqlite3_open(dbPath.UTF8String,&db);
//判断师傅成功
if (result==SQLITE_OK) {
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 *updateString=@"UPDATE student SET sid = '1' WHERE sid = 6";
int result=sqlite3_exec(db, updateString.UTF8String, NULL, NULL, NULL);
if (result==SQLITE_OK) {
NSLog(@"修改成功");
}
else
{
NSLog(@"修改失败");
}
}
#pragma mark 删除数据
-(void)deleteMessage
{
NSString *deleteString=@"delete from student where sid=5";
int result=sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL);
if (result==SQLITE_OK) {
NSLog(@"删除成功");
}
else
{
NSLog(@"删除失败");
}
}
#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 *selectString=@"select * from student where ssex=? ";
//伴随指针
sqlite3_stmt *stmt=nil;
//预执行
int result=sqlite3_prepare(db, selectString.UTF8String, -1, &stmt, nil);
if (result==SQLITE_OK) {
//绑定?的值
//'1'代表第一个?和参数绑定
sqlite3_bind_text(stmt, 1, sex.UTF8String, -1, NULL);
while (sqlite3_step(stmt)==SQLITE_ROW) {
//取出sid(第0列)
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);
}
//结束伴随指针
sqlite3_finalize(stmt);
}
else
{
//关闭查询指针
sqlite3_finalize(stmt);
NSLog(@"查询失败");
}
}